Filtering data in Excel

Mastering Filtering in excel is one of those watershed skills. I regularly teach it as the point you stop using Excel as a place to store a list of data and start using the data to gain insights to make decisions and act on the results.

Filtering lets you make a rule, or set of rules against your data set and hides all the data that does not meet the condition(s) of the rule(s).

One of the best things about filtering is that it is one of the easiest of the ‘advanced’ skills in Excel that you can try out, and very quickly start showing off your new found skills within a couple of minutes.

For best practice, you should select all of your data to be filtered and be sure that all of your columns have headings. Excel will be able to cope with you doing it other ways but sometimes it’s auto detect doesn’t detect what you want it to detect. If you don’t select a range it will try to guess what you are doing, but often this will end with it missing out columns in its range.

To activate the filters to this range go to Data>Filters on the ribbon. There, you have done it. Your first filter is activated!

 

 

Each heading that you have included in the filter will be denoted with the filter available symbol if any of the columns you want to be included don’t have this you can press the button in the toolbar Data>Filters to remove them and start again. If a filter is applied already the symbol will change to filter applied symbol.

Now you have your filters turned on you can begin filtering the data. There are three different types of filter you can use: values, criteria and colour.

Filtering by values lets you select certain entries from your list so you can see them together. i.e. You want to see all the entries from a person or about a product (or in the example below, your Pokemon!), this can be used to select multiple values to compare results. You can either use the tick boxes or just start typing in the search box.

 

 

Filtering by colour lets you pick one or multiple fill colours from within that column and show only those, this becomes very powerful when combined with conditional formatting.

Filtering by criteria allows you work with more complex rules and is a huge step in reading the important insights from your data. Some examples of criteria would be: all values greater than 10; all values that do not contain “South”; top 10 values.

You can also combine multiple criteria, either in the same column using the <and> option in the advanced filter window, or by setting up separate filters on separate columns.

 

 

As a bonus feature, Excels filters almost leave the inbuilt sort option redundant. By using the drop down lists you can also choose sorting options for each column. If you need multiple levels of sorting you can just do them one at a time from lowest level tier to the highest.

Find more quick tips like this and more on my post: Top skills to make you look like a wizard!

 

The Excel cheat sheet mugs below will give you a head start to becoming a Spreadsheet wizard!