March 19, 2014

Auto Filter Microsoft Excel

Auto-Filter in MS Excel allows us to extract information from our raw data quickly and easily.
Implementing Auto Filter:

  • Click inside your data
  • Click on the Data Tab
  • From the Sort & Filter Group select Filter
  • You’ll observe drop-down arrows on each of your headers in the top most or first row
  • Now let’s say you wish to find information about students whose name starts with J. This is your Principal’s or boss requirement
  • Click on the drop down arrow next to Name of the Student
  • Navigate to Text Filters and from the menu that pops up select ‘Begins With…’
  • A new window called Custom AutoFilter opens
  • To the right of ‘begins with’ enter ‘j’ or ‘J’
  • Click OK
  • Your data is filtered
  • The information about all students whose names begin with J is displayed

The AutoFilter feature can also filter data on multiple criteria as you saw on the Custom AutoFilter window. You could apply an ‘Or’ filter to information for example on students with names beginning with ‘J’ or ‘K’. That is, the data would now display all the students whose names begin with J and K.

Let’s learn to apply a number filter now. Let’s say you wished to find all the students who got >=80 marks in the subject Marks3.

  • Click on the drop down arrow next to Marks3
  • Go to Number Filters and select ‘Greater Than or Equal To…’ from the pop up menu
  • In the Custom AutoFilter window next to the criteria ‘is greater than or equal to’ type 80
  • Click OK
  • All the students who got marks greater than or equal to 80 are displayed

If you noticed in Custom AutoFilter you could again as described above you could apply multiple criteria by selecting the option button ‘And’ and applying, for example, a second criteria like ‘less than or equal to ‘ 89 and you would get all the students information whose marks lie between 80 and 89. In our case you would get only one student i. e. Barbara because Susan and John have 90 marks in subject Marks 3.
You would have also noticed that when you click on the drop down arrow next to the header you get other options also like:

  1. Sort A to Z
  2. Sort Z to A
  3. Sort by Color
  4. Clear Filter from
  5. Filter by color
  6. A search box
  7. The last item shows check boxes starting with ‘Select All’ and then the names of the students or all the Marks3 depending on which drop down box you have selected. If you de-select the ‘select all’ check box then you selectivel check the ‘names’ of your choice and view their information quickly

The other options are self-explanatory.
AutoFilter is therefore a great feature in Microsoft Excel to get information from your Excel data.
View the Excel training video: