Tweet

@dhensonroyall

This Thread may be Removed Anytime!

Twitter may remove this content at anytime! Save it as PDF for later use!

More from @benlcollins

Ben Collins

@benlcollins

Aug 8

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!

The four numbers you enter as arguments to the SEQUENCE function represent:

✅ Number of rows (e.g. 100)
✅ Number of columns (e.g. 1)
✅ Start number (e.g. 100)
✅ Step to increase/decrease by (e.g. -1)

Arguments 2, 3 and 4 are also optional.

Read 4 tweets

Ben Collins

@benlcollins

Apr 8

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)

Insert a new blank column on the left of this dataset. Add the title "ID" in cell A1 and this formula in cell A2:

=SEQUENCE(COUNTA(B2:B))

(3/9)

Read 9 tweets

Ben Collins

@benlcollins

Apr 7

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)

And here's how to do it:

1️⃣ Go to File > Share > Publish to web.

2️⃣ On the popup, select the Sheet (or Sheets) you want to publish and hit Publish.

3️⃣ Share the URL wherever you want!

There's even an option to embed the Sheet within an existing web page. 😃

(3/3)

Read 4 tweets

Ben Collins

@benlcollins

Apr 6

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.

🤔

To reference another spreadsheet cell, we close the text string, reference the cell, and then open the text string again.

What the...?!

Read 6 tweets

Ben Collins

@benlcollins

Apr 5

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)

The function operates on data in the range A1:E100.

It returns only columns B, D and, E from that data in columns A to E, and applies a filter so that only rows where column D is equal to "Europe" are in the results.

Cool, right?

(3/4)

Read 5 tweets