Which of the folllowing Excel formulas looks easier to understand?
They’re both the same formula — the second one just has some line breaks inserted to make understanding it easier. Excel will evaluate them the same way, too!
If you picked the second one, you’ve obviously had the problem before where you tried to figure out why a formula wasn’t calculating the way you thought it should…
When I get a complicated, usually multi-conditional (multiple IF tests), formula, I like to break the logical pieces of the formula into different lines. Even though I did that for explanatory purposes, Excel will actually use that formatting in its calculations, as you can see in the first example.
Breaking a formula into multiple lines makes it much easier to debug when it’s not calculating correctly because of a misplaced comma or parenthesis.
While I haven’t figured out how to make Excel break the formula into multiple lines, it’s easy to do in the free editor software Notepad++. Then, just past the formula into the cell-editing field in Excel.
Don’t paste it directly into the cell, as Excel will paste each line of the formula to a different cell. Select the cell, then paste the formula into the cell-editing field near the top of the Excel window.
Let’s take a look at the formula in Notepad++:
While Notepad++ isn’t really designed for editing Excel formulas, it is a programmer’s editor. Notice how Notepad++ highlights the matching parentheses in the formula. Rather than trying to do all of them at one time (like Excel does), Notepad++ highlights based on where the cursor is located.
Personally, I prefer this style to Excel’s use of multiple colors — some of which get confusing when there are a lot of parentheses involved.
Whenever I have a formula that isn’t working right, I copy it into Notepad++ to help me figure out what’s wrong.
After all, the price is right — Notepad++ is free. It’s actively updated, and checks for updates when you start Notepad++.
If it finds that there’s a new version, it will offer to download and update to the newer program. If your firewall (or your office firewall) blocks updating this way, you can always download the new version and install it in the usual manner.
One final tip on Notepad++: if you want to look at a web site’s source code and you use Internet Explorer, be sure to check the install option that let’s you use Notepad++ as the default source code viewer. The color coding of the source code is far superior to regular Notepad’s black text. As far as I know, that function only applies to Internet Explorer. Firefox and Opera have their own source code viewers that provide color displays.