Skip to main content

How to Filter and Sort Data in Microsoft Excel

Joshua earned an MBA from USF and he writes mostly about software and technology.

Filtering and sorting data sets in spreadsheets help people understand how to decipher trends and anomalies.

Filtering and sorting data sets in spreadsheets help people understand how to decipher trends and anomalies.

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.

Titanic Data

The supplemental Excel spreadsheet in this example contains data about each passenger that boarded the Titanic.

The supplemental Excel spreadsheet in this example contains data about each passenger that boarded the Titanic.

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

The sort and filter options are located under the home tab in the Excel ribbon.

The sort and filter options are located under the home tab in the Excel ribbon.

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.

how-to-filter-data-in-microsoft-excel

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.

Scroll to Continue

Step 2. Select one of the first two sorting options.

how-to-filter-data-in-microsoft-excel

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

This illustration shows the filtering option where data be excluded by unchecking boxes.

This illustration shows the filtering option where data be excluded by unchecking boxes.

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.

  1. Click the down arrow on the header
  2. Select text filters and equals
  3. 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.

how-to-filter-data-in-microsoft-excel

References

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

Related Articles