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. 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 examples.
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 they 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 the C column; click on the drop-down list where it shows all the brands 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 brands; let us consider that we need to check the year-wise sales, so by using the filter option, we can do this task to display it.
- Select the cells to apply the filter.
- Go to the Data tab and click the 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 to 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 the highest sales numbers.
- Select the F column cell named Local.
- Click on the drop-down box where we will get various number filters 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 the “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 the filter option will display the sales data that has the 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 the 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 the 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 the Medium, and Green Indicates the 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 the filter, make sure that all cells are 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 a downloadable excel template. You can also go through our other suggested articles –
