Blog

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 Copy Only Visible Cells In Google Sheets

In this post, you’ll learn how to copy only visible cells in Google Sheets so that you only paste the results you want and don’t include any hidden data.

For this tutorial, we’ll use this dataset, shown in full to begin:

Data in Google Sheets

Now suppose that columns B and C, and rows 6 – 9, are hidden so the data looks like this:

Hidden data in Google Sheets

If you just highlight this data and copy it, then when you paste it elsewhere the hidden data shows up.

So, how do you copy only visible cells?

Continue reading How To Copy Only Visible Cells In Google Sheets

How To Draw The SierpiƄski Triangle In Google Sheets

The SierpiƄski triangle is a fractal set in the shape of an equilateral triangle, divided into smaller triangles infinitely.

Graphically, we can draw an approximation of the SierpiƄski triangle in Google Sheets:

Sierpinski Triangle In Google Sheets

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

It is named after the Polish mathematician WacƂaw SierpiƄski and is also known as the SierpiƄski gasket or SierpiƄski sieve.

It has the property of being self-similar, meaning it looks the same at any magnification.

See Wikipedia for more on the SierpiƄski triangle.

Continue reading How To Draw The SierpiƄski Triangle In Google Sheets