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 because it breaks the rules but I find it a useful tactic in certain situations. Let me know what you think...

By next week's newsletter I hope to be fully recovered. In the meantime, I hope you have a great week!

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

_______

➜ Google Sheets Tip #302: Add Totals to column Titles with the TEXT function

This is a neat tip that I use frequently in my own project management Sheets.

Suppose we want to know the total of a column of data in my table. A helpful metric that we can see at a glance.

But we don't want to add totals to the bottom of the data table because new rows of data are frequently added. Not to mention that it's inconvenient to scroll to the bottom of the Sheet to see the totals.

And we don't want to add a totals row above the table because we want the column headers on row 1.

What can we do?

We can sneak the column total (or other metric) into the column heading!

Consider this dataset of blog posts I've published so far this year:

Specifically, look at the two orange columns and notice the values in parentheses. These numbers are the totals for those columns.

So, at a glance, we can see the total word count and the 30-day web traffic totals.

How can add totals to our titles?

The column titles are text values. To combine numbers and text values we can use the "&" concatenation operator.

For example:

="The meaning of life is "&42

simply outputs "The meaning of life is 42".

Of course, we can reference the number from a cell rather than directly typing it into our formula.

Thus, with the number 42 in cell A2, this formula

="The meaning of life is "&A2

gives the same output.

Knowing that, we can include a SUM function in our column heading:

="Word count "&SUM(D2:D)

which gives the output "Word count 9527".

Note that we leave the range reference open (i.e. D2:D rather than say D2:D100) so that it will always include new rows of data.

To make it look better, let's add parentheses around the number. Note that we have to use the "&" to include the closing ")" too.

="Word count ("&SUM(D2:D)&")"

which now gives the output "Word count (9527)"

Finally, Google Sheets has a useful TEXT function that converts numbers into text values with a specific format.

So we can add the thousands separator into the number too. Our final formula becomes:

="Word count ("&TEXT(SUM(D2:D),"#,##0")&")"

and the output is "Word count (9,527)".

For more examples, check out this article:

​How To Combine Text And Numbers In Google Sheets​

Note

One thing to keep in mind with this method is that the numbers are now an integral part of the column heading. That means the numbers will show up in pivot table headings etc.

So I tend to use this method only for datasets where I don't anticipate doing further analysis. If I'm creating pivot tables, then these embedded numbers can become confusing. And there not needed since you'll be calculating totals etc. in your pivot tables anyway.

_______

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

Have a great week!

Cheers,
Ben

P.S. As the world's second worst singer, after my Dad, it's a good job I wasn't in the audience for this beautiful sing-a-long!