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.

1. 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:

= B1/B2

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.

Keep Excelling,