This post may contain affiliate links. Please read my disclosure for more info.

Top tips for working with complex formulas

Tips for complex formulas

Tips for complex formulas

Note: This post was originally only shared with my newsletter subscribers, but I decided it was too good to only share once.


Have you ever had a formula which looks like this?

Complex Formula Example

Of course you have, we all have.  Just by looking at the formula you know it is going to take a while to unravel the logic behind it.  It’s even worse when it’s your formula and you can’t work out why it’s not working correctly.  Today, I want to share with you a few tips to help make working with long and complex formulas just that little bit easier.


TIP #1 – Using ALT + Enter to insert a line break

When you have text in a cell you can press ALT+Enter to create a line break, which forces the text onto a new line.  ALT+Enter also works with Formulas.  That’s right, you can actually break the formula down into specific parts so they appear on different lines.

Complex Formula with ALT+Enter

There we go, that’s much easier to read.


TIP #2 – Add comments into the function

It is possible to add a comment against a Cell by right-clicking the cell and selecting Insert Comment.  But, that’s not what I’m talking about here.  I’m talking about adding a comment into the formula itself.  In the formula type +N(“Your comment here”) to add a comment.

Complext Formula with Comments

Yes, this does make the formula longer, and in some circumstances, may even make it harder to read, but it’s an option, right?  Obviously, don’t include the comment within a text string, else it will output it as part of the text string.



Tip #3 – Use F9 to calculate specific sections of the formula

Within the formula bar it is possible to calculate the result of any section individually.  Simply highlight the formula

Complex Formula F9 Highlight

Press F9

Complex Formula F9 Result

Now you can see how each section of the formula is calculating – this is a great way to troubleshoot formula errors.
Here is the most important bit: Press Ctrl+Z to undo the calculation and turn it back to a function, or press Esc to leave the Formula Bar without making any changes.


Tip #4 – Use the Tool Tip to move around a function

The Tool Tip is the small box which appears below a function when you select any of the arguments.  You can click any of the arguments in the Tool Tip box, it will highlight the selected section of the function.

Complex Formula Tool Tip

In the screenshot above I have clicked on the “[value_if_true]” option from the first IF function.  It now highlights everything which is within that part of the function.


Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: