Three-in-one today. That’s right, today you will discover one shortcut to add a comment to an Excel formula, discover how to use the N() function to add a comment and learn about N(). Using comments provides potential users -and yourself- with additional information about the data which is shown. It’s good practice to document your work and it makes a worksheet easier to understand.
The usual way
Head over to the Review tab and click New Comment or press SHIFT+F2. A yellow sticky pops up and you can type your comment there. A red triangle in the corner of the cell indicates that a comment is attached to that cell. Fine.
The N() way
You don’t necessarily want users to see that you have comments in your spreadsheet, yet you might want to be able to retrace your steps yourself. In that case, the N() function could be a good alternative. Also, if you apply protection to the sheet, there is no way users will see that one.
Type your formula as usual and then type the + sign, insert the N() function and in between brackets, using double quote signs (“) type the comment. Let’s recap: type your formula then type +N(“This is a comment between double quotes”)
Done.
What’s the N() function anyway?
Found in the Information category of functions, N converts ‘stuff’ to numbers or to zeros (it’s all explained in the next picture). Quite handy when the result of your AND, your IF or your SUMPRODUCT array gives you something you can’t use inside another formula.
But I digress. Today we are using it with text so that
N("This is a comment between double quotes")
evaluates to zero.
When you add zero to a number, it doesn’t change the result. Nifty, eh?
Try it and let me know what you think. Go on. You can start and do some real work after that.
Thank you for this article – the N() funtion is very usefull and I use it often in my work.
Anyway, I have two unsolved issues with it, see if you can help:
– Is it possible to automatically highlight (e.g. with conditional formatting) those cells, which contain this function?
– Since I have hundrets of cells with the N() fuction, is there a way extract as text in other cells the comments, embeded in the function?
Thank you
Dobrin, thanks for your question. Loved it and I published the answer as a new post here Extract commments embedded in Excel N() function
Hope this helps.
It is a useful trick François, but it only works if your formula returns a number. For text results you can use one of these solutions (I use the caret symbol to make stripping out the comment easier):
=IFERROR(IF(ISODD(A5),”Ones”,”Twos”)+N(“Formula to test for odd or even^”),IF(ISODD(A5),”Ones”,”Twos”))
=LEFT(IF(ISODD(A5),”Ones”,”Twos”)&N(“Formula to test for odd or even^”),LEN(IF(ISODD(A5),”Ones”,”Twos”)))