Sort worksheet data

Although Excel makes it easy to enter your business data and to manage it after you’ve saved it in a worksheet, unsorted data will rarely answer every question you want to ask. For example, you might want to discover which of your services generates the most profits or which service costs the most for you to provide. You can discover that information by sorting your data.

When you sort data in a worksheet, you rearrange the rows that contain the data based on the contents of cells in a specific column or set of columns. The first step in sorting a range of data is to identify the column or columns that contain the values by which you want to sort. You can sort by as many of the columns as you want to.

If you want to sort data by only one column that contains words, numbers, or dates, you can quickly do so from the Home tab of the ribbon.

The options on the Sort & Filter menu vary depending on whether you’re sorting a column of words, numbers, or dates. Excel sorts words from A to Z or Z to A, numbers from smallest to largest or largest to smallest, and dates from newest to oldest or oldest to newest.

You perform multiple-column sort operations from the Sort dialog, in which you can specify the columns to sort by, the order in which to sort each column, and the order in which to perform the sort operations. When creating similar rules, you can save time by copying one rule and changing only the field name.

If cell values within the data range have font or fill colors applied to them, either manually or though conditional formatting, you can sort the data to place a specific color at the beginning or end of the column.

You can easily change the order in which rules are applied, as well as edit and delete rules, in the Sort dialog.

To display the Sort & Filter menu

• On the Home tab, in the Editing group, select Sort & Filter.

To sort a data range alphanumerically by a single column

1. Select a cell in the column by which you want to sort the data range.

2. Display the Sort & Filter menu.

3. Do either of the following:

   • Select Sort A to Z, Sort Smallest to Largest, or Sort Oldest to Newest to sort the data in ascending order.

   • Select Sort Z to A, Sort Largest to Smallest, or Sort Newest to Oldest to sort the data in descending order.

Or

1. Select the data range you want to sort or a cell in the range.

2. Display the Sort & Filter menu and then select Custom Sort.

3. If appropriate, select the My data has headers checkbox.

4. In the Sort by list, select the field by which you want to sort.

5. In the Sort On list, select Cell Values.

6. In the Order list, do either of the following:

   • Select A to Z or Smallest to Largest to sort the data in ascending order.

   • Select Z to A or Largest to Smallest to sort the data in descending order.

To sort a data range by cell color, font color, or icon

1. Select the data range you want to sort or a cell in the range.

2. Display the Sort & Filter menu, and then select Custom Sort.

3. If appropriate, select the My data has headers checkbox.

4. In the Sort by list, select the field by which you want to sort.

5. In the Sort On list, select Cell Color, Font Color, or Conditional Formatting Icon.

6. In the Order list, select the cell or font color or icon that you want to isolate. Then, in the last list box, do either of the following:

   • Select On Top to sort that color or icon to the beginning of the list.

   • Select On Bottom to sort that color or icon to the end of the list.

   

7. Select OK to sort the values.

Or

1. In the data range, right-click the cell that has the color or icon you want to isolate, and then on the context menu, select Sort.

   Tip

   To display a context menu, right-click or long-press (tap and hold) the element.

2. On the Sort submenu, select Put Selected Attribute On Top.

   

To sort a data range based on values in multiple columns

1. Select the data range you want to sort or a cell in the range.

2. On the Home tab, in the Editing group, select Sort & Filter.

3. On the Sort & Filter menu, select Custom Sort.

4. If appropriate, select the My data has headers checkbox.

5. In the Sort by list, select the first field by which you want to sort.

6. In the Sort On list, select the option by which you want to sort the data (Cell Values, Cell Color, Font Color, or Conditional Formatting Icon).

   

7. In the Order list, select an order for the sort operation.

8. For each additional column by which you want to sort, do the following:

   1. Select Add Level.

   2. In the Then by list, select the column.

   3. Select values in the Sort On and Order lists.

9. Select OK to sort the values.

   

To copy a sorting level

1. In the Sort dialog, select the sorting level you want to copy.

2. Select the Copy Level button.

To move a sorting rule up or down in priority

1. In the Sort dialog, select the sorting rule you want to move.

2. Do either of the following:

   • Select the Move Up (^) button to move the rule earlier in the order.

   • Select the Move Down (v) button to move the rule later in the order.

To delete a sorting rule

1. In the Sort dialog, select the sorting level you want to delete.

2. Select Delete Level.

Sort data by using custom lists

By default, Excel sorts words in alphabetical order and numbers in numeric order. But that pattern doesn’t work for some sets of values. For example, if you’re sorting a series of months, alphabetical order begins with April and ends with September.

