Joshua earned an MBA from USF and he writes mostly about software and technology.
Filtering data in a worksheet allows an Excel user to quickly analyze raw data. In this text, we will see exactly how to drill down on data with basic sort and filter tools available in Excel.
The illustration below is a screenshot of a spreadsheet with information about the Titanic ticket purchases. The spreadsheet lists fourteen attributes about passengers and will be used to show several filtering and sorting examples. To practice with this data set and follow along you can download the workbook here.
Enabling Sort & Filter for a Data Set
The first step to sorting and filtering data is enabling the capabilities of a dataset. Each of the steps needed to enable sort and filter in Excel is shown below:
Step 1. Select the row of the headers.
Step 2. While on the "Home" tab, click the sort and filter button in the editing section of the ribbon.
Step 3. Click on the filter option.
Enabling Sort & Filter
Each column header will have an arrow in the cell that can be clicked to display a menu of sort and filter tools. These added arrows are highlighted in the illustration below.
Sorting A to Z in Excel
Sorting from A to Z (or sorting from Z to A) can be accomplished in two steps. The
Step 1. Select the down arrow on the column.
Step 2. Select one of the first two sorting options.
Excluding Data in Excel
Data can be excluded by clicking on the down arrow located on the header and making changes to the checkboxes in the menu. Unchecking a box will exclude it from the data set.
Perhaps you have outliers in the data that you would like to exclude temporarily. If you require only a few pieces of data, the select all check box can be unchecked giving you the opportunity to select what data is displayed by only checking the corresponding boxes.
Date Exclusion Option in Excel
Using the Custom Auto Filter in Excel
Text filters can come in handy especially is you have multiple criteria. With this tool you can filter by equals, does not equal, greater than, less than, begins with ends with, and contains.
For a text filter example, let's look that the equals filter. The equals filter will display the exact data that is specified. In the illustration below I walk through each step to use this tool to filter by 2.
- Click the down arrow on the header
- Select text filters and equals
- Enter 2 into the field after equals
Note that you are not limited to just one filter. An additional filter like one that that will show all numbers greater than 4 can be added as well.
Microsoft. (n.d.). Filter data in a range or table. https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e.
Microsoft. (n.d.). Guidelines and examples for sorting and filtering data by color. https://support.microsoft.com/en-us/office/guidelines-and-examples-for-sorting-and-filtering-data-by-color-b1bf3982-051d-49b8-8330-80e99c94365b.
To learn more about executing other common processes I recommend using The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2022 Joshua Crowder