EXCEL

Filtering Data

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 filter.

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 criteria:

Find all rows where Rainfall is less than 30.

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 diagram below 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.

webmaster@allaboutoffice.co.uk