Excel 2019 (Part 18): Sorting Data

In Excel , as you add more content to a worksheet, organizing this information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you can sort a list of contacts by last name. Content can be sorted alphabetically, numerically, and in several other ways.

 

 

Classification methods

When sorting data, it is important to first decide whether you want to apply it to the entire worksheet (1) or just a range of cells (2).

1. In case (1), all data in the worksheet will be organized into one column. Related information on each row is saved together when the sort is applied. In the example below, the Contact Name column (column A) has been sorted to display names in alphabetical order.

 

1. In case (2), only the data in a range of cells is sorted, which can be useful when working with a worksheet that contains multiple tables. Sorting a range will not affect other content in the worksheet.

How to arrange a spreadsheet

For example, we will sort t-shirt orders alphabetically based on Last Name (column C).

1. Select a cell in the column you want to sort. For example, we would select cell C2.

2. Select the Data tab on the Ribbon, then click the AZ command to sort from A to Z or the ZA command to sort from Z to A. The example will select sorting from A to Z.

3. The worksheet will be sorted by the selected column. In this example, the worksheet is currently sorted by last name.

 

How to sort a range of cells

For example, a separate table would be selected within the t-shirt order form to sort the number of shirts ordered for each class.

1. Select the range of cells you want to sort. For example, we would select the range G2:H6.

2. Select the Data tab on the Ribbon, then click the Sort command .

3. The Sort dialog box will appear. Select the column you want to sort. For example, if you want to sort the data by the number of t-shirt orders, the Orders column will be selected.

4. Decide on the sorting order (ascending or descending). For example, we will use the Largest to Smallest option .

5. Once you are satisfied with your selection, click OK.

6. The range of cells will be sorted by the selected column. In the example, the Orders column will be sorted from highest to lowest. Note that other content in the spreadsheet is not affected by this sorting.

 

If your data isn't sorted correctly, double-check the cell values ​​to ensure they're entered into the spreadsheet accurately. Even a small typo can cause problems when sorting a large spreadsheet. The example below forgot to add a hyphen to cell A18, resulting in slightly inaccurate sorting.

Custom classification

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

How to create custom taxonomies

For example, if you want to sort a spreadsheet by T-Shirt Size (column D), the usual sorting method would sort the sizes alphabetically, which is impractical. Instead, you need to create a custom list to sort from smallest to largest.

1. Select a cell in the column you want to sort. For example, we would select cell D2.

2. Select the Data tab , then click the Sort command.

3. The Sort dialog box will appear. Select the column you want to sort, then select Custom List. from the Order field. For example, we will choose to sort by T-Shirt Size.

4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists box.

5. Enter the items in your desired custom order into the List entries: box. For example, if you want to sort your data by t-shirt size from smallest to largest, you would enter the values ​​Small, Medium, Large , and X-Large , pressing Enter after each item.

 

6. Click Add to save the new sorting order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK.

7. The Custom Lists dialog box will close. Click OK in the Sort dialog box to apply the custom sort.

8. The spreadsheet will be sorted in a custom order. In this example, the spreadsheet is currently sorted by t-shirt size from smallest to largest.

Sorting levels

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

How to add a level

The example below will sort the spreadsheet by T-Shirt Size (Column D), then by Homeroom # (Column A).

1. Select a cell in the column you want to sort. For example, we would select cell A2.

2. Click on the Data tab , then select the Sort command.

3. The Sort dialog box will appear. Select the first column you want to sort. This example will sort by T-Shirt Size (column D) using the custom list previously created for the Order field.

4. Click Add Level to add another column for sorting.

5. Select the next column you want to sort, then click OK. For example, this will sort by Homeroom # (column A).

 

6. The spreadsheet will be sorted in the selected order. In the example, the orders are sorted by t-shirt size. Within each group t-shirt size, students are sorted by class number.

If you need to change the sorting order across multiple levels, 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 level.