excel 18 advancedfiltering

8/12/2019 Excel 18 AdvancedFiltering

http://slidepdf.com/reader/full/excel-18-advancedfiltering 1/14

Microsoft Excel 2010

Advanced filtering

When filtering your data according to more than one filter criterion, AutoFilter only allows you to select one

group of criteria. If you need to select more than one group, you can do this using an advanced filter.

Advanced filters also allow you to filter your data based on calculations. For example, you could set the

filter to convert a value in imperial units to a value in metric units before selecting all values above a certain

value in metric.

Setting up an advanced filter

You will have to make some alterations to your spreadsheet in order to set up an advanced filter. To

specify your filter criteria, you must first duplicate the row containing your headers (the labels for your

columns).

Duplicating the header rowTo duplicate your header row:

• Select the entire top row by clicking on the number at the left-hand end of it

• Click the copy button on the home tab of the ribbon

• Click the insert button

• Press the escape key to exit copy mode

Select the row by

clicking on the

row header –

then click copy

Click the insert button to

duplicate the top line –

then press the escape key to exit copy mode

This will duplicate the

data headers for your

columns




Creating the cr iteria rowsYou must then create one blank row for each set of criteria that you want to include in your filter. Criteria

rows should be between the two data header rows.

To create a criteria row:

• Select the second data header row

• Click the insert button on the home tab of the ribbon

• Repeat until you have all the criteria rows you need

If you decide later that you need more groups of criteria added to your filter, you can add more rows. If you

have added more rows than you need, then you must be careful to select the right ones when you apply

your filter.

NOTE: you can create your second header row and criteria rows anywhere in your spreadsheet as long as

you specify where they are when applying the filter

To create criteria rows,

select the second row of

data headers Click the insert button to create blank

rows between the two sets of headers

The criteria for

your filter will be

entered into the

blank rows –each row forms a

group of criteria




Advanced fi lter cri ter ia

To perform an advanced filter, you will need to set the filter criteria in the blank rows you have created.

Each criteria row forms a group of criteria which must be found in combination in a row of data for that row

to be shown when the filter is applied – items which do not fulfil all the criteria in a row will be hidden by the

filter.

Each cell containing a criterion has a position within a row and a column. The column indicates which

column of the data that criterion will be applied to, and the row indicates which criterion group that particular

criterion belongs to.

Defining criteriaCriteria are defined using formulae which return either “true” or “false” as their results. The filter will select

only those records where the result of the function is “true”.

Filter criteria are very similar to the logical tests applied when constructing an IF function.

In effect, each criterion asks question of the cells in its column to which there is either a “yes” (true) or “no”

(false) answer.

Comparing numerical valuesTo set your criteria for columns containing numerical data, you will need to construct an expression which

compares the numbers in the column to either an absolute number or the result of a function or formula. To

do this, you will need to construct a formula using comparison operators – less than, more than, equal to

and so on.

For example, if we have a spreadsheet containing information about a series of food orders, we can set the

filter to pick out all orders over £100 by constructing a filter criterion which asks the question “is the order

cost over £100?”

We will enter the filter criterion into the top blank row, in the order total column. To ask the question, we will

use a comparison operator, and type in:

When we apply the filter, it will then select all records where the value in the order total column is more than100.

Excel needs to read filter criteria as text rather than as formulae. Therefore, you may need to put an

apostrophe at the start of your criterion to indicate that it is not a formula (particularly if you are using an

‘equals’ criterion, as without the apostrophe Excel will try to construct a formula from what you type into the

cell). This apostrophe will not display in the cell, but will show in the formula bar if you select the cell.

If you are using < or > at the start of your criterion, Excel will automatically add the apostrophe for you.

>100

http://www.mmu.ac.uk/itservices/helpguides/Excel-8a-IF.pdf







Comparison operatorsThe comparison operators you can use in constructing filter criteria are as follows:

Criterion OperatorEquals =

Not equal to <>

Greater than >

