How to Add Comments Inside Excel Formulas

Hi ,

Excel formulas can be difficult to understand, especially when you return to a workbook months later or hand it to someone else.

A formula may work perfectly, but that does not necessarily mean its purpose is obvious. This becomes a problem in shared workbooks, financial models, payroll files, reporting templates and any spreadsheet that needs to be reviewed or audited.

One way to make formulas easier to follow is to add a comment directly inside the formula itself.

This article shows you how to do that using three useful Excel techniques:

You will also learn why the N() trick sometimes causes a #VALUE! error, and how to choose the right method for your formula.

What Does the Excel N Function Do?

The N function converts a value into a number.

Its syntax is simple:

The result depends on the type of value you give it.

At first glance, this function can seem a little underwhelming. There are many ways to convert TRUE and FALSE into 1 and 0, and most users do not need to turn a date into its serial number.

The useful behaviour is this:

Because text always returns zero, you can place explanatory text inside a formula without changing the result.

That gives you a practical way to create formula comments that travel with the formula wherever it goes.

Why Put Comments Inside Excel Formulas?

Excel already has comments and notes, so why not use those?

Cell comments and notes can be useful, but they also have limitations:

A comment inside the formula lives with the logic itself.

When someone selects the cell and views the formula bar, they can see not only how the calculation works, but also why it was written that way.

This is particularly useful for formulas with business rules, exceptions, thresholds or assumptions that are not immediately obvious.

How to Add a Comment to a Numeric Excel Formula with N

Suppose you are calculating a monthly sales bonus.

Sales representatives earn a 5% bonus only when their monthly sales meet or exceed a target.

A typical formula might look like this:

In this example:

The formula works, but someone reviewing it later may not immediately understand the business rule.

You can add an explanation using N():

The text inside N() returns zero.

Because adding zero does not change the calculation, the result stays exactly the same.

However, the explanation is now visible in the formula bar whenever the cell is selected.

Why This Technique Works

The formula works because Excel evaluates this part:

as:

So Excel is effectively calculating:

Original Formula + 0

The output is unchanged, but the formula contains useful documentation.

This is a simple technique, but it can make a workbook far easier to review and maintain.

The +N("comment") technique is best for formulas that return numbers.

Why +N Does Not Work for Text Formulas

The +N("comment") method only works when the main formula returns a number.

It does not work when the formula returns text.

For example, suppose you are creating a full name for invoice headers:

You might try to add a comment like this:

However, this returns a #VALUE! error.

The problem is that Excel cannot add a number, even zero, to text.

The formula is trying to combine text with arithmetic:

That does not work.

Fortunately, there is another formula trick that solves this problem.

How to Add a Comment to a Text Formula with REPT

For text formulas, use REPT() instead of N().

The REPT function repeats text a specified number of times.

For example:

returns:

Ha Ha Ha

But if you repeat text zero times:

Excel returns an empty string.

An empty string behaves like blank text, so you can join it to another text formula without changing the displayed result.

Here is the full name formula again, this time with a built-in comment:

The result still displays the full name.

The text inside REPT() does not appear in the cell because Excel repeats it zero times.

However, the explanation remains visible in the formula bar.

Why the REPT Comment Technique Works

This part of the formula:

returns an empty string:

So Excel is effectively calculating:

Joining an empty string to text changes nothing.

This makes &REPT("comment",0) the text equivalent of +N("comment").

Use These Two Patterns for Formula Comments

You can remember the techniques with one simple rule.

For formulas that return numbers, use:

For formulas that return text, use:

&REPT("Your comment here",0)

Here are examples of both.

Use LET to Create Self-Documenting Excel Formulas

If you use Microsoft 365, the LET function provides an even cleaner way to make formulas easier to read.

LET allows you to assign names to parts of a formula and then use those names in the calculation.

The basic syntax is:

Instead of repeating cell references and complex expressions, you can give them meaningful names.

For example, here is a standard bonus formula:

It works, but it is not especially readable.

Here is the same logic using LET:

This formula is easier to understand because each input has a meaningful name.

You can read the final calculation almost like a sentence:

IF sales is greater than or equal to target, multiply sales by rate, otherwise return zero.

How LET Creates a Built-In Comment Slot

Notice the first variable in the formula:

The note variable is not used in the final calculation.

Excel allows this, which means it can act as a built-in comment slot.

Unlike the N() approach, there is no need to add zero to the calculation.

Unlike the REPT() approach, there is no need to join an empty string.

The note simply sits at the start of the formula as documentation.

This is one of the less obvious but very useful features of LET.

Benefits of Using LET in Excel Formulas

Using LET can improve formulas in several ways.

Check out the comprehensive LET function tutorial here.

N vs REPT vs LET: Which Method Should You Use?

Each technique has a different purpose.

Excel Version Compatibility

The N() and REPT() techniques work in older versions of Excel, making them particularly useful for files shared across teams with mixed software versions.

The LET function is available in Microsoft 365 and newer perpetual versions of Excel that support dynamic array-era functions.

If you regularly share workbooks with people using older versions of Excel, it is worth knowing both approaches.

Make Your Excel Formulas Easier to Understand

A good Excel formula should do more than return the right answer.

It should also be understandable when someone reviews it later.

Using N() comments for numeric formulas, REPT() comments for text formulas and LET() for clearer logic can make your spreadsheets easier to audit, maintain and share.

The key patterns are simple.

For numeric formulas use:

and for text formulas use:

Once you start using these techniques for important assumptions and business rules, you can make even complex spreadsheets far easier for others to follow.

For more advanced Excel formula techniques, explore the Advanced Excel Formulas course, where you will learn how to combine functions, simplify complex logic and build more powerful spreadsheets.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub