FILTERS, ADVANCED FILTERS

AND RANGE NAMES

LESSON WORKBOOK:  Level 2 Lesson 6 2007.xlsx

In this lesson we will look at Filters, Advanced Filters and Range names. Filters and Advanced Filters 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 Filters and Advanced Filters. The ability for these two features to operate to their full capabilities can often hinge on the data they are being used on.

Table and List Guidelines

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 Filter and Advanced Filter. 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 Filters and Advanced Filters.

If we follow these guidelines as close as possible, using Filters will be a relatively easy task.

Filter

Excels Filter 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 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! 

There are three types of filters you can create: by list values, by format, or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both. To get the best results, it is best not to mix the different types of data within a column such as text and numbers or numbers and dates.

To apply Filters to your table or list click within any single cell of your data and go to Sort & Filter options under the Data tab.  Select the Filter option. 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 Filters will be applied to all headings. If not, you can select your headings and then go to Sort & Filter options under the Data tab and select the Filter option. You will see the Filters as small grey boxes with arrows on them.  Click on any one of them and you will see a number of options.  We will look at the data in the large white box first.  You should see:

Let's look at each.

Select All

No big secrets here! This will simply show all your data in the column chosen if your data is being filtered down.

While the (Select All) option is always available it will have no effect on unfiltered data. You can tell if a list or table is currently filtered as the arrow on the grey box changes and all visible row numbers will turn blue.

< A list of your data for that column>

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.

Lets look at the other options available to us under the Filter button.

Sort A to Z/Sort Smallest to Largest

Selecting this option sorts your filtered list in A to Z order if you have text in your columns or Smallest to Largest if you have numbers in your columns.  Note that even though your Filter switch is active, there are no hidden rows.

Sort Z to A/Sort Largest to Smallest

The reverse of the previous option, selecting this sorts your filtered list in Z to A order if you have text in your columns or Largest to Smallest if you have numbers in your columns. Again, note that even though your Filter switch is active, there are no hidden rows.

Sort by Color

Selecting this option will sort your list by either font or cell colour (Note that there is an Automatic option here.  Automatic is the main colour of your printer (usually, but not always black).  There is also an Custom Sort option here, that will enable you to easily customise your filters.  Again, no hidden rows here either.

Custom Sort

When you select Custom Sort from the Sort by Color options, you will see the Sort Dialog and the headings will be Column, Sort on, Order.  These are pretty self explanatory and allow you to sort your list by more than one column at a time. 

Clear Filter From ....

Selecting this option will clear the Filter from the column you are currently clicked in.

Filter by Color

Selecting this option will filter your list by either font or cell colour. 

Text/Date/Number Filters

Using this option you can drill down and glean much more detailed information from your data.  If you have text in your column, the option will read Text Filters, if you have dates in your column, the option will read Date Filters.  If you have numbers  in your column, the option will read Number Filters.  Options are different for each of the three data types.  Each data type has a Custom Filter option at the end of the list.

Once you have selected the setting for your criteria, a dialog box will pop up with your selection in the first white box on the left.  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 (stated on the dialog box) 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 Filter 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.

Compound Filtering

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 the Filter). 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.

Clearing a Filter

It is really easy to clear Filters.  If you want to clear them all, select the Clear button from under Filter & Sort options on the Data tab.  Alternatively if you only want to clear a Filter on one column, select the Clear Filter From .... option.

Note that there are a number of the most common options attached to the right mouse button when you right click from within a list.

Advanced Filter

The Advanced Filter is used when you need to either filter your data down in a way that the regular Filter cannot (like with using calculations) and/or you want the result of your filtered data to be copied to another area on the Worksheet. The Advanced Filter dialog can be found by going in the same location as the Filter by going to Sort & Filter options under the Data tab and selecting Advanced.

