Software

An unconventional way to comment your Excel formulas

Excel provides a number of ways to add special comments or notes to your spreadsheets. Here's a trick that lets you add comments at the formula level.

When you distribute “soft” or electronic copies of your Excel spreadsheets, you can’t assume everyone who reviews your data will understand how you arrived at your results. Excel offers a couple of obvious and one not-so-obvious way to add comments to your spreadsheets. Here’s the scoop.
Get valuable tips for using Excel, Word, and Access, all delivered straight to your inbox every Wednesday and Friday. Best of all, they’re absolutely free! Sign up for Microsoft Office Suite—one TechMail that will bring you power tips for working with documents, databases, worksheet functions, VBA, and much more.
The traditional way: Insert a comment
The most obvious way to add a comment to a spreadsheet is to draw a text box and type your comments in it. Unfortunately, these kinds of comments are obtrusive and can confuse your readers.

A more elegant solution is to select the cell with which you want to associate a comment, and go to Insert | Comment. Type your text and then click anywhere else in the sheet to close the comment box. When you do, Excel “flags” the note by displaying a small red triangle in the top-right corner of the cell. Figure A shows a sample spreadsheet with a couple of comments inserted.

There are two ways to display the comments. To view them one at a time, just hover over the cells that contain the comments. You can also select View | Comments to display them all at once, as shown in Figure B.

Figure A
You can spot the cells that contain inserted comments by looking for the red corners.


Figure B
When you go to View | Comments, Excel displays the full text of all the comments.


The unconventional way: Use the N function
Excel’s “number” function, N, provides an unconventional way to annotate the formulas in your worksheet. The N function takes the form =N(item), where item can be a string, a value, or any valid expression. You typically use the N function to convert a string to a number. (There are other uses for this function, but we won’t go into them here.)

The key to this trick is that N returns a value of zero when item is a string. So, if you want to explain why you hard-coded a certain value in a function call or if you simply want to write yourself a note to remember why you used a certain value or function, use N in the following form:
+N(“Write your comment here”)

Because you provided the N function with a text string, it returns zero. Therefore, you can add that function call to the rest of your formula (and embed your note in the cell) without affecting the value being calculated. Figure C shows a sample annotated formula.

Figure C
We used Excel’s N function to sneak the comment (about the .85 multiplier) into the formula itself.

To comment on this tip, please post a comment below or drop us a note.

Editor's Picks