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. 

The sample import Sales.sheet

This tutorial is designed for those who want to enjoy a full walkthrough of the topic or those who just want to jump to the formulas with the Table of Contents below.

Filtering IMPORTRANGE data with FILTER

We can use the Google Sheets FILTER function on IMPORTRANGE to filter data. The FILTER function takes a range to display as the first argument and then any number of conditions in the following arguments. To use FILTER with IMPORTRANGE it would look a little like this.:

Example  1: Get All Sales by a Specific Company

In this example, we want to display a list of all the sales by a specific company. Let’s say we want to see all the sales for the company ‘Kulas Moen’. Our formula would look like this:

In our first FILTER argument, we add the IMPORTRANGE formula. Here, we select the Sales sheet from range from A2:0 selecting all the columns in the range. This will be the range we want to be displayed. Line 2

For the next FILTER argument, we add IMPORTRANGE again, but this time we only want to look at the range in column B where our list of companies is contained. Here we select from range B2:B excluding the headers.

We then compare this argument to see if it is equal to the condition, ‘Kulas-Moen’. Line 3

Example  2: Get All Sales by a Sales Rep

Just like we did in the example above, we could display a list of all of the rows by a specific sales rep to check on their performance.

Let’s take a look at how ‘Gilbert Chikli’ is doing in our Sales Rep column over in Column N.

Here’s the formula:

Again, on the first line, we grab the entire range data from the  Sales tab. Line 1

You can see that our criteria column had moved to column N to search for the sales rep and find any occurrence of “Gilbert Chikli”.

Example 3: Sorting the Filtered IMPORTRANGE data

We can sort our filtered IMPORTRANGE data by applying the SORT function around the FILTER function. SORT takes a range as its first argument followed by argument pairs in the form of the column to sort and whether the sort is ascending or descending. Here’s what the formula might look like.

Let’s go ahead and apply this to our first example.

We might want to see the total amount sold for each sale for “Kulas-Moen” and order those sales from highest to lowest. If we look at our Sales Google Sheet we can see that this is available in Column L.

The SORT function requires us to indicate the column as a number counting from the start of our range (Col A) to the end (Col L).

HINT! You don’t have to count this by hand. One easy way to get the number of columns from your start col to your end is to use the COLUMN function. If your range starts from Col A, then simply enter your COLUMN function in the desired column to extract the column number. In our case, this is Col L.

If your range starts from a different column, then you could subtract your end column from your start column range and then subtract by 1. E.g. =COLUMN(S1) - COLUMN(C1) - 1 = 15.

Here’s the formula:

Note that we have selected FALSE to sort descending from highest to lowest values.

Example 4: Filter All Sales by a selected sales rep that are greater or equal to $1,000 and sort from the highest.

To make compound FILTER conditions with IMPORTRANGE, we need to call the IMPORTRANGE function on each condition.

In this example, we want to see all the sales for the rep, ‘Simon Lovell’ that had a ‘Total’ value greater than or equal to $1,000.

As you can see above on line 5, we have added the total column to our sales rep filter (Col L) and our condition is to display any value greater than or equal to (>=) 1000.

Pros and Cons of using FILTER in IMPORTRANGE

Pros:

• FILTER is easy to use and understand.
• FILTER brings over images, links and formulas.

Cons:

• The formula can get really large and unwieldy really fast making it hard to read.
• It is really difficult to only select certain columns to display. Basically, you would have to run an IMPORTRANGE on every range you want to be displayed and then add that into an array with curly braces before continuing with your FILTER formula.
• You have to use other formulas like SORT to sort the data.

I would probably only use this approach if I need to display the images and links in a range.

Watch the VIDEO

Filtering IMPORTRANGE data with QUERY

QUERY has been called the secret superpower of Google Sheets by many a spreadsheet guru for a long time now. QUERY uses a syntax similar to what you may have seen before in relational databases with SQL called Google Visualization API Query Language.

We can use the QUERY function on IMPORTRANGE in a similar way that we would use QUERY on ranges in a single Google Sheet. However, we must change our column references from using letters (e.g. A, B, C), to Column numbers that reference a range (e.g. Col1, Col2, Col3).

For example, if we select the range C3:F100, then we would reference the first column in our range (C) as Col1 and the last column (F) as Col4.

This will become clearer for you as we work through an example in a moment.

The QUERY function takes 3 arguments:

1. The select range to query.
2. The query parameters.
3. The number of headers in the query.

Incorporating IMPORTRANGE into our QUERY would look like this:

To me, it looks a lot tidier than the FILTER function. We are only using the IMPORTRANGE function once in our formula and our query argument handles what we want to be displayed on our destination spreadsheet.

