Sometimes, when working with spreadsheets, you need to know how many rows or columns are in a table, without particularly needing to know anything about their contents.
For example, if you have a table that might get additional entries and you need to keep track of it, you can use this function. To do so you’ll need to insert rows (which is very similar to inserting columns) in the table, instead of just adding text at the bottom. If you don’t, you’ll need to update the function by changing the range.
It’s also helpful when you want to know how many rows or columns are available in a workbook. If you specify the entire workbook as the range by clicking on the gray square in the corner between the column and row headers, it’ll give you your answer. You can, of course, go to the bottom or the extreme right to check on it, but there’s a couple of problems with this.
First, when you’re looking at columns, it gives you a letter instead of a number. I don’t know about you, but I can’t look at something like “IV” and easily figure out which number that should be. I’d have to count up to I (the 9th letter), multiply by 26 (for the number of letters in the alphabet), then figure out V’s position (5th from the end, which makes it #21) and add that. Or I could use a function to tell me.
Second, whether you’re looking at rows or columns, some spreadsheet programs remember the furthest you’ve gone and think that all the previous rows & columns must have data in them. This isn’t a big deal, other than increasing file size, unless you happen to need to insert a row. Then it will give you an error, saying that it cannot insert because doing so would push data off the workbook. I have, in the past, had to delete a completely blank column in order to convince the program that there was room to insert a new column.
As I mentioned last week, there’s one function for each direction. As with any function, you start by selecting the cell you want the function to be in, and then enter it either by typing or by using the menus to retrieve it. You’ll find them in the “Lookup & Reference” category. These functions have the same syntax in all programs. The “range” inside the parenthesis can be either a direct reference (b7:j29) or a named reference.
- columns(range): This returns the number of columns in the range. Columns(A10:Z15) would return 26 — the same number as there are letters in the alphabet, since there’s one column for each letter starting with A and ending with Z.
- rows(range): This returns the number of rows in the range. Rows(A10:Z15) would return 6. If your instinct was to say that it would return 5, that’s because you were subtracting 10 from 15. However, since row 10 is included in the range, it’s actually one more than that.
There are also a couple of related functions that tell you which one, rather than how many. That is to say, row(A47) would return 47, and column(B17) would return 2. So if you aren’t getting the results you expect, make sure you’re using the right function. Just remember: rows() counts the how many rows (hence the plural), and row() tells you which one it is.