Auto Filter in Microsoft Excel

What is Auto Filter?

Auto  Filter is an option available in Excel that makes it easier for a user to find, show or hide the specific data he/she wants. Once the data has been filtered, one can edit it however one wants and print it directly. By filtering data, the user can find the information he/she is looking for much easier. In this module, we will look at the following points with regard to Auto Filter:

  • How to Apply Auto Filter
  • Filter Data with Specific Criteria
  • Clearing These Set Conditions
  • Removing AutoFilter

How to Apply Auto Filter?

Rita is the class monitor of 12th B. Her class teacher has given her the following spreadsheet and asked her to separate the details of students who have failed. Auto Filter is an easy way to do this.

AutoFilter 1

These are the steps she should follow.

Open her Microsoft Excel Worksheet. Select the range of cells that she wants to filter. Click on the ‘Home’ menu.

Now, click on the ‘Sort and Filter’ dropdown list from the ‘Editing’ group in the ribbon. A dropdown list as shown below will appear.

AutoFilter 2

Click on the ‘Filter’ option.

Her cells will have a small dropdown arrow beside it.


Filter Data with Specific Criteria

As her teacher has given her specific criteria, Rita can use these to specify her filter and make it easier.

She should click on the small dropdown arrow that appears after you have applied AutoFilter on the range. A dropdown list showing you details about the various data will be given with small checkboxes beside them.

She can uncheck the checkboxes against the names of failed students and click ‘Ok’ once she is done.

In case her teacher had given her marks of each student as well and asked her to separate out the data based on this, she could have used these options. She can add other criteria by going to the ‘Text Filters’ option in the dropdown list produced by the arrow. The Text Filters available are:

AutoFilter 3

Equals: Equal to the value specified.

Does Not Equal: Not equal to the value specified

Begins With: Beginning with a particular character

Ends with: Data ending with a particular character

Contains: Data containing specific characters

Does not contain: Data that does not contain the specific character

Custom Filter: To create a filter of your choice

She can choose


Clearing the Set Conditions

Once she is done, Rita may want to remove the set conditions to return the sheet. She can follow these steps.

Select the range of cells that she wants to filter and click on the ‘Home’ menu.

Now, click on the ‘Sort and Filter’ dropdown list from the ‘Editing’ group in the ribbon. A dropdown list will appear. Click on ‘Clear’ button.

All the conditions that she had applied would be cleared.

Auto Filter


Removing Auto Filter

By following the given steps, she can remove AutoFilter to return the sheet as it was.

Select the range of cells that she wants to remove the filter of. Click on the ‘Home’ menu.

Now, click on the ‘Sort and Filter’ dropdown list from the ‘Editing’ group in the ribbon. A dropdown list will appear.

Click on ‘Filter’. The dropdown arrows present in cells before will disappear.

Leave a Comment

Your email address will not be published. Required fields are marked *