 |
|
|
|
|
Download the associated Workbook for this lesson
In this lesson we will look at AutoFilters, AdvancedFilters and Range names. AutoFilters and AdvancedFilters will refer most often to a range of data set up in a table or list, while Range names can refer to any type of data. We will start by looking at how a table or list should be set up to take full advantage of AutoFilters and AdvancedFilters. The ability for these two features to operate to their full capabilities can often hinge on the data they are being used on.
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 I 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 another 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 lists and tables. This will aid Excel in recognising them as separate.
Avoid blank cells within your data.
Sort your list or Data. This is 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.
The first type of Filter we will look at is the AutoFilter.
Excels AutoFilter is one Excels most useful features as it allows us to quickly and easily view data from within a table or list by hiding data we do not want to view. It does this by hiding rows that are within our data. One thing to keep in mind about the fact that it hides rows is that all 256 columns within the row will be hidden. It not possible to hide only a specified amount of columns. So hiding a row means the entire row!
To apply AutoFilters to your table or list click within any single cell of your data and go to Data>Filter>Auto Filter. Excel will detect your data by using what is known as the Current Region of the selected cell. So, as long as the table or list is set up correctly the AutoFilters will be applied to all headings. If not, you can select your headings and then go to Data>Filter>Auto Filter. You will see the Filters as small grey boxes with arrows on them. Click on anyone of them and you will see:
(All)
(Top Ten....)
(Custom....)
< A list of your data for that column>
The first three will always be the same and have the same meaning. Let's look at each in turn.
No big secrets here! This will simply show all your data in the column chosen if your data is being filtered down. There is also a Show All option under Data>Filter if the AutoFilters are hiding any rows. However, selecting Show All will unhide filtered rows for the entire Worksheet and not just the selected column.
While the (All) option is always available it will have no effect on unfiltered data. You can tell if a list or table is currently filtered by the arrow on the grey box (it will be blue) as will the visible row numbers.
This option only applies to numeric entries. So if the column you opt to filter contains no numeric entries, selecting it will have no effect. When you select this option you will see the Top Ten AutoFilter dialog box. Although the name says Top 10 we can change this to be the top or bottom of any number. It can also apply to a percentage. We simply set the first box to Top or Bottom and then spin (or type) up or down to the number we want. Be aware though the number must be between 1 and 500. If you select a number between 1 and 500 and it is not possible to achieve the result, Excel will still accept it and make your data appeared filtered (blue arrow and row numbers) even though it's not. In the final box you choose either Item or Percent.
A good example of this would be a list of consecutive numbers between 1 and 100. If we chose Top-50-Item we would see the numbers 51 to 100 and all other rows would be hidden. The exact same would apply if we chose Top-50-Percent. If we then applied Bottom-10-Item or Bottom-10-Percent we would see the numbers 1 to 10. In other words even though our list is only showing 51-100 the criteria is applied to all rows whether they are filtered or not. The same, however, does not apply if another column within your data is currently filtered. In this case, using the Top Ten would apply only to the currently visible rows.
When you select Custom from your filter options, you will see the Custom AutoFilter Dialog and the heading will be Show Rows Where: with the name of your chosen column heading directly below it. Below this there are four boxes.
Starting from the top left box - to the right of this you will see a drop down arrow. Click the arrow to select the settings for your criteria, eg; equals, does not equal etc. These options are always the same regardless of the type of data your table or list contains. Once you have selected the setting for your criteria, you would then move to the box immediately to the right. This is where you select the actual criteria that your setting will be applied to. If you click the drop arrow in this box, you will see a list of all unique items that are within the selected column. So for instance if you selected the Custom Filter option from a column that contains a list of data, let's say the 12 months of the year, and selected the setting greater than and the criteria April, and clicked OK, then you will see all the months that are greater than April. The reason we have used this example is to again stress the importance of using real dates in your cells and formatting them accordingly. In other words, if you simply had text dates, or text month names in your cells, and custom filtered as described above, Excel would hide only the row containing the word April. In other words, you would not get the result you expected.
You will probably also have noticed that you can use a question mark (?) to represent any single character and the asterisk (*) to represent any series of characters. An example of using this might be if you had a list of people's names and you only want to view all the names containing the letter g. To do this you would choose equals from the setting area and in the criteria box you would type *g* and select OK. You would be presented with a list of names that contain the letter g within them. You should note that the criteria you set is not case sensitive.
If you have a list which actually contains either one or both of these wildcard characters, and you wish to filter your list to display them only, you need to first type ~ (Tilde) followed by the wildcard character.
You will also have noticed that there are two option buttons on the Custom AutoFilter Dialog. These are And and Or. Using the same example as above (names) we could select begins with, G, select the or option and in the two boxes below select begins with and L. This will produce a list of names beginning with either G or L. If we chose begins with and G, then selected the and option and selected does not contain and n, we would get a list of all names beginning with G and not containing the letter n.
The terms in the Criteria Settings Box that contain the words greater or less will apply to numeric data only. All other settings apply to both text and numeric data.
This heading is in reference to all the items that you have in your currently selected column. The list will always be in ascending order, ie; A - Z, 1 - 100 regardless of how your data is sorted. You will only see one instance of each item regardless of how many times it appears within your list. In other words, you could have the name John in your list 20 times, but you will only see it appear in this list once. If however, you selected John from your list using a filter, you would see all 20 rows containing John and all other rows between hidden. In other words, selecting an entry from this list is exactly the same as using the Custom option with the setting equals to whatever.
Compound filtering in this context refers to filtering a table by more than one column. Doing this enables us to filter down a table by many different criteria. As mentioned before, when you apply a second criteria to a column that is already filtered, the first criteria will be removed. This is as opposed to applying a second criteria to your table in another column.
By doing this, we are telling Excel that we only want to filter down all visible rows (or rows that have not been hidden by an AutoFilter). An example of this could be a table with 10 columns and "X" amount of rows, with the first column being headed Names and the fourth column being headed Age. We could apply a filter criteria to the Names column to show only John, and then apply a filter to the Age column to show only ages greater than 20. Doing this our table would end up displaying only the information relating to people with the name John who are older than 20.
At times you may notice at the very bottom of your list displayed by the filter, the words (Blanks) and (NonBlanks). It can be a bit confusing as to why sometimes you may have these options, when other times you may not, even though you have a blank cells. Probably the best way to describe this is with a simple example. Let's use the same examples used above in Compound Filtering.
You may have blank cells within this table that are randomly distributed throughout. So long as you do not have 10 blank cells, all on the same row (the number 10 because we have 10 columns), you will have the options of Blank and NonBlanks if you select a column that contains blank cells. If however, you do have 10 blank cells, all on the same row, you will not have these options. In fact, Excel will, by default, assume that this is the end of your table. Again, this highlights the importance of avoiding blank cells if at all possible.
We can get by this by using a bit of trickery. There are a few ways in which this can be done.
Method 1
Probably the simplest method is to simply highlight your entire table, blank cells included, go to Edit>Replace, leave the Find What box blank and type any character you wish to use to represent your blank cells in the Replace With box.
Just be sure, use a character that is not in any way the same as any of your other data. The reason being that should you wish to remove these afterwards you want to be sure that you can use the Edit>Replace, type your character in the Find What box and leave the Replace With box blank. In other words, don't go using an alphabetic letter as you may end up inadvertently removing all those letters from your table.
Method 2
Create a dummy heading to the immediate left or right of your table, place any number or character in the first cell below the heading and use your fill handle to copy this to the last row of your data. Then apply the AutoFilter to include this dummy column. This will trick Excel into thinking that it is part of your table.
Method 3
Find your 10 consecutive blank cells on the same row, place any text or number in any one of the blank cells, apply your AutoFilter to the table, then remove the text or number you just typed in the blank cell. This again will trick Excel into thinking the cell that you typed your text or number in is still valid and exists within your table.