On Google Sheets, if you’re using spreadsheets to manage huge amounts of data, you may have been in a situation where you’re disoriented due to the breadth of information being displayed. This is where the Filter view function in Google Sheets shines. Read on to learn more about how to use the filter function in Google Sheets.
Why You Should Filter Data in a Spreadsheet
Filters Views make it easy for you to read and understand your data. Whether you only want to change the view for yourself or for anyone accessing your spreadsheets, it’s a nifty feature that can save you a lot of time and provides you with a new perspective on your data.
In this tutorial, we’ll show you how to filter data in Google Sheets spreadsheets and the basic filter types you can use.
Creating Filter Views on Google Sheets
You can better analyze your data by hiding away the clutter when you don’t need them. To do this, you’d want to create a filter view that only shows relevant data or a filter view that excludes data you don’t have to see.
However, do take note that when you create a filter with Editor permissions, these filters become available to use to anyone with access to your file. Also, other Editors will be able to change the filter you’ve made.
Filter views are only available on the desktop version of Google Sheets so make sure you use your computer for this. For this tutorial, we’ll demonstrate how to create filter views using a dummy inventory and price list spreadsheet.
Step 1. Pull up the spreadsheet you’d like to create a filter in on Google Sheets.
Step 2. Select the range of cells you’d like to apply a filter to. Include the headers on your selection to automatically generate filters based off of them. The top row is important to a functional filter view.
Step 3. Go over to Data on the menu bar then select Filter views. On that pane, select Create a new filter view.
Success! You’ve now added a filter view to your sheets.
Applying Filters by Value, by Color, or by Condition
Once you’ve successfully applied a filter view, you should see the Filter button on the top rows. This allows you to isolate whatever data you want based on your spreadsheet. Again, for this demonstration, we’re using an inventory sheet from a fictional business to illustrate various examples.
Example 1. Filter by Value. Filter using the Size parameter. Let’s say we’d like to only show XL-sized shirts, we click on the filter icon beside Size and select XL.
By default, all the options may be selected so go over and Clear that selection. This is easier than manually unchecking each option.
After clearing the selection, choose the options we wish to isolate. In this case we only want to see XL sizes so we’ll go for that.
Voila! We’ve generated a filter view that only shows XL-sized shirt options for our fictional Apparel business.
Using Filter by Value, we can also filter for the following sample scenarios for this inventory sheet.
- Filter by other parameters like Size, Item type, Price, SKU, Style, Color, etc.
- Filter out options you don’t need i.e. showing everything but XS sizes.
- Isolate single rows, say, we only need to display one unique SKU.
Example 2. Filter by Color. If you use specific color codes to identify specific data sets, you can filter by color!
On our sample spreadsheet, we used Fill Colors with different hex codes, some with nicknames to represent Shirt Colors. To filter using those, click on the Filter Icon on the header of the relevant column. Go to Filter by Color > Fill Color then select the item you’d like to isolate.
The same could be done if you use Text Colors as identifiers.
Example 3. Filter by Condition. This one has the most complex and customizable functions. We’ll go in depth in a separate tutorial but for this example, we’re going to filter inventory values that go below the value 50. This filter removes from view the lines that are 50 or above.
To Filter by condition, we’re going to click on the Filter icon then go to Filter by condition > Filter by values. You will have an assortment of conditions to choose from. For now, we’ll use the Less than and use the value 50.
By applying this particular filter, we’ll only show values that are only less than 5. You can select what criteria you like based on the functions available.
Conclusion on How to Use the Filter Function in Google Sheets
Taming the task of understanding your spreadsheets is much easier when you use filter views on Google Sheets. It helps you declutter and isolate only the relevant information while keeping your data intact when you need a full view. It only takes a few seconds and it will save you from headaches and eyestrain from looking at overwhelming amounts of rows you may not need all the time.
Looking to be even more productive? We at FileDrop offer a powerful and free add-on for Google Docs and Google Sheets that even more tightly integrate those apps with your Google Drive. It allows you to drag and drop files into your documents and spreadsheets, provides a way to organize those files in a library, and has even more features like optical character recognition for converting PDFs and images to text! Install FileDrop for free using this link.
Learn more about our add-on by visiting FileDrop.
Subscribe to our newsletter below to get amazing new Google Sheets articles, but also tips and tricks.