Less than <

Greater than or equal to >=

Less than or equal to <=

Filtering for specific textIf you are filtering for cells containing particular bits of text, you will need to put inverted commas around

the text to ensure that Excel searches for the right word or phrase.

For instance, we could search for all orders processed by Julia Joyce. To do this we would need enter the

criterion into column C as that is where the employee names are.

To search for a particular name, we would type in:

We use the ="=[text]" construction because Excel treats the equals sign as an indicator that we are

entering a function or formula, so if we just put in =Julia Joyce, Excel would try to look for a function starting

with ‘Julia Joyce’. The inverted commas tell Excel that it is looking for the set phrase meaning ‘equal to

Julia Joyce.

Picking out cells which begin with certain words or charactersTo search for text that begins with a particular string of characters, simply type an apostrophe and then

the string of characters.

For example, if you are looking for all items made by Sir Rodney, your criterion would be:

This asks the question “does the cell in this column begin “sir rod”?” The filter will return results where this

is true – so it would return cells containing either “Sir Rodney’s Scones” or “Sir Rodney’s Marmalade” as

both begin “sir rod”.

NOTE: text filter criteria are not case-sensitive, so you do not need to capitalize words.

="=Julia Joyce"

‘sir rod




Defining a criteria groupTo filter for data that satisfies more than one criterion at a time, you should put all the criteria you want to

use in combination into the same row of filter criteria. The filter will only show records which meet all the

conditions set out in the row of criteria.

Each row therefore forms a criteria group.

For example, if we wanted to find out how many times a particular employee had processed an order for

more than £100, we would put two filter criteria in the first row – one to search for the employee name and

one to search for orders over £100.

Using multiple criteria groupsUp to this point, everything that we have looked at using advanced filtering can be achieved using an

AutoFilter. One big advantage of using an advanced filter rather than an AutoFilter is that it allows you to

set up multiple groups of criteria by using several rows.

When you use more than one row, the filter will show rows of data which meet all the criteria in either one

row or the other. Each row acts as a separate filter, and then all results for each row are shown.

To find all the instances where Julia Joyce

processed orders over £100, we need to

create two cr iteria on the same row




In this example, the first criteria group (in row 2) picks out all records processed by Julia Joyce where the

line total was over £100, and the second criteria group (in row 3) picks out all records where a quantity of

Teatime Chocolate Biscuits was ordered to a line total value of less than £150. The filter results will show

records which meet either the conditions in row 2 or the conditions in row 3.

Applying an advanced fi lter

Once you have set up the criteria for your filter, you will need to apply it to see the filter results. This

involves specifying a list range (the data to which the filter will be applied) and a criteria range (the range

of cells which contain your criteria). You can also choose to copy the filtered data to another location within your workbook, and can set the filter to only copy over unique records.

To apply an advanced filter:

• Go to the data tab on the ribbon

• Click on the advanced button in the sort & filter group

• In the advanced filter dialogue box, specify a list range – this is the data below your second

header row, and is usually selected automatically

• Enter a criteria range – this is the first header row and any non-blank criteria rows you have

created

• Click OK to apply the filter

NOTE: if you accidentally include a blank row in your filter criterion, the filter will not work as it will select

and keep all the cells in your data range.

Row 2 selects records where Julia Joyce

processed orders for more than £100

Row 3 selects records where an order for

Teatime Chocolate Biscuits was made

which totalled less than £150

The results for this filter will show items

where either the row 2 conditions are met

or the row 3 conditions are met




As with an AutoFilter, the row header numbers will appear in blue for your filtered data to indicate that some

rows are hidden.

To apply an

advanced filter,

set your criteria

in the top few

rows, then click

the advanced

button

Enter a list range –

the cells which

contain the data that

you want to filter

Enter a criteria range –

this tells Excel which rows

contain your criteria

You can use

the roll-up

buttons to

select your

ranges using

the mouse

Click OK to

apply the filter

The row headers

