Filter Your IMPORTRANGE Data in Google Sheets

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.
importrange-function.png

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”.
connect-google-sheets.png

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. 

filters-importrange.png

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. 

Filtered-data.png