When you need to find specific information in an Excel spreadsheet, the Filter tool is an easy way to get information fast. This lesson will discuss the Filter tool in Excel and explain how to use the tool to narrow down the data in your worksheet.
Some Excel worksheets can have a lot of information. So much so that finding the information you want can be difficult, especially if you have to sort through rows and rows of data. Over the years, I have lead several discussions and classes on the use of data, how to manage it and use it to your advantage. My message is always the same. I tell students that the benefit of any database, catalog of information or list of records is the ability to find what you are looking for and use it. This lesson will explain the Filter tool in Excel. This feature can help you quickly get to the information you need - and only the information you need.
Imagine you are a sales rep and over the years you have tracked all of your contacts, their interests, email address, personal information, phone numbers, etc. You have details of every conversation and correspondence with each contact, including dates and notes. It's well-built and, for all intents and purposes, a well-organized listing of information. It most certainly has value. However, if you can't find what you are looking for, when you need it and use the information to your advantage, what's the point? That's when the Filter tool comes in handy.
Before you can apply a filter to data in an Excel worksheet, your list of information must have a header row. A header row is the row of titles that identify each column. The header row is typically the first row of your spreadsheet. In order to perform a filter, Excel needs to know what type of information is in each column. In order to really explain how this all works, we need an example.
Let's say that you are a highly respected and somewhat wealthy poker player living in Las Vegas. Due to your reputation as a fun-loving, outgoing and generous player and friend, you have amassed quite the following of celebrity friends. Next month you are having a get-together to raise funds for charity. You have decided to hold a poker tournament. You plan to have everyone put up a few bucks (okay - lots of bucks) into the pot and the money will go to the winner's choice of charity organization.
Looking at the spreadsheet of celebrity information, the first row contains our column titles or header row:
Spreadsheet for example
We have the First Name, Last Name, Email, Phone, City, State and Interest. How do you find just the poker players? One option is to sort by Interest. However, ultimately, you need a list of only the celebrities you will invite and hand off the list to your friend who is helping you host the event. You want just the list of poker players.
How to Run a Filter
We have our list of celebrities and each column is titled (or our header row). Let's go through the process, step by step, and run a filter so that only the poker players remain:
- We'll start by going to the Data menu in the ribbon.
- And look in the Sort & Filter grouping of commands.
- Click on the Filter command. A drop-down arrow will appear in the header cell for each column.
- Then, click on the drop-down arrow for the column you want to filter. For our example, this will be the column titled Interest.
- The Filter menu appears.
- And, then, to quickly deselect all the data (since we only need Poker), uncheck the box next to Select All.
- Check the box next to the data or columns you wish to filter. For our example, we will check Poker.
The data will be filtered, temporarily hiding any content that doesn't match the criteria. Notice we have a list of our poker players:
Example spreadsheet after filtering data
Let's 'Shuffle Up and Deal!'
Running Multiple Filters
One last scenario before we wrap up the lesson. Keep in mind, we can filter on more than one column. For example, we could look for poker players that live only in Las Vegas. Essentially, we are going to run two filters. Following the steps earlier in the lesson, we were able to filter for poker players. If we go to the drop-down arrow for City, we can select only Las Vegas. We end up with a list of celebrities that play poker that live in Las Vegas. Finally, to clear any filters you have applied, click back on the Filter command in the ribbon.
This lesson reviewed the steps to using the Filter tool in Excel. You can narrow down your data using the column titles or header row of your worksheet. The Filter command is located under the Data menu in the ribbon in the Sort & Filter grouping of commands. You learned that a header row is necessary for using the filter tool and that you can filter using one column or multiple columns. Try it out! This is a great tool to help you easily find information fast.
Once you've completed this lesson, you'll be able to:
- Explain the usefulness of the Filter tool in Excel
- Identify the importance of the header row in filtering
- Describe the steps for filtering one or multiple columns