How To Create Arrays In Google Sheets (a.k.a. Array Literals)

Arrays in Google Sheets are collections of data, consisting of rows and columns. You can use arrays in formulas in the same way that you use regular A1-type ranges.

You construct arrays in Google Sheets with curly brackets: { }

They’re also known as ARRAY LITERALS.

Continue reading How To Create Arrays In Google Sheets (a.k.a. Array Literals)

How To Rank Data In Google Sheets With The RANK Function

Google Sheets has three functions to rank data: the RANK function, the RANK.EQ function, and the RANK.AVG function.

All three functions return the rank (position) of a value in a dataset.

RANK and RANK.EQ are equivalent to each other and return the top rank for values that are tied. RANK.EQ is the more modern notation, to explicitly differentiate itself from RANK.AVG.

The RANK.AVG function differs by returning the average rank of any entries that are tied.

Consider this dataset showing the three RANK functions in action, with a tie highlighted in yellow:

All Rank Functions

Both RANK and RANK.EQ display the tied values with the rank 5, whereas RANK.AVG shows the average rank of 5.5 (i.e. the average of position 5 and position 6).

The RANK formula in column C:

=RANK(B2,$B$2:$B$11)

And RANK.EQ formula in column D, giving the same answer:

=RANK.EQ(B2,$B$2:$B$11)

Finally, RANK.AVG formula is in column E:

=RANK.AVG(B2,$B$2:$B$11)

🔗 Get this example and others in the template at the bottom of this article.

Continue reading How To Rank Data In Google Sheets With The RANK Function

How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

Since Google Sheets are files in the cloud, not on your desktop, you can’t click on a cell in a different Sheets file to connect them.

Instead, you use the IMPORTRANGE function in Google Sheets to connect Google Sheet files and import data from one Sheet file into another.

Once set up, the function will automatically sync with the source data so that changes are reflected in the destination Sheet.

IMPORTRANGE In Google Sheets

If you look closely, you’ll see a URL in the formula — the URL of the source Google Sheet file, where the data is being imported from.

Continue reading How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

How To Highlight The Top 5 Values In Google Sheets With Formulas

In this post, you’ll learn how to find and highlight the top 5 values in Google Sheets.

For all the examples that follow, we’ll use this dataset, which is available in the downloadable template at the end of this post:

Example Dataset

We’ll see how to highlight the rows with the top 5 values, as well as how to extract those values using SORTN.

Continue reading How To Highlight The Top 5 Values In Google Sheets With Formulas

How To Mimic Radio Buttons With Formulas and Checkboxes

In this post, we’ll look at how to mimic radio buttons with formulas and checkboxes in Google Sheets.

Radio button behavior with checkboxes and formulas

This formula method has one limitation: you have to uncheck the current selection before you can check a different one.

With true radio buttons, when you click any button it will unselect the other one for you. You don’t have to do the extra step of manually unselecting it first.

For that reason, I recommend using an Apps Script method to create true radio buttons in Google Sheets.

However, the formula method presented in this post is useful if you don’t have access to Apps Script or simply don’t want to add code to your project.

Continue reading How To Mimic Radio Buttons With Formulas and Checkboxes