Excel 2013

Introduction

Video: Sorting Data

Watch the video (5:50).

As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.

Optional: Download our Lesson 18 Practice Workbook.

Types of sorting

When sorting data, it's important to first decide if you would like the sort to apply to the entire worksheet or just a cell range. 

• Sort sheet organizes all of the data in your worksheet by one column. Related information across each row is kept together when the sort is applied. In the example below, the Contact Name column (column A) has been sorted to display the names in alphabetical order.
  Sorting a sheet
  
• Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet.
  
  Sorting a cell range

To sort a sheet:

In our example, we'll sort a T-shirt order form alphabetically by Last Name (column C).

1. Select a cell in the column you wish to sort by. In our example, we'll select cell C2.
   Selecting cell C2
2. Select the Data tab on the Ribbon, then click the Ascending command  to Sort A to Z, or the Descending command  to Sort Z to A. In our example, we'll click the Ascending command.
   Clicking the Ascending command
3. The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name.
   
   The sorted worksheet

To sort a range:

In our example, we'll select a separate table in our T-shirt order form to sort the number of shirts that were ordered on different dates.

1. Select the cell range you wish to sort. In our example, we'll select cell range A13:B17.
   
   Selecting cell range A13:B17
   
2. Select the Data tab on the Ribbon, then click the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Choose the column you wish to sort by. In our example, we want to sort the data by the number of T-shirt orders, so we'll select Orders.
   
   Selecting a column to sort by
4. Decide the sorting order (either ascending or descending). In our example, we'll use Smallest to Largest.
5. Once you're satisfied with your selection, click OK.
   Clicking OK
6. The cell range will be sorted by the selected column. In our example, the Orders column will be sorted from lowest to highest. Notice that the other content in the worksheet was not affected by the sort.
   
   The sorted cell range

If your data isn't sorting properly, double-check your cell values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. In the example below, we forgot to include a hyphen in cell A18, causing our sort to be slightly inaccurate.

A small typo in cell A18 causing an incorrect sort

Custom sorting

Sometimes you may find that the default sorting options can't sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order.

To create a custom sort:

In our example below, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we'll create a custom list to sort from smallest to largest.

1. Select a cell in the column you wish to sort by. In our example, we'll select cell D2.
   
   Selecting cell D2
2. Select the Data tab, then click the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Select the column you want to sort by, then choose Custom List... from the Order field. In our example, we will choose to sort by T-Shirt Size.
   
   Selecting Custom List... from the Order: field
4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists: box.
5. Type the items in the desired custom order in the List entries: box. In our example, we want to sort our data by T-shirt size from smallest to largest, so we'll type Small, Medium, Large, and X-Large, pressing Enter on the keyboard after each item.
   Creating a Custom list
6. Click Add to save the new sort order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK.
   
   Clicking OK to select the custom list
7. The Custom Lists dialog box will close. Click OK in the Sort dialog box to perform the custom sort.
   Clicking OK to sort the worksheet
8. The worksheet will be sorted by the custom order. In our example, the worksheet is now organized by T-shirt size from smallest to largest.
   
   The worksheet sorted by t-shirt size

To sort by cell formatting:

You can also choose to sort your worksheet by formatting rather than cell content. This can be especially helpful if you add color coding to certain cells. In our example below, we'll sort by cell color to quickly see which T-shirt orders have outstanding payments.

1. Select a cell in the column you wish to sort by. In our example, we'll select cell E2.
   Selecting cell E2
2. Select the Data tab, then click the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Select the column you wish to sort by, then decide whether you'll sort by Cell Color, Font Color, or Cell Icon from the Sort On field. In our example, we'll sort by Payment Method (column E) and Cell Color.
   Choosing to sort by cell color
4. Choose a color to sort by from the Order field. In our example, we'll choose light red.
   
   Choosing a cell color to sort by
5. Click OK. In our example, the worksheet is now sorted by cell color, with the light red cells on top. This allows us to see which orders still have outstanding payments.
   
   The worksheet sorted by cell color

Sorting levels

If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column.

To add a level:

In our example below, we'll sort the worksheet by Homeroom Number (column A), then by Last Name (column C).

1. Select a cell in the column you wish to sort by. In our example, we'll select cell A2.
   Selecting cell A2
2. Click the Data tab, then select the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Select the first column you wish to sort by. In this example, we will sort by Homeroom # (column A).
4. Click Add Level to add another column to sort by.
   Clicking Add Level
5. Select the next column you wish to sort by, then click OK. In our example, we'll sort by Last Name (column C).
   
   Sorting by Homeroom # and Last Name
6. The worksheet will be sorted according to the selected order. In our example, the homeroom numbers are sorted numerically. Within each homeroom, students are sorted alphabetically by last name.
   The worksheet sorted by homeroom number and last name

If you need to change the order of a multilevel sort, it's easy to control which column is sorted first. Simply select the desired column, then click the Move Up or Move Down arrow to adjust its priority.

Changing the sorting priority for a column

Challenge!

1. Open an existing Excel workbook. If you want, you can use the Lesson 18 Practice Workbook.
2. Sort a worksheet in ascending  or descending order. If you are using the example, sort by Homeroom # (column A).
3. Sort a cell range. If you are using the example, sort the cell range in the cell range G3:H7 from highest to lowest by Orders (column H).
4. Add a level to the sort, and sort it by cell color, font color, or cell icon. If you are using the example, add a second level to sort by cell color in column E.
5. Add another level, and sort it using a custom list. If you are using the example, create a custom list to sort by T-Shirt Size (column D) in the order of Small, Medium, Large, and X-Large.
6. Change the sorting priority. If you are using the example, reorder the list to sort by T-Shirt Size (column D), Homeroom # (column A), and Last Name (column C).