Example 5: Total sales by a select client.

Let’s duplicate example 1 in our FILTER section of this tutorial by using QUERY.

Here, we will get the total sales for the company ‘Labadie Ltd’. We will go ahead and save ourselves some work and include the header in our query too. Take a look:

You can see that our first argument is our IMPORTRANGE where we have selected the Sales tab over the range A1:O to query. Line 2

Next, we make our query. Here we are saying that we want to select all columns where the second column has the cell ‘Labadie Ltd’. Line 3

We use the asterisk (*) wildcard to select all columns here. Interestingly, we can also omit the ‘SELECT *’ portion and it will select all columns for you regardless. It’s a personal preference to keep it in more than anything to help me understand the query better at a glance.

On the last argument of our QUERY, we select 1, because we only have one header row to display. If you don’t want to display the header, make it a zero.

Note! You may have noticed that, unlike FILTER, QUERY doesn’t carry over formulas, images and links. 

Example 6: Total sales by a select client with selected columns.

One of the cool things about using QUERY is that we can specify which columns we want to be displayed in our query.

Let’s say that we want to just display the company ID and name, the description of the item sold along with the price, quantity and total sales and finally the rep who sold the item and their id.

If you take a look at the sales data this would be Cols A, B, H, J to N and would translate to Col1, Col2, Col8, Col10, Col11, Col12, Col13 and Col14.

Here is what our formula would look like:

Which will would result in this:

Example 7: Total sales by a select client with selected columns ordered by Total sales.

Let’s expand on example 6 further and sort our results by the total of each sale. Unlike FILTER where we need to use the SORT function to order our data, we can use the syntax in QUERY to order our data using the ORDER BY clause.

This can be done by simply appending ‘ORDER BY’ to our query followed by the column to order (For our example this will be Col12 the ‘Total’ column) and then whether or not we want our order to be ascending (asc) or descending (desc).

Take a look at our updated formula (I’ll put each part of the query on a separate line for ease of reading):

Example 8: Total sales by selected client & sales rep over selected columns and ordered by total sales.

In our final example for QUERY, let’s see what sales were made by a sales rep for a selected company. We will keep our nice neat columns and sort the sales total from highest to lowest to see how well the rep did.

Here we want our ‘WHERE’ clause to look at two conditions. We want to show all the rows that contain the company ‘Kulas-Monen’ in column B (Col2) so long as the sales rep, ‘Kevin Foster’ is in the same row in column N (Col14).

We can join these two conditions together simply by using the ‘AND’ clause in our QUERY syntax.

Take a look at our updated formula:

Notice the inclusion of our ‘AND’ clause and our search for Kevin in Col 14. Line 4

This will now display our data like this in our Google Sheet:

Pros and Cons of using QUERY in IMPORTRANGE

Pros:

• Only a single use of the IMPORTRANGE function.
• Handles all the conditions and queries elegantly using the query language.
• Can provide a convenient header.
• Can help you select only the columns you want to be displayed.
• A lot easier to read what is going on in the formula than in the FILTER example.

Cons:

• Does not display formulas, images or links.
• Has a bit of a learning curve to master the query language.

Watch the video

Release date 12 May 22

Creating a Dynamic Dropdown Dashboard using IMPORTRANE data in Google Sheets

One of the best ways of getting yourself comfortable with filtering IMPORTRANGE data and figuring out how and when to use it is by running through an example.

In this example, we will expand example 8 above and instead of using a static company name and sales rep that we have to dig into the formula to manually change, we will create a dynamic dropdown list. You will be able to select a company and then select from a list of sales reps who made sales to that company.

Take a look at the end result:

Take a special note of the dropdown menus for the company (C1) and sales rep (E1).

Step 1: Reference the company and sales rep in our QUERY from selected cells.

Setup the selection row

First, we need to set up the main title row. Follow these steps:

1. In cell A1, add: “Total Sales For Client:” and then merge cells A1:B1.
2. In cell C1, add “Labadie Ltd” as our temporary static company. We will change this to a dropdown later.
3. In cell D1, add “and Rep: “.
4. In cell E1, add “Simon Lovell” as our temporary static sales rep. We will change this to a dropdown later. Merge this cell to cover range E1:F1 or E1:G1.

It’s probably a good idea to change the background colour and borders for your rep and client names so that your users can see that they can use these as dropdowns.

Your top row should look a little like this (minus the dropdown arrows. We haven’t got there yet):

Update the formula

Next, we are going to convert our formula in example 8 so that the company value is drawn from C1 and the sales rep value is drawn from E1. You can copy and paste that formula over to cell A2 or copy it from below.