will appear in

blue in your

filtered sheet to

show that some

rows are hidden




Showing fil tered results in another locationExcel can place a copy of the filtered results from your data into another portion of your worksheet so that

you can refer to both the original and filtered results. The copy of the filtered data remains available even if

you clear the filter from the original data.

You can then copy the filtered data into another worksheet or workbook if you need to.

To copy filtered data to another location:

• Set up your filter criteria

• Click on the advanced button on the data tab of the ribbon

• In the advanced filter dialogue box, specify the list range and the criteria range for your filter

• At the top of the dialogue box, click the copy to another location button

• In the copy to box, specify the cell which will form the top left-hand corner of the filtered data*


• *you can do this by clicking on the cell. Make sure that there is space for the rest of the data to be

pasted around this cell – it is a good idea to choose a cell to the right-hand side of your original

data.

To extract the filter results to a

different place on your worksheet,

click copy to another location

Click on a cell in your sheet or type

here to specify where you want the

top-left cell of the extracted data to go

The extracted datawill be pasted into

your sheet starting

in the cell you have

specified




Adjusting and reapplying an advanced fi lter

If you need to alter the criteria for your filter, or add more filter criteria rows, you can do this by adding in or

changing the relevant cells in the filter criteria, and then clicking on the advanced button on the data tab of

the ribbon. You will then need to follow the usual steps to reapply the filter.

Removing duplicated records using advanced filter

In the process of creating an advanced filter, you can have the filter remove duplicate records from your

data. You do not have the option to review the duplicates before you remove them using this method.

To remove duplicates using an advanced filter:

• Set up your filter criteria

• Click on the advanced button on the data tab of the ribbon

• In the advanced filter dialogue box, specify the list range and the criteria range for your filter• Tick the box for unique records only


Creating more complex filter criteria

You may find that you need to increase the complexity of the criteria that you put into each of your criteria

rows. You can do this by using formulae and functions to perform calculations to make your criteria.

Using a formula or function as a criterionIf you are using a formula or function as a criterion, you must make sure that the formula you choose gives

a result that is either ‘true’ or ‘false’.

Examples of where this is required include filtering for numbers which fall between two values, or looking

for items which are a certain amount or percentage above the average value.

When entering a formula or function, you should use the first line of your data to enter any cell references

into the formula. For instance, here our data starts in row 6, so we will use row 6 in any formulae we

construct in the filter criteria.

NOTE: The formula will need to be written in a column which does not contain data.

To remove duplicate records

during the filtering process, tick

the unique records only box

As the data to be filtered begins in row 6, we will use

cell references from row 6 in our function criteria




Filtering for numbers between two valuesThis is an example of using a function as a criterion.

If you want to pick out records where the value in a particular column falls between two values, you can dothis by creating a criterion using the AND function. This lets you set both the upper and lower limits for your

value within the same criterion cell.

The AND function in our criterion cell will look like this:

To use this function in a filter, it must be in a separate column to the data being fi ltered. In this instance

we will put the function in column K.

When the filter is applied, this example will now show all items where the line total is between £100 and

£500.

=AND(J6>100,J6<500)

Any criterion requiring calculation based

on your data must be put into a column

which does not contain data

Cell references in

calculated criteriashould refer to cells

in the top row of data

Functions or formulae used in criteria must give the results

“TRUE” or “FALSE” – the AND function will return “TRUE” if

both these conditions are met and “FALSE” if they are not




Filtering for values more than 50 per cent above the average valueThis is an example of using a calculation (containing a function) as a criterion.

To select records based on a calculated value, you will need to use a column that does not contain anydata. You should then construct a formula for the calculation you will perform. In this instance, we will use

the AVERAGE function and multiply it by 1.5 to give a value 50 per cent above the average. We will then

use a comparison operator to make the formula into a true/false statement – either the item is more than 50

per cent above average, or it is not.

For calculating the average, we will need to use an absolute reference to ensure that all values are being

