It’s common in a lot of coding languages to be able to add comments to your code to help the readability of the code to anyone looking at it for the first time.
Excel doesn’t allow comments to be added to its formulas but there is a workaround to this using the N function, which can add comments to formulas that are only visible when you look at the formula in the formula bar.
As this is a workaround rather than an inbuilt feature there are some downsides, such as this only working with formulas that return a numeric value.
Download File
If you would like to follow along download the attachment below.
N Function
The N function in Excel is designed to convert values into numeric form, including text which it converts to a zero.
The syntax of the formula has only one parameter, value.
=N(value)
When adding text to the value of an N function it needs to be wrapped in double quotation marks, so if you wanted to write hello it would show in the function as "hello".
Impact of N on Data Types
N is not the most useful function and is more of a legacy as a lot of what it does is now done automatically in Excel, but if you did want to use N to change the output of a specific value the table below shows the impact the function has on different data types.
As N converts text to zero any text you enter in a formula or function will be treated as the number zero and won’t change the value of any calculations or show the text anywhere on the spreadsheet unless you click into the formula.
Using N for Comments in Formulas That Return a Numeric Value
Using SUM as an example, a description could be added saying 'this sums up the year' using N with the SUM function.
Using N in functions that return a text output
One of the limitations of using the N function to add comments is that if your formula or function returns a text value adding in the N function will return an error.
Using an IF statement as an example, if you were using the criteria of sales greater than 10 qualify for a bonus, an IF statement could be used which would return the text output 'bonus' or 'no bonus'.
Using N to add comments to the IF statement would return an error as the calculation would be trying to add a text value to a numeric value.
Conclusion
Using the N function to add comments in your Excel formulas is a workaround that can help make complex calculations clearer to follow by adding comment directly into the formula rather than being visible on the spreadsheet, but as it is a workaround and not a built in commenting feature it only works with formulas and functions that return a numeric value.
Comments