This topic would be a time saving Google Doc Spreadsheet tips for many. Here we are going to learn a common spreadsheet task called filter but in a different way. We normally filter data to get specific details from it and remove the filter to go for another. But how it would be getting all different filters always be available in different sheets? For this we are going to automatically move filtered data to separate spreadsheets using a function.
It is easy to filter data using the menu option or command which many of you already may familiar with. But using the filter function sounds unfamiliar to some of you, right? Using filter function in Google Sheets, you can filter data or in other words move filtered data to different sheets.
You can filter data to separate sheets or ranges in Microsoft Excel using the advanced filter option. But the difference here with filter function in Google Sheets is that the latter copy the filtered data with links. So the changes in your master data will be reflected in your filtered data.
Before coming to the tutorial, as a side note, let me tell you one more thing. This has the reversing effect of the data consolidation feature, which is not a default Google Doc Spreadsheet function unlike Microsoft Excel.
Tips to Move Filtered Data with Links to Different Sheets or Sheet Tabs
Here we are going to use filter function in Google Doc Spreadsheet. The syntax is as follows;
FILTER(range, condition1, [condition2, …])
The whole process is pretty simple if you follow the below spreadsheet tutorial. Just follow the below example to master the filter function and apply as your wish later.
Please type the data as it is in a new sheet. No changes to the order please.
You can see from the data that it is a list of the members of a club. The members’ name we are going to filter based on their age group. There are three different age group in this sample data [see column B, i.e. “Age Group”]
We need three tabs [new three sheets] to organise the data as the numbers of age group is three. Create three new tabs or sheets and name them as below.
Copy the column header to the first row of each new created sheets.
This is final step. We need to apply the function to the second cell of each new tabs, i.e. cell A2.
The function for first tab is;
=filter(‘Master Folder’!A1:D19,‘Master Folder’!B1:B19=“5-10“)
Please don’t get confused with the function. Check the syntax given above. We just need to select the range and give the filter criteria. The function may seems complicated simply because of the sheet names coming in between.
The function for second tab is;
=filter(‘Master Folder’!A1:D19,‘Master Folder’!B1:B19=“11-17“)
The function for 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! You have now total four sheets. One the master data base and three others with the filtered data. Do remember to change the function in each sheets, if you add more rows to your master data later.