=(C5-B5)/$C$100 Duh???... Revealing Spreadsheet Formulas
By Fred Kagel, Director Freehold Computer Training

How many times have you sat down in front of someone else’s spreadsheet and had to figure out what formulas were used to create the results? It can two seconds or it can take two weeks. Spreadsheets, by their very nature, are poorly documented. Often the creator doesn’t remember the cell references. Of course, if all you do is to use a spreadsheet for is to put data into nice columns or to add up a bunch of numbers using the AutoSum, it’s no big deal!

Older generations of spreadsheets permitted cells to formatted as text. When a cell which contains a formula is formatted as text, the formula appears instead of the result. That helps! Also, if you have a real old spreadsheet, by editing a cell and inserting a space at the beginning of cell’s entry will display the cell as text.

Microsoft Excel, since version 4, has had a little know keyboard switch to toggle a spreadsheet’s display from results to formulas. The magic keys are: CTRL+~. The tilde is usually in the upper left hand corner of the standard 101 keyboard, but your own your own with laptops. So with one keystroke, you can decipher what is data vs. what is a formula.

This is only one part of the task in deciphering someone else’s spreadsheet. To make spreadsheets more self-documenting, creators and developers should use cell and range names. It makes more sense to read "=(JanActual-JanAllocation)/AnnualAllocation" than the formula in the title of this article.

In Excel, the drop down box immediately above column A allows you to type in a name for a cell. Select the cell, and type in a name instead of the cell reference. Now when you create your formulas, you can use the English like names instead of the cryptic cell references.

Now, using cell names and Ctrl+~ comes the closest thing to self-documentation within a spreadsheet. Give it a try!

[Copyrighted by Fred Kagel, 1996. All rights reserved.]