Excel formulas can get complicated, especially when you’re working with advanced functions or creating solutions for others to use.
Adding comments directly within your formulas can make them more understandable and easier to maintain in the future.
It’s like leaving helpful notes for yourself or anyone else who might need to work with your spreadsheets later.
While Excel formulas do not natively support adding comments, in this article I am going to show you three simple tricks you can use to add comments to any Excel formula you are working with.
Example Formula Used for Demonstration
Imagine we have names in column A, their corresponding scores in column B, and we’re using an XLOOKUP formula to fetch a specific person’s score.
Below is the XLOOKUP formula I have used to fetch the score based on the name in cell D2.
=XLOOKUP(D2,A2:A11,B2:B11,"NA")
Now, within this formula, I want to add a comment.
Let me show you three ways to do this.
While this example is straightforward, the techniques I cover in this article work equally well for any complex formulas where comments would be more valuable.
Method 1: Using LET Function
The LET function is the most elegant solution for adding comments to your Excel formulas.
This function is available in Excel with Microsoft 365 subscriptions only. So if you are using an older version of excel you can use the next method.
The LET function allows you to assign numbers, text, or calculations to variables that you can then use throughout your formula. The clever trick here is that we can create variables that hold our comments but aren’t actually used in the calculation.
Below is the LET formula that returns the same result as our original XLOOKUP formula, and also has two sample comments.
=LET(
fx, XLOOKUP(D2,A2:A11,B2:B11,"NA"),
comment1, "This is an example comment explaining the XLOOKUP.",
comment2, "This formula finds the score for the name in cell D2.",
fx
)
When you press Enter, this formula will return the same result as your original XLOOKUP, but now it contains helpful explanatory comments.
Note: Each comment can be a maximum of 255 characters long. If it is more than 255 characters long, it would give an error.
Pros of this method:
- You can add multiple comments throughout your formula (by assigning them to different variables)
- Very readable format with clear separation between formula and comments
- Comments can be placed anywhere within the LET function
Cons of this method:
- Only available in Excel with Microsoft 365
- Each comment is limited to 255 characters (though you can use multiple comment variables to overcome this)
Method 2: Using IF Function
If you don’t have access to the LET function, you can use the IF function instead, which works in all versions of Excel.
The IF function takes three arguments:
- A logical test that returns TRUE or FALSE
- The value to return if the test is TRUE
- The value to return if the test is FALSE
The trick is to set the logical test to always return TRUE, put your actual formula in the second argument, and use the third argument (which will never be executed) for your comment.
Below is the formula that will give us the same result as the XLOOKUP formula, and also has a comment as the last arguemnt
=IF(TRUE,XLOOKUP(D2,A2:A11,B2:B11,"NA"),"This is an example comment explaining what this formula does.")
Since the first argument is always TRUE, the formula will always return the result of the XLOOKUP, but the comment is still there in the formula for reference.
Pros of this method:
- Works in all versions of Excel
- Simple to use
Cons of this method:
- Limited to one comment per IF statement (though you could nest IF statements)
- Less readable than the LET function approach
- Comment is at the end of the formula, which might be less convenient for long formulas
- Comment can be upto 255 character long only
Method 3: Using the N Function
The third method uses the N function, which is the simplest approach but has an important limitation: it only works when your formula returns a numeric result.
The N function converts non-numeric values to numbers. Text values are converted to 0.
By adding +N(“your comment”) to the end of a formula that returns a number, you effectively add 0 to your result (not changing it) while incorporating a comment.
Below is the formula that will give us the same result as the XLOOKUP formula, and also has a comment within the N function in double quotes.
=XLOOKUP(D2,A2:A11,B2:B11,"NA")+N("This XLOOKUP finds the score for the name in cell D2")
The N function converts the text comment to 0, so adding it to your numeric result doesn’t change the outcome.
Pros of this method:
- The simplest and easiest method to implement
- Works in all Excel versions
- Very concise syntax
Cons of this method:
- Only works with formulas that return numeric values
- Limited to one comment per formula (though you could chain multiple N functions)
- Each comment is limited to 255 characters
So to sum it all up
Of the three methods we’ve covered:
The LET function is the best overall approach if you have Excel with Microsoft 365, offering multiple comments and better readability.
The IF function is a solid alternative that works in any version of Excel.
The N function is the simplest method but only works with formulas that return numeric values.
Choose the method that works best for your specific situation and Excel version. Your future self (and colleagues) will thank you for the added clarity in your formulas!
Do you know of any other methods for adding comments to Excel formulas? Have you found these techniques helpful in your work? Let me know in the comments!
Other Excel articles you may also like:
