 |
|
|
|
Download the associated Workbook for this lesson
In this lesson, we will have a good look at one of Excel's arguably most useful features and that is the Advanced Filter. Before we do however, we would like to outline what we believe is very important in regards to tables and lists.
In the context we are discussing here, a Table is no more than a List with more than one Column of data. A List is often referred to in the context of a Table as well. The 'good practice' that applies to setting up a List will aid us greatly when we need to filter down our data via the use of the AutoFilter and AdvancedFilter.
When we extract data via the use of Lookup functions or Database functions we can be a little less stringent in how we set up our Table or List. This is because we can always compensate with the aid of a function and probably still get our result. However, having said this we should always set up our List or Table as well as possible. When we use Excels built-in features they will and do assume a lot about the layout and setting up of your data. They do have a degree of flexibility but more often than not you will find it easier to follow the guidelines for setting up your Table or List.
Let's look at what we believe to be the most important aspects of setting up a Table or List. The term "Filters" will be in reference to AutoFilters and AdvancedFilters.
Headings. This is a must! They should always be in the row directly above your data. Never have a blank row between your data and the headings. Make them distinct in some way eg; bold them.
Leave at least three blank rows above your headings. These can be used for formulas, critical data etc. You can hide the rows if you wish.
If you have more than one List, use a different worksheet for each List. This way you can use Filters on any List without it interfering with another List.
Organise your data so that related data is close. This will make reading from a Filtered list much easier.
Filters will only hide Rows not Columns.
If you have data that needs to be seen all the time don't place it to the left or right of your data as it will be hidden once you apply Filters.
If you do have more than one List or Table on the same Worksheet leave at least one blank column between your List and Table. This will aid Excel in recognising them as separate.
Avoid blank cells within your data.
Sort your List or Data. This in not so much for Filters, but for the person reading the data.
If we follow these guidelines as close as possible, using Filters will be a relatively easy task.
As you are now at the Advanced Level of Excel, you should be fully aware of Excel's AutoFilter feature. While in most cases the AutoFilter is quite sufficient for most tables or lists, there are times when you need to extract data that cannot be extracted via Excel's AutoFilter. Probably the three biggest differences between the Advanced Filter and Excel's AutoFilter are:
Advanced Filter will allow us to nominate where we would like our filtered data to be placed. The choices are: Filter the list, in place or Copy to another location.
Advanced Filter has a built-in function that will allow us to filter by unique records.
Advanced Filter allows us to use a formula as our criteria.
Before we go into detail on these, there are a couple of rules that must be adhered to when using the Advanced Filter in certain ways.
In relation to Point 1 - The list must be filtered to a nominated range on the same Worksheet as the list or table resides. This means simply if our list or table is on Sheet 1, Excel will not allow us to nominate the Copy to another location option and copy to another Worksheet, we have to copy to the same Worksheet that our list or table resides on. If you do wish to copy to another Worksheet simply opt to Copy to another location on the same Worksheet, then cut and paste the results to another Worksheet.
In relation to Point 3 - When we use a formula as our criteria, the formula itself must evaluate to either True or False. We will be using formulas later, and you will see what I mean when I show you the example.
Let's start off with something relatively simple, so download the attached workbook and and open the file ExcLev3L02a.xls
In the table on the AdvancedFilters worksheet, the heading Names is in cell A6. The reason we have started in this cell is because it is good practice to always leave at least three spare rows above your data. We usually opt for more than this as we have 65536 rows in total. This is so we can use them for setting a Criteria for an Advanced Filter. When you set a Criteria you refer to it using the Criteria range box. Your criteria range should include the column headings of your List or Table. So we simply copy our column headings to the top blank row. It is below these headings that we put our criteria.
An important point to note here is that column heading names should be unique. Again, this will become apparent once we start using a criteria for our example.
When you first activate the Advanced filter, it will (by default) include all surrounding cells that have data in them. This is known as the Current Region. For this to happen though you should select any single cell within your table before activating the Advanced filter.
What we would like you to do now is copy the headings and paste to cells A1:C1. Now select the range A6:C27 and name this MyRange. Then follow the steps below.
Type John in cell A2 and Dave in cell A3.
Select any cell within your Table and go to Data>Filter>Advanced Filter.
Select Filter the list in place.
Make sure your List range is correct. Advanced Filter will automatically pick up your list range as you have clicked in a cell inside your list and your list is set up correctly. The List range should be A6:C27, although you could type in MyRange if you prefer.
Put A1:A3 in our Criteria range box
Click OK
Our Table should now only display the information for John and Dave. All other rows should be hidden in the same manner as the AutoFilter hides rows. In other words all visible rows will have blue row numbers. To get or data back to how it was go to Data>Filter>Show All, do this now.
To add more names we could insert some more rows above our data and use these to add the extra names. However we believe it is better to have the extra rows there to begin with, and as we have said previously, leave at least three rows above your data and hide them.
Now all your data is visible again follow these steps.
Select any cell within your Table and go to Data>Filter>Advanced Filter.
Make sure our List range is correct. It should be A6:C27 (MyRange)
Put A1:A3 in our Criteria range box
Check the Copy to another location option.
Click the collapse dialog box to the right of the Copy to box.
Select cell F6 then click the expand dialog box.
Click OK
This time instead of the names that do not meet the criteria being hidden they are simply not included in the copied table.
We could also extend our criteria to not only show John and Dave's information, but to show only John and Dave IF they are aged between 25 and 52. To do this we would put: >24 in the cell directly below our copied heading Age and <53 in the cell below this. Now apply the Advanced Filter and include the new criteria in the Criteria box ie; A1:C3, although our new criteria for the age is in cells C1:C3. It wont matter that there is no criteria in cells B1:B3 as blank cells are ignored.
Now return the Worksheet to how it was originally. In other words have the table only, but retain the copied headings in row 1.
We can also use formulas as our criteria when using Advanced Filter, which can make our filtering capabilities even more complex. However any formula we use:
Must evaluate to either TRUE or FALSE.
The formula we use, must use a relative cell reference to the first data cell in the column we want to filter.
The range within the formula itself must be an absolute reference.
If we do not apply these three rules the Advanced Filter will not work as expected.
So let's say we wanted to find the information on the person who has the third largest pay rate. To do this we need to place the formula: =B7=LARGE($B$7:$B$27,3) in cell B2. Where B7 is our first Pay Rate and $B$7:$B$27 is the entire range below Pay Rate.
When we use the Advanced Filter this time we must not have our column heading Pay Rate in cell B1. We would still refer our Criteria range in the Advanced Filter dialog to: $B$1:$B$2 but we cannot have a column heading in B1 that is the same as one in the Table. In other words we use the formula cell and a blank cell above it, or type any text just as long as it is not the same as a column heading from our Table. For this example we will leave it blank, so delete the words Pay Rate from cell B1. Notice also that we are using $B$1:$B$2 and not $A$1:$C$2. This is because we can only include cells which do have a criteria set.
Now follow these steps:
Select any cell within your Table and go to Data>Filter>Advanced Filter.
Make sure our List range is correct. It should be A6:C27 (MyRange)
Put B1:B2 in our Criteria range box
Check the Copy to another location option.
Click the collapse dialog box to the right of the Copy to box.
Select cell F6 then click expand dialog box
Click OK
You should now have Jim - $21.00 - 40 as your result.
The other very handy feature of the Advanced Filter is that we can use it to create a List of Unique records only. To do this we simply need to check the Unique records only box and leave the Criteria range box blank. When we use this option it is usually using a single column as our List range. Try this to see what we mean. But first remove all data leaving only your table.
Select any cell within your Table and go to Data>Filter>Advanced Filter.
Make your List range C6:C27
Ensure the Criteria range box is blank.
Check the Filter the list, in place option.
Check the Unique records only option.
Click OK
You should now have a filtered list which only shows one occurrence of each age!
We could also use this same method to show only one occurrence of duplicated row data, try this:
Copy A12:C12 and paste over the top of A11:C11
Select any cell within your Table and go to Data>Filter>Advanced Filter.
Make your List range A6:C27
Ensure the Criteria range box is blank.
Check the Filter the list, in place option.
Check the Unique records only option.
Click OK
This time, row 12 will be hidden as it is a duplicate of row 11.
We have included a Workbook example of Advanced Filters (ExcLev3L02b.xls) to give you an idea on how they can be used. There is also some good information in the Excel help under: Filter a list using advanced criteria that is worth reading. As with the use of most formulas in Excel, it can take some trial and error before we get the result we want. However, making mistakes is a great way to learn!!!