You will see the option to either Filter the list, in place or Copy to another location. If you opt to Filter the list, in place, your table or list (list range) will be filtered in the same way as the Filter would do so. In other words it will hide all rows that do not meet the criteria as specified in the Criteria range: If you opt to Copy to another location your list range that meets the criteria as specified in the Criteria range will be copied to another location on the same Worksheet. The Copy to another location option does not allow you to copy your results to another Worksheet! If you do wish to do this simply opt to Copy to another location on the same Worksheet, then cut and paste the results to another Worksheet.

You may remember that point 2 in Table and List Guidelines was:

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.

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.

Using the list of names example, we would have a copy of our heading Names in, say cell A1.

Our table would then only display the information for John and Dave. To add more names we would 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. Note that there are no drop-down arrows to the right of your list headings, but the row numbers have gone blue as in the case of Filters.

We could, of course, do exactly the same and use the Copy to another location option, the only thing we would need to do is place a cell reference in the Copy to: box to start our new table in.

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 older than age 24. To do this we would put: >24 in the cell directly below our copied heading Age and >24 again in the cell below this. Then apply the Advanced Filter and include the new criteria in the Criteria box ie; A1:D3, where our new criteria for the age is in cells D1:D3.  It does not matter that there is no criteria in cells B1:C3 as blank cells would be ignored.

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:

If we do not apply these three rules the Advanced Filter will not work as expected.

So let's say we have the column heading Pay Rate in cell B6 and we wanted to find the information on the person who has the third largest pay rate. To do this we could use the formula: =B7=LARGE($B$7:$B$27,3) place it 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. In other words we use the formula cell and a blank cell above it. We can place data in cell B1 but it cannot be a column heading from our table.

We have included a Workbook example of Advanced Filters 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.  But as we have said before "making mistakes is a great way to learn".

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 record only box and leave the Criteria range box blank. When we use this option though we can only use include a single column list range.

It is also possible to create a list of Unique record only that also meet a criteria we have set. To do this we simply refer the Criteria range to a range that contains a criteria.

Named Ranges

In Excel it is always good practice to name ranges, formulas and constants with meaningful names. Before we do so though, it may pay to have a quick refresher on some rules that must be adhered to when naming any of the above.

See below from the Excel Help

Guidelines for naming cells, formulas, and constants in Microsoft Excel

What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.

How many characters? A name can contain up to 255 characters.

Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.

Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

 end of Help Text

Let's look at naming ranges first.

Naming Ranges

By far the quickest and easiest way in most cases is to simply highlight the range of cells and type the name in the NameBox to the left of the formula bar. This, however, does limit us to naming ranges only, ie; you cannot use this method to name constants or formulas.

There are two other methods can be found by going to Defined Names options on the Formula tab.

Let's look at each one of these in turn.

Name Manager

When you select the Name Manager option on the Formulas tab (or push Ctrl+F3) you will see the Name Manager dialog box. It is in this box that you will see a list of all Names in the Workbook. By selecting any one of these Names you will see the Worksheet and cell address they refer to in the Refer to box at the bottom of the Define name dialog box. If you place your mouse insertion point anywhere within the Refers To box while you have a name highlighted, you will be taken to that location. This is also where you would go to delete any redundant Names. You would do this by simply selecting the Name and clicking Delete.

The Define Name dialog box is arguably the most useful for naming as it is here you can write formulas for your ranges and the only limit is ones own imagination. See Using Names in Excel. for some examples. It is also here that we can name Formulas so we can use them over and over again. These might be as simple as multiplying two constants or a deeply nested Formula. For example suppose you have a business with "x" number of employees and 5 different levels of pay rates. You could do the following.

You will get the hourly rate for the person named in the cell directly above multiplied by 40. Whenever and wherever you use this formula it will always use the correct hourly rate for the name in the cell directly above.

This is just a simple example of the sort of things you can do with named ranges.  As you get better at writing formulas and using Excel Functions you can start to come up with very imaginative ways of doing this type of thing.

Use in Formula

This option allows you to select the name you want from a list of all names within the Workbook. 

