SUMMARY: Instructions on how to filter data in an Excel 2010 worksheet, with screenshots showing filters performed on an example worksheet.
When viewing a worksheet in Microsoft Excel 2010 with a large series of data, it might prove useful to only display a subset matching desired criteria. Examples may include only showing salespeople with over $25,000 in monthly sales, budget items costing $5,000 or more, or students scoring at least an 80 on a test.
To display this subset you can use Excel 2010’s Filter tool.
1. To filter just a subset of your worksheet, select the cells you want to filter, ignoring column headers, descriptory text, etc. If you want to use the filter tool with the entire worksheet, just select a cell inside the data and try step 2. If Excel 2010 fails to figure out the data range correctly, highlight all appropriate cells in the worksheet first.
2. Click the “Home” tab in the Ribbon.
3. In the “Editing” section, click the “Sort and Filter” button.
4. When the pull-down menu appears, click “Filter”.
You should now see arrows in the top row of all columns / fields in your data set. If Excel 2010 recognizes that your data has a header row, the arrows may appear there instead.
Click on an arrow to activate filtering / sorting functions for that column / field.
Filtering an example worksheet in Excel 2010
* Click “Sort Smallest to Largest” or “Sort Largest to Smallest” to sort the data (these menu items may differ depending on the data type – i.e. for dates you will see “Sort Oldest to Newest” and “Sort Newest to Oldest”).
* Click “Sort by Color” to group cells by background color with the desired color first. This is only available if cells have background colors, such as via Conditional Formatting.
* Click “Clear Filter From” to clear the filter for the current field / column.
* Click “Filter by Color” to only show rows with cells in the column matching a given background color. As mentioned above, this is enabled only if cells have background colors.
* Depending on the data type, you may then see another menu item such as “Number Filters” or “Date Filters”. Click this item to perform standard filtering such as “Does Not Equal”, “Greater Than”, or “Less Than”, or advanced filtering such as rows with numbers in a certain range, or rows with cells in the top 10 of the particular column. You can also perform a custom filter to combine the results of two filters together, as shown below.
Custom AutoFilter example in Excel 2010, filtering rows in the table where the Pop Quiz value is between 80 and 89 (students who have scored a B on the Pop Quiz)
* Underneath you can check or uncheck individual items to show/hide rows where the field matches the list of checked items.
Note that you can apply filters on any or all columns, and unless ScreenTips have been disabled you can hover over an arrow to see the filter performed on said column.
Multi-column filter on example data in Excel 2010, filtering rows in the table where all numbers are 80 or above (students who have scored a B or A on all quizzes / tests)
To turn the filtering off, repeat steps 2-4 above to un-highlight the “Filter” menu item.