How to Sort Excel Table Records - dummies

By Stephen L. Nelson, E. C. Nelson

After you place information in an Excel table, you’ll find it very easy to sort the records. You can use the Sort & Filter button’s commands to sort and organize your information.

Using the Sort buttons

To sort table information by using a Sort & Filter button’s commands, click in the column you want to use for your sorting. For example, to sort a grocery list by the store, click a cell in the Store column.

An open table in Microsoft Excel.

After you select the column you want to use for your sorting, click the Sort & Filter button and choose the Sort A to Z command from the menu Excel displays to sort table records in ascending, A-to-Z order using the selected column’s information. Alternatively, choosing the Sort Z to A command from the menu Excel displays sort table records in descending, Z-to-A order using the selected column’s information.

Using the Custom Sort dialog box

When you can’t sort table information exactly the way you want by using the Sort A to Z and Sort Z to A commands, use the Custom Sort command.

To use the Custom Sort command, follow these steps:

  1. Click a cell inside the table.

  2. Click the Sort & Filter button and choose the Sort command from the Sort & Filter menu.

    Excel displays the Sort dialog box.

    In Excel 2007 and Excel 2010, choose the Data→Custom Sort command to display the Sort dialog box.

    Excel's Sort and Filter dialog box.

  3. Select the first sort key.

    Use the Sort By drop-down list to select the field that you want to use for sorting. Next, choose what you want to use for sorting: values, cell colors, font colors, or icons. If you choose to sort by values, you’ll also need to indicate whether you want records arranged in ascending or descending order by selecting either the ascending A to Z or descending Z to A entry.

    Ascending order, predictably, alphabetizes labels and arranges values in smallest-value-to-largest-value order. Descending order arranges labels in reverse alphabetical order and values in largest-value-to-smallest-value order. If you sort by color or icons, you need to tell Excel how it should sort the colors by using the options that the Order box provides.

    Typically, you want the key to work in ascending or descending order. However, you might want to sort records by using a chronological sequence, such as Sunday, Monday, Tuesday, and so on, or January, February, March, and so forth. To use one of these other sorting options, select the custom list option from the Order box and then choose one of these other ordering methods from the dialog box.

  4. (Optional) Specify any secondary keys.

    If you want to sort records that have the same primary key with a secondary key, click the Add Level button and then use the next row of choices from the Then By drop-down lists to specify which secondary keys you want to use. If you add a level that you later decide you don’t want or need, click the sort level and then click the Delete Level button.

    You can also duplicate the selected level by clicking Copy Level. Finally, if you do create multiple sorting keys, you can move the selected sort level up or down in significance by clicking the Move Up or Move Down buttons.

    Note: The Sort dialog box also provides a My Data Has Headers check box that enables you to indicate whether the worksheet range selection includes the row and field names. If you’ve already told Excel that a worksheet range is a table, however, this check box is disabled.

  5. (Really optional) Fiddle-faddle with the sorting rules.

    If you click the Options button in the Sort dialog box, Excel displays the Sort Options dialog box. Make choices here to further specify how the first key sort order works.

    The Sort Options dialog box in Microsoft Excel.

    For a start, the Sort Options dialog box enables you to indicate whether case sensitivity (uppercase versus lowercase) should be considered.

    You can also use the Sort Options dialog box to tell Excel that it should sort rows instead of columns or columns instead of rows. You make this specification by using either Orientation radio button: Sort Top to Bottom or Sort Left to Right. Click OK when you’ve sorted out your sorting options.

  6. Click OK.

    Excel then sorts your list.