Beginner’s Guide to Excel 2010 Data Filters
- select the contributor at the end of the page -
Coauthored by: Heather Ackmann
In Excel 2010, you can store over a million rows of data (1,048,576 to be exact). And as great as that is, if you don't know how to find the data you are looking for within those millions of rows, then your Excel experience can be quite frustrating. Knowing all the tips, tricks, and tools of the Excel 2010 filters can help you find that needle in the haystack.
Sorting a List of Data in Excel 2010
Below is a screenshot of an employee list detailing the employee's first and last name, social security number, date of hire, department, store number, hourly rate, hours per week, and gross pay.
Example Scenario: Let's say we want to find our lowest paid employee. There are a few ways you could locate this information, but the fastest way is to simply sort the list by Hourly Rate.
To Sort an Excel 2010 List:
1. Click any cell in the Hourly Rate column.
Note: In order for this to work, you must have your list “properly formatted,” meaning there is a distinct header row with some kind of formatting that sets it apart from the other rows (be it bold, underline, italics, etc.). You also cannot have any blank rows or columns; however, individual blank cells are not a problem.
2. On the Home tab in your Excel ribbon, in the Editing group, click on the icon labeled “Sort & Filter.”
3. Click on “Sort Smallest to Largest” since we want to locate our lowest paid employee.
And now we can see that Bill Mosher is our lowest paid employee.
AutoFilter an Excel 2010 List
Example Scenario: Instead of locating our lowest paid employee, let's say we want to get a little more specific with our criteria and identify our highest paid cashier. This is where AutoFilter can help us. AutoFilter allows users to hide data based on specific criteria within each column. So, in our example, we want to hide all employees who are not cashiers.
To Apply AutoFilter to an Excel List:
1. Select any cell within the list. And yes, it doesn't matter which cell you select.
2. On the Home tab in the ribbon, in the Editing group, click on the icon labeled “Sort & Filter” to open the Sort & Filter options menu.
3. From the Sort & Filter drop-menu, select “Filter.”
Now, you should notice little tiny arrows to the right of your column headers.
4. Click on the arrow of the column that you wish to filter. In our scenario, since we only want to view cashiers, we will need to click on the arrow of our “Dept.” header.
That will open a Sorting and Filtering options menu.
In the bottom of the Sorting and Filtering options menu, you will notice a list of your unique data within that column. Next to that data, you will see a series of checkboxes. These checkboxes allow us to select which data is viewable. By default, all data is checked.
5. To view only cashiers, you will need to uncheck all the boxes except the Cashier checkbox.
Tip: A faster way is to first uncheck the “(Select All)” box, which will uncheck all options. Then, you can simply check the Cashier box.
6. Click OK to apply the filter.
There are a few things you should notice about our filtered list:
- You are now viewing only those employees who are cashiers.
- The row numbers to the far left have turned blue. This is Excel's way of telling you that your list has been filtered. Also notice how the row numbers are no longer sequential. This is because Excel is hiding those rows from view. So, don't worry, you haven't lost or deleted the other data; it's just hidden from view.
- Also notice that the arrow in the “Dept.” column now has a funnel symbol next to it. This is to let you know that a filter is in place in this column.
You are not limited to filter by only one criteria. By checking additional boxes within a column, you can see additional rows of data. Or if you want to further limit your search results, you can filter by additional columns as well.
Example Scenario: Now that we have our list filtered to show only those employees, let's say we now want to see ALL of our employees, once again. For that, we will need to clear any filters we have in place.
To Clear a Filter:
- Click the funnel icon in the header of the column that has been filtered. In our example, that would be our “Dept.” column.
- If a filter is in place, you should see an option “Clear Filter From ‘Dept.'” Click on this option to clear that column's filter.
Tip: To clear multiple column filters at once, you can also go back to the Sort & Filter menu on your ribbon, and select “Clear.”
Text Filters and Number Filters
With Excel 2010, we also have the ability to get more specific with our filtering. For example, instead of using the checkbox to match results to data already in the list, like we did with our filter criteria “Cashier”, you can use features like the Text Filters to broaden (or refine) a filter using conditional statements like “Begins with,” “Ends with,” “Contains,” or even “Does not contain.” But our personal favorite Custom Filters are the Number Filters, which opens a wealth of custom number formats from which to choose.
Applying a Custom Number Filter
Example Scenario: Going back to our Employee List, let's say we'd like to identify only those employees whose Hourly Rate is between 10 and 14 dollars.
To Filter by a Number Range:
Note: You will need your AutoFilters applied to the list. See above.
1. Click on the filter arrow inside the column you wish to filter.
2. Inside the Hourly Rate filter menu, click on Number Filters to launch yet another submenu.
This submenu will list a variety of number conditional statements from which to choose. In our scenario, since we are looking for a number with a range, we can actually use a couple of options. We could use the “Greater Than Or Equal To…” or the “Between…” number filter. For the purposes of this example, we will demo “Between…”.
3. Click the number filter “Between…” to open the Custom AutoFilter dialog box.
4. Enter 10 in the box next to “is greater than or equal to” and 14 in the box next to “is less than or equal to” since we want employees who make between 10 and 14 dollars.
5. Click OK to apply the filter.
And now our list only displays employees whose hourly rate is between 10 and 14 dollars.
If the above filtering options are not enough (and if you are really working with over a million rows, they won't be) then you can use Excel's Advanced Filter tool. The Advanced Filter tool allows you to filter by multiple columns using unique criteria. Since this can get a little tricky, we've included a video demonstrating how to use the Advanced Filter tool:
Although the video demo uses Excel 2007, the process is still the same in Excel 2010.