Excel Filters
An Excel Filter is an excellent way to quickly analyse data. By selecting one or more variables you can quickly narrow down a huge set of data so that you’re only looking at the entries you’re interested in.
To apply filters simply highlight the row of headings at the top of your data and select Data, select Filter and then click on AutoFilter in the resulting menu. Once you’ve done this you’ll find that drop-down arrows appear next to each of your headings.
If you’re using Microsoft Excel 2007 or later you can apply filters by selecting the Data tab and clicking on Filter.
Now that you've applied an Excel filter to your data you can choose to view only certain records. The example above is a list of working hours for various members of staff, so it might be useful to view all of the hours for one particular member of staff. To do this, click on the drop-down arrow next to the heading you want to use – In this case ‘Staff member’. Depending on the version of Excel you’re using you’ll be confronted by one of these menus:
 |
 |
You’ll notice that the later versions of Office seem to have vastly superior capabilities for filtering in Excel. It’s certainly true that filtering has been made simpler, but very little functionality has actually been added.
Returning to our working hours example, the most basic form of Excel filtering would be to view all of the rows relating to a single staff member. To do this simply select the data you want to filter by, in this case we might want to see all of Hannah’s working hours. In Office 2003 or earlier it’s just a case of clicking the option you want in the drop-down list, whereas in Office 2007 or later you’ll need to deselect everything you don’t want to see and then click on ‘OK’. This does take longer, but it also presents you with the advantage of being able to keep as many options selected as you want to see.
Once you’ve selected the filter you want to apply, all of the rows which do not fit that filter will be hidden – You’ll be left with only those rows of data which fit your requirements.
If you need to apply a broader or more specific filter you’ll need to select the ‘Custom’ option. This will enable you to provide Excel with a wider variety of requirements to use in the filtering process. One useful addition to the later versions of Excel is that they can automatically detect whether a column contains text or numbers and offer filtering options accordingly in a sub menu which will either be titled ‘Text filters’ or ‘Number filters’. Some of the custom filtering options available have been provided in these sub menus, but if you want to apply multiple filters to the same column you’ll still need to select ‘Customer Filter’.
Regardless of the version of Office you're running, once you've chosen to apply a custom Excel filter you'll be greeted with a menu that looks like this:
From the drop-down lists on the left hand side of the menu you’ll be able to select one of the following arguments:
Equals
Does not equal
Is greater than
Is greater than or equal to
Is less than
Is less than or equal to
Begins with
Does not begin with
Ends with
Does not end with
Contains
Does not contain
In the right hand boxes you can either enter values manually or select an option using the drop-down arrows. The values available will be the same as those found in the original filtering drop-down list, so in the case of our example we’d find a list of employee names.
Essentially all that needs to be done is to select an argument and enter a value; The combination of these 2 elements will give Excel enough information to provide you with the data you’re looking for.
You’ll notice that you also have an option to add a second Excel filter by completing the bottom row in the ‘Advanced filter’ menu. If you wish to use this functionality you’ll also need to select either the ‘And’ or ‘Or’ Arial button. By selecting ‘And’ you’ll be telling Excel that you want to search for data rows that meet both of the criteria you’ve entered, whereas by selecting ‘Or’ you’ll be asking for data rows that meet either criteria. Returning to our example for one final time, we can see one possible way to use an advanced filter like this.
In this case we’re asking Excel to show us of the data rows with staff names that either equal ‘James’ or contain ‘ah’. When we click OK we’re met with our results:
The first criteria was simple, our data contains all data rows which contain the name ‘James’. The second set of criteria has returned all data rows which include names that contain the letters ‘ah’ – In this case those names are ‘Sarah’ and ‘Hannah’. Any data rows which don’t fit with either set of criteria have been hidden, so we’re only seeing the data we’re interested in.
If desired you can apply Excel filters to multiple columns at once – This is done in exactly the same way as described above. Once you’ve applied your desired filters to a column, you can simply progress to another column and apply further filters.
In order to unhide the hidden rows and return to a full data set, just click on the drop-down filtering arrow once again and select ‘All’ in Office 2003 or earlier, or in later versions select ‘Clear filter’. You will need to do this for each column that you’ve applied filters to if you want to return to the full data set.
So that’s all for this Excel Filters Tutorial, I hope you’ve found it helpful. Please let me know your thoughts by completing the feedback form at the bottom of the page.
Looking for more? Return to Excel Tutorials or the Office Software homepage