compared to the same average.

The formula we will use for this calculation is:

When this filter is applied, it will compare each value in column J to the average for that column, and pick

out those values which are more than 1.5 times the average value.

NOTE: If you add more data to the worksheet, you will need to make sure to update the absolute cell

reference to include the new data before applying the filter.

=J6>1.5*AVERAGE($J$6:$J:$2091)

The greater than symbol

ensures that this formula

gives a true/false response

The calculation for the

average needs to use

absolute references

so that the averagedoes not change as

each value is checked

The first equals sign tells

Excel that it will need to

make a calculation




Wildcards

A wildcard is a character you can insert into a text filter criterion in order to account for missing characters

or misspelled words. Where a wildcard is used, the filter will skip over any text containing the wildcard

characters and find items which match just the beginning and end of the cell contents.

There are three different types of wildcard that you can use:

Wildcard character Use Example

Question mark (?) Skipping a single letter wom?n – would find both“woman” and “women”

Asterisk (*) Skipping more than one letter Re*tion – would find “reaction”,“relation”, “revelation”, “repetition”

and so on

Tilde (~) Searching for characters whichare usually wildcard characters –used in combination with eitheran asterisk or a question mark

Why~? – would find “why?”(“why” without the question markwould be ignored)

Calculations performed on fil tered data

If you want to perform a calculation on a range of data which you will then filter, the calculation will not

usually take account of the filter you apply. This means that if, for instance, you calculate the averagevalue of a range, that average will not update to exclude the data you have filtered out.

To create a calculation which updates when you apply a filter, you should use the SUBTOTAL function.

The SUBTOTAL functionWhen you use the AutoSum feature it will automatically insert a SUBTOTAL function into the selected cell.

This function has two required parameters: a function number and a range of cells. The function number

added by default is one which includes hidden cells. To alter this so that hidden cells are not included in

the function output, you will need to add 100 to the function reference number.

=SUBTOTAL(109,J6:J2091)

The function number tells Excel

which calculation to perform –

function numbers above 100 will

ignore any hidden cells when

making the calculation

You will also need to enter a

range to show Excel which cells

to perform the calculation on




You can also create a SUBTOTAL function by typing it into the cell rather than using the AutoSum button.

If you are doing this, you will see a list of function numbers and can choose the one most appropriate to

your calculation.

Finding blank cells

If your data is missing certain information, you may want to perform a filter to find the empty cells so that

you can check and add the missing information.

To do this, you can use the ISBLANK function.

The ISBLANK functionTo use the ISBLANK function, select a criterion cell above an empty column. The ISBLANK function

requires just one parameter: the reference for the cell to be checked. This will usually be the first cell of a

column of data to be checked.

In this example, the data begins in row 6 and there are blanks in column D, so we will construct the

ISBLANK function using D6 as a reference:

If you are typing in the

SUBTOTAL function, you will

see a list of possibly

functions – double-click on a

function number to use it

Numbers over 100 will

ignore any hidden cells

=ISBLANK(D6)

The ISBLANK funct ion searches

for blank cells – if the cell is empty

it will return “true”, and if there is

anything at all in the cell it willreturn “false”

The parameter for this function is

the cell reference for the first data

cell in the column to be checked




Clearing an advanced f ilter

To clear the results of an advanced filter, go to the data tab on the ribbon and click on the clear button in

the sort and filter group. This will unhide all your filtered results but will not remove the filter criteria.

You do not need to clear a filter before applying new criteria – simply adjust your filter criteria and then click

the advanced button again.

If you have chosen to copy your filter results to a different location while applying the filter, your copied

results will not be cleared when you clear the filter from your main data. If you pasted your filtered data

alongside your original data, when you unhide the original, the pasted data will rearrange itself so that there

are no blank cells where there were hidden rows before.

To clear an advanced filter, click

on the clear button – your

criteria will remain in place but

your hidden cells will be shown

excel 18 advancedfiltering

Documents