Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
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: ➜ NewsI. 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 FormulasLast 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 CellThe 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 cellRight-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 functionAdd 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", 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 functionThe 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, |
by Ben Collins
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Hi Reader, Greetings from sunny Harpers Ferry! ☀️ The rain has stopped (until tomorrow at least!) and I'm finally feeling better so I'm optimistic for the week ahead! Welcome to this issue of the Google Sheets Tips newsletter, #303, your Monday morning espresso, in spreadsheet form! Wow, that's a lot of exclamation points in the first few sentences! Oops, that was another. It's the optimism I feel today ;) Today I want to share a data visualization technique for showing change between two...
Hi Reader, Welcome to the Google Sheets Tips newsletter #302, your Monday morning espresso, in spreadsheet form! I'm sick at the moment, battling a nasty sinus infection. I'm not a good patient because I'm too impatient. There are so many things to do! But I'm wiser now than a decade ago and realize that rest comes first. At least I wrote this week's newsletter before getting sick. And I think it's an interesting newsletter. But it's one that might divide opinion. Purists will dislike it...
Hi Reader, Welcome to the Google Sheets Tips newsletter #301, your Monday morning espresso, in spreadsheet form. Earlier this year I sat down to record a new course but progress stalled before I got far. I've been creating courses for 7 years and realized that I need to freshen things up. One change I want to make is to inject more meaning into my work. I'm deeply interested in our natural world and protecting it for future generations. I want to use the skills I have and apply them towards...