Skip main navigation

How to Sort and Filter Data in Excel

In this article, we explain how to sort and filter data in Excel.

Sorting data in Excel

There are many options for sorting data.
You can sort data on common attributes, such as:
  • text (A to Z, or Z to A)
  • numbers (low to high, or high to low)
  • dates and times (newest to oldest, or oldest to newest)
  • format (e.g. cell colour).
As well as sorting by individual properties, you can sort data over multiple columns or rows. For example, the following table is initially sorted by ID.
idtitleseasonimdb_rating
10Homer’s Night Out17.4
12Krusty Gets Busted18.3
14Bart Gets an ‘F’28.2
17Two Cars in Every Garage and Three Eyes on Every Fish28.1
19Dead Putting Society28.0
35Blood Feud28.0
37Mr. Lisa Goes to Washington37.7
39Bart the Murderer38.7
41Like Father, Like Clown37.7
44Saturdays of Thunder37.9
But we might want to sort these by season and rating to find the best episodes in each season.
Excel offers additional sorting options, including sorting by custom lists. Custom lists are useful when you want a user-defined order that doesn’t follow existing sort rules. For example, you can’t use an alphabetical sort to sort the values High, Medium, and Low. If you create your own custom list, you can specify the values you want to sort by, in the order you want to sort them.
To sort data in Excel:
  1. Select a cell in the column you want to sort.
  2. In the Data tab, go to the Sort & Filter group. Then you have two options.
    1. To sort values in ascending or descending order based on Excel’s interpretation of the column, click the Sort A to Z or Sort Z to A icons.
    2. For more sorting options, click the Sort button. You can then specify the Column, what to Sort On, and Order. With the Add Level option, you can perform a secondary level of sorting if needed.
Go to: Sort data in a range or table [1]
Microsoft Support has further instructions and examples of sorting in Excel.

Filtering data in Excel

You can use filters to temporarily hide some of the data in a table, so you can focus on the data you want to see. When filtering, you can specify exact matches or comparisons (‘more than’, ‘less than’) or data that doesn’t match specific criteria. The following comparison operators are available in Excel. You can compare two values by using the following operators. When you use these operators to compare two values, the result is a logical value—it’s either TRUE or FALSE.
Comparison operatorMeaningExampleResult (A1=1, B1=2)
=Equal toA1=B1FALSE
>Greater thanA1>B1FALSE
<Less thanA1<B1TRUE
>=Greater than or equal toA1>=B1FALSE
<=Less than or equal toA1<=B1TRUE
<>Not equal toA1<>B1TRUE
To create a filter in Excel:
  1. Select the data you want to work with.
  2. Select Data > Filter from the ribbon menu.
  3. At the top of your selection, select the column header arrow (grey box with downwards arrow).
  4. Select Text Filters or Number Filters, and then select a comparison, such as Between. Screenshot of Filter options in Excel
  5. Enter the filter criteria and select OK. Screenshot of filter criteria in Excel
For more complex filtering you can use logical operators such as AND and OR to select results—depending on which criteria evaluate to true. AND will only evaluate to true if both criteria evaluate to true (e.g. 1<2 AND 3<4). OR evaluates to true if either criteria, or both, evaluate to true (e.g. 1<2 OR 3>4 would evaluate as TRUE).
You can access the Advanced Filter dialog box under Data > Advanced.
Screenshot of the ‘Sort & Filter’ section of the menu ribbon in Excel showing the ‘Advanced’ option
Go to: Filter data in a range or table [2]
Go to: Filter by using advanced criteria [3]
These Microsoft Support pages have more examples and instructions about filtering.

Pivot tables

Pivot tables are built into Excel. They allow you to group and summarise large quantities of data quickly and easily. If you have an input table with tens, hundreds, or even thousands of rows, pivot tables allow you to extract answers to a series of basic questions about your data with minimal effort.
Pivot tables can quickly turn this:
Screenshot of raw data in Excel
Into:
Screenshot of pivot table in Excel
If you have typical sales data, you can use a pivot table to:
  • find the sum of total sales per customer
  • count the total number of orders by customer
  • find the sum total of sales by item type
  • create a summary of sales by customer and item type
  • find the average amount of sales to a particular customer in a quarter
  • create a summary showing the maximum order value by customer and month
  • create a breakout summary of orders by customer, month, and item type.

References

  1. Sort data in a range or table [Internet]. Microsoft Support. Available from: https://support.microsoft.com/en-us/office/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654?ui=en-us&rs=en-us&ad=us
  2. Filter data in a range or table [Internet]. Microsoft Support. Available from: https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e?ui=en-us&rs=en-us&ad=us
  3. Filter by using advanced criteria [Internet]. Microsoft Support. Available from: https://support.microsoft.com/en-us/office/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b?ui=en-us&rs=en-us&ad=us
This article is from the free online

Data Analytics for Business: Creating Databases

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education