Lets say you were using the =40*HrlyRate formula in a cell and were a bit unsure of the name you had applied. You would type: =40 and then select Use in Formula under Defined Names options on the Formulas tab, then HrlyRate from the list and click OK.   Excel will insert the name for you.

There is also a Paste Names option here.  If you select this Paste Names and then Paste List, you will create a list of all the names in the Workbook in one column and all their associated references in the column to the right. Just make sure you don't have any data that will be replaced. If you do just use the Undo command (Ctrl+Z).

Create from Selection

This dialog (Ctrl+Shift+F3) will allow you to quickly use column or row headings as range names. Let's say you had a table set up on your Worksheet and you wanted to use the column headings as names for all the data below the heading. You would select the entire table (headings included) push Ctrl+Shift+F3, check the Top row CheckBox only and click OK. Excel will apply the names of each of your column headings and refer them to the data below the heading.

Lets say you have the names of 1000 employees in cells A2:A1002 and in cells B1:B10 you have the names of different locations that your employees need to travel to on a daily bases. Each time an employee travels to a particular location you put a tally in the intersecting cell of the Location and Employee name.

In other words, if Bill has been to New York five times this year, there would be a number 5 in the cell that intersects the column heading New York and the row heading Bill. Now Let's say that the pay office needs to know how many times Bill (or any employee) has been to New York (or any location). You could select the range A1:K1002 (your entire table) push Crtl+Shift+F3 and select the options Top row and Left column and click OK.   Then in any cell you could type the formula:  =Bill NewYork and Excel will return the value of the cell that intersects Bill and New York. You MUST push the Space bar after Bill.  In other words you must have a space between Bill and NewYork.

Apply Names

This option is found by selecting the drop down to the right of Define Names under the Formulas tab and is used to replace cell references within formulas to their range names. Let's say you have been using a simple SUM function like: =SUM(A1:A100) throughout a Workbook. You then decide to name the range A1:A100 Totals. When you do so all your =SUM(A1:A100) formulas will still refer to the range address rather than its new name (Totals).

This is where you would use the Apply dialog box. You would simply activate it while you have a single cell selected and select the name: Totals and click OK. Excel will go through the Workbook and replace all formulas that are using the range A1:A100 with the name Totals. If you did the same with more than one cell selected, Excel will only replace references in the selected range. To apply more than one name hold down the shift key and select the names.

When we applied the name Totals, Excel by default will use absolute references ie; $A$1:$A$100. If we had a mix of absolute and relative references in our formulas ie; A1:A100 and $A$1:$A$100 we may only wish to replace the ones using $A$1:$A$100 (absolute references). In this case we would uncheck the Ignore Relative/Absolute Checkbox then click OK and Excel will only replace the reference $A$1:$A$100 with the name Totals. So basically clearing this check box will make Excel replace absolute references with absolute names, relative references with relative names, and mixed references with mixed names.

Leaving the Use row and column names box checked will force Excel to use the names of row and column ranges that refer to the cells if the exact names for the cells can't be found.

If you click the Options>>> button you will see

Omit column name if same column:

Replaces the reference with the row-oriented name without including the column name. Clear the check box if you want to include the column name.

Omit row name if same row:

Replaces the reference with the column-oriented name without including the row name. Clear the check box if you want to include the row name.

Name order:

Row column Column row

When a cell reference is replaced by both a row-oriented and a column-oriented range name, click Row column. Click Column row if you want the column name to appear first.

Summary

So as we can see we can use Excels Filters (Advanced and regular) to display virtually any criteria we want, so long as we have set up our table or list is a sensible manner. One aspect we have not mentioned is that Filtered data in Excel has a special Function that is designed to work with a Filtered range. It is called SUBTOTAL and can be used to return the result of rows that are visible as the result of Filtering. There is a good description of this easy to use Function in the Excel help under: SUBTOTAL.

We can also use Names in our Workbooks that can not only make our formulas easy to read but also very easy to edit en masse. We can also use Names to create a formula that we may need to use often or is one that refers a table of data. The only real limit to this is our own imagination and determination.

 

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.