Thanks to reader Guy L. who asked this question.

How can we fill in blank rows of data in a column, based on the rows with data?

It's a classic question that I've seen countless times in my career.

For example, consider this dataset of appointment times. Each blank row needs to have the time filled in to match the non-empty row above. I.e. rows 3 and 4 are at 9:15 am.

Of course, you could copy and paste the times, but that is not efficient beyond a handful of rows of data.

A little step-by-step tutorial to start the week. 👇

Let's create a mini RSS reader in #GoogleSheets to keep us up-to-date with the latest Google Workspace product releases.

We'll be using the IMPORTFEED function to import the Google Workspace Product Updates RSS feed. First, in cell A1, use the IMPORTFEED function to import the raw RSS feed:

=IMPORTFEED("feeds.feedburner.com/GoogleAppsUpda… ")

🚨🚨 BREAKING: 🚨🚨

If you're a Google Sheets user, stop what you're doing and check this out...

...they just dropped 10 new functions on us!!! 🔥🔥🔥

Christmas came early for Sheets nerds 🎅

Let's check them out 👇 1/ NAMED Functions

Named Functions in Google Sheets let you save and name your own custom formulas and then re-use them in other Google Sheet files.

This reusability is a HUGE development. I'm SUPER pumped on this one.

E.g. here's a function called STARCHART that I created:

🔥 AMAZING SPREADSHEETS 🔥

To some, Google Sheets is just a humble piece of work software...

...but to others, it's a blank canvas to build incredible things with.

10 creations you won't believe are built with #GoogleSheets: 1) Wordle in Google Sheets, by @aTylerRobertson

Tyler builds amazing games in Google Sheets, using only the built-in formulas.

His wordle game is just one of many:

👉 zapier.com/blog/wordle-in…

The SEQUENCE function is an amazingly useful function in #GoogleSheets. It lets you create sequences of numbers from a single formula. The obvious use case is to create a count up 1, 2, 3, 4, 5 etc.

But did you know you can easily create a count down in your Sheets too? 👇 This single, simple formula:

=SEQUENCE(100,1,100,-1)

counts down from 100 e.g. 100, 99, 98, 97, 96...1

To count down from some other number, for example 50, change it as follows:

=SEQUENCE(50,1,50,-1)

Easy-peasy!

If you create #GoogleSheets that multiple people use, one super helpful thing you can do is add a sidebar to your Sheets, containing key info or instructions.

Today I'm going to show you how to create sidebars using the group columns feature... STEP 1️⃣

Highlight some columns, right click, and choose Group columns.

This adds a button above the columns, which you can toggle to show/hide these grouped columns.

Let's talk about building numbered lists with the amazing SEQUENCE function.

It used to be that you had to resort to obscure array formulas like =ArrayFormula(row(1:5)) to get lists of numbers. Things got ugly fast if you wanted to customize these lists.

(1/13) With the SEQUENCE function, you specify these arguments:

1) the number of rows
2) the number of columns
3) a start value
4) a step size

(2/13)

In today's #GoogleSheets tip, I'm going to show you how to highlight the top 5 values in your data.

There are loads of ways to do this, like adding a filter and sorting the values highest to lowest, but today we're going to be using formulas and conditional formatting. Here's the dataset we'll be using. Let's dive in!

1️⃣ Highlight your data, but exclude the header row, i.e. A2:C21 in this example

2️⃣ Open the menu: Format > Conditional formatting

3️⃣ Under Format rules, select Custom formula is

Today I want to show you a quick way to add an ID column to your #GoogleSheets tables, using the SEQUENCE and COUNTA functions.

It's a quick way to see how many records you have in your table.

Let's go! 👇

(1/9) We need a dataset for this example so let's grab an education dataset...

Here are some student test scores along with some variables that affect those scores.

(2/9)

Did you know you can publish your #GoogleSheets as web pages?

You can then share these web pages with the world, so people can see your Sheet as a distinct, lightweight webpage.

It's a good idea if you want to show the Sheet to a very large audience.

(1/3) Here's an example:

docs.google.com/spreadsheets/d…

(2/3)

Yesterday I introduced the QUERY function using this formula:

=QUERY(A1:E100,"select B, D, E where D = 'Europe'",1)

It returns only columns B, D and, E, and only rows where column D is equal to "Europe."

Today's question: How can we change "Europe" to a certain cell, like G2? Well, if you try to put the cell reference straight into the select statement it won't work, because the select statement is a text string.

Also, Europe is surrounded by single quotes, and these need to be included even when we reference another cell.

🤔

The QUERY function in #GoogleSheets is pretty much the most powerful function in the spreadsheet world.

It operates on your data and has the functionality of many other functions, like sorting, aggregation, filtering, etc.

It's like a pivot table in function form.

(1/4) It's a tricky function to learn because it's so different from regular functions. You use query language to write a statement that operates on your data.

Here's an example:

=QUERY(A1:E100,"select B, D, E where D = 'Europe'",1)

(2/4)

10 powerful Google Sheets formulas advanced users should know: 🧐 1) QUERY

The boss for manipulating data, this function can do it all: sorting, filtering, aggregating, transforming...

Let's learn how to use custom checkbox values to use checkboxes directly in #GoogleSheets formulas. 🎉

(1/6) When you create a checkbox through the Data > Data validation menu, you have the option to set custom values, instead of TRUE/FALSE.

For example, you could set the checked value to 1, instead of TRUE, and the unchecked value to 0, instead of FALSE.

(2/6)

A great way to be more efficient in #GoogleSheets is to learn basic shortcuts for selecting data.

That's what I'm going to show you today - how to select data for formulas without your hands leaving the keyboard. ⌨️

It's so much quicker than grabbing the mouse! 🖱️ I remember how awkward it felt when I first learned these shortcuts, and how it was initially slower than just using the mouse to highlight the data.

But after a few days, it was significantly quicker than using the mouse. And it will be for you too.

Here we go ⬇️

The OFFSET function is not a function you need particularly often, but it's worth knowing about because it allows you to move ranges around very easily.

#GoogleSheets thread. Let's go. 👇 The OFFSET function returns a reference to a range that is offset from a starting point in a worksheet.

For example, in the table shown in this image, imagine you want to align these columns at the top of the column.

Have you come across floating bar (or column) charts before?

They're a useful way to show data when you want to compare ranges or high and low values.

For example, you can use floating bar charts with salary data, weather data, stock prices, blood pressure readings, etc. To create a floating bar (or column) chart in #GoogleSheets, you’ll need two series in your dataset, e.g. a set of low values and a set of high values, like this salary range dataset.

A thread about #Lego, including a live build 🤓 👇

I grew up loving the stuff, from ages 3(?) to perhaps 15, from duplo blocks to cutting edge Technic. I credit Lego in part for my deep love of STEM topics.

As an adult, I've started playing it again with my own kids (1/n) About 3 years ago, I bought the Lego Saturn V to decorate my home office. 🚀

Because #Lego + Space. No more explanation needed.

Amazing, amazing build!!

You can really appreciate the engineering in this one. Highly recommended if you can get hold of a set.

(2/n)

Mega thread of #GoogleSheets #productivity tips & tricks coming up...

Starting with this humdinger

Type “sheet.new” or "sheets.new" into your browser to instantly create a new Google Sheet

(1/n) From inside a folder of @GoogleDrive press

Shift + S

To create a new Google Sheet in that folder
(2/n)