Sort data in a range or table

Sorting data is an integral part of data analysis. You might want to arrange 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.

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 you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Notes: 

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Sort text

  1. Select a cell in the column you want to sort.

  2. On the Data tab, in the Sort & Filter group, do one of the following:

    • To quick sort in ascending order, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort A to Z).

    • To quick sort in descending order, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Z to A).

Notes: Potential Issues

  • 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, you need to format them all as either numbers or text. If you do not apply this format, the numbers stored as numbers are sorted before the numbers stored as text. To format all the selected data as text, Press Ctrl+1 to launch the Format Cells dialog, click the Number tab and then, under Category, click General, Number, or Text.

  • Remove any leading spaces    In some cases, data imported from another application might have leading spaces inserted before data. Remove the leading spaces before you sort the data. You can do this manually, or you can use the TRIM function.

  1. Select a cell in the column you want to sort.

  2. On the Data tab, in the Sort & Filter group, do one of the following:

    • To sort from low to high, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort Smallest to Largest).

    • To sort from high to low, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Largest to Smallest).

Notes: 

  • Potential 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 instead of 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 Fix text-formatted numbers by applying a number format.

  1. Select a cell in the column you want to sort.

  2. 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 A to Z command in Excel that sorts A to Z or smallest number to largest (Sort Oldest to Newest).

    • To sort from a later to an earlier date or time, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Newest to Oldest).

Notes: Potential 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 instead of 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.

  • 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, so the sort operation would be based on alphanumeric data. For more information, see Show dates as days of the week.

You may want to 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 column and an 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.

  1. Select any cell in the data range.

    Select a range in two or more columns

  2. On the Data tab, in the Sort & Filter group, click Sort.

    The Sort & Filter group on the Data tab

  3. In the Sort dialog box, under Column, in the Sort by box, select the first column that you want to sort.

    Select the first column to sort

  4. 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.

  5. 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.

  6. To add another column to sort by, click Add Level, and then repeat steps three through five.

    Click Add Level

  7. To copy a column to sort by, select the entry and then click Copy Level.

  8. 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.

  9. To change the order in which the columns are sorted, select an entry and then click the Up or Down arrow next to the Options button to change the order.

    Entries higher in the list are sorted before entries lower in the list.

If you have manually or conditionally formatted a range of cells or a table column by cell color or font color, you can also sort by these colors. You can also sort by an icon set that you created with conditional formatting.

  1. Select a cell in the column you want to sort.

  2. On the Data tab, in the Sort & Filter group, click Sort.

    The Sort & Filter group on the Data tab

  3. In the Sort dialog box, under Column, in the Sort by box, select the column that you want to sort.

    Under Column, click Sort by, and choose an option

  4. Under Sort On, select Cell Color, Font Color, or Cell Icon.

    Under Sort On, choose an option

  5. 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.

  6. Next, select how you want to sort. Do one of the following:

    • To move the cell color, font color, or icon to the top or to the 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 to the 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.

  7. 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.

You can use a custom list to sort in a user-defined order. For example, a column might contain values that you want to sort by, such as High, Medium, and Low. How can you sort so that rows containing High appear first, followed by Medium, and then Low? If you were to sort alphabetically, an “A to Z” sort would put High at the top, but Low would come before Medium. And if you sorted “Z to A,” Medium would appear first, with Low in the middle. Regardless of the order, you always want “Medium” in the middle. By creating your own custom list, you can get around this problem.

  1. Optionally, create a custom list:

    1. 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 as in this example.

      Create a list from high to low in a range of cells

    2. Select the range that you just entered. Using the preceding example, select cells A1:A3.

    3. Go to File > Options > Advanced > General > Edit Custom Lists, then in the Custom Lists dialog box, click Import, and then click OK twice.

      Notes: 

      • You can create a custom list based only on a value (text, number, and date or time). You cannot create a custom list based on a format (cell color, font color, or icon).

      • The maximum length for a custom list is 255 characters, and the first character must not begin with a number.

  2. Select a cell in the column you want to sort.

  3. On the Data tab, in the Sort & Filter group, click Sort.

    The Sort & Filter group on the Data tab

  4. In the Sort dialog box, under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list.

  5. Under Order, select Custom List.

  6. In the Custom Lists dialog box, select the list that you want. Using the custom list that you created in the preceding example, click High, Medium, Low.

  7. Click OK.

  1. On the Data tab, in the Sort & Filter group, click Sort.

    The Sort & Filter group on the Data tab

  2. In the Sort dialog box, click Options.

    Click Sort to open the Sort dialog box

  3. In the Sort Options dialog box, select Case sensitive.

    In the Sort dialog box, click Options

  4. Click OK twice.

It's most common to sort from top to bottom, but you can also sort from left to right.

Note: Tables don't support left to right sorting. To do so, first convert the table to a range by selecting any cell in the table, and then clicking Table Tools > Convert to range.

  1. Select any cell within the range you want to sort.

  2. On the Data tab, in the Sort & Filter group, click Sort.

    The Sort & Filter group on the Data tab

  3. In the Sort dialog box, click Options.

    Click Sort to open the Sort dialog box

  4. In the Sort Options dialog box, under Orientation, click Sort left to right, and then click OK.

    In the Sort Options box, click Left to Right

  5. Under Row, in the Sort by box, select the row that you want to sort. This will generally be row 1 if you want to sort by your header row.

    Choose the row you want to sort

    Tip: If your header row is text, but you want to order columns by numbers, you can add a new row above your data range and add numbers according to the order you want them.

  6. To sort by value, select one of the options from the Order drop-down:

    1. For text values, select A to Z or Z to A.

    2. For number values, select Smallest to Largest or Largest to Smallest.

    3. For date or time values, select Oldest to Newest or Newest to Oldest.

  7. To sort by cell color, font color, or cell icon, do this:

    1. Under Sort On, select Cell Color, Font Color, or Cell Icon.

    2. Under Order select a cell color, font color, or cell icon, then select On Left or On Right.

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.

To sort by a 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 text functions to separate the parts of the cells or you can use the Convert Text to Columns Wizard. For examples and more information, see Split text into different cells and Split text among columns by using functions.

Warning: It is possible to sort a range within a range, but it is not recommended, because the result disassociates the sorted range from its original data. If you were to sort the following data as shown, the selected employees would be associated with different departments than they were before.

Example of sorting a small range within a larger one.

Fortunately, Excel will warn you if it senses you are about to attempt this:

Click Continue with the current selection

If you did not intend to sort like this, then press the Expand the selection option, otherwise select Continue with the current selection.

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

Note: You cannot sort this way in a table.

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 might change when the worksheet is recalculated. In this case, make sure that you reapply the sort 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 the heading on or off 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.

Note: In Excel Online, you can sort tables and ranges by single or multiple columns (both ascending and descending order), but you can't sort by rows (a left to right sort).

Sort data in a table

Did you know that a table is ready for sorting with its built-in arrows? You can quickly sort your data, and it lets you filter too.

  1. If your data isn't already in a table, then create a table. This will automatically add the sort arrow at the top of the columns.

  2. Click the sort arrow at the top of the column you want to sort on, and pick the sort order you want.

    Sorting a table column

  3. To undo a sort, use the Undo button on the Home tab.

    Undo button, on the Home tab

Sort data in a range

  1. Pick a cell to sort on:

    • If your data has a heading row, pick the heading you want to sort on, such as "Population."

      Select the heading cell for the column

    • If your data doesn’t have a heading row, pick the topmost value you want to sort on, such as 452084.

      Select the topmost data cell for the column

  2. On the Data tab, pick one of the sort methods:

    • Sort Ascending to sort A to Z, smallest to largest, or earliest to latest date.

    • Sort Descending to sort Z to A, largest to smallest, or latest to earliest date.

