Hi Reader,

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

Quick announcement: the Google Sheets Tips newsletter is taking a summer break for July!

It'll be back in your inbox on August 5th with the 310th edition.

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

➜ News

I.
​Is Excel a sport? Err, no. But this is an interesting piece from The Verge about the Excel World Championships in Las Vegas, where spreadsheet wizards duke it out for a $3k top prize!

​Read Spreadsheet Superstars here >>​

II.
​Another outside-the-box spreadsheet example, this time using Google Sheets to track dating data and find a suitable partner!

​My secret to dating in San Francisco is a spreadsheet​

_______

➜ Google Sheets Tip #309:

In this tip, let's take a look at a neat example of the SWITCH function.

The SWITCH function is used to test an expression against a list of cases. It looks for exact matches rather than conditional tests (so we don't use < or > with SWITCH).

Think of it like a special, more succinct version of an IFS formula.

And, I'm going to show you something secret I discovered recently. Something that makes makes me love the SWITCH function even more.

SWITCH Example

With the Olympics fast approaching, we're going to use this dataset, which shows the results from the Women's 100m final in Tokyo 2020:

Let's build a SWITCH formula to add medals to the table:

=SWITCH(A2,
1,"Gold",
2,"Silver",
3,"Bronze",
"Finisher")

The first argument is the input cell, A2, that we want to test.

On the next line, SWITCH tests to see if A2 equals 1 and, if it does, returns the result "Gold".

If that test fails, then it tests against 2, then 3 if needed.

Finally, if A2 is not equal to 1, 2, or 3, then the last argument is our default result to be returned if all the other results are false.

Drag the formula down column E and our table looks like this:

The top 3 are labeled with their medals, and the remaining athletes are classified as "Finisher".

If we only want to display the medal positions, we can omit the final argument BUT KEEP THE COMMA to display blank cells.

The formula is:

=SWITCH(A2,
1,"Gold",
2,"Silver",
3,"Bronze", )

Note the comma after "Bronze" still.

With Emojis

We can use the medal emojis for Gold, Silver, and Bronze to write a super succinct formula:

=SWITCH( A2 , 1, "🥇" , 2 , "🥈" , 3 , "🥉" , )

In our Table:

SWITCH's hidden array superpower!

In our table, let's delete all the SWITCH formulas except for the one in the first row.

Then change the range from A2 to A2:A9 (shown in blue) like so:

=SWITCH( A2:A9 , 1 , "🥇" , 2 , "🥈" , 3 , "🥉" , )

And boom!!

It automatically expands to fill the column. Without requiring an array formula wrapper.

It's one of those rare native array functions. Very cool!

(Note, the formula needs "room" to expand, which is why you must delete the other formulas in the rows below. You'll see this error otherwise: #REF! Array result was not expanded because it would overwrite data)

Finally, we might consider converting this data into a nice, new shiny Table so we can take advantage of all the benefits of Google Sheets Tables...

_______

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

Have a great week!

Cheers,
Ben

P.S. Fascinating thread of abandoned places...​