Last week I launched a new Google Sheets course — The QUERY Function In Google Sheets — so I thought it would be interesting and helpful to share how such a course comes about.
It’s my 14th online course and my 19th launch. Along the way, I’ve made just about every mistake in the book, but I’ve now settled on a reliable framework for online course creation, the outline of which I share below.
I think about technical video course creation in six distinct phases:
- Course Creation
Continue reading A Behind The Scenes Look At How I Create A Technical Video Course
Heat maps in Google Sheets are a great way to add context to your data.
They bring attention to the high and low values in your data, to outliers that demand attention.
Best of all, heat maps in Google Sheets are easy to create.
Consider this dataset showing monthly temperatures for Washington D.C.:
Without any formatting, it’s boring to look at, doesn’t convey any immediate takeaways, and it’s hard to spot trends such as which years were hotter than others.
Now compare that to the same dataset with a heat map overlay (click to enlarge):
Wow! The stories jump off the page at you now. You can easily compare the years and see which years had longer winters, or hotter summers.
Let’s see how to create a heat map in Google Sheets.
Continue reading How To Create A Heat Map In Google Sheets
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)
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:
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:
And RANK.EQ formula in column D, giving the same answer:
Finally, RANK.AVG formula is in column E:
🔗 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
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.
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