Home > Data > Sort > Multiple Columns

How to Sort Multiple Columns in Excel

How to sort multiple columns in Excel, to organize data in table or list. Use Quick Sort buttons, or use the Sort dialog box, for more options when multi-level sorting.

Before You Sort Excel Data

I've seen many people mess up their Excel data, by doing a quick sort on a worksheet.

So, before you sort your Excel data, be sure to do these 3 things:

-- 1) Make a backup of your Excel file

-- 2) Check the data that you're going to sort

-- 3) Fix any blank rows or columns in the data

-- 4) For best results, set up the data in a named Excel table, to avoid problems

NOTE: There are detailed steps on the How to Sort in Excel page, if you need them.

Video: Sort Multiple Columns in Excel

See how to sort an Excel list by multiple columns. First, a 2-level sort, using the Quick Sort buttons. Next, do a 3-level sort, using the Sort dialog box.

There are written steps below the video, and a sample file to download.

Video Timeline

• 0:00 Sort by Multiple Columns
• 0:14 2-Level Sort
• 0:42 Plan the Sort Steps
• 1:28 3-Level Sort
• 1:52 Sort Dialog Box

Sort Multiple Columns With Sort Buttons

In Excel, you can quickly sort multiple columns in your data, by using the A-Z (ascending order) and Z-A (descending order) buttons on the Ribbon's Data tab.

• TIP: If you sort frequently, add the Sort buttons to your Quick Access Toolbar

Multiple Sorting Steps

If you only need to sort by 2 or 3 columns in Excel, I recommend using the Sort buttons, as a quick and efficient way to do that.

With this method, you will:

• Sort each column separately
• Sort the columns in reverse order of importance.

Sort By Region and Sales Rep

In this example, there is a worksheet list with sales orders, in a named Excel table:

• Region names in column B
• Sales representative (Rep) names in column C

Plan the Sorting Outcome

Before sorting any of the columns, decide how you want the sorted data organized.

For this list of sales orders, here's the result that I want:

1. Regions sorted alphabetically, A to Z, in column B
2. For each region, Rep names listed A to Z, in column C

For this Excel sort, Region column is more important, and Rep name is less important.

Sort by Reverse Importance

When sorting multiple columns, work in reverse order of importance.

1. First, sort the Rep column
2. Next, sort the Region column.

1) Sort Sales Rep Column

The Sales Rep column is less important in this sort, so follow these steps, to sort it first:

• Select any cell in the Sales Rep column.
• On the Excel Ribbon, click the Data tab.
• Click Sort A to Z (smallest to largest / oldest to newest)

In the list, the Sales Rep names are in alphabetical order

2) Sort Region Column

The Region column is most important in this sort, so follow these steps, to sort it last:

• Select any cell in the Region column.
• On the Excel Ribbon, click the Data tab.
• Click Sort A to Z (smallest to largest / oldest to newest)

In the list, the Sales Rep names are in alphabetical order

Check the Results

Immediately after sorting, and before you do anything else:

• Check the sorted data, to see if the data has been sorted correctly
• Did all the rows stay together, as expected?

Undo, If Necessary

If anything looks incorrect, or out of order:

• Immediately click the Undo button on the toolbar, or use the "Undo" keyboard shortcut: Ctrl + Z

Sort 3 Columns - Sort Command

Another way to sort multiple columns in Excel is with the Sort command on the Excel Ribbon. This command opens the Sort dialog box, where you can set up a multi-level sort.

Plan Sorting Outcome

In this example, I want to sort the sales order data by 3 columns, Cost, Item and Rep

1. Cost - largest to smallest
2. Then, in the rows for each cost, sort Item names A to Z
3. Finally, for each Cost/Item, sort Rep names A to Z

The screen shot below shows the result that I want. For example:

Named Table or Not?

The next step is to important! Be sure to check your data, to see how it is set up:

• Named Table: The sales orders in this example are in a named Excel table. This structure makes it easy to sort one or more columns, and avoid sorting problems.
• ⚠️Non-Table: For data that is NOT in a named Excel table, take extra precautions to avoid sorting problems. Be sure to select all the list cells (data and headinga), before you begin the steps below.

Open the Sort Dialog Box

To get started, follow these steps:

• First, select any cell in the Excel Table
  • OR, for a non-table list, select all cells and headings
• Next, on the Excel Ribbon, click the Data tab.
• Then, in the Sort & Filter group, click the Sort command button.

The Sort dialog box will open, ready for you to set the sort options.

In the Sort Dialog Box

In this example, the goal is to sort the sales order data by 1) Cost (Z-A), then 2) Item, and then 3) Rep.

• NOTE: When you use the Sort dialog box, the columns will be selected in order of their importance, so Cost will be the first level in the sort.

When you first open the Sort dialog box:

• There is a sorting level in the list, ready for you to select a Column name.
• The Sort On option, and the Order option have default settings showing.

Select First Level Column

Follow the steps below, to set up the first sorting level, for the Cost column:

• At the left of the Sort window, under the "Column" heading, click the drop-down arrow, to see a list of column names
• From the Sort by dropdown, select the first column you want to sort -- Cost, in this example.

Note: After you select a column name, the default settings might change, for the Sort On option, or the Order option. See the next section for more information.

Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers. (This option is automatically selected for a named Excel table, and cannot be turned off)

Select Sort Options

After you choose a column, you can leave the default settings for the sort options, or select different options.

Option 1: Sort On

In this example, the Cost column is selected, and the default setting for Sort On is Cell Values.

• To see the other options, click the drop-down arrow at the right side of the Sort On box
• The 4 options are Cell Values (default), Cell Color, Font Color, and Conditional Formatting Icon
• We want to sort the Cost column amounts, based on the values, so select the Cell Values option

Option 2: Order

In this example, the Cost column is selected, and the default setting for Order is Smallest to Largest.

• To see the other options, click the drop-down arrow at the right side of the Order box
• The 3 options are Smallest to Largest (default), Largest to Smallest, and Custom Sort
• We want to sort the Cost column amounts, with the highest amounts at the top, so choose Largest to Smallest

Add More Sorting Levels

To sort on multiple columns, you can add a level for each column that you want to sort.

• To add another level, click the Add Level button,
• Then, select options from its drop down boxes, just as you did for the first sort level

In this example we are sorting by Cost, then by Item (second level), and then by Rep (third level). In the screen shot below, all 3 fields have been added in the Sort dialog box.

• Sort On: All 3 fields are sorted on Values.
• Order: Because the Cost column contains only numbers, its Order drop-down options are slightly different from the text column options.

Apply the Sort

After you have selected all the Sort levels that you need, and their options, it's time to apply the sort settings.

• In the Sort dielaog box, click the OK button.

Check the Sort

After you click OK, the data will be sorted in the order that you specified.

• Warning: It's good practice to check the sorted data, immediately after you sort it.
  • Then, if you spot any problems, you can use the Ctrl+Z shortcut, to undo the sort.

For this example, in the screen shot below, everything was sorted as expected:

1. Cost is sorted first, largest to smallest
2. Then, in the rows for each cost, Item names are sorted A to Z
3. Finally, for each Cost/Item, Rep names are sorted A to Z

Get the Sample Workbook

To try the multi-column sorting techniques, download the Sort Multiple Columns sample workbook. The practice workbook is in xlsx file format, and is zipped. It does not contain any macros.

More Tutorials

Sort Data in Excel

Sort Data With Macros

Pivot Table Sorting

Sort a Row in Excel

Numbers Don't Sort Correctly