Sort data in a range or table (2024)

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 (Sort A to Z).

   • To quick sort in descending order, click (Sort Z to A).

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 (Sort Smallest to Largest).

   • To sort from high to low, click (Sort Largest to Smallest).

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 (Sort Oldest to Newest).

   • To sort from a later to an earlier date or time, click (Sort Newest to Oldest).

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.

   

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

   

3. In the Sort dialog box, under Column, in the Sort by box, select the first column that you want 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.

   

   See Also

   Inventory Excel Formulas You Should Know7 Inventory Management Formulas to Boost your Business — Katana20 Best Inventory Management Excel FormulasHow to Keep Track of Inventory Accurately | AutoStore

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.

   

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

   

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

   

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.

      

   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.

   

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.

   

2. In the Sort dialog box, click Options.

   

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

   

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.

   

3. In the Sort dialog box, click Options.

   

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

   

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.

   

   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.

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.

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

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 .

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 changedIf 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 sortHidden 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 settingSort 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 rowIf you need multiple line labels, wrap the text within the cell.

Turn on or off the heading rowIt'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.

If your data is formatted as an Excel table, then you can quickly sort and filter it with the filter buttons in the header row.

1. If your data isn't already in a table, then format it as a table. This will automatically add a filter button at the top of each table column.

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

   

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

   

1. Pick a cell to sort on:

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

     

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

     

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.

Let's say you have a table with 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.

Select any cell within your data range.

1. On theData tab, in theSort & Filtergroup, click Custom Sort.

   

2. In the Custom Sort dialog box, underColumn, in theSort by box, select the first column that you want to sort.

   

   Note:The Sort On menu is disabled because it's not yet supported. For now, you can change it in the Excel desktop app.

3. UnderOrder, select how you want to sort:

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

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

   

5. To change the order in which the columns are sorted, select an entry and then click theUp orDown arrow next to theOptionsbutton.

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 theDatatab, in theSort & Filtergroup, selectCustom Sort.

3. In theCustom Sortdialog boxunderColumns,select the column that you want to sort.

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

5. UnderOrder, select the order you want (what you see depends on the type of format you have). Thenselect a cell color, font color, or cell icon.

6. Next, you'll choose how you want to sort by moving the cell color, font color, or icon:

   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 move to the top or to the left:SelectOn Topfor a column sort, andOn Leftfor a row sort.

   • To move to the bottom or to the right: SelectOn Bottomfor a column sort, andOn Rightfor a row sort.

7. To specify the next cell color, font color, or icon to sort by, selectAdd Level, and then repeat steps 1- 5.

• Make sure that you the column in theThen bybox and the selection under Order are the same.

• Repeat for each additional cell color, font color, or icon that you want included in the sort.

1. On theDatatab, in theSort & Filtergroup, click Custom Sort.

   

2. In the Custom Sortdialog box, clickOptions.

   

3. In theOptions menu, selectCase sensitive.

   

4. ClickOK.

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 theDatatab, in theSort & Filtergroup, selectCustom Sort.

   

3. In the Custom Sortdialog box, clickOptions.

4. UnderOrientation, clickSort left to right

5. UnderRow, in the 'Sort by' drop down, select the row that you want to sort. This will generally be row 1 if you want to sort by your header row.

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

   • 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