LESSON WORKBOOK: Level 3 Lesson 2 2007.xlsx
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 table
s 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 the 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 t 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 Sheet 2, 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 Level 3 Lesson 2 2007.xlsx
Click in the table on the Advanced Filter worksheet, on 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 over a million 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 the Data tab and under Sort & Filter select Advanced.
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. (Example 1 of the workbook)
To get or data back to how it was select Clear under Sort & Filter, 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 the Data tab and under Sort & Filter select Advanced.
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. (Example 2 of the workbook)
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. Try the following:
Put the heading Age in D1 (again, copying it from C1 is the best way).
Enter >24 in cell C2 and <53 in D2.
Apply the Advanced Filter and include the new criteria in the Criteria box ie; A1:D3, although our new criteria for the age is in cells C2:D2. It wont matter that there is no criteria in cells B1:B3 as blank cells are ignored. (Example 3 of the workbook)
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 the Data tab and under Sort & Filter select Advanced.
Make sure your 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. (Example 4 of the workbook)
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 the Data tab and under Sort & Filter select Advanced.
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! (Example 5 of the workbook)
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 the Data tab and under Sort & Filter select Advanced.
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. (Example 6 of the workbook)
We have included Workbook examples of the above Advanced Filters and a few more (Level 3 Lesson 2 2007.xlsx) to help you understand this very valuable feature of Excel. 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!!!
This seems to be another one of Excels little known or used features, but as you will see it can be a very handy little tool. The purpose of this feature is to allow a user to easily separate values that reside in a single cell and have them placed in their own cells.
Lets say we have a list of names,
addresses and phone numbers. The problem is, instead of having the names in
one column, the addresses in another column and the phone numbers in another
somebody has entered them all in the same cell. To make matters even worse,
you have been given the task of separating them all so that there is a First
Name, Surname and Address column! This is where the Text to columns
feature can be invaluable. We will use this as an example as this should give
you a good idea of its capabilities.
Select the cells A1:A7
Select Text to Columns under Data Tools on the Data tab.
Check the Delimited
option and click Next.
Select the Space option only and
click Next
Now
holding down the Shift key click
on each column heading (General)
except the first and last names.
With all the other columns selected, click the
Do
not import columns (skip) and click
Finish
As you will see we now have two columns of data, one for first names (column A), and one for surnames (column B)
Go back to the original data again and copy it. This time paste it into
cell D1
Repeat steps 3,4 and 5 but at step 5 use the Do not import columns (skip) option on all columns except the Address columns In other words skip the first name, second name and phone number columns. Click Finish
Now
as we have left column C blank we
place in the formula:
=D2 &" "& E2 &"
"& F2 &" "& G2 in cell
C2
Double
click the Fill handle of (or copy down) cell
C2
With all the formulas selected in column C, copy, then
right click and select Pastespecial and select
Values. Now
delete Columns D, E, F and
G
This can turn a 10 hour job into five minutes or less!
As you would have seen while in Step one
of the Text to column Wizard there
is also a Fixed width option we can use. This option will allow us to
place in Column breaks at any point. Using
this we could go as far as separating our data into separate characters. The
steps in this Wizard are fairly self-explanatory. If we cannot achieve our
result in one pass through the Wizard we need to "step outside the box"
and use a bit of lateral thinking.
Included in this lesson is some valuable information on Custom formats.
To access Excel’s Custom Formats go to the Home tab and select the
dialog box launcher in Number options, then select Custom from the
list (or right click and select Format cells) . Then all
we need to do is use any one of Excel’s existing functions as our starting point
and change the format to what we want. Although we are going over an existing
format, we will not be replacing it but rather adding to the list.
Excel offers us a very rich selection of formats that we can apply to our
cells in a Worksheet. But there are times we would like to add our own unique
type of format to a cell or range of cells. This is where
Custom Formats
can become very helpful. Before we start though, it is very important to
understand that altering the format of any cell, does not change its underlying value.
We can always see the underlying value of a cell by selecting it and looking in
the Formula bar or by selecting it and pushing F2. The reason we mention this is
because many people believe they can change a cell value by applying a different
format. For example try this
Type the number 5.6 in cell A1
Type the number 5.6 in cell A2
Select cells A1 and A2 and go to the dialog box launcher in Number options on the Home tab, then select Custom from the list
Type the Format 0 and click OK
Put the formula
=A1+A2 in cell A3
As you can see you have a result of 11. A result like this could be very confusing and cause many errors
in a spreadsheet. We have seen this many times where people have used Excel to
perform a calculation and when they cross check the result with a calculator
they believe that Excel has given them an erroneous result.
This would be a good time to mention Excel’s Option –
Precision as displayed. Make sure you are in a Workbook you do
not need. Go to the Office
button>Excel options and select Advanced on the left hand side of the
dialog. Under
Workbook options check the
Precision as displayed option button
and click OK. Say yes to the
warning, but take heed! Now double click in cell A3 (the one containing
A1+A2) and push enter. As you can see Excel will now give a result of 12. This operation cannot be undone! Close
the Workbook without saving.
The default formatting for all cells is the General format. Excel will always try to make an educated guess on the type of formatting you want by the way in which you enter a value into a cell.
If, for instance, you type a date Excel will apply the default date format as set in the Control Panel of Windows. If we type a currency symbol Excel will apply a currency format, type a percentage sign and Excel applies a Percentage format and so on. We can alter the default Time, Date, Currency and Number formats in our computers Windows options. Be aware though that the settings apply to all other applications, not just Excel.
When we use Custom Formats we can use what are called Placeholders to
tell Excel what format to apply. The type of placeholders that are used
are:
From Excels
Help
To format fractions or numbers with decimal points, include the following digit placeholders in a section. If the number has more digits to the right of the decimal point than there are placeholders in the format, the number is rounded to as many decimal places as there are placeholders. If the number has more digits to the left of the decimal point than there are placeholders in the format, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point.
# displays only significant digits; does not display insignificant zeros.
End of Excel's
help
We
could possibly tie ourselves in knots and confuse a relatively simple issue by trying to explain this in words. What we believe is a simpler method is to apply a standard number format to a cell, then select Custom and you will see how the Placeholders are used.Lets now look at the basis that Excel uses for number formats. The good
thing here is once we understand this we can use Custom Formats to inform users
of possible errors. We can do this by changing the font colour and/or the
display of the number entered. However, the colour display is not really
applicable any longer as since Excel 97 we can use Conditional Formatting
We will use a small example to explain how number formats work in Excel.
Just like an Excel Function has a syntax, so to do Number Formats! In
this context though the correct term is
Sections
and each section must be separated by semicolons.
Positive;Negative;Zero
We can elaborate on this by using:
Do
this if Positive; Do this if Negative; Do this if Zero
So we could use a Custom format like this:
"1 or higher";"less than 1";"Zero"
Then when we type a number in the cell that has the formatting it will tell us what the number is!
Try this.
Click in any blank cell on an Excel worksheet.
Go to the Home tab and select the dialog box launcher in Number options, then select Custom from the list and under Type enter the format "1 or higher";"less than 1";"Zero"
Click OK, then go to any blank cell and then type in a number and the relevant statement will be displayed according to the number entered. For instance if you type in a 2, the words 1 or higher will appear in your cell.
We could now take this one more step by
using a Custom Format like
[Blue]"1 or higher";[Red]"less than 1";[Green]"Zero":
This will not only display the appropriate text, but also a different
coloured font. So while Conditional Formatting has made the Custom Format
redundant as far as font colour goes, we can still use it for applying other
format types. The only colours we can use with Custom Formats are: [BLACK]; [BLUE];[CYAN] [GREEN];[MAGENTA] [RED];[WHITE];[YELLOW]
We mention above that the Sections for a Number format are Positive;Negative;Zero. While this is
true for numbers there is one more Section that follows after Zero. This is
Text, so the full syntax (Sections) for formatting is Positive;Negative;Zero;Text. By using this we can tell Excel do to
another Format type if text is typed into a cell. Using the same Custom Format
we applied to our number, change it to this:
[Blue]"1 or higher";[Red]"less than 1";[Green]"Zero";[Cyan]"No text please"
Now type any text in the cell and we will see "No text please" with a font colour of Cyan in the cell.
Ok, so we now know that the set order, segment, syntax (whichever) is Positive;Negative;Zero lets use this
for a cell or range of cells so that we suppress certain values, eg; We do not
want to see zeros in these cells, in this case we could use a format like:
General;-0;;@
In this example we have told Excel to not display a value of Zero. We
have achieved this by omitting the Zero segment in the Custom Format. We could
of course tell Excel to not display certain values by omitting a format in the
appropriate segment. This one will not display text
0;-0;0;
We can use this one to suppress error values in a cell that should return
a number. This is because an error value is text. However be cautious when doing
this as the error value is for a reason. To hide ALL entries in a cell we can
apply the format;
;;;
But again be cautious when using this.
We can also use comparison operators in Custom Formats and apply a format
accordingly. Lets say we wanted to
show decimal places for all values above 10, but no decimals for values below.
To achieve this we would use the Custom Format
[>10]0.00;0
This is saying: If the cell value is greater than ten apply the format 0.00 otherwise use the format 0
Possibly one of the most frequent questions we get is how can we have cells
display leading zeros without
applying a Text format to the cells. Well the answer is very simple. Lets
assume we have a thousand rows of numbers in Column A of a Worksheet. The
numbers range from 1 to 5000 and we want to have all numbers be displayed as six
digits with leading zeros, eg;
000254
000012
004587
000001
To have our cells displayed like this we would apply the Custom Format of 000000
We will now look at using symbols in cells automatically. Lets say we have a certain column in our spreadsheet where we want to collect temperatures. Rather than having to use the Character map we can apply a Custom Format. This is how we could do this;
Select the cells or the column
Go to the Home tab and select the dialog box launcher in Number options, then select Custom from the list
Type 0.00 then
hold down the Alt key and push 0176 on the numeric keypad and then
release the Alt key.
All our cells will now automatically display the degree symbol after the
number entered.
There many symbols we can use in Custom Formats and we can get the numeric code via the Character map or in Excel by going into the VBE (Visual Basic Editor) and typing "ASCII" into the Help section. Push Alt+F11 to get into the VBE, and then push F1 for Help. Search on the computer, not the internet.
When you use these you must always hold down the Alt key, type a zero followed by the Symbols ASCII number.
The last one we will look at is a very handy currency format we can apply to cells. Select a range of cells and type this format in the Custom format box; 0 "dollars and" .00 "cents" Now type any dollar amount in the cells.
So as you can see Excels Custom Format feature can be used in a lot more
ways than first meets the eye. The good part of it all is that no matter what
format we apply, the cells underlying value will not change. This is great
because even if our cell is displaying a format like
22
dollars and .25 cents
We can still use this in calculations. This means we can make our
spreadsheets a lot easier to read and have certain formats applied depending on
a cells value.
Be very careful if you apply the "precision as displayed" option as it cannot be undone and could ruin a perfectly good spreadsheet, I would strongly suggest NOT using it at all.
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.