Tag: Google Sheets

Displaying Odd and Even Row Data in Google Sheets

Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?

You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.

In this tutorial, we will cover three scenarios:

  1. Separate a range of data by odd and even rows.
  2. Separate a range by odd and even where a selected column has a list of numerical ids.
  3. Separate a range by odd and even where a selected column contains a list of alphanumeric ids.

If you want to play along, you can get a copy of the starter sheet here:

Odd and Even | Starter Google Sheet*

*Contains Easter Eggs.

Continue reading “Displaying Odd and Even Row Data in Google Sheets”

Filtering IMPORTRANGE data in Google Sheets

Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.

In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.

Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.

I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:

Sales.sheet

Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios. 

Continue reading “Filtering IMPORTRANGE data in Google Sheets”

Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial here:

Importing Range Data From One Google Sheet to Another

And here is a link to the sample data that I am importing if you want to play along:

Template – Project Tasks (IMPORTRANGE)

You’ve grabbed data from a source Google Sheet and used the IMPORTRANGE function to insert it into your destination sheet carefully selecting the range and sheet tab of the data that you want to display.

So you might be thinking, Great! I’ve managed to only display the content from my original Google Sheet that I want my users to see and hidden the rest from them. 

While this may be the case if you are only providing ‘View’ or ‘Comment’ permission to a Google Sheet, I do have an ugly little surprise for you if you.

via GIPHY

Are you sitting down? Are you comfortable? Do you have a support network nearby?

Let’s have a look at the IMPORTRANGE docs:

IMPORTRANGE editors can access other parts of the original Google Sheet - What to do
Click to Expand!

So basically, if you have other editors on your new sheet editing your document, they can access anything in the imported Google Sheet by copying the access granted IMPORTRANGE. All they need to do is change the Sheet tab and the range location.

Continue reading “Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet”

Importing Range Data From One Google Sheet to Another

One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google Sheet that you have imported the data to.

Everything starts with the IMPORTRANGE Google Sheets function.

But before we get started on the “How to’s,” you might be wondering why you might need to import live Google Sheet data from one sheet to another.

Or check out the table of contents to dive straight into what you need right now!

Continue reading “Importing Range Data From One Google Sheet to Another”

How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

  • Automatically change your start and end times.
  • Changing your step increments from a minute to, say, every five minutes or an hour.
  • Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

  1. A whole day list of times in 1-minute intervals
  2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

Continue reading “How to create a time sequence in Google Sheets”

%d bloggers like this: