How to Sort Data Lists on a Single Field in Excel 2013

By Greg Harvey from Excel 2013 For Dummies

Every data list you put together in Excel 2013 will have some kind of preferred order for maintaining the records. You can sort on a single or multiple fields. Depending on the list, you may want to see the records in alphabetical order by last name.

In the case of a client data table, you may want to see the records arranged alphabetically by company name. In the case of the Employee Data list, the preferred order is in numerical order by the ID number assigned to each employee when he or she is hired.

When you initially enter records for a new data list, you no doubt enter them in either the preferred order or the order in which you retrieve their records. You will soon discover, you don’t have the option of adding subsequent records in that preferred order. Whenever you add a new record, Excel tacks that record onto the bottom of the database by adding a new row.

Suppose you originally enter all the records in a client data list in alphabetical order by company, and then you add the record for a new client: Pammy’s Pasta Palace. Excel puts the new record at the bottom of the barrel — in the last row right after Zastrow and Sons — instead of inserting it in its proper position, which is somewhere after Acme Pet Supplies.

This isn’t the only problem you can have with the original record order. Even if the records in the data list remain stable, the preferred order merely represents the order you use most of the time. What about those times when you need to see the records in another, special order?

For example, if you usually work with a client data list in numerical order by case number, you might instead need to see the records in alphabetical order by the client’s last name to quickly locate a client and look up his or her balance due in a printout.

When using records to generate mailing labels for a mass mailing, you want the records in zip code order. When generating a report for your account representatives showing which clients are in whose territory, you need the records in alphabetical order by state and maybe even by city.

To have Excel correctly sort the records in a data list, you must specify which field’s values determine the new order of the records. (Such fields are technically known as the sorting keys in the parlance of the database enthusiast.) Further, you must specify what type of order you want to create using the information in these fields. Choose from two possible orders:

Ascending order: Text entries are placed in alphabetical order from A to Z, values are placed in numerical order from smallest to largest, and dates are placed in order from oldest to newest.
Descending order: This is the reverse of alphabetical order from Z to A, numerical order from largest to smallest, and dates from newest to oldest.

When you need to sort the data list on only one particular field (such as the Record Number, Last Name, or Company field), you simply click that field’s AutoFilter button and then click the appropriate sort option on its drop-down list:

Sort A to Z or Sort Z to A in a text field
Sort Smallest to Largest or Sort Largest to Smallest in a number field
Sort Oldest to Newest or Sort Newest to Oldest in a date field

Excel then re-orders all the records in the data list in accordance with the new ascending or descending order in the selected field. If you find that you’ve sorted the list in error, simply click the Undo button on the Quick Access toolbar or press Ctrl+Z right away to return the list to its order before you selected one of these sort options.

Excel shows when a field has been used to sort the data list by adding an up or down arrow to its AutoFilter button. An arrow pointing up indicates that the ascending sort order was used and an arrow pointing down indicates that the descending sort order was used.