Jan
23

How to add and use filters in Google Sheets

by | updated on

When you analyze your tables in Google Sheets, very often you need only a part of the information, while the table may contain dozens of various datasets. What's more, a bunch of various values may simply interfere.

Sure, you can always duplicate the table and delete those temporarily unwanted values. But isn't it a waste of time?

That is when Google Sheets filters lend a helping hand.

So, what is a "filter"? It's one of many Google Sheets options that lets you hide a part of data that you don't need at the moment. This way you don't need to delete the data. You simply hide it temporarily and can display it back any time.

Besides, this enables you to sort the data. Isn't that a pleasant perk? ?

How to filter your data in Google Sheets?

To filter your table, you need to select the range of interest manually. But something tells me you have loads of data.

So, to do the job faster, I advise you to select entire columns. I explained how to do that in one of my previous articles.

If you want to filter the entire table, just click its any cell and go to Data > Create a filter right away:
Filter data using the Google Sheets menu.

Or simply use a corresponding button on the Google Sheets toolbar:
Filter option on the Google Sheets toolbar.

Note. When you enable Google Sheets filter, it will work only for columns with data. If you have completely empty columns, the filter won't treat them as part of your table. You can include them by selecting the range with these columns manually.
Tip. You may want to lock the first row of the table that contains filter buttons. This way you won't have to scroll all the way up to adjust filter settings.

So, the filter is there. Each column header contains kind of an upside-down pyramid icon meaning that the table can now be filtered.
The table can be filtered now.

Now, let's try and adjust it.

Filter by values in Google Sheets

Let's start with the most basic filter option – filter by value. Decide by which column you'd like to filter first and click that pyramid icon next to the column header:
The triangle icon opens filter options.

Uncheck all the values you want to hide temporarily and click OK.

You'll notice that some of the data has disappeared from the screen right away. Don't worry, the entries weren't deleted. They are there, in your table, you just don't see them because of the Google Sheets filter.

You can sort the remained values either A to Z or Z to A:
Sort the rest of your data A to Z.

Tip. The filter icon changes next to those column headers where the filter is applied.

If necessary, you can always set additional filters. I'll do it with the Product column, to see the information about Dark and Extra Dark Chocolate only:
Apply additional filter to a table using another column.

As a result, both filters are applied to the whole table and there are even fewer entries than before:
Two filters are applied to our table.

The rows we see now meet our two requirements.

Filter by color in Google Sheets

It may very well be that you use different font and background colors to present your data in the best and self-explanatory way possible. For cases like this, there’s another extremely useful option available in Google Sheets – filter by color.

For example, here I have a list of purchased items per order. My conditional formatting rules dictate the following: if the number of items is less than 40, the number is colored red. If greater than or equal to 100, the fill color is green:
Using text and background colors in the table.
Let's apply Google Sheets filters to this table.

Option 1. Filter by text color in Google Sheets

I'm going to filter data by font color and hide all records but those in black:

  1. Apply the filter to your table first.
  2. Then click the pyramid icon in the column to filter by and select Filter by color > Text Color > black:
    Google Sheets: filter by text color.

    Note. Only the rows with the color you select will remain in the table. Other rows will be hidden. So make sure to choose the color that you want to see in your table.

As you can see, all records with any text color but black have been filtered:
Records in red have been hidden.

Option 2. Filter by fill color in Google Sheets

Another way would be to filter by fill color in Google Sheets. I can filter the table and keep only rows with the green background color with the following: Filter by color > Fill Color > light green 3:
Filter by fill color in Google Sheets.

Now I see only rows where Qty cells are colored green:
Only rows with the green background in column E remain.

Note. At the moment, Google Sheets allows neither filtering by both fill and text color nor filtering by several font hues/background hues.

Option 3. Remove all filters by color

To remove any filtering by color simply go to Filter by color > None, and all formerly filtered rows will appear anew:
Remove filtering by color.

So, filtering the table by values or colors is not rocket science. But will it be enough for the list of 100+ entries and a bunch of parameters to consider?

In cases like this, I believe, you'd better apply filters by conditions in Google Sheets. There's even a super tool for the task – the knowledge of absolutely no formula is required. ;)

In addition, that article will teach you how to share different filtering results with different people within one document.

You may also be interested in