A common use for Excel spreadsheets is to create databases of contacts or event attendees but a mere spreadsheet of names and details isn’t usually of much use to us until we do something with it. More often than not, all we want to know is one simple piece of information, but how can we extract that information easily without having to trawl through our list one record at a time?
In this example we are using an RSVP list is created for attendees for an event. The deadline for the event has passed, and the VA needs to confirm to the caterers the names of the people that have responded ‘yes’. The obvious solution would be to go through and delete the attendees that have responded ‘no’, however, if you have a large list of data, or even if you don’t, this is not a practical or efficient way to complete the job. An easier way to do it would be to filter the results by those who have said yes, and copy these to a new sheet.
It is possible to carry this out manually if there is just one criteria to be used, using Excel’s usual filter function. For example, if we just want to know who is attending, we can use the filter tool to filter all those who have said ‘no’ and then delete the results. An even better way would be to use the advanced filter.
Advanced filter lets you use multiple criteria to filter your database. You can then carry out a variety of actions on the filtered data. In this case we need to know who has replied after the deadline of 7/5/2012. We then need to send this additional data to the caterers.
When setting up your database. The important criteria is that the first row must have headings, and your table must be formatted well. By this, I mean that there should be no blank rows within the database and there should be a blank row at the end of the database and a blank column to the right of the database. These are basic rules to creating a spreadsheet, and essential if you are using any of the built in Excel functions to manage your data, but you would be surprised at how often they are ignored by Excel users. So here is our original database of event responses.
Preparing to use Advanced Filter
In order to filter your data, you must have a criteria table. This table should be on the same page as your database with headings that match those in your database. The easiest way to do this, is to add a few blank lines above your database and copy and paste the headings into the top row. In the next row you set your criteria. This criteria is what will be used to filter your database, therefore it must be data that you would find in that particular column. When setting your criteria, you can use defining values such as “=” “>” “<” etc. to indicate values equal to, greater than or less than etc.
As you can see here, we are looking for responses that have said ‘yes’ after 7/5/2012.
With this setup, we can then use advanced filter. Using this you can filter your data and choose to leave it in place, or to copy the results to a new sheet. In this case it would be useful for us to copy to a new sheet, so that we can e-mail this list to a caterers. To do this, firstly, click on a new worksheet or the location that you want to copy the results to, and then open the advanced filter by going to the data tab > sort & filter group > advanced filter. You will then be presented with the advanced filter window.
Setting the Advanced Filter
- Firstly under ‘Action’ select the option to ‘copy to another location’
- List range is the range of that contains your database. In this case cells A5:D13
- Criteria range is the criteria that we want to use to filter the database. In this example, our criteria is located in cells A1:D2.
- Finally, we want to set the location which we contain to copy the filtered data to and in this case we’re going to put it on Sheet 2.
Click ‘OK’. Your data complete with headings will automatically filter and be copied to the new location. Only the records matching your criteria will be shown, but the original data will remain untouched where it is.
This is quite a simple example of how to use this tool but you can also use it to create much more advanced filters. Watch out for our next article on using multiple AND/OR criteria.