One of the great things about Excel is that it allows you store multiple rows and columns of data with many different relationships. Take your classic customer information database as an example. You can store all of your customer’s information and the relationships between different pieces of this information (such as all the customers who ship to a certain city, order a certain item, etc.). However, even though the relationships are stored, it may not be readily apparent what they are. For example, say you want to see all of the customers who are based in Denver. If your data has more than a few rows, it could take a lot of time to do this manually. This is obviously not practical. What’s the solution? The answer is simple: The AutoFilter.

The AutoFilter option is quick and simple to use. It allows you to sort through your data by applying drop-down lists to your columns. These drop-down lists are populated with the unique values for that column. Let’s take a look at the following example:

*Note: If this was a real life example, filtering would be overkill as the data set is so small.

Using the above data, how would we filter on the City field to get only the companies that are based in Denver? Follow the steps below:

1. Select the column that contains the City field.
2. Click on the Data menu.
3. Select Filter.
4. Select AutoFilter.

The City field should now have a drop-down arrow on the right hand side of the cell. If you click on this arrow, the following should be displayed:

This drop-down list will be populated with all of the unique values from the selected column. As shown in the above screenshot, the two values displayed are “Denver” and “Miami”, even though there are three rows with data. This is because two of these rows contain “Denver” in the City field. These unique values are the values that you can filter on.

Finally, to show only those companies based in Denver, click on “Denver” in the drop-down list. The following is the result:

As you can see, only the rows that have a value of “Denver” in the City field are now displayed. To undo this, select the All option from the drop down list. All of the rows will now be displayed.

To remove the filter, just click off the AutoFilter option from the Filter menu (basically the same way you turn the AutoFilter on as described above). This will also undo any filter selections made.

You can also use this same technique to filter on multiple rows All you have to do is select the columns you want to filter on when applying the AutoFilter. Using our example, if I wanted to filter on both the City field and the Phone Number field, I would just select both columns before turning on the AutoFilter.

*Note: In the drop-down, there are also options for “Top 10″ and “Custom”. These are a little more advanced than your basic filter, play around with them to see what works for you. I also plan on writing another article on using these options, as this article is just for the basic use of the AutoFilter.

The AutoFilter is a great option because it is so simple to use, and it allows you to sort through large amounts of data very quickly. I use this feature often, especially when I need to get results fast on a large set of Excel data.