Filters and advanced filtering

Excel gives you many options when it comes to filtering your data.

If you have a database with numerous entries, you can filter the data so that only the relevant information appears. Then, when you remove the filter, you have all of your data. Built in comparison operators such as “greater than”, “less than”, “between”, “top 10” or even custom filters can show the data you want and hide the rest.

How to Filter

First, select the data you want to filter. It’s helpful if your columns have headers.

  • DATA > Sort and Filter > Filter 
    Your columns should now have arrows next to each of the headers
  • Click the arrow in the column header, then choose Text Filters or Number Filters
  • Choose one of the comparison operators
  • In the Custom AutoFilter box, type or select the criteria you wish to use to filter the data
  • Click OK to apply the filter.

You’ll notice that for this example we used the Between comparison filter. Your data should now look like this:

Advanced Filtering

If you want to filter data to more specific parameters, for example based on a specific criteria, or to remove any duplicate values, you can use the Advanced Filter dialog box.

Here we have a list of constituents and the regions they live in. We want to filter our list to only see the regions where we have constituents:

 

DATA > Sort & Filter > Advanced Filter

This will launch the Advanced Filter dialogue box. Choose Copy to another location. Select the column (in this case column A) you want to pull the unique records from.

In the Copy to: box select which column you want the unique records to be listed. (in this case we’re listing the unique records in column K)

Make sure to check the box for Unique Records Only and click OK

Our worksheet now shows only ONE of each of the areas where constituents are located in Column K where we placed the unique records:

 

For more information on filtering, and to try some practice exercises, login to your course and visit Section 4 “Data Handling”, Unit 1 “Sorting and Filtering”.

 

 

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles