Spreadsheet

How to Filter Data to Separate Sheets with Links in Google Sheets

This post has the potential to be a valuable time-saving tip for many beginners working with Google Docs Spreadsheets. Here, you’ll delve into a common spreadsheet task: filtering, but with a unique approach.

In Google Sheets, there are two functions, FILTER and QUERY, that can be employed to filter data. However, when it comes to filtering data to separate sheets within the same workbook, the FILTER function is the method of choice, as opposed to QUERY. This is because the latter does not retain hyperlinks.

Typically, the filter menu command is utilized to sift through large datasets, aiding in the focus on relevant information. By utilizing the FILTER function in Google Sheets, you can efficiently filter data, essentially transferring filtered data to different sheets.

Now, what exactly does it mean to filter data with links?

This concept has two implications:

  1. The filtered data will automatically update when changes are made to the source data.
  2. The filtered data will not lose any hyperlinks.

Filtering Data to Separate Sheets: Can It Be Done in Excel Too?

You can filter data to separate sheets or ranges in Microsoft Excel using the advanced filter option. However, there’s a distinction with the FILTER function in Google Sheets.

The FILTER function in Google Sheets copies the filtered data with links to other tabs or ranges. This means that changes in your master data will be reflected in your filtered data, and any hyperlinks will be retained.

Update: The FILTER function is now available in Excel as well (not in all versions), though the usage might be slightly different.

Here, we are going to use the FILTER function in Google Docs Spreadsheet. The syntax is as follows:

Syntax:

FILTER(range, condition1, [condition2, …])

The entire process is straightforward if you follow the spreadsheet tutorial below. Just refer to the example to master the FILTER function and apply it later as needed.

Steps

Step 1:

Enter the following data in a new Google Sheets spreadsheet or copy my sample sheet with the sample data and all the formulas by clicking the button immediately after the image.

Sample Sheet

You can observe from the above data that it is a list of the members of a club.

We are going to filter the members’ names based on their age group. There are three different age groups in this sample data [see column B, i.e., “Age Group”].

Step 2:

Create three new tabs or sheets and name them as follows: ‘Age Group 5-10,’ ‘Age Group 11-17,’ and ‘Age Group 18-30.’

Step 3:

Copy the column header to the first row of each of the newly created sheets.

Step 4:

This is the final step. Apply the function to the second cell of each new tab, i.e., cell A2.

The formula for the first tab is:

=FILTER('Master Folder'!A1:D19,'Master Folder'!B1:B19="5-10")

Please don’t get confused with the formula. Check the syntax given above. We want to select the range and provide the filter criteria.

The formula for the second tab is:

=FILTER('Master Folder'!A1:D19, 'Master Folder'!B1:B19="11-17")

The formula for the third tab is:

=FILTER('Master Folder'!A1:D19, 'Master Folder'!B1:B19="18-30")

This will automatically generate the filtered data in the newly created sheets. That’s it!

Conclusion

Now you have a total of four sheets. The first one is the master dataset, and the other three contain the filtered data. Remember to change the cell reference in the formula in each sheet if you add more rows to your master data later. Alternatively, you can use open-ended ranges such as A1:D and B1:B instead of A1:D19 and B1:B19.

The sample data may have hyperlinks. If it does, the FILTER function will transfer them along with the filtered data. When you hover your mouse pointer over the text, you can see the clickable link.

Will the above filtered data in separate sheets refresh when we add or delete data in the source sheet?

Yes, it will. If you want to explore advanced filtering, please check my tutorials under the resources below.

Resources:

  1. How to Use the Filter Function in Google Sheets (Basic and Advanced Use)
  2. How to Use Date Criteria in Filter Function in Google Sheets
  3. How to Use AND, OR with Google Sheets Filter Function – Advanced Use
  4. Comma-Separated Values as Criteria in Filter Function in Google Sheets
  5. One Filter Function as the Criteria in Another Filter Function in Google Sheets
  6. IF Statement within Filter Function in Google Sheets
  7. How to Hardcode DATETIME Criteria within FILTER Function in Google Sheets
  8. IMPORTRANGE Within FILTER Function in Google Sheets
Tags: Google Doc Spreadsheet

