Excel tips and tutorials
If you’re hosting a golf tournament, with cash prizes for the top ten players, what happens if two or more players are tied in rank?
Maybe if the top two players are tied, you’d have a playoff, but if two players are tied at 3rd, you wouldn’t try to break the tie that way.
If you’re filling in an order form in Excel, you can use the VLOOKUP function to find the selling price for each item in the sales order. For example, in the screen shot below, the order form is on the Orders worksheet, and a VLOOKUP formula in column D pulls the cost from a pricing table on the Prices worksheet.
You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what appears in the next drop down. You’ll see different drop down lists in the same cell!
Continue reading “Different Drop Down Lists in Same Excel Cell”
August seems to be a very popular birthday month among my Excel friends. I won’t mention any names here, because most of them are quite elderly, and the shock might upset them. 😉 Anyway, to all of them, and you, if you’re celebrating this month — happy birthday!
Continue reading “Highlight Current Month Birthdays in Excel”
It’s Shark Week on the Discovery Channel, so here are a couple of handy Excel functions you can use in case of a shark attack. Of course, if you stay home and watch television, you should be safe. The infamous land shark rarely attacks.
Continue reading “Excel LARGE and FLOOR Functions for Shark Week”
Good news, if you’re spelling challenged — or too lazy to type long words. You can combine cells in Excel, without CONCATENATE function. Keep reading, to learn the easy way to combine cells, and add some fancy formatting to the dates and numbers.
Continue reading “Combine Cells in Excel Without Concatenate”
Sometimes you need to count the number of items that are in a range, such as between 5 and 10. For example:
In Excel 2007 and higher, use the COUNTIF and COUNTIFS functions to calculate the answers to those questions.
In earlier versions, COUNTIFS is not available, and you can use COUNTIF.
Continue reading “Count Numbers Between X and Y – Excel COUNTIF COUNTIFS”
You’re comfortable with Excel, and can change formulas on the fly, when necessary. If you’re creating Excel workbooks for other people to use, their Excel skills probably aren’t as strong as yours.
Last month we looked at the COUNTIF formula, and how you can type an operator, then refer to a worksheet cell, to set a minimum value for counting.
In the comments, Kanti Chiba mentioned that the operator could be included in that referenced cell.
For example, you could type >=50 in a cell, and refer to that cell in the formula.
I usually keep the operator in the formula, so it’s separate from the number, and users won’t have to worry about typing it. Kanti’s comment made me think about other options, and how we could let users select both the operator and the target number.
So, I typed a list of operators on a different sheet in the workbook, and named that list as OpList.
The next step was to create a drop down list of operators, in the cell to the left of the Score input cell. I used data validation to create the drop down list, then selected one of the operators.
The final step was to change the COUNTIF formula, so it refers to the Operator cell. Now the COUNTIF formula results will change, if a different operator is selected.
To see the steps for creating a COUNTIF formula with a drop down list of operators, please watch this short Excel tutorial video.
For more examples of using the Excel COUNTIF function, see these blog posts:
Check Winning Numbers with COUNTIF
Use COUNTIFS for Multiple Criteria
Count Cells Greater Than Set Amount
____________
One of the tasks you have to do quite often in Excel is to count things. Here’s how to count cells greater than set amount with Excel COUNTIF function.
Continue reading “Count Cells Greater Than Set Amount With Excel COUNTIF Function”
You’ve probably used the Excel Paste Special command to multiply cells by a specific percentage, or to add the same amount to a group of cells. Today, you’ll see how to update multiple Excel formula cells in one step.
Continue reading “Update Multiple Excel Formula in One Step”