Sort Ascending and Sort Descending

Sort by multiple columns

Note: Your data should be in a table if you want to sort by multiple columns. See Create a table in Excel Online for more information.

  1. Select any cell in the table.

  2. On the Home tab, click Sort & Filter > Custom Sort.

    Custom Sort
  3. Under Column, in the Sort by box, select the column to sort by.

  4. Under Order, select from ascending or descending.

  5. To add another column to sort by, click Add and then repeat steps 3 and 4.

  6. To change the order in which the columns are sorted, select an entry and then click the Move Up or Move Down arrow.

  7. Click OK.

Quickly scanning data by date order can be difficult if your data is in a random order like in this picture.

unsorted dates

In Excel Online, the dates you want to sort need to be formatted as a table. Here’s how:

  1. Select the dates you want to sort and click Format as Table.

Button to format data as a table

  1. CheckMy table has headerseven if your date column doesn’t have one.

Dialog box for converting data range into a table

  1. Click the column header, and pick a sort order:

    • Click Sort Ascending for oldest date on top.

    • Sort Descending for newest date on top.

      sort either ascending or decending order.

Just check the direction of the arrow.

Sort Ascending has an upward pointing arrow.

Dates sorted in ascending order from oldest to newest

Sort Descending has a downward pointing arrow.

sorted in decending order with the newest on top

Tip:  When you add new dates to the table, select the column and reapply the sort order.

If you want to sort a list of names by last name, but they start with the first name (like "Elsie Carr"), you'll need to change them so they start with the last name (like "Carr, Elsie"). To do this, we'll use a few formulas.

This shows some formulas in B2, C2, and D2 that we'll bring together in cell E2.

Formulas that convert a full name to Last, First

Here's a description of the formulas shown above:

Cell

Formula

Description

Result

B2

=FIND(" ",A2)

Finds the position of the first space in "Elsie Carr"

6

C2

=MID("A2,B2+1,30)

Finds the last 30 characters in "Elsie Carr" starting at the first position (7) after the space (this is the "C" in Carr). There's no problem with asking for a lot more characters than you need.

Carr

D2

=LEFT(A2,B2-1)

Finds the first name in "Elsie Carr" by returning the leftmost number of characters that are found before the first space (at position 6). So, the first 5 characters (position 6 minus 1) are returned—"Elsie."

Elsie

E2

=D2 & ", " & C2

Combines "Carr," a comma with a space (", "), and "Elsie" to return "Carr, Elsie."

Carr, Elsie

In E2, we've converted "Elsie Carr" to "Carr, Elsie." Now, drag the formula in E2 down through the cells below it to copy the formula and convert the rest of the names from column A.

Before you try to sort the names, you'll need to convert them from formula results to values:

  1. Select the cells starting with E2, and press Ctrl+C to copy them.

  2. On the Home tab, click the arrow under Paste, and click Paste Values.

Finally, to sort the names, select the cells and click Sort Ascending or Sort Descending.

Sort Ascending and Sort Descending

In Excel Online, you can use the Undo command on the Home tab to undo any sorting you've applied to a list or a table. This example shows data in a list that was originally arranged by city in ascending order and then was sorted on population in descending order. The undo action restored the original order (city).

Undoing a sort on a range

In a table, columns have filter arrows, so you can use them to sort in addition to filtering. In the example below, notice the down arrow next to the filter for the Population column that shows it's sorted in descending order. After sorting on the City column, notice the up arrow next to the filter for City.

Just like with a range, you can undo sorting on a table by using the Undo command on the Home tab. This example shows both the result of an undo action that restores the original order, and using the filter to sort on the city.

Undoing a sort on a table

In Excel Online, you can sort tables and ranges, but you can't save or reuse the sort order on another range or table of data. You can't do this in the Excel desktop application, either.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See also

Video: Sort data in a table

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Excel functions (alphabetical)

Excel functions (by category)

Discover more Excel training at LinkedIn Learning

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×