Mapping the Great Unknown

Every now and then, my work involves wading through an ocean of data. I’m sure I’m not the only one in these data driven days, so it’s fairly certain that I have plenty of company the world over.

And I’m equally certain that most members of my tribe experience a sinking feeling the first time they cast an eye over the large amount of data that needs analyzing.

And it goes on and on and on! Go down a couple of rows, and then a dozen and then a hundred, but there’s no end in sight. Go right for a couple of columns, and then a dozen…. but you get the idea.

Now it’s almost a given that I’m going to have to use pivots or some equally big, nasty weapon to subjugate this monster, and that’s fine. But to begin with, I’d prefer to know the size of what I’m dealing with – and what I’d like is a couple of functions that tell me just that: how many rows, pray tell, and how many columns?

Turns out, there are a couple of functions that do nothing but just that: return the number of rows and columns for a specified array.

And here’s how it works:

Head on over to the first cell in your gargantuan array, like so. 

Hit ALT-I-R a couple of times, followed by ALT-I-C a couple of times. These are keyboard shortcuts that insert rows and columns in your sheet, leaving it looking something like this. 

Why do that? Well,  so that you can type in the following command in cell A1:

Now fill in the argument by selecting the first cell in your array, and hitting SHIFT-END-DOWN. If you hit ENTER post this little operation, Excel quickly, quietly and efficiently (as it its wont) returns just what you wanted, the number of rows in that rather large array.

Much the same operation along transpose-ian lines will give you the number of columns in your database as well. And hey, who doesn’t like a little formatting? So a touch here and a touch there will leave you with the following:

The point being, whichever unfortuante soul (including you!) needs to work on this file knows exactly what she is up against. It also helps with preliminary data validation, as you might have guessed.

How do you guage your opponents in Microsoft Excel? Got any other methods that you think are spiffier?