Three Unknown Uses of the AutoFilter Tool

The AutoFilter tool in Microsoft Excel enables you to filter a large list of data to see only the records you want to see. It is a popular everyday tool for working with large Excel lists and helping you to find records and analyse your data.

This tutorial looks at three relatively unknown uses of the AutoFilter tool.

Find Typos and Spelling Mistakes

If you suspect that there may be a typo or spelling mistake causing inaccuracies in your data, the AutoFilter tool can be a quick way of checking. Yes I know there is a spell checker in Excel, but sometimes a spelling mistake creates a different but legitimate word.

The image below shows the AutoFilter tool being used to identify typos on the words “London” and “York”. They are clear to see in the list and the AutoFilter is a quick way of identifying these inconsistencies in your data. Typing people’s names is always a classic example of people spelling a name differently.

Filter by Colour

From Excel 2007 the ability to filter a list by the colour of the cell or font has been available. This feature was a great addition to Excel and works very nicely along with Conditional Formatting.

Conditional Formatting can be used to format the cells in a list that meet specified criteria such as dates that have passed, targets that are met etc. AutoFilter can then be used to filter the list by the colour of a cell to see only those records.

Count Records

You may be familiar with the count functions in Excel such as COUNT, COUNTA, COUNTBLANK and COUNTIF. These functions are extremely useful but are not always necessary. You can count records in an Excel list by filtering the list.

The image below shows a list that has been filtered to show only those records where seafood was ordered. Excel displays the message “116 of 799 records found” on the Status Bar at the bottom of the screen.