Software

Document Excel formulas with embedded comments

Need to share information about a formula with other users or developers? Drop them a note, right into the formula.

Cell comments display related information about the contents of a cell. They can serve as a reminder to yourself and they're a great way to share information with users and other developers. You're probably already familiar with this feature and if you're like me, you've sprinkled your sheets with useful comments. To do so, simply right-click a cell and start typing. Once you've inserted a comment, Excel displays a small red triangle in the cell's upper right corner. To view the comment, simply hover over the cell.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Cell comments aren't the only way to share relevant information about the contents of a cell. Did you know that you can insert a comment into a formula? Most likely, you'd use this feature to document a formula when the formula itself isn't clear. For instance, perhaps a formula is long or it references oddly-named ranges. Or, you might want to include a note about what the formula does or the type of data it should return.

To add a comment to a formula, use the following form:

Formula + N("your comment")

For instance, the following formula contains a note that explains the formula's purpose:

=($C2-$B2 + ($B2>$C2))-$D2+N("Returns the elapsed time between two time values")

In this particular formula, there's no way to tell what's going on, so the note adds valuable information to other users. Of course, users can easily see the formula's purpose by simply viewing the spreadsheet! Of more value, might be a note that shares the required data types, or even a development note that identifies the person who entered the formula, or perhaps the person who last modified the formula and why—if that information is relevant to other users in some way.

This feature begs for abuse though, so keep notes simple and relevant. The more notes you add, the less likely users are to pay attention to them.

This feature works in all versions of Excel.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
stapleb
stapleb

I have tested it and I'm impressed. My main thought is that Comments can be printed if I need a copy, and this print includes a cell address plus the name of the person who inserted the Comment. The N formula could only be printed if formulae are being displayed, and would not be as easy to read.

stapleb
stapleb

Hello dhays. Fair comment, particularly as you have to turn on the function keys. I only provided the keyboard method because I know it. I believe we all need to know what options are available to us. Yes, sometimes the mouse is faster, and sometimes the keyboard. I have very strong belief that it helps users to know their options and choose the method they prefer.

nameb
nameb

so simple and yet so very useful.thanks

DBlayney
DBlayney

I like this very much, I have often wanted to insert a comment to remind me of how clever I was to produce such a complicated or elegant formula. It needs to be understood the the N function is not a commenting function at all, it converts things into numbers. It works in the example because anything that really can't be converted into a number delivers a value of zero; a text string always yields a zero. So, if the result of the formula is numeric, adding a zero will not affect the result. However, if the formula delivers a text string adding a comment this way will generate a error: try ="A" + N("A") as an example.

stapleb
stapleb

Thanks again Susan for bringing such an interesting feature to our attention. I do use Comments, but did not know about Notes in a formla so I will have to head off to test it out to see if it is something I can use to share with others. Oh, and just because I know, Shift + F2 can be used to insert a Comment, or if you are in a cell that contains a Comment you will be placed into the Comment to edit it. If you want to delete a Comment, Shift +F2 to "edit" it, Esc once to get to the Comment "container" and then press Delete.

dogknees
dogknees

Seems to be a useful idea, but one that should be used with care. I see two "issues". First, the recipient needs to be aware of this usage or the N() calls will just confuse rather than elucidate. In my experience, few Excel users (or even developers) are familiar with N(). Second, if you named the cells in question something meaningful, the formulas intent would be more obvious. I usually find this is that best way to "document" formulae. If you use named formulae as well, you can condense the cell formula to something that is meaningful. Regards

Editor's Picks