Next, we need to reference our client and rep cells. To do this we break out of the query by closing it with a double quotation mark (") and then join or concatenate it to our client cell C1 or rep cell E1 using an ampersand (&). We then need to reverse the process by connecting back to the query with an ampersand and double quotation mark (&").

It is important to note that we are still using our double quotation mark around the cell reference. It is common to forget this and get an error.

Have a look at our QUERY now with the new changes.

Step 2 Creating the dropdown data validation.

For our Company and Sales Rep dropdowns, we need to create a list of all the companies and sales reps to choose from. To do this we are going to use our IMPORTRANE function again.

Normally a good practice would be to create a separate sheet tab and add the two dropdown lists there before hiding this sheet tab so other users can’t see where they are.

In this example, we will put them to the right of our data separated by a column in columns J and K respectively so it is easier for us to see what we will be doing. By the time you finish this step your Google Sheet should look a little like this:

Importing the list of companies

In Col J2, type ‘Company’ as a header.

Next, we need to generate a unique list of all the companies from our imported Sales Google Sheet. We will be combing our IMPORTRANGE this time with the SORT and UNIQUE functions.

We will be putting the following formula in cell J3.

First, we use our trusty IMPORTRANGE on column B or the Sales tab (Sales!B2:B). This will give us the full list of all the companies in the column. Line 3

We then want to remove the duplicates by using the UNIQUE function to only select the first occurrence of each business name.  UNIQUE takes a range as an argument and this range is our imported list of companies. Line 2

Next, we want to SORT our list alphabetically to make it easier for our users to find the company that they are looking for. The first argument for SORT is our unique list of companies. We only have one column so our sort column is 1 for our next argument and this column will be sorted in ascending order so we set the final argument to true. Lines 1, 5, 6

importing the list of sales reps

In Col K2, type ‘Rep’ as a header.

Extracting the list of sales reps follows the same process as the company dropdown but we will be extracting our list of reps from col N (Sales!N2LN).

Add the following formula to cell K3.

creating the data validation

Now that we have our company and rep lists let’s create our two dropdown menus using data validation.

The Monster Guide to Data Validation in Google Sheets: Free Course (Updated Feb 2022)

Right-click the company cell, C3. Select View more cell actions > Data Validation.

A dialogue pop-up window will appear.

1. Ensure the ‘Cell range’ matches C1.
2. Ensure the ‘Criteria’ is set to ‘List from a range’.
3. Click on the little grid next to the criteria and then select from column J2:J1001 (or at least up to 200 just in case new companies are added to the list).
4. Check, ‘Show dropdown list in cell’.
5. Check, ‘Reject input’. 
6. Select, Save.

Done.

You should now be able to select among companies and see the sales for the current rep.

Next, repeat the steps to create your data validation dropdown for your sales rep in cell E1 with your rep list in range K2:K1001.

Now you will be able to select from either dropdown to generate the sales for the selected company by the selected rep.

A small problem

You might have frustratingly noticed that sometimes the sales rep did not make a sale for a particular company. As such, you just end up with some empty data. This is pretty annoying. Let’s fix this up in the final step of this project.

Step 3: Improving the sales rep selection so that it only displays the sales reps who made sales for the selected company

In this last step, we want to improve our Sales Rep dropdown list so that it will only display the sales rep who made a sale for the selected company and not display any other rep.

QUERY to the rescue again.

Let’s update our sales rep list formula in cell K3 with the following:

First, we will adjust our IMPORTRANGE to include column A through to column N. We need to check the ‘Company’ column, column B (Col2). Line 3

Next, we want to still select the rep column (Col14), but only when the company column matches the company in C1. Lines 4, 5

Then, we order our list of reps inside our QUERY in ascending order. Line 6

Finally, we set our header to zero to effectively remove it.

Now, when we change the company you will notice that the list of sales reps will change along with it.

You have just built a dynamic dropdown dashboard to review sales by company and sales rep in a Google Sheet separate from your core Sales sheet.

Great job!

Watch the video

Release date: 18 May 2022

Conclusion

In this tutorial, we covered two approaches to filtering IMPORTRANGE data in Google Sheets; using QUERY and FILTER. While FILTER can display formulas, images and links, QUERY is much more flexible in how it displays your selected data.

You then build your very own dashboard with dropdowns drawn from imported data using more QUERY, SORT and the UNIQUE function.

So now that you have a good understanding of filtering IMPORTRANGE data in Google Sheets, what are you going to do with it? I love to hear how folks apply these technique and expand on their in their own products. So please share them in the comments below.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Learn more about importing range data between Google Sheets:

• Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function
• Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet
• Copy and Paste Range Values from one Google Sheet into another with Google Apps Script
• Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values
• Google Sheets: How to use OR inside a FILTER

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.