Which of the folllowing Excel formulas looks easier to understand?

=VLOOKUP(B9,$A$2:$C$6,2,TRUE)+VLOOKUP(B9,$A$2:$C$6,3,TRUE)*(B9-(VLOOKUP(B9,$A$2:$C$6,1,TRUE)))

or

=VLOOKUP(B9,$A$2:$C$6,2,TRUE)+

VLOOKUP(B9,$A$2:$C$6,3,TRUE)*

(B9-(VLOOKUP(B9,$A$2:$C$6,1,TRUE)))

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.

Tech Tip

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.

JohnM says

I’ve heard of Notepad++ before but never actually used it. I do a fair amount of coding with html and css, so it might be worth looking into.

That macro function on the menu bar looks interesting. I’ve used macros in Microsoft Office very effectively. They can save literally hours of work if deployed astutely.

It might be worth pointing out that the default html editor can be set in both Windows Control Panel and via Internet Explorer’s Tools menu. If you wanted to set Notepad++ to the default code editor subsequently to installing it, then it’s still possible using either of those methods.

Anything that can simplify formula-building in Excel is a good thing!

Francis Hayes says

Hi Terry, you can easily break a long formula into multiple lines in Excel the same way you break text into multiple lines a cell…hold down the ALT key and press Enter to insert a line break. And now with Excel 2007/2010 you can even drag the bottom of the formula bar to resize it to display to whole multi-line formula.