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?
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.
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.
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
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.
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.