icrosoft Excel is a powerful tool for writing user interfaces for spreadsheet applications. Apart from the ability to display information in a tabular form, Excel has scores of functions capable of addressing most calculation needsand if those aren't sufficient, you can create your own functions, because Excel has Visual Basic for Applications (VBA) embedded in it.
One powerful function that shipped with Excel is the IF
function. The IF
function is very similar to the IF
statement in most programming languages, giving applications a way to choose one of several options. However, unlike the IF
statement, you can not add comments to an IF
function, because comments aren't permitted within function calls in worksheet cells. Therefore, it can be very difficult to understand a complex nested IF
Moreover, a programming language lets you indent lines of code, which helps make it much easier to read and comprehend what that code does. The worksheet IF
function does not allow indentation.
Finally, you can nest worksheet IF
functions, so it's easy to end up with a formula that's difficult to understand. Here's an example:
Lest you think I simply made this formula up as a "straw man" example, I didn't. I saw it in a real spreadsheet in a real financial institution (although I did change some literal strings to disguise it).
If you are in a pressure situation where you need to debug a spreadsheet formula, you will yearn for a tool to help simplify reading and understanding the code. Excel does give you some minimal help by color-coding the opening and closing braces, but the Formula Formatter tool discussed in this article goes several steps further. After you install the Formula Formatter, right clicking on any cell containing an IF
function allows you to view the formula in a nicely-formatted manner, and with any nested IF
functions neatly indented.
function examines a worksheet formula and converts it into pseudo code, displaying the function in an easy-to-understand form. For example, after parsing and formatting the IF
function, the formula shown earlier looks like this:
As you can see, while still complex, it's far easier to read than the original packed one-line version.