Google Sheets has become one of the best alternatives to Microsoft Excel for many companies. Google Sheets offers several ways to filter data and create filter views. When you apply filters, you can concentrate on the specifics of your dataset.
Using filtered views, you can apply few filters to an array of data and then save them for use later. You can sort the data without disrupting the order in the original dataset. As a result, your colleague who might be working on the same dataset at the same time will not be disturbed.
Sample Data to Create Filters
Below you can find sales data for various grocery items for different states in the United States.
So let’s say you are only interested in the meat sales data for different states. One way to do this is to sort the dataset and focus on your desired data. However, this will change the order of your data in the original sheet.
In this article, we discuss alternative methods of filtering data that include meat sales information. Find out how you can do that.
Selecting the Dataset to Apply Filter
1. Select any cell from the whole dataset.
2. Go to the toolbar and click on the create a filter.
The entire dataset has been selected, as shown above. However, there are cases when the filter function cannot detect the entire dataset when there are gaps between different columns.
Since there is a gap between rows 4 and 6, it can filter only a part of the dataset. Therefore, remove any gaps and irregularities from the dataset before moving forward.
Selecting the Right Dataset to Apply Filter
To keep the gap between columns in place, manually select the whole dataset.
- Select the data starting from the header till the end of the dataset (Including any gaps in-between).
- Click on the create a filter option.
Although the gap remains, the filter view will apply the filters to the selected data.
Applying Filter From the Data Ribbon
- Choose the data you want to filter on.
- Go to data ribbon.
- From the Filter views menu, click on create a filter.
Options to Filter the Data
By clicking on the filter icon in front of any header, you will see different options to apply the filter to the data in that row.
You can filter the data through colors, conditions, and values, as shown above. All four grocery items are already checked by default, which you won't need in this case. Using the Clear button above the checkboxes for grocery items, uncheck all the boxes.
You can filter the data by checking any of the available options. Let’s apply the filter to extract only the meat data from the whole dataset.
- Check the meat box (for example).
- Click on OK.
Doing this will filter the meat information in your sheet without affecting the actual worksheet shared by multiple users.
You can see above that the data is still in the same rows with a few rows missing that aren’t part of the filter.
Copying Data From Filter View
An advantage of filter views is that you can copy and paste the filtered data into other sheets. Whenever you do this, it takes only the filtered data without copying the whole set. Also, pasting it in a new sheet will not skip any hidden rows in your filter view.
- Copy the data by pressing CTRL+C.
- Open a new sheet.
- Paste data with CTRL + V.
The filtered data was initially in rows 4, 7, 11, and 17. However, after pasting, the order is normal. So, if you need to send the filtered data to a colleague, you can copy the data without any need to alter rows and columns.
Filtering Data With Pre-Defined Conditions
In addition to limiting the type of grocery, let’s say you want to condition the above data regarding profit as well. Filter-only meat profits that are greater than 10,000. Then, click the filter arrow for the profit column.
Like in the previous example, you can make selections by checking boxes. The checkboxes available to us do not allow us to condition the data according to our needs. You can instead use the filter by condition option here.
1. Click on the filter by condition.
2. From the available conditions, choose greater than.
3. Now, enter the value, which is the profit of 10,000.
4. Click OK to implement the filter.
In Utah, the sales of meat resulted in a profit of 8435, which is less than 10,000. As a result, that data isn't included in the filter view since it doesn't meet the profit threshold.
Sort Data in Filter Views
Sorting the data in the filter view takes more attention. If you sort a specific column directly, either ascending or descending, you will alter the sort order in the original datasheet. To sort it, you'll have to create a filtered view.
1. Go to filter toggle and click on the down arrow.
2. Select save as filter view.
The interface changes to a new one with dark borders confirming you are in the filter view. Here, you can sort data in a way that does not change the original sort order.
1. Click on the filter arrow in the profit header.
2. Choose sort A – Z.
Now, the values are sorted in descending order.
3. Rename the file, and it’ll be automatically saved.
Again, you can change the filter view's name, update the range you sorted, duplicate, or even delete it by going to the filter view settings on the right-hand side.
Press Cancel, and you will return to the normal view.
Checking the Saved Filter View
- Go to filter toggle.
- Select any of the saved filter views.
You'll get back to the filter view if you click on Profit greater than 10,000.
Get More Out of Your Google Sheets
When you're in a filter view, you're not affecting the experience of people who are simultaneously viewing the same sheet. There will still be access to the original dataset. This feature can be helpful when reviewing data in shared sheets.