Excel AutoFilter (Table of Contents)
AutoFilter in Excel
IAutofilter in excel allows us to filter the data on various criteria but not limited to Equal to, Greater than, Greater than or equal to, Contains and Does not contain, etc. And there are 2 ways to enable this. First is, by pressing short cut keys Shift + Ctrl + L together and choosing the relevant filter criteria from the drop-down menu list. And other is, by enabling the filter from the Data menu tab and then selecting the appropriate criteria from that dropdown.
In excel we can find the Autofilter option in the data tab in the sort and filter group, Click filter as shown in the below screenshot:
The shortcut key for applying a filter is CTRL+SHIFT+ L where it applies a filter for the entire spreadsheet.
How to Use AutoFilter in Excel?
AutoFilter in Excel is very simple and easy to use. Let’s understand the working of Excel AutoFilter by some example.
AutoFilter in Excel – Example #1
Consider the below example where we have BRAND sales data like local, zonal and national wise.
Assume that we need to filter only the specific brand sales to view the sales data. In this scenario, filters can be used and it will be very useful for displaying only the specific data. Now let’s see how to apply the Excel Autofilter by following the below procedure.
- First, select the cell to apply a filter. Go to Data Tab. Choose the Autofilter option.
- We can see that filter has applied for the entire cells.
- Now we need to filter the specific brand to check the sales.
- We can find BRAND in C column click on the drop down list where it shows all the brand as per the below screenshot.
- Unselect all the check mark and select the brand BRAUN from the following list.
- Once we select BRAUN from the following list excel filters only BRAUN and it will hide all the other rows and display the specific brand as the output as follows.
- The above screenshot shows that only brand sales of BRAUN like local, zone and National wise.
Clear AutoFilter in Excel
- Once the Excel Autofilter has been applied we can clear the filter where Autofilter has a clear filter option to remove it which is shown in the below screenshot.
- The above screenshot shows the option “Clear Filter from BRAND” click on it. So that the filters will be getting removed from the entire spreadsheet.
AutoFilter in Excel – Example #2
In this example, we are now going to check the year wise sales along with brand wise sales and consider the below example.
The above screenshot shows the year-wise sales data that has happened for all the brand, let us consider that we need to check the year wise sales so by using filter option we can do this task to display it.
- Select the cells to apply the filter.
- Go to the data tab and click filter option.
- Now the filter has been applied as follows. We can see that filter has been applied for the entire cell.
- Select the Year column and click on the drop-down list and the output as follows.
- Once we click on the drop down list we can see the month name that has been checked now go the specific month we need to check for the sales data. Assume that we need to check for April month sales. so remove the select all checkbox from the list as shown below.
- Now we can see that all checkbox is unselected and now choose for the “APRIL” month so that it filters only the “APRIL” month sales.
- Here the April month sale has been displayed along with brand wise and Local, Zone and National wise sales. Hence we can give the month wise sales data by applying the filter option.
AutoFilter in Excel – Example #3
Here in this example, we are going to see the number filter which has various filters as follows.
Let’s consider the same sales data assuming that we need to find out the zone wise with highest sales numbers.
- Select the F column cell named Local.
- Click on the drop-down box where we will get various number filter as equal, does not equal, greater than etc.
- At the bottom, we can see the custom filter where we can apply a filter by our own choice.
- Click on the custom filter.
- After choosing the custom filter, we will get a custom dialogue box as follows.
- Click on the custom Autofilter drop down and we will get the list as follows.
- Choose “ is greater than” option and give the corresponding highest sales values to be get displayed.
- In the above screenshot, we have given the sales value “is greater than 2000”, So filter option will display the sales data that has highest sales value more than 2000 and the output is displayed as follows.
AutoFilter in Excel – Example #4
In this example, we are going to see how to get the top 3 highest sales that have been happened for this year.
Consider the same sales data, we can get the top 5 highest sales figure by choosing the Top 10 options given in the number filter as shown below:
- Click on the top 10 option as shown above and we will get the dialogue box as follows.
- Here we can increase or decrease the top option numbers as per our choice in this example we are going to consider the top 3 highest sales and then click ok.
- The below result shows that top 3 sales that have been happened in this year.
AutoFilter in Excel – Example #5
In this example we are going to see how to apply a filter by using color, Normally in FMCG field, low sales data values have been highlighted as RED and Medium as Yellow and Highest Values AS Green. Let us apply the same color for the values and check with the same example as follows.
In the above screenshot, we have applied three different colors where Red Indicates Lowest, yellow indicated Medium and Green Indicates Highest Sales value.
- Select the cell F column named Local.
- Click on the drop-down box and select “Filter by Color”.
- Now it shows five different color choices for selection.
- First, we will select the Green color to check the highest sales value.
- The output will be as follow.
Now the above result shows that filter by color has been applied which shows the highest sale value.
Things to Remember
- Ensure your data is clear and accurate so that we can Autofilter the data easily.
- Excel AutoFilters will not work if the header is blank so make sure that all the headers are named before applying AutoFilter.
- Excel Autofilters will not work if the cells are merged so before applying filter make sure that all cells are an individual.
- Do not use blank rows and columns while using the Excel AutoFilter.
Recommended Articles
This has been a guide to AutoFilter in Excel. Here we discuss how to use AutoFilter in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
