How to create filters in Google Sheets

Spreadsheets are not the easiest things to read. Especially, if your values are numerical and set to 5 decimal places! Your screen can begin to look like it has an ant infestation. When reading the data, it can be very easy to lose your place. Trying to hone in on sections of the data and interpret it, well that is just a recipe for a headache!

This is when the Google Sheets filtering function comes in real handy! Filters allow you to isolate rows of data that meet certain criteria. They could be specific values, or values within a range: alphabetical, alphanumerical, dates, prices, qualities — you name it! By using filters, you get to focus purely on the data that is relevant to the job at hand, saving a lot of time — and your sanity — in the process!

So whether you are an inventory manager who needs to find out what stock needs restocking, a sales representative who wants to chase up leads within a specific location, or a musician looking to curate a set list of songs sporting a bpm of 120 and higher, this blog is for you. So let’s set up those filters!

How to create a filter in Google Sheets

1. Open a Google Sheet.
2. Identify the range of cells you would like to create a filter for.
3. Column
4. Row
5. Selection within the Google Sheet
6. Insert a row above your identified data selection.

Why? When you create a filter, a filter dropdown menu will be generated in the top cell of your selection. As a result, any data that was inputted into that first row will become separated from the rest of the data set and will not be filterable. It would almost look like it is the title for your dropdown menu. So by adding a row of headings above your data selection, you are ensuring that all inputted data can be filtered.

1. Select the range of cells you would like to create a filter for. This could be a:
2. Column
3. Row
4. Selection within the Google Sheet
5. Right-click on the selection.
6. Select the option “Create a filter.”
7. Click the filter icon which has appeared at the top of your range of selected cells to access filter options.

Filter options in Google Sheets

In Google Sheets, you can filter your data by:

• Colour
• Condition
• Value

Filtering by colour

If you have colour coded your data, you can filter it by colour — text or fill colour.

To filter by colour on Google Sheets:

1. Click the filter icon generated when you created the filter.
2. Select “Filter by colour” from the list of options that appear on the pop-up menu.
3. Select “Fill colour” or “Text colour” as applicable.
4. Select the colour as applicable.

Filtering by condition

If you want to filter your data to a specific range of values, you can do so with the “Filter by condition” feature. A condition is a set of rules that the data must comply with. For example, if your data set was parishioners in a church congregation and you wanted to find all entrants who were in their 20s to invite them to a 20s event, you could filter the age column to “Is between 20 and 29.”

To filter by condition on Google Sheets:

1. Click the filter icon generated when you created the filter.
2. Select “Filter by condition” from the list of options that appear on the pop-up menu.
3. Select one of the preexisting conditions from the dropdown list available.

• Is empty
• Is not empty
• Text contains
• Text does not contain
• Test starts with
• Text ends with
• Text is exactly
• Date is
• Date is before
• Date is after
• Greater than
• Greater than or equal to
• Less than
• Less than or equal to
• Is equal to
• Is not equal to
• Is between
• Is not between
• Custom formula is

1. Enter the “Value(s) or formula(s)” you want to filter by into the fields provided.
2. Click “OK.”

Filtering by a value

“Filter by value” is the way to go when you want to isolate rows of data within your Google Sheet that possess a specific value. Google Sheets actually generates a drop down list of values that you can quickly access and select from. This is only useful if the values are a finite set — rather than a continuum — and the total number of them is relatively small. For example, if you wanted to find out which employees haven’t completed their training, they either have or they haven’t, giving you two possible statuses.

When your range of values is more of a continuum or larger in number, the generated list isn’t that helpful. In theory, the number of values in the dropdown list could extend to the number of cells available within a Google Sheet… which is 10 million! So if you were to scroll through the generated list of options, you could be there for a very long time! In cases like these, it is far quicker to type the value into the search field provided.

To filter by value on Google Sheets:

1. Click the filter icon generated when you created the filter.
2. Select “Filter by value” from the list of options that appear on the pop-up menu.
3. Either:
4. Press “Clear.”
5. Select the values in the list provided you would like to filter. A tick should appear when selected.

Or

1. Press “Clear.”
2. Enter the value you want to filter in the search bar.
3. Click the value in the list of results - a tick should appear when selected.
4. Clear the search field and repeat the process to add more values.
5. Click “OK” when you are happy with your selection.

How to remove filters in your Google Sheet

To remove a filter in your Google Sheet:

1. Right-click the filter icon of the filter you want to remove.
2. Select “Remove filter” from the list of options on the pop-up menu that appears.