Keeping a tidy and organized Excel spreadsheet is all about knowing how to locate data quickly. A jumbled mess of data won’t offer much value to anyone. Fortunately, filtering is a great way to make your Excel spreadsheet easier to navigate and nicer to look at.
But if you’re relatively new to Excel or you simply haven’t tinkered around with different features, you might have a lot of questions about how Excel filters work. For instance, what kinds of filters can you add to your spreadsheet? What kind of criteria can you use? How can you remove filters? Finally, where can you get help with Excel from trained IT experts?
In today’s guide, we will answer all of these questions and more, so let’s get started!
How To Add A Filter In Excel
In essence, filters let you focus on specific subsets of data. In most cases, you’ll want to filter data based on specific criteria relevant to your dataset. You can just follow these steps to add a filter:
- Click on a cell within the range you want to filter
- Go to Data > Filter
- Click on a Filter toggle on one of your column headings
- Select Text Filters or Number Filters
- Choose a comparison method and input the specific data you want to see
- Click “OK” to finalize your filter
- (Optional) You can quickly apply a chosen filter with the Excel shortcut CTRL + Shift + L
Once you actually start this process, you’ll see that there are a lot of different ways to filter your data. For example, if you choose the “Number Filters” option, you can choose from any of the following:
- Equals…
- Does Not Equal…
- Greater Than…
- Greater Than Or Equal To…
- Between…
- Top 10…
- Above Average…
- Below Average…
- Custom Filter…
Alternatively, if you choose the “Text Filters” option, you can choose from the following:
- Equals…
- Does Not Equal…
- Begins With…
- Ends With…
- Contains…
- Does Not Contain…
- Custom Filter…
So, when you go to add a filter, you need to understand what kind of data or range of data you’re looking for. Otherwise, you may feel a little overwhelmed with all of the options!
Excel also has built-in features to help you filter data based on appearance. More specifically, you can filter data by cell color, font color, and cell icon. However, to do this, you’ll have needed to change the default colors or icons of your dataset, otherwise, Excel won’t be able to filter anything.
In the sections below, we’ll go through the step-by-step process for filtering by colors or icons:
Filter By Cell Color
This method will let you filter your data based on the background colors of the cells. To filter by cell color, simply follow these steps:
- Click on a cell within the range you want to filter
- Go to Data > Filter
- Click on a Filter toggle on one of your column headings
- Hover over “Filter by Color” and select the color you want to filter by
Filter By Font Color
If you have varied font colors, you can use this filter to only show text of one particular color. To filter by font color, follow these steps:
- Click on a cell within the range you want to filter
- Go to Data > Filter
- Click on a Filter toggle on one of your column headings
- Hover over “Filter by Color”
- In the side menu, choose the font color you want to filter by
Filter By Cell Icon
In the Conditional Formatting tab, you have the option to create different cell icons for your Excel spreadsheet. If you’ve already done this, you can filter your data by cell icons using these steps:
- Click on a cell within the range you want to filter
- Go to Data > Filter
- Click on a Filter toggle on one of your column headings
- Hover over “Filter by Icon”
- Choose the cell icon you want to filter by
Excel Filters With Multiple Criteria
So what happens if you want to filter your data using multiple criteria simultaneously? Fortunately, it’s not too complicated to filter even a large dataset when more than one condition needs to be met. To begin the process of filtering with multiple criteria, follow these steps:
- Click on a cell within the range you want to filter
- Go to Data > Filter
- Click on a Filter toggle on one of your column headings
- Uncheck the box next to “Select All”
- Check the boxes next to the criteria you want to filter by
- Click “OK” to finalize your filter
If you want to create an Advanced Filter, you´ll need to manually input the criteria you want to use. Some of the most common criteria symbols are included in the table below:
| Symbol | Meaning |
| = | Equal To… |
| > | Greater Than… |
| < | Less Than… |
| >= | Greater Than Or Equal To… |
| <= | Less Than Or Equal To… |
| <> | Not Equal To… |
Advanced filters require you to use the same columns as the data you want to filter. For example, if you want to see results in Column A that are greater than 1,000, you would choose an empty cell in Column A and type “>1000” to get the results you want. Keep in mind that you can use multiple columns for multiple criteria and even multiple rows for more complex filtering. However, using more than one criteria in the same row applies an AND condition, while using more than one criteria in different rows applies an OR condition.
Once you’ve set up your Advanced Filter, you can follow these steps to execute it:
- Go to Home > Data > Advanced > Sort & Filter
- In the dialog box, customize your filter with the following options:
- Action: Choose if you want to replace your existing data or make a copy of it in a different location
- List Range: Apply the range of your data if it has not been automatically selected
- Criteria Range: Select the range of your criteria (including header names)
- Click “OK” to finalize your advanced filter
Filter Horizontally In Excel
By default, Excel filters data vertically. However, if you want to filter horizontally, you’ll need to use the correct formula. The formula for horizontal filters looks like this:
=FILTER(Array, Include [Condition])
Let’s say you’re working with the following dataset that includes the make of a car brand (Row 1) and the approximate value of a car from each brand (Row 2).
| A | B | C | D | |
| 1 | Chevrolet | Honda | Kia | Subaru |
| 2 | 25000 | 14000 | 26000 | 18000 |
Now let’s say you want to filter the brands and focus on the values that are equal or greater to 20000. To do this, you would use this formula:
=FILTER(A1:D2, A2:D2>=20000)
This should result in a new dataset that looks like this:
| A | B | C | D | |
| 1 | Chevrolet | Honda | Kia | Subaru |
| 2 | 25000 | 14000 | 26000 | 18000 |
| 3 | ||||
| 4 | Chevrolet | Kia | ||
| 5 | 25000 | 26000 |
How To Remove Filters In Excel
If you accidentally added a filter or you no longer need a filter you’ve already applied, the process is extremely simple:
- Go to Home > Editing
- Click on Sort & Filter
- Click “Clear”
And you’re all set! Once you select “Clear,” any active filters will be cleared and your spreadsheet will return to its default state.
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!