Filter Your IMPORTRANGE Data in Google Sheets

Filter Your IMPORTRANGE Data in Google Sheets

Heather Phillips
January 26, 2018

In previous blog posts we’ve shared how you can use Google Sheets IMPORTRANGE function to sync your categorized transactions between Google Sheets templates and the basics of using IMPORTRANGE. But what if you only want to get certain data from one sheet to another?

Perhaps you only want to see data from a specific account to share with your spouse or accountant. Maybe you want to run analysis on specific categories in a separate Google Sheet. Or maybe your spouse only needs to see spending in a certain category. In this post we’ll explain how easy it is to filter your data using IMPORTRANGE.

Prepping the IMPORTRANGE Function

  1. It’s helpful to use a Notepad, TextEdit or another simple text editing program to prep the IMPORTRANGE function before you insert it into your new Google Sheet. So go ahead and open one of these up. We don’t recommend Microsoft Word or more advanced word processors because it can corrupt the formatting of the function.
  2. Paste this function into the text editor document: =IMPORTRANGE(“spreadsheetURLfromStep1“,”Transactions!A:H“)
  3. Open the Google Sheet that’s going to be your data source, the one you want to use to feed the data into the other sheet, and copy the URL of this sheet.
  4. Paste this into the IMPORTRANGE function in the text editor document in the place of the spreadsheetURLfromStep1. Be sure to keep this in between the quotes.

Adding The IMPORTRANGE function to a new Google Sheet

  1. Create a new Google Sheet in your Google drive and then paste this function from the notepad into cell A1 of that new sheet.
  2. You’ll see a #REF! Error in that cell. Click on it to display the allow access option to authorize the new Google Sheet to access data from the data source sheet.
  3. Click “Allow Access”.

Add a filter to IMPORTRANGE

1. Paste this modified function in your text editor document:

=FILTER(IMPORTRANGE(“[spreadsheetURLfromStep1]”,”Transactions!A:H”), INDEX(IMPORTRANGE(“[spreadsheetURLfromStep1]”,”Transactions!A:H”),0,3)=”Groceries”)

2. Update the spreadsheetURLfromStep1 in the modified function to use the URL of your data source sheet.

3. Update the column number and filter criteria. In the above IMPORTRANGE filter function “3” indicates the column number that corresponds to the the Category column and “Groceries” is the criteria we’re using to filter.

If you want filter by a certain account you’d update the column number to correspond to the Account column in your data source Google Sheet, and then update the criteria to filter on one of your account names.

4. Paste this modified function into cell A1 of your new Google Sheet.

Now you should see the filtered data from your data source Google Sheet in this new Google Sheet.

Related Stories

December 3, 2017

Customize Your Household Budget With A Monthly Spending By Group Table

The Tiller Budget spreadsheet template brings your spending into focus for the current month, but what about reviewing spending across groups for previous months? You can easily add this customized view of your spending with a few simple steps

Google Sheets Templates
June 11, 2018

Using A Query In Google Sheets to Organize Your Bank Account Transactions

Learn how to pull the transactions for a single bank account into a separate Google Sheet using a quick query.

Collaborate Google Sheets
May 29, 2016

Collaborative Financial Worksheets: Sharing your Tiller Sheet

Google Sheets makes collaborating on your financial worksheets very simple. Tiller makes this even better by allowing you to be selective about what financial data is shared with whom.