Fortunately, Excel recognizes days of the week and months of the year as special lists that it uses for sorting and when filling series. You can add custom series to Excel for the same purposes, either by entering the list values or copying them from a worksheet.

To define a custom list by entering its values

1. Display the Advanced page of the Excel Options dialog.

2. Scroll to the General section, and then select Edit Custom Lists.

3. In the Custom Lists dialog, with NEW LIST active, activate the List entries box.

4. Enter the custom list items in order, pressing Enter between items.

   

5. After you enter all the list items, select Add to move them to the Custom Lists pane.

6. Select OK, and then select OK again to close the Excel Options dialog.

To define a custom list by copying values from a worksheet

1. Enter the custom list values in a column, in the correct sort order (first to last, from top to bottom), and then select the cells that contain the list values.

2. Open the Custom Lists dialog.

3. Verify that the Import list from cells box contains the correct cells, and then select Import.

   

4. Select OK, and then select OK again to close the Excel Options dialog.

To sort worksheet data by using a custom list

1. Select a cell in the list of data you want to sort.

2. On the Home tab, select the Sort & Filter button, and then select Custom Sort.

3. If necessary, select the My data has headers checkbox.

4. In the Sort by list, select the field that contains the data by which you want to sort.

5. If necessary, in the Sort On list, select Values.

6. In the Order list, select Custom List.

7. In the Custom Lists dialog, select the list you want to use. Then select OK.

8. In the Sort dialog, select OK to close the dialog and sort the data.

Outline and subtotal data

When your data range is sorted into the order you want, you can use the Subtotal feature to have Excel outline the data by specific categories and summarize the categories. The available summary functions are SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDDEV, STDDEVP, VAR, and VARP.

In a subtotal operation, you choose the column by which to group the data, the summary calculation you want to perform, and the values to be summarized. After you define the subtotals, Excel displays them in your worksheet.

Excel also defines groups based on the subtotal calculation. The groups form an outline of the data based on the criteria you used to create the subtotals. For example, all the rows representing months in the year 2019 could be in one group, rows representing months in 2020 in another, and so on. You can use the controls in the outline area on the left side of the worksheet to hide or display groups of data.

The numbers above the outline controls are the level buttons. Each represents a level of data organization. Selecting a level button hides all levels of detail below that of the button you selected.

The following table describes the data contained at each level of a worksheet with three levels of organization.

You can add custom levels of detail to the outline that Excel creates by grouping specific rows. (For example, you can hide revenues from specific months.) You can also delete groups you don’t need and remove the subtotals and outlining entirely.

To organize data into levels

1. Sort the data range, ensuring that the top-level sort is on the column by which you want to group the data.

2. Select the data range you want to sort or a cell in the range.

3. On the Data tab, in the Outline group, select the Subtotal button.

4. In the Subtotal dialog, in the At each change in list, select the field by which you want to group the data.

   Important

   You must first sort the data by the field that you plan to summarize by selecting the Subtotal button.

5. In the Use function list, select the summary function you want to use for each subtotal.

6. In the Add subtotal to group, select the checkbox next to any field you want to summarize. Then select OK.

   

To show or hide detail in an outlined range

• To hide or show one group, select its Hide Detail (+) or Show Detail (–) control.

• To hide or show one level, select the level button.

To create a custom group in an outlined range

1. Select the rows you want to include in the group.

2. On the Data tab, in the Outline group, select the Group button (not the arrow).

   

To remove a custom group from an outlined range

1. Select the rows you want to remove from the group.

2. On the Data tab, in the Outline group, select the Ungroup button (not the arrow).

To remove subtotals from a data range

1. Select any cell in the range.

2. On the Data tab, in the Outline group, select Subtotal.

3. In the Subtotal dialog, select Remove All.

Key points

• You can sort the data in a range or table by the values in one or multiple columns. Excel discerns the basic sort order (A to Z, small to large, old to new) based on the data in the column. You can also sort data to bring a single cell color, font color, or icon to the top.

• If the default sort orders don’t meet your needs, you can provide Excel with a list by which it should sort. Custom lists with the days of the week and months of the year in order of occurrence are built in to Excel, but you can make up your own.

• You can outline, group, and subtotal data by using the Subtotal feature (which is different from the SUBTOTAL function). To successfully subtotal data, you must first sort it by the column that will define the data groups.

Practice tasks

Before you can complete these tasks, you must copy the book’s practice files to your computer. The practice files for these tasks are in the Office365SBSCh08 folder. You can save the results of the tasks in the same folder.

The introduction includes a complete list of practice files and download instructions.