View Comments

  • Hi! I am trying to create a check register/budget that I can type all of my transactions in, and then, based on the category of spending, have that amount put into a spending category on a separate sheet.

    I am attaching a sample sheet I made. I've tried several formulas, and I think it is more complex than I am figuring. Thanks!

    • Hi, Elizabeth Overman,

      You require Query, not Filter.

      Your column A contains the date of transactions, F contains the amount, and E contains the category.

      In cell A1, the first cell in a blank Sheet in your shared Spreadsheet, try either of the below Query formulas. I couldn't enter the below Query formulas in your sheet. Because your file is 'View' only.

      Date and Category Wise Summary:

      =query(Register!A3:F,"Select A,sum(F) where A is not null group by A pivot(E) label A'Date'",0)

      Category Wise Summary:

      =query(Register!A3:F,"Select E,sum(F) where A is not null group by E label E'Category'",0)

      Month and Category Wise Summary:

      =query(Register!A3:F,"Select month(A)+1,sum(F) where A is not null group by month(A)+1 pivot(E) label month(A)+1'Month'",0)

      You May Like To Read: How to Group Data by Month and Year in Google Sheets.

  • Thank you and help! I got this formula to work for filtering for one term. In my situation, I need to filter by gender, then need to filter three grade levels in a second column. Here is my Sheet: LINK REMOVED.

    • Hi, Jessica,

      To test the formula first enter "girl" in FR!E5 as there are not enough values to test.

      Then use this Filter.

      =FILTER(FR!A1:G100,((FR!E1:E100="girl")*((FR!F1:F100="3rd")+(FR!F1:F100="4th")+(FR!F1:F100="5th"))))

      It's called AND, OR Use in Filter.

      Actually, in such multiple criteria use, Query will be the perfect one.

      Eg.

      =query(FR!A1:G100,"Select * where E='girl' and F matches '3rd|4th|5th'")

  • I tried this but it didn't work. Did Google change the way Sheets work? Would it be OK if you include a sample sheet in all of your tutorials? Thanks.

    • Hi, Kevin,

      You can share your sheet filled with mockup data. I would be happy to assist in my leisure time.

      You may not be able to filter data to separate sheets using my formula due to the regional settings (the , to ; issue).

  • Is there a way to reference another Google workbook altogether? Both in the range and condition aspects of the Filter function.

  • Thank you very much Prashanth, I would be glad to share the copy with you. To what address should I send the sharing?

    • Hi, Mike,

      From the screenshot (which I didn't publish) provided, I guess, I could find the cause of the error.

      You should make changes to my formulas based on your regional setting. I have my Sheet set to the UK as the locale in File > Spreadsheet settings. From the error value (I used Detectlanguage function to find language), I guess your Sheets locale is set to France. So the formula would be;

      =filter('Master Folder'!A1:D19;'Master Folder'!B1:B19="5-10")

      See this related tutorial - How to Change a Non-Regional Google Sheets Formula.

      Best,

        • Hi Prashant,

          Actually I have an issue with the filter function in my google spreadsheet. In every new tab, I am creating there is one or several rows missing from the master tab. Kindly find the links to the corresponding screenshots, one highlighting the row in the master tab named "Nouveaux Formulaires" and the second shows that the said row was not filtered to the new tab named "Khadija". Sorry, it is in French but it is straight forward and easy to understand.

          Many thanks in advance for your guidance.

          Best

          • Hi, Mike,

            Please check whether there is any extra space at the end of that value in cell C453.

            Use TRIM.

            Eg.

            =FILTER(C2:C,C2:C="Khadija")

            Should be replaced with;

            =FILTER(C2:C,trim(C2:C)="Khadija")

            Best,

    • Hi, Mike,

      Please share the link via comment. I just want mockup data with the error replicated. I will duplicate your Sheet and publish that link only.

  • Hi, thank you for this. I used these steps by one by one but what I get only "Error " when I enter the formula of the filter.

        • Hi, Mike,

          Probably due to the double-quotes used around the criterion. You must remove and re-type that doubles-quotes in the formula if any.

          Share a copy (if not containing personal/sensitive data), I will be happy to assist you!

  • Hi Prashanth,

    Thank you for the tutorials. All are helpful and easy to digest.

    I am trying for reversing the effect of the data consolidation feature. But couldn't find out the solution. Can you please explain how to do it?

    What I am doing is, I import the filtered data in 5 different Google Sheet (whole new files) from one master data file. I want to pull back the updated row/cell info from all the 5 files in the respective cell in a master file.

    Appreciate your help.

    Shahid

    • Hi, Shahid,

      I have seen your email and checked the example sheets. Hope you have seen the Vlookup formula which I have entered in one of that Sheet.

      You must learn to Vlookup an imported (importrange) data in Sheets. The following post may come in handy for you.

      How to Vlookup Importrange in Google Sheets [Formula Examples]

      Best,

    • Hi, Shahid,

      An example would be useful. You can share a screenshot using any image sharing application online. Here is one such program.

      Lightshot

  • I have formulas and data validation in my Master sheet that I want to copy onto the new tabs using the filter function as outlined in this article, but it seems that when using FILTER it removes all of the formulas and data validation? Does anyone know a way around this?

    • Hi, Dan,

      You can't copy formulas and data validation rules using the Filter or any other function in Google Sheets.

      Best,

  • My formula is like this:

    =FILTER(JUN!A3:E1000; JUN!B3:B1000=(CELL("contents"; B1)))

    The cell B1 is a drop-down menu of different companies. I want to also replace JUN!A3:E1000and JUN!B3B1000 with drop-down menu.

    The menu should be the month and I have 12 sheets with 12 months named as JAN for January... JUN for Jun...
    So the second drop-down menu should only choose sheet to filter.

    In conclusion: I need two drop-down menus, one with companies, second with the month (sheet) to filter.

    Any suggestions?

    • Hi, Dragan,

      I could understand that you want to extract data from 12 sheets (tabs). Each sheet (tab) is named using month names from Jan to Dec.

      Further, you want to filter companies in that Sheets.

      Without using the Indirect function in Filter, you can't do that. The reason you want to refer sheet names thru' a drop-down. I know it's quite difficult to explain. So I have prepared a demo sheet for you!

      Filter Multiple Tabs - Demo Sheet

      See the formula in cell A3 in the Home tab.

      =iferror(filter(indirect(D1&"!A3:E"),indirect(D1&"!B3:B")=B1))

      Note: If you are from an EU country, please follow the below instruction.

      Go to the File menu, Spreadsheet settings. My Sheet is set to the United Kingdom. You can change that to your locale so the formula will be adjusted to your locale.

      Best,

Leave a Comment

This website uses cookies.