There are several ways to count cells in Excel. Here are a few.
1. Subtract rows
This is how our grandparents did it. If your data is in one column, you can compute in your head
. Watch out for those off by one errors.
2. Use QuickSum
On the right side of status bar (runs along the bottom of the Excel application), you might see
. If you right click, you can change to Count. This has the advantage of working with rows, columns, or any size block. It also ignores blank cells.
3. Use the Name Box
The Name Box is the area to the left of the formula bar. Normally it shows the address of the selection. This is my favorite because it can be all keyboard and it works with rows and columns. If I have a big range of random data, I can select it and before I release the mouse button (or the control+shift key combination) the name box will show me the rows and columns.
If there are blanks in the data, this obviously doesn’t work. This is also handy when constructing a VLOOKUP formula. The third argument of VLOOKUP is the column you want to retrieve. In the above example, let’s assume I want to retrieve the telephone number. I type:
then start selecting my lookup range. While your in formula entry mode, the name box won’t help, but the same information is in a tooltip following your cursor.
Note that I pause when I get to the Telephone column and the tooltip says “1R x 9C”. I can continue to select the range knowing that 9 will be my third argument.
is the resulting formula that returns the Telephone column.