Complicated spreadsheets are a double edged sword – for the Excel enthusiast, they are fun to prepare. But no matter how much of an enthusiast you are, decoding somebody else’s excesses is a right royal pain.

And this is especially true when it comes to decoding the thought process that somebody else put into making an Excel file with 50 spreadsheets. Multiple formatting styles, different cell widths and heights, dates, text and number formatting that varies by column are just some of the problems that you are likely to face. Of course, it doesn’t end there – certain rows or columns might be hidden, others might be resized and so on and so forth.

If you are the unfortunate soul upon whom lies the heavy responsibility of editing or adding to this spreadsheet… well, it would seem there are long nights in store for you, my friend.

Well, not quite. 

An especially impressive arrow in Excel’s quiver is the “Cell” formula. The “Cell” formula can take multiple arguments ans return a wide variety of information about the cell in question, unlocking all of it’s myriad mysteries.

For example, let’s say you need information about whether the text in a cell is formatted as a date, or if it simply is text. One way to find this out might be to right click on the cell, and check its formatting. But you can’t go about doing that for a thousand cells at once, right?

Enter the “Cell” formula.

The cell formula is a quick and easy way to find out what exactly lies in a cell in terms of formatting, contents and so on. Here’s a quick and easy example:

D1, in the middle cell, is the value that is returned if you use the formula shown in the cell to the right. It simply tells you that the contents of the first cell are formatted as a date, and in particular, in the D1 format. Particularly useful if you have to figure out varying date formats over a thousand rows or so.

Of course, there are many such tricks up the “Cell” formula’s sleeve. Here’s another one:

This tells you if the cell contains text or numbers. Quite often, what seems like numbers actually turns out to be a cell containing text – and good luck trying to edit that complex formula you entered in a cell many, many spreadsheets away. A “Cell” formula in a nested “if” formula can save you a lot of time.

Sometimes, the variant shown above can turn out to be particularly useful – simply displaying the contents of a cell elsewhere.

This one is, I think, is my personal favorite. It returns the width of a cell – very useful if you are as wonkish as I am about getting the formatting on every spreadsheet just right. I hate different widths across columns, across spreadhseets, and this formula has saved me many a frustrating hour.

Here’s a full list of all the arguments possible in this formula – you’d probably want to check these out, it’s a very helpful list indeed.

Any innovative uses of the “Cell” formula that you have had occasion to come up with in the past? Let us know in the comments!