
Sep 04, 2014
Filtering is a common task in Microsoft Excel and the normal filtering (the ‘autofilter’) can do quite a lot of what you will need. However, there are some filters that even the normal filtering can’t do. This is where you can use an Advanced Filter. By the way, ‘Advanced Filter’ means that it can do very complicated filters but doesn’t mean that it is complicated or hard to set up. In some ways, an Advanced Filter is easier to use than a normal filter. To set up an Advanced Filter, first you need to set up a filter Criteria Area. This has the same headings as the area you want to filter. The easiest way to do this is to copy and paste the heading row of your data. It is easiest to put the Criteria Area above (or below) the area you are filtering, because when you filter, you hide the whole row and having half of your Criteria Area hidden makes it harder to see what is going on. So, in this example file, I have copy and pasted the part of the heading row that includes the columns I want to filter on from Row 6 (E6:H6) and into J1:M1. Why am I copying and pasting? I’m doing this because the column heading names in the criteria area have to be exactly the same as those in the data area, otherwise the Advanced Filter won’t marry them up. Now, I use the area underneath the headings in my Criteria Area to put in the things I want to filter for. For example, if I want to find all the ‘Maine’ ‘Sales’ people as well as the ‘Vermont’ ‘Staff,’ I would proceed with the below. A normal filter could NOT do this particular filter. To begin, I’ll put this in the Criteria Area: To perform the actual filtering, click in the area you want to filter (not the Criteria area) and then go: Data – Sort & Filter – Advanced: You will then come to this menu: The top box should have the area of data you want to filter already in it. (You can select it manually if it doesn’t.) Then click in the Criteria range input area and then select the Criteria area we just set up, including the rows with the actual criteria (Maine and Sales and so on). In this example, I1:M3 Extra Option: There is an option to take the result and ‘Copy to another location’. So instead of just hiding the rows you don’t want to see and filtering the list in-place, you could have the result rows that the filter finds pasted onto another sheet. Given you can always copy and paste, I don’t use the ‘Copy to another location’ very often. When you click on OK, the filter will do its job. Note: A blank in the Criteria area means ‘anything’. Also, realise that each row of your Criteria area is an ‘AND.’ A logical AND means you have to meet all the criteria, so in this case DIVISION has to be ‘Maine’ and DEPT has to be ‘Sales’, DATE OF HIRE can be anything, and GROSS PAY can be anything. Between the rows of the Criteria area is an ‘OR.’ A logical OR means you can meet either (any one) of the criteria. So for the filter to show a row of data, that data has to meet either all of the first row or all of the second row. To clear off an Advanced Filter, click on the Clear button in the data tab. To do an example with number columns, I put the following text into cell M4: >800 When I perform the Advanced Filter again I have to be careful to change the Criteria area to include row 4. The menu looks like this: This will find three groups of people:- Maine Sales regardless of what they earn
- Vermont Staff regardless of what they earn
- People who earn over $800 regardless of what division or dept they work in.
How do your Excel skills stack up?
Test NowNext up:
- Passwords and alternative credentials in PowerShell
- I Know!
- Cloud computing with Microsoft Azure
- Why you need to know about the Show/Hide button ¶
- Connecting people in SharePoint 2013
- How to import a Microsoft Access resources list into Microsoft Project file
- Use conditional formatting to create a Gantt Chart
- Certificate Error, go back to start, do not collect $200
- Telling people that their feet smell and other delightful office conversations
- Action buttons and hyperlinks in PowerPoint
Previously
- The Silver Rule of Negotiation
- Voice Resiliency Features of Lync Server 2013
- Heading styles in Microsoft Word
- Setting up your first Office 365 Tenant account
- 5 steps to create a custom field in Microsoft Project
- Communication Across Generations – Quiz
- Why would I or my company want to use SCCM?
- Using background pages in Visio 2010 & 2013
- Xbox, oh Xbox, give me media!
- Dress up and present your data with Power View