Sort data in a range or table
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 colors 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.
Note 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. For more information,
see
Filter data in a range or table and
Add, change, or clear conditional formats.
What do you want to do?
Learn about sorting
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 color, font color, 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.
When you reapply a sort, different results appear for the following reasons:
- Data has been added, modified, or deleted to the range of cells or table column.
- Values returned by a formula have changed and the worksheet has been recalculated.
Top of Page
Sort text
- 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.
- On the Data tab, in the Sort & Filter group, do one of the following:
- To sort in ascending alphanumeric order, click
Sort A to Z.
- To sort in descending alphanumeric order, click
Sort Z to A.
- Optionally, you can do a case-sensitive sort.
- On the Data tab, in the Sort & Filter group, click Sort.
- In the Sort dialog box, click Options.
- In the Sort Options dialog box, select Case sensitive.
- Click OK twice.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Issue: Check 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. 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.
Issue: Remove any leading spaces In some
cases, data imported from another application might have leading spaces
inserted before data. Remove the leading spaces before sorting the
data.
Top of Page
Sort numbers
- 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.
- On the Data tab, in the Sort & Filter group, do one of the following:
- To sort from low numbers to high numbers, click Sort Smallest to Largest.
- To sort from high numbers to low numbers, click Sort Largest to Smallest.
Issue: Check that all numbers are stored as numbers 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.
Top of Page
Sort dates or times
- 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.
- Select a column of dates or times in a range of cells or table.
- On the Data tab, in the Sort & Filter group, do one of the following:
- To sort from an earlier to a later date or time, click Sort Oldest to Newest.
- To sort from a later to an earlier date or time, click Sort Newest to Oldest.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Issue: 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. For more
information, see
Convert dates stored as text to dates.
Note 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.
For more information, see
Show dates as days of the week.
Top of Page
Sort by cell color, font color, or icon
If you have manually or conditionally formatted a range of cells or
table column, by cell color or font color, you can also sort by these
colors. You can also sort by an icon set created through a conditional
format.
- Select a column of data in a range of cells, or make sure that the active cell is in a table column.
- On the Data tab, in the Sort & Filter group, click Sort.
The Sort dialog box is displayed.
- Under Column, in the Sort by box, select the column that you want to sort.
- Under Sort On, select the type of sort. Do one of the following:
- To sort by cell color, select Cell Color.
- To sort by font color, select Font Color.
- To sort by an icon set, select Cell Icon.
- Under Order, click the arrow next to the button, and then, depending on the type of format, select a cell color, font color, or cell icon.
- Under Order, select how you want to sort. Do one of the following:
- To move the cell color, font color, or icon to the top or left, select On Top for a column sort, and On Left for a row sort.
- To move the cell color, font color, or icon to the bottom or right, select On Bottom for a column sort, and On Right for a row sort.
Note There is no
default cell color, font color, or icon sort order. You must define the
order that you want for each sort operation.
- To specify the next cell color, font color, or icon to sort by, click Add Level, and then repeat steps three through five.
Make sure that you select the same column in the Then by box and that you make the same selection under Order.
Keep repeating for each additional cell color, font color, or icon that you want included in the sort.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Top of Page
Sort by a custom list
You can use a custom list to sort in a user-defined order. Excel
provides built-in, day-of-the-week and month-of-the year custom lists,
and you can also create your own custom list.
- Optionally, create the custom list.
- In a range of cells, enter the values that you want to sort by,
in the order that you want them, from top to bottom. For example:
- Select the range that you just typed. In the example above, you would select cells A1:A3.
- Click the Microsoft Office Button
, click Excel Options, click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
- In the Custom Lists dialog box, click Import, and then click OK twice.
Notes
- You can only create a custom list based on a value (text,
number, and date or time). You cannot create a custom list based on a
format (cell color, font color, and icon).
- The maximum length for a custom list is 255 characters, and the first character must not begin with a number.
- Select a column of data in a range of cells, or make sure that the active cell is in a table column.
- On the Data tab, in the Sort & Filter group, click Sort.
The Sort dialog box is displayed.
- Under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list.
- Under Order, select Custom List.
- In the Custom Lists dialog box, select the list that you want. In the preceding example, you would click High, Medium, Low.
- Click OK.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Top of Page
Sort rows
- Select a row of data in a range of cells, or make sure that the active cell is in a table column.
- On the Data tab, in the Sort & Filter group, click Sort.
The Sort dialog box is displayed.
- Click Options.
- In the Sort Options dialog box, under Orientation, click Sort left to right, and then click OK.
- Under Column, in the Sort by box, select the row that you want to sort.
- Do one of the following:
By value
- Under Sort On, select Values.
- Under Order, do one of the following:
- For text values, select A to Z or Z to A.
- For number values, select Smallest to Largest or Largest to Smallest.
- For date or time values, select Oldest to Newest or Newest to Oldest.
By cell color, font color, or cell icon
- Under Sort On, select Cell Color, Font Color, or Cell Icon.
- Click the arrow next to the button, and then select a cell color, font color, or cell icon.
- Under Order, select On Left or On Right.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Note When you sort
rows that are part of a worksheet outline, Excel sorts the highest-level
groups (level 1) so that the detail rows or columns stay together, even
if the detail rows or columns are hidden.
Top of Page
Sort by more than column or row
You might sort by more than one column or row when you have data that
you want to group by the same value in one column or row, and then sort
another column or row within that group of equal values. For example,
if you have a Department and Employee column, you can first sort by
Department (to group all the employees in the same department together),
and then sort by name (to put the names in alphabetical order within
each department). You can sort by up to 64 columns.
Note For best results, the range of cells that you sort should have column headings.
- Select a range of cells with two or more columns of data, or make
sure that the active cell is in a table with two or more columns.
- On the Data tab, in the Sort & Filter group, click Sort.
The Sort dialog box is displayed.
- Under Column, in the Sort by box, select the first column that you want to sort.
- Under Sort On, select the type of sort. Do one of the following:
- To sort by text, number, or date and time, select Values.
- To sort by format, select Cell Color, Font Color, or Cell Icon.
- Under Order, select how you want to sort. Do one of the following:
- For text values, select A to Z or Z to A.
- For number values, select Smallest to Largest or Largest to Smallest.
- For date or time values, select Oldest to Newest or Newest to Oldest.
- To sort based on a custom list, select Custom List.
- To add another column to sort by, click Add Level, and then repeat steps three through five.
- To copy a column to sort by, select the entry, and then click Copy Level.
- To delete a column to sort by, select the entry, and then click Delete Level.
Note You must keep at least one entry in the list.
- To change the order in which the columns are sorted, select an entry, and then click the Up or Down arrow to change the order.
Entries higher in the list are sorted before entries lower in the list.
- To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.
Top of Page
Sort by a partial value in a column
Sorting is based on the entire value in a column. If you want to sort
by part of a value in a column, such as a part number code (789-
WDG-34), last name (Carol
Philips), or first name (Philips,
Carol),
you first need to split the column into two or more columns so that the
value you want to sort by is in its own column. To do this, you can use
functions or the Convert Text to Columns Wizard.
For examples and more information, see
Split names by using the Convert Text to Columns Wizard and
Split text among columns by using functions.
Top of Page
Sort one column in a range of cells without affecting the others
Warning Be careful when using this
feature. Sorting by one column in a range may produce results that you
don't want, such as moving cells in that column away from other cells in
the same row.
Note You cannot do the following procedure in a table.
- Select a column in a range of cells containing two or more columns.
- To select the column that you want to sort, click the column heading.
- On the Home tab, in the Editing group, click Sort & Filter, and then click one of the available sort commands.
- The Sort Warning dialog box is displayed.
- Select Continue with the current selection.
- Click Sort.
- Select any other sort options that you want in the Sort dialog box, and then click OK.
If the results are not what you want, click
Undo 
.
Top of Page
Learn more about general issues with sorting
If you get unexpected results when sorting your data, do the following:
Check to see if the values returned by a formula have changed
If the data that you have sorted contains one or more formulas, the
return values of those formulas can change when the worksheet is
recalculated. In this case, make sure that you reapply the sort or do
the sort again to get up-to-date results.
Unhide rows and columns before you sort
Hidden columns are not moved when you sort columns, and hidden rows are
not moved when you sort rows. Before you sort data, it's a good idea to
unhide the hidden columns and rows.
Check the locale setting Sort orders vary by locale setting. Make sure that you have the proper locale setting in
Regional Settings or
Regional and Language Options in
Control Panel on your computer. For information about changing the locale setting, see the Windows help system.
Enter column headings in only one row If you need multiple line labels, wrap the text within the cell.
Turn on or off the heading row It's
usually best to have a heading row when you sort a column to make it
easier to understand the meaning of the data. By default, the value in
the heading is not included in the sort operation. Occasionally, you may
need to turn on or off the heading so that the value in the heading is
or is not included in the sort operation. Do one of the following:
- To exclude the first row of data from the sort because it is a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, and then select My data has headers.
- To include the first row of data in the sort because it is not a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, and then clear My data has headers.
The
data in each column should all be the same type. For instance, do not
mix text in a column with numbers, or numbers in a column with dates. 
Note:
If you select an entire column instead of a single cell before clicking
the AutoFilter command, an AutoFilter arrow will appear only on the
selected column, not on all columns of the data. 
When you click West, Excel hides all the rows on the worksheet except for those that contain that text in this column.
You
can filter columns in any order you choose. The filters are applied
progressively, in the order you apply them. Each filter limits the data
to which you can apply the next filter.
Show All
Hide All