Sort a list of data

In Excel, you can sort numbers, text, weekdays, months, or items from custom lists that you create. You can also sort by font color, cell color, or icon sets. Sorts can be case-sensitive.

When you sort a column, you rearrange the rows of the column. When you sort multiple columns or a table, you typically rearrange all the rows based on the contents of a particular column.

Important:

If the columns that you want to sort contain both numbers that are stored as numbers and numbers that are stored as text, the numbers will not sort correctly. Numbers that are stored as text are displayed aligned left instead of right. To format selected numbers consistently, on the Home tab, in the Number Format box, select Text or Number.

Newer versions Office 2011



Sort a column

Click a cell in one of the columns that you want to sort.

Data in adjacent columns will be sorted based on the data in the column that you selected.

On the Data tab, select Ascending Ascending sort icon or Descending Descending sort icon .

To sort	Click
The lowest values to the top of the column	A to Z
The highest values to the top of the column	Z to A

Note: If the results aren't what you expected, check that all numbers are stored as numbers. For example, negative numbers imported from some accounting systems are stored as text.

Sort a list by two or three columns

You can sort by several columns by adding levels of sorting criteria. For example, you could sort a sales report by region, then by date, and then by salesperson. Each sort level is represented by a single row in the Sort dialog box.

Click a cell in one of the columns that you want to sort.
On the Data tab, select Sort.
If your data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
In the row next to Sort by, under Column, click the blank space, and then click the column that you want to sort by.
Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.

You can also choose to sort based on cell or font color, or on the icon in a cell.
Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.

Tip: If the sort order you want is not listed, select Custom List. If the custom sort order you want isn't listed, see the section "Create a custom list to sort by."
If you selected Cell Color, Font Color, or Cell Icon in step 5, then under Color/Icon, click the row, and then on the shortcut menu, click the criteria that you want.
For each additional column you want to sort by, click Add Level.

Then fill in the Column, Sort On, Order, and Color/Icon columns for the new row.

Sort a list by rows

Note: This procedure can't be performed in a table. To remove table formatting so that you can sort by rows, on the Table tab, select Convert to Range.

Click a cell in the row that you want to sort.
On the Data tab, select Sort.
If your data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
Click Options.
Under Orientation, click Sort left to right, and then click OK.
In the first row, under Row, click the blank space next to Then by, and then on the shortcut menu, click the row that you want to sort next.
Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.
Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.

Tip: If the sort order you want is not listed, select Custom List. If the custom sort order you want isn't listed, see the section "Create a custom list to sort by."
For each additional row you want to sort by, click Add Level.

Then fill in the Row, Sort On, Order, and Color/Icon columns for the new row.

Create a custom list to sort by

Excel includes custom lists that you can sort by: days of the week and months of the year. In addition, you can create your own custom lists, for example, grade levels in a school, such as Freshman, Sophomore, Junior, and Senior. Once your custom list is created, to use it, in the Sort box, under Order, select Custom List.

On the Excel menu, click Preferences, and then under Formulas and Lists, click Custom Lists .
Click Add.
Type the values for your list in the order that you want them sorted, with a comma between each value.
When you are finished, click Add, and then close the Custom Lists box.

Sort by a custom list

Excel includes custom lists that you can sort by: days of the week and months of the year. In addition, the previous procedure explains how you can create your own custom lists, for example, grade levels in a school, such as Freshman, Sophomore, Junior, and Senior.

Click a cell in one of the columns that you want to sort.
On the Data tab, select Sort.
If the data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
Under Order, click Custom List.
Select the list that you want to sort by, and then click OK.

Note: The custom sort order applies only to the column specified under Column. To sort multiple columns by weekday, month, or another custom list, use the previous "Sort a list by two or three columns" procedure to sort each column separately.

Sort dates or times

Select a column of dates or times in a range of cells or in a table.
On the Data tab, select Ascending or Descending .

Note: If the results are not what you expected, the data might contain dates or times that are stored as text or numbers, instead of as dates or times. To store the numbers in date or time format, select the column, and on the Home tab, select Date or Time in the Number Format box.

Sort case-sensitive text

Click a cell in one of the columns that you want to sort.
On the Data tab, select Sort.
In the Sort box, select Options, and then select Case sensitive.

Sort by cell color, font color, or icon

If there are cells formatted by cell color or font color in the column that you are sorting, you can sort by these colors. You can also sort by an icon set that was created by using a conditional format. Because there is no default sort order for cell color, font color, or icons, you must define your own order for each sort operation.

Click a cell in one of the columns that you want to sort.
On the Data tab, select Sort.
If the data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort by.
Under Sort On in the same row, click Values, and then on the shortcut menu, click Cell Color, Font Color, or Cell Icon.
Under Color/Icon, select the color or icon.
Under Order, choose whether the selected color or icon should be at the top or bottom of the list.
For each additional column you want to sort by, click Add Level.

Then fill in the Column, Sort On, Order, and Color/Icon columns for the new row.

Sort one column in a range of cells without affecting the other columns

