Here are three ways to add an explanation or note within an Excel formula itself, instead of separate notes.
The ‘Achilles Heel’ of Excel or any spreadsheet program has always been explaining or commenting of cell formulas. Adding comments is an important part of any code including Excel’s VBA but it’s MIA from formulas themselves.
The result is long, complex formulas that even the original author can’t understand let alone anyone else!
Excel has various features that can be used to explain a formula; cell notes or the modern threaded comments, naming cells/ranges and a text box or sheet with remarks.
What’s missing is an ‘in-line’ way to add comments directly into the formula line. There are Excel functions that can adapted be to add notes to formulas which return either a number or text.
The trick is to add a function which has text visible in the formula bar but always returns either 0 or an empty string so the function has no effect on the visible cell result.
Comment on a number value
If the function returns a number value (including serial dates) then there’s a simple way to add a comment using the N() function.
N() is a little-used compatibility function which returns 0 (zero) if it contains a string of almost any kind. It’s been in Excel since at least 2007 so compatibility should not be a problem.
All these formulas return 0:
=N(“returns the full price of the product with all taxes”)
=N(“can anyone explain what this does????”)
Or even … =N(“Three Blind Mice”) in other words, any text you like
In practice, add the +N() to the end of the formula and it has no effect on the result for a number or date.
=N("calc full price to customer inc all taxes")+B3+Tax_Fed+Tax_State+Tax_Local
You can add them anywhere in the formula, though that’s usually overkill.
=B3+N("pre-tax price - should be named")+N("add Federal and State taxes")+Tax_Fed+Tax_State+Tax_Local+N("Local taxes are complex - need to check")
Comment in a text value
It’s a little more complicated for a text formula.
Note: maybe there’s a more elegant way to do this? If you can think of anything, please let us know.
=If(True,"","explanation text goes here")
This formula will always return an empty string so you can add it to the end of any text returning formula.
More elegant text comment
That IF statement works but is a little messy. Lambda() in Excel 365 lets you make a simple custom function that hides the IF statement and gives you a text version of using N() for numbers.
We’ve called our function CText() but you can use almost any name (but T() is already used by Excel).
The Lambda formula is:
=LAMBDA(Comment,IF(TRUE, "", Comment))
You could do the same for number formula comments but it seems unnecessary to create, say CNum() when the inbuilt N() does a perfectly good job already. FWIW: =LAMBDA(Comment, N(Comment))
If you’re new to LAMBDA() this is a simple starter. Here’s how the named function looks in Name Manager.
LAMBDA() functions are a lot easier to manage with Excel Labs Advanced Formula Environment.
