Excel filters are very powerful for analyzing and performing deep-dives on your data, best of all it is possible to filter your data in Excel very easily. This guide will show you the basics on Excel filters.
Why use an Excel Filter?
Excel filters are powerful and and can save you a lot of time in analyzing your data. For example suppose you have a table of insurance policy sales. The table contains data on sales regions, teams, people, insurance policy types and values. You can use Excel filters to answer questions such as:
- Which regions have sold the most policies?
- Which people have sold the most policies?
- What policy sales have a value of over $100,000?
Filtering in Excel
If you would like to work through the examples in this post you can download the Insurance policy sales data Excel file:
Download the Excel DataThe Excel filter option is located by selecting Data on the Excel Ribbon then Filter in the Sort & Filter section of the Excel Ribbon:
To apply an Excel filter select the first cell of your table (upper most left cell) or the entire range of data you want to apply the filter against.
- Select cell A6 in the example download
- Apply the Excel Filer using DATA then FILTER
If the filter has been applied properly you will notice small downward pointing arrows in the bottom right corner of the cell. This will show in every Excel column the filter is applied against, telling us an Excel filter has been applied to the column:
When the filter option has been added to the table it possible to perform some actual filtering.
Click on one of the downward filter arrows (depending on what column you want to perform a filter on). If you are following the example click on the arrow in cell A6 (Sales Region).
A popup box appears showing the available values, this will be every unique value found in the column. In the example we have East, North, South and West regions available to select.
You can chose to filter on one or multiple values. For the example first uncheck Select All (this is a quick way to clear all current selections) then select North and West by checking the box.
When you click OK the data table will automatically filter and show only records that are from the North and the West regions.
Multiple Excel Filters
You are not restricted to filtering the data table to just one column. You can add a filter to as many of the columns as you like. To do so it is a case of repeating the previous process, for example if in addition to North and West sales region you can subset the data to just Public Liability insurance policies.
Select the Insurance Policy Type filter in cell D6. Uncheck the Select All option to clear all the current selections. Now check the box next to Public Liability:
As soon as you click OK the data filters again, this time showing just Public Liability insurance sales for the North and West Sales Regions.
Tip for Excel Filters
Adding multiple filters can get complicated quickly, how do you remember which filters have been applied? A useful tip is to pay close attention the downward filter arrows:
The downward arrows will indicate whether a filter has been applied to the column, the icon changes slightly every time you apply some criteria creating a filter:
Removing Excel Filters
After applying filters to your Excel data it is always useful to know how to start over, perhaps to answer a different question. One method is to go into each filter, check the Select All option which effectively removes your existing criteria. However there are two main techniques that are used when you have multiple filters, they are more efficient.
1. Clear Filter
The clear filter option is used to remove all existing filter criteria. This should be used when you want to start over with a new set of filtering criteria. To clear Excel filters select any cell in the data table then Data on the Excel Ribbon then Clear in the Sort & Filter section of the Excel Ribbon:
2. Remove Filter
Removing a Filter is used when you have finished filtering and want to set the data back to it’s original state. To remove the filter click on any cell in the data table then click the Excel filter option, located by selecting Data on the Excel Ribbon then Filter in the Sort & Filter section of the Excel Ribbon. You’ll notice the icon is shaded when the filter is applied so by clicking the icon again it will remove the filter:
Considerations with Excel filters
Excel filters are powerful but not without a few nuances to be aware of. The quality of data is crucial to the Excel filter process and it can be easy to make some mistakes.
- Blank rows or columns in the data: This can cause issues with the initial range the filter is applied on. Excel automatically searches for the first blank row or column and ends the range at that point so be aware of this. You can overcome by manually highlighting all the data you want the filter to apply to and then add the filter.
- Mixed data types: If you have columns with mixed data types, i.e. values and text, the filter may not work properly so be on the look out for anything like that in your data.
- Forgetting to clear filters: A common mistake to make. Take note of the guidance above on how to check for filters and clear/remove them.
- Unmanageable filters: Try to limit the number of filters you apply to a single data-set. Too many filters and you can lose track of things, if you need more than 5 or 6 filters it might be better to take a subset of the data and work on filtering that.
Summary
This guide has covered the basics of filtering data with Excel, showing you how to add filters, identify them and clear or remove them from your data. You can use filters to make your work more efficient and you can help you analyse your data easier.
Keep Excelling,
Want more Excel mastery? Now you understand filtering in Excel, ensure your data’s security with our guide on ‘7 Ways to Protect Your Data in Excel‘. Dive in for top-notch data safety tips!”
