A little-known trick in Excel is adding a comment to the Excel formula, a comment like a brief description of what the formula does in layman terms that other users may find helpful. To find out how to add a comment to an Excel formula read on…
Examples of when adding a comment may be useful:
- Complicated formula such as IF statements
- Lookup formula identifying where the formula is calculating data from
- Formula that uses named ranges that a user may be unfamiliar with
- Creating templates that will be used by Excel beginners
- Creating Excel training tools
Add a comment to an Excel formula
The trick is completed using the N function built-in to Excel. The N function is rarely used, perhaps you have never even heard of it, but its true purpose is to convert a value to a number. For example if cell A1 is set to 2 then =N(A1) would equal 2, if the text “Harry Potter” was in cell A1 then the calculation would return a zero to identify the cell contains text.
For this Excel trick we can use the N function within our formula and force it to act like a comment. Excel will ignore that part of the formula so it will continue to work as intended but you will have a neat little comment that describes what your formula is doing.
- The first step is to simply create your Excel formula as you normally would. In the below example we have:
- Total Sales of $100,000 in cell B1
- Number of Staff of 10 in cell B2
- Average Sales Per Staff Member in cell B4 (This is a calculation =B1/B2)
2. The next step is to add the N function to the end of your formula and enter your comment. For this example our current formula is:
and we add the N function to the end of this using the + sign followed by our function and comment:
= B1/B2 + N(“This is calculated by dividing total sales by the number of staff”)
This works in Excel as shown in the below image (check the formula bar):
Notice that the formula still works as intended, =B1/B2, but now you have a helpful comment so any other users can easily understand how the cell value was calculated.
One thing to watch out for is the 255-character limit for formulas in Excel. If you have a complicated multiple IF statement you may run out of room to write a meaningful comment. As an alternative, you can Add a cell comment by right-clicking on the cell with your formula in and selecting the option Insert Comment from the pop-up box.