Important: Be careful when you use this feature. Sorting by one column in a range can produce results that you don't want, such as moving cells in that column away from other cells in the same row.

Select a column in a range of cells that contains two or more columns.

Note: This procedure can't be performed in a table. To remove table formatting so that you can sort one column, on the Table tab, select Convert to Range.
Select the column data that you want to sort.
On the Data tab, click Sort.
In the Sort Warning that appears, select Continue with the current selection, and then click Sort.

If the results are not what you want, click Undo .

Learn more about sorting

Data analysis begins with sorting. You can sort 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 that you create (such as Large, Medium, and Small). Or you can sort by format, including cell color, font color, or icon set. Most frequently, you will sort by column, but you can also sort by rows.

When you sort, you rearrange data into some order. In contrast, when you filter, you hide extraneous data. For more information about filtering, see Filter a list of data.

When you sort on a range of cells, the sort criteria aren't saved with your workbook. If you want to save sort criteria so that you can reapply it the next time that you open the workbook, you can save the data as a Excel table. Save your data in a table when you are sorting multiple columns or if you create complex sorting criteria that takes a long time to create.

When you reapply a sorting criteria, you may see different results. This can occur if values that are returned by a formula have changed and the sheet is recalculated. It can also occur if the range of cells or table column has had data added, changed, or deleted.

Excel sorts data by using the following ascending sort order: 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z FALSE TRUE (sheet errors) (blank cells).

Sort a column

Click a cell in one of the columns that you want to sort.

Data in adjacent columns will be sorted based on the column that you sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort.

Do one of the following:

To sort	Click
The lowest values to the top of the column	Ascending
The highest values to the top of the column	Descending

Note: If the results aren't what you expected, check that all numbers are stored as numbers. For example, negative numbers imported from some accounting systems are stored as text.

Sort a list by two or three columns

Click a cell in one of the columns that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Add Level .

If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box.
Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort by.
Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.
Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.

If you selected Font Color, Cell Color, or Cell Icon in step 5, then under Color/Icon, click the row, and then on the shortcut menu, click the criteria that you want.
For each column that you want to sort by, repeat steps 3 through 6.

Sort a list by rows

Click a cell in the row that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Options.
Under Orientation, click Sort left to right, and then click OK.
Click Add Level .
Under Row, click the blank space next to Then by, and then on the shortcut menu, click the row that you want to sort next.
Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.
Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.
For each row that you want to sort by, repeat steps 5 through 8.

Create a custom list to sort by

On the Excel menu, click Preferences, and then under Formulas and Lists, click Custom Lists .
Click Add.
Type your list entries in the order that you want them sorted. When you are finished, click OK.

Sort by a custom list

Click a cell in one of the columns that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Add Level .

If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box.
Under Order, click the current sort order, such as A to Z, and then click Custom List.
Select the list that you want to sort by, and then click OK.

Note: The custom sort order applies only to the column specified under Column. To sort multiple columns by weekday, month, or another custom list, use the previous "Sort a list by two or three columns" procedure to sort each column separately.

Sort dates or times

Select a column of dates or times in a range of cells or in a table.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Ascending or Descending.

Note: If the results are not what you expected, the column might contain dates or times that are stored as text or numbers, instead of as dates or times. To store the numbers in date or time format, select the column, and on the Home tab, under Number, point to Number Format, and then click Date or Time.

Sort by cell color, font color, or icon

Click a cell in one of the columns that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Add Level .

If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box.
Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort by.
Under Sort On in the same row, click Values, and then on the shortcut menu, click Cell Color, Font Color, or Icon Color.
For each column that you want to sort by, repeat steps 3 through 5.

Sort one column in a range of cells without affecting the other columns

Select a column in a range of cells that contains two or more columns.

Note: This procedure cannot be performed in a table.
Select the column data that you want to sort.
On the Data tab, under Sort & Filter, click Sort.
In the Sort Warning that appears, select Continue with the current selection, and then click Sort.

If the results are not what you want, click Undo .

Sort Chinese text by character pronunciation or character stroke numbers in Excel

You can sort Chinese text by Chinese character pronunciation or Chinese character stroke numbers.

Important: To complete this procedure, you must first turn on Chinese language features. For more information, see Turn on Chinese language features.

Click a cell in one of the columns that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
To add another sorting criteria, click Add Level .

If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box.
To change the sort order, under Order, click the current sort order, such as A to Z, and then click Custom List.

Click Options, and then do one or more of the following:

To	Do this
Sort top to bottom	Under Orientation, click Sort top to bottom.
Sort left to right	Under Orientation, click Sort left to right.
Sort by stroke ordering	Under Method, click Stroke Ordering.
Sort by syllabary ordering	Under Method, click Syllabary Ordering.

Click OK.

Note: The custom sort order applies only to the column specified under Column. To sort multiple columns by weekday, month, or another custom list, use the previous "Sort a list by two or three columns" procedure to sort each column separately.

Learn more about sorting

When you sort, you rearrange data into some order. In contrast, when you filter, you hide extraneous data. For more information about filtering, see Filter a list of data.

Microsoft Office Tutorials

Wednesday, October 10, 2018