Advanced Filters
As requested, this entry will focus on advanced filters, what they are and examples of how they might be used in everyday life.
There are three major differences between AutoFilter and Advanced Filter:
AutoFilter strains through the existing data range while Advanced Filter allows you to extract that data elsewhere.
Unlike AutoFilter, Advanced Filter allows the user to filter out duplicate values.
Advanced Filter allows you to filter across multiple columns (i.e. you can filter out for only rows that contain a certain values). It allows you to filter data using complex criteria combinations (i.e. by “US” and # of Bananas Sold, etc.).
Although you’ll likely be using Advanced Filter for diff. 3 only, I’d still like to cover 1-2 briefly before we go through advanced criteria.
Filtering Duplicate Values/Extracting Data Elsewhere:
When using Advanced Filter dialog box, there are 4 areas of customizability:
Filter the list in place: exactly what it sounds like, having this selected keeps the filtered data in place; it is selected by default.
Copy to another location/Copy to-: Allows you to choose where to copy the filtered data.
Criteria Range: The range of cells Excel will look at when deciding how to filter the data.
Unique Records Only: Selecting this will filter out any rows with identical values.
You can use Excel to extract a unique list of data elsewhere in the spreadsheet.
Suppose you have a table as such:
Either as a result of bad data entry or compilation, there are three different rows in which Alfalfa Aloysius is mentioned.
In order to extract just the unique data to a separate location you can do as follows:
Click on any cell within the table
Navigate to the ‘Data Pane’ on the top of the application, and select Advanced under ‘Sort & Filter’
Click on Copy to another location, Unique records only, and click on the textbox next to Copy to: & click on any cell outside the table.
The extracted data should look as such:
This is because Excel only considers rows that have completely identical data duplicates.
Filtering by Criteria:
Although Advanced Filter can be utilized for filtering out duplicate values, the meat and bones of the tool lies in the ability to filter out data using an unlimited amount of complex criteria.
In using Advanced Filter, you must provide a range of criteria that Excel then uses to filter your data. In order to format the criteria range, you should have the headers of the columns you’d like to filter down as well as the conditions beneath them.
There are two types of conditions through which you can employ criteria to filter out extraneous data, AND & OR, that you can use individually or in conjunction with one another.
Note that criteria are determined by row.
OR:
OR criteria are listed by columns, meaning that you can combine different criteria by going down a row in the criteria range.
Ex. You’re a scientist and you’ve just conducted an experiment and you have the following data table:
You want to filter out your data table for only rows that contain a certain value, say 11. This is impossible to do through AutoFilter since you can’t filter across columns.
However, there is a way you can do this through Advanced Filter:
Copy and paste the headers of the columns you’d like to filter (Trial 1 - Trial 7) in a separate location in the spreadsheet.
Note: Advanced Filter will only work properly when these headers are in the same order as the original data range.
Type 11 in a diagonal across as such:
This works like as such: Excel goes through criteria by row and determines for what column they are associated with via the header they’re under. Therefore, the first 11 collects rows that have 11 as a value under Trial. Because Excel uses OR to combine criteria (which are grouped as rows), all these rows that have 11 under any of the columns will be combined and returned.
Go to Data - > Sort & Filter - > Advanced, and fill out the dialog box as such:
List range: should have the table with all your original data selected and then Criteria range: should have the area with all your criteria selected. Click OK.
The data should look as such:
AND:
You MUST specify criteria horizontally across columns when using AND criteria. When using multiple AND criteria, you can have repeated headers in your criteria range.
Ex. You are the head of inventory management for a major company. You have a table of thousands of rows that contain the data for that same number of items as such:
You want to filter this table so that it only shows rows from ‘Asia’ with a ‘Unit Cost’ of between 100 and 200 and a Total Profit of over $800,000. This is actually possible using AutoFilter, but it’ll take ages and multiple layers of filtering for you to do so. In addition, there’ll be no way for you to add on extra rows of filtering that way.
In order to do it the easy way, you can do as follows:
Copy and paste the headers of the columns you’d like to filter (‘Region’, ‘Unit Cost’, ‘Total Profit’).
Note: Unit Cost appears twice because, in applying multiple criteria to the same column in the data within a single row in the criteria, you have to repeat column headers since you can’t go down a row (that’d make it a completely new filtering rule).
Fill in your criteria range as such:
Go to Data - > Sort & Filter - > Advanced, and fill out the dialog box as such:
List range: should have the table with all your original data selected and then Criteria range: should have the area with all your criteria selected. Click OK.
The data should look as such:
If you found some of the stuff with rows and columns a little confusing, don’t worry; I’ll explain it here. There are two levels of criteria: columns and rows.
Excel considers criteria by row and combines them via AND logic, meaning that the data rows will only show if they match ALL the criteria in that row. This is what I consider column level criteria.
Excel then combines that column level criteria with criteria that might be in other rows using OR logic, meaning, at the end, that rows that follow rules from one row OR any of the other rows will appear. This is what I consider row level criteria.
Excel will first consider column level criteria first. Excel will find rows in the data that match the criteria in each column for a row and then do repeat that process for each row in the criteria. It’ll then go through the rest of the rows of criteria doing the same thing. At the end, Excel returns all the rows of data that matched all of the criteria within one or more of the rows.
Here’s an example of how to use both row and column criteria when filtering through a table:
Again, you’re the head of inventory management for a major company and ou have a table of thousands of rows that contain the data for that same number of items as such:
This time however, you want to compare how well ‘Cereal’ and ‘Meat’ sell in ‘Asia’ and ‘North America’. However, to keep the data as accurate as possible, you’ll only consider data with ‘Units Sold’ of over 9,000 for Asia and 4,000 for North America.
In order to do it the easy way, you can do as follows:
Copy and paste the headers of the columns you’d like to filter (‘Region’, Item Type’, ‘Unit Cost’).
Fill in your criteria range as such:
Go to Data - > Sort & Filter - > Advanced, and fill out the dialog box as such:
List range: should have the table with all your original data selected and then Criteria range: should have the area with all your criteria selected. Click OK.
The data should look as such:
Yeah, this data is pretty unorganized but don’t worry, I’ll be tackling Sorting sometime in the near future. Alright, so that pretty much sums up everything, thanks for reading my blog. : - )
Comments
Post a Comment