Data Sorting in Microsoft Excel

Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colours or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions. To find the top or bottom values in a range of cells or table, such as top 10 grades or bottom 5 sales amounts, use AutoFilter or conditional formatting.

Sort data in a range or table

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Small) or by format, including cell colour, font colour, or icon set. Most sort operations are column sorts, but you can also sort by rows.

Sort criteria are saved with the workbook so that you can reapply the sort each time that you open the workbook for an Excel table, but not for a range of cells. If you want to save sort criteria so that you can periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is especially important for multicolumn sorts or for sorts that take a long time to create.

In Excel 2007, you can apply sort states with up to sixty-four sort conditions to sort data by, but earlier versions of Excel support sort states with up to three conditions only. To avoid losing sort state information in earlier versions of Excel when the workbook is opened in an earlier version; you may want to change the sort state to one that uses no more than three conditions.

All sort state information remains available in the workbook, even after opening in an earlier version and is applied when the workbook is opened again in Excel 2007, unless the sort state information is edited in the earlier version of Excel.

Sort Text

1. Select a column of alphanumeric data in a range of cells, or make sure that the active cell is in a table column containing alphanumeric data.
2. On the Home tab, in the Editing group, and then click Sort & Filter.
3. Do one of the following:

1. To sort in ascending alphanumeric order, click Sort A to Z.
2. To sort in descending alphanumeric order, click Sort Z to A.
Optionally, you can do a case-sensitive sort.
4. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
5. In the Sort dialog box, click Options.
6. In the Sort Options dialog box, select Case sensitive.
7. Click OK twice.

Note

1. Ensure that all data is stored as text. If the column that you want to sort contains numbers stored as numbers and numbers stored as text, then you need to format them all as text. If you do not, the numbers stored as numbers are sorted before the numbers stored as text.
2. To format all of the selected data as text, on the Home tab, in the Font group, click the Format Cell Font button, click the Number tab, and then under Category, click Text.
3. Remove any leading spaces before sorting the data. In some cases, data imported from another application might have leading spaces inserted before data.

Sort Numbers

1. Select a column of numeric data in a range of cells, or make sure that the active cell is in a table column containing numeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
1. To sort from low numbers to high numbers, click Sort Smallest to Largest.
2. To sort from high numbers to low numbers, click Sort Largest to Smallest.
Important

Check to ensure that all numbers are stored as number. If the results are not what you expected, the column might contain numbers stored as text and not as numbers. For example, negative numbers imported from some accounting systems or a number entered with a leading ' (apostrophe) are stored as text. For more information, see Convert numbers stored as text to numbers in Section five of this book.

Sort Dates or Times

1. Select a column of dates or times in a range of cells, or make sure that the active cell is in a table column containing dates or times.
2. On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
1. To sort from an earlier to a later date or time, click Sort Oldest to Newest.
2. To sort from a later to an earlier date or time, click Sort Newest to Oldest.
Important
1. Check that dates and times are stored as dates or times. If the results are not what you expected, the column might contain dates or times stored as text and not as dates or times. For Excel to sort dates and times correctly, all dates and times in a column must be stored as a date or time serial number. If Excel cannot recognize a value as a date or time, the date or time is stored as text. You can also see Section five on how to convert dates stored as text to dates.
2. If you want to sort by days of the week, format the cells to show the day of the week. If you want to sort by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the sort operation would be based on alphanumeric data.

Sort by Cell Colour, Font Colour or Icon

If you have manually or conditionally formatted a range of cells or table column, by cell colour or font colour, you can also sort by these colours. You can also sort by an icon set created through a conditional format.
1. Select a column of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by box, select the column that you want to sort.
4. Under Sort On, select the type of sort.
Do one of the following:
1. To sort by cell colour, select Cell Color.
2. To sort by font colour, select Font Color.
3. To sort by an icon set, select Cell Icon.
5. Under Order, select how you want to sort.
Do one of the following:
1. To move the cell colour, font colour, or icon to the top or left, select On Top for a column sort, and On Left for a row sort.
2. To move the cell colour, font colour, or icon to the bottom or right, select On Bottom for a column sort and On Right for a row sort.
6. To specify the next cell colour, font colour, or icon to sort by, click Add Level, and then repeat steps four through five.
Make sure that you select the same column in the Then by box and that you make the same selection under Order.
7. Keep repeating steps five through six for each additional cell colour, font colour, or icon that you want included in the sort.
8. Click Ok.