When you have a spreadsheet full of data and need to find something specific, filters in Google Sheets can help. Just enter some criteria for the data you need and see your results in a few clicks.
While filters are robust, they don’t have to be intimidating, so we’re going to go through the basics of applying a few simple filters in Google Sheets.
Enable the Filter in Google Sheets
Once you enable the filter feature in Google Sheets, it’s on and ready to use. This applies a filter icon to the top of columns with data. You can then filter whichever column you need.
You might need to select a cell in the data area of your sheet depending on how you set it up. For instance, if you have blocks of data separated by blank columns, choose a cell in the block where you want the filter.
Or, if you want to be able to filter any column in the sheet, select the entire sheet by clicking the rectangle on the top left where the A and 1 meet.
Go to the right side of the toolbar and click “Create a Filter.”
Now, you’re ready to apply a filter. You can filter by color, condition, or values.
Filter by Color
If you use color in your spreadsheet to highlight text or cells, you can filter by the colors that you use.
Click the filter icon in the header for the column that you want to filter. Move your cursor to “Filter by Color,” pick “Fill Color” or “Text Color,” and then choose the color in the pop-out list.
Your sheet will update with the filtered data, hiding the remaining data in the column.
Filter by Condition
Maybe you want to filter out blank cells or those with certain text, dates, numbers, or formulas. Filtering by condition gives you all of these options with specifics in each one.
For example, you can filter text that starts or ends with something in particular, dates that are before or after another date, or numbers that are greater than or less than a certain value.
Click the filter icon in the column header and expand “Filter by Condition.”
In the drop-down box, choose the condition.
For our example, we’ll filter numbers that are “Greater Than” or “Equal To” 250.
So, after you pick the condition, enter the value (or formula) in the box directly below it. Click “OK” to apply the filter.
And there we have it. Only values in our column that are greater than or equal to 250 will display.
Filter by Values
One final way to filter data in Google Sheets is by specific value. When you use this filter, Sheets will display only the values in that column for you, so you can choose those in the list that you want to see or use the Search box to find one quickly.
Click the filter icon in the column header and expand “Filter by Values.” Depending on how much data you have in the column, you might want to use the “Select All” or “Clear” options. Then select or deselect only those that you want.
Alternatively, you can go down to the data list and check or uncheck the values there or find one with the search box. Click “OK” to apply the filter.
And like the other filter types, your sheet will update to display only the data in your filter while hiding the rest.
Disable the Filter in Google Sheets
Unlike the filters in Microsoft Excel, Google Sheets doesn’t currently provide a way to clear a filter with a click. So, when you finish using a filter, you can simply turn it off the same way you turned it on.
Go to the right side of the toolbar and click “Turn Off Filter.”
If using the filters in Google Sheets works well for you, you can also save filter views. Check out our tutorial for how to filter when collaborating in Google Sheets, which walks you through it.