If you only want to look at data
meeting certain criteria you may be able to filter your worksheet to achieve
this. Filtering, like the name
suggests, is a method of sifting out the information you want to see and
temporarily discarding the rest, you can later remove the filter and see all
your data as it was before.
There are various methods of
filtering, the easiest is probably the Autofilter.
To set up an Autofilter, click on the Data drop-down menu and
choose Filter and then Autofilter. Drop-down arrows appear on the
column headings at the top of your list of data as shown below and you can
choose the information you would like to filter from these lists.
If we choose Top 10 for our filter
we can choose to see the top or bottom number of items or percent from these
values, a window appears as below where you can set up the criteria for this
Applying this filter to the rainfall
column in our data displays the filtered results shown below.
If you choose the Custom option from the Autofilter drop-down box you can
set up your own criteria for a filter as below.
This can allow you to see two categories of data at once.
This allows you to set two criteria
on one column. If you want to
filter by two different columns you can filter by one first and then use the
Autofilter drop-down arrow at the top of another column to place another filter
on top of the existing one. For
instance, we could filter out the top 10% of rainfall values and then filter out
the values for 2002 to see which months in 2002 fell in the top 10% of rainfall
over the period covered.
To remove a filter you can choose
the All value from the drop-down Autofilter list at the top of the
column. To remove the Autofilter
boxes, click on the Data drop-down menu and choose Filter and then
Autofilter, your worksheet will now return to normal.
The Advanced Filter option under the Filter
section of the Data drop-down menu gives you the option of carrying out
much more powerful filters and copying the filtered data to another part of your
workbook. To set up the criteria
for your advanced filter you need to enter some details in blank rows above your
list to give Excel the criteria you wish to use.
There should be at least one blank row between your criteria cells and
the list as shown below.
The example above shows the data being filtered to show rows where the date is between 1
April 1991 and 31 November 1992, the filtered list will appear in place of the
current data. The criteria range
boxes in cells A1:B2 tell Excel that we want to select rows where the Month is
greater that 31 March 1991 AND Month is less than 1 December 1992. The fact that the values are both on the same row denotes that there is
an AND joining these criteria.
Here are some examples of different
Find all rows where Rainfall is less
Find all rows where Rainfall is less
than 30 OR Rainfall is greater than 140.
Find all rows where Rainfall is
greater than 29 AND less than 141 i.e. Rainfall is between 30 and 140 inclusive.
It is also possible to filter out
records based on a formula, this can be used to filter out all the records that
are less than or greater than the average or to find values that appear more
than once. In order to use a
formula as the basis of your filter criteria you need to have a blank cell above
the cell containing the formula, NOT a field heading cell as you would normally.
For instance, to filter out all the
records where the rainfall was greater than the monthly average we would set up
our spreadsheet as shown below
You can see the formula that is
entered in cell A2 in the formula bar at the top of the window, it is =C6>AVERAGE($C$6:$C$173),
you can tell whether you have entered the expression correctly because it should
evaluate to either TRUE or FALSE as shown above.
The formula is constructed by comparing the first value in the list (cell
B5 in our case) to the AVERAGE (or you could use SUM, MAX, MIN, etc) of the
whole range of the list (or you could refer to another group of data and compare
to that). Note that the value B5 is
a relative reference, this is important as Excel will increase this to B6, B7,
etc as it moves down the list comparing the value to see whether to filter out
that row. The aggregate function
AVERAGE has to refer to a range denoted by absolute
references ($B$5:$B$172) so that this range does not move as Excel moves
down the list. The
shows the advanced filter being set up using the formula in the
criteria, the Copy to location option is also being used to copy the
results of the filter to an area at the bottom of the worksheet.
Another useful application of
formulas in filters is to find items that have been repeated in a list.
The example below shows a list of names, some of which are duplicated.
To find those which have been duplicated we can enter the formula =COUNTIF($A$5:$A$500,
A5)>1 in our criteria section (where the value TRUE is showing below).
When you have set up your criteria formula, make sure you have
clicked on a cell within the list you want to filter and then click on the Tools
drop-down menu and then choose Filter and Advanced Filter. We have set up the filter
to copy to another location to help us see the duplicated names and have ticked
the Unique records only box so that we only get one item per name in our
filtered list. You can see the results of this advanced filter in column
D, the location set in the Copy to box in the Advanced Filter window when
we set the option to Copy to another location.
The filtered list will contain one
entry for each duplicated name, we have placed it in cell D3 onwards so it is
placed beside our original list. When
choosing the location to copy to you only need to select the top left corner
cell of the area you want to copy to. You
could later select the filtered list and sort it if you needed the unique names
in alphabetical order.
Click here to
return to the Excel index.