Hi Reader,

Welcome to the Google Sheets Tips newsletter #299, your Monday morning espresso, in spreadsheet form!

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ News

I.
​Did you catch the huge Google Sheets feature announcement last week?

At the Google Cloud conference they announced the new Tables feature coming to Sheets. I'm excited to get this functionality in Sheets, with pre-built formatting options and table referencing.

​Take a peek at what the new Tables will look like >>​

It sounds like it's only a few weeks away...

_______

➜ Google Sheets Tip #299: How To Add Comments In Formulas

Last week (#tip 298) we looked at the LET function and I mentioned that it could be used to add comments to formulas. Today, let's see how to do that, along with three other methods.

A comment is a short sentence that explains something in the formula, e.g. "this QUERY sorts the data alphabetically".

It's a good idea to explain complex formulas. It'll be a huge help to our future self or our colleagues. It makes it easier to share our work and extend it in the future.

Programmers use comments to explain their code. We're doing the same thing with our formulas.

Simple formulas — such as SUM(A1:A10) — do not benefit from comments. (In fact, adding a comment makes the formula more confusing.)

But with complex formulas, comments are helpful. An extreme example is the Chess formula I created earlier this year, where I used comments to label each move.

1. Note in Adjacent Cell

The easiest way to add comments to our formulas is to simply add a note in the adjacent cell (shown in italic):

The drawback to this method is that it requires an extra cell (which might interfere with other data or calculations), which is not linked to the formula. If someone inserts a column or moves data around, then it's possible the comment becomes detached from the formula.

2. Comment or Note on cell

Right-click on the cell containing a formula and select "Comment" or "Insert note" from the menu:

Both add a text box popup attached to that cell. Comments have much richer functionality (e.g. tagging other people, marking as read) but for formula comments, I favor the simplicity of the note.

Cells with notes have a tiny black triangle in their top right corner (red arrow in the following image). The note shows when we hover over the cell:

3. Add comments with the LET function

Add a comment with the LET function by including a named variable to hold the comment. Here's an extremely simple example:

=LET(about,"Adds two numbers", A1+B1)

The "about" variable is not used in the calculation formula.

For more complex formulas, you can add extra comments:

=LET(about,"Adds two numbers",
x, A1, comment_1, "the x variable from column A",
y, B1, comment_2, "the y variable from column B",
x+y)

The comment is now part of the formula, so it can't become separated from the formula. Also, it's hidden until you look at the formula so it doesn't clutter your Sheet.

(Press Ctrl + Enter inside the formula editor bar to add line breaks to your formulas.)

4. Add comments with the N function

The N function returns the argument provided as a number. However, if we apply N to a text value, it returns 0.

Thus:

=N("Adds two numbers")

evaluates to 0.

So we can add this to a number without changing the value of that number (1 + 0 is still 1).

This allows us to add comments to any numeric values in the formulas. Here's how we could apply it to our simple example:

=A1+B1+N("Adds two numbers")

In our Sheet:

Here's another simple example.

And here's a complex example of using the N-comment method.

Personally, since the introduction of the LET function, I favor this method to add comments to complex formulas. It's more flexible than the N-method.

As always, let me know your thoughts or if you have any other ways you like to document your formulas.

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. The fascinating topography of the United States​