Home > Skills > Data Entry > Sorting

Sort Data in Excel & Avoid Problems

How to sort in Excel list by row or column. Tips show how to avoid painful mistakes when sorting your spreadsheets. How to sort multiple columns, rows, or sort in custom order.

Avoid Sorting Trouble: Check Your Data

In the sections below, there are 3 key steps, to help you avoid problems when sorting your data in Excel.

-- 1) Make a backup

-- 2) Check your data

-- 3) Fix blank rows and columns

1) Make a Backup of Your Data

Be sure to make a backup copy of your Microsoft Excel file, before you start sorting the data. Then, you can go back to the saved version, if anything goes wrong.

Tip: For a quick and easy backup copy, get my free Excel Backup tool. This tool makes a backup copy in the current folder, and does NOT affect the active workbook. The backup tool is in xlam format, so it's easy to install on your computer - just like any other Excel file.

2) Check Your Data

Before you sort data in Excel, be sure there are no blank rows or columns within the data range on the worksheet. The steps below show how to do that.

Why is it important to check your data?

• If there is a blank row or blank column in the data, part of the data might be sorted, while other data is not sorted.
• You could end up with names and phone numbers that don't match, or orders with the wrong customer address.
• Check for formulas that include an sheet name, in references to cells on the current sheet. For a formula example, see the VLOOKUP Sorting Problem below.

How to Check For Blank Rows or Columns

To help prevent sorting problems, follow these steps before you sort Excel data:

1. Select one cell in the column you want to sort.
2. Press Ctrl + A, to select the entire region.
3. Check the selected area, to make sure that all the data is included.
   • For example, in the screen shot below, hidden column E is blank
   • After pressing Ctrl+A, columns F, G (hidden), H and I are not selected.

3) Fix Blank Rows and Columns

1. If some of the data was NOT selected, find the blank rows or columns
• You might need to unhide rows or columns, to find the blank ones
2. After you find a blank row or column:
   • If the blank row or column is not needed, delete it
   • If the blank row or column IS needed, enter at least one item in the row or column.
     • For example, type an "x" in a column heading, as a placeholder.

Then, after you fix any blank columns or rows:

• Press Ctrl + A again, to see if the entire region is selected.
  • If not, look for other blank rows or columns, and delete or fill them
  • If the entire region IS selected, the data can be safely sorted

Quick Sort With Sort Buttons

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

Follow these steps to sort with the Quick Sort command buttons:

1. Select one cell in the column you want to sort.
2. On the Excel Ribbon, click the Data tab.
3. Click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)
• 

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.

If anything looks incorrect, or out of order:

• Immediately click the Undo button on the toolbar

Sort Buttons on Quick Access Toolbar

If you sort frequently, you can add the Sort buttons to your Quick Access Toolbar (QAT). That makes it even easier to do a quick sort in Excel. There are "how to" steps on the Quick Access Toolbar page. that show how to add buttons.

Then, to use those Sort icons, follow the steps in the Quick Sort with A-Z Buttons section, but use the QAT buttons instead.

Sort Two or More Columns

If you want to sort 2 or more columns in an Excel table, use the Sort dialog box, where you can set up a multi-level sort.

In this example, the table contains personal data, and it will be sorted by 3 columns:

1. First, by Gender
2. Next, by State
3. Finally, by Birth Year

Sort by 3 Columns

Follow these steps to safely sort the data set, by the 3 columns - gender, state and birth year:

• First, select all the cells in the list.
  • This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list -- but it's not 100% certain. Some of the data may be missed.
• On the Excel Ribbon, click the Data tab.
• In the Sort & Filter group, click the Sort button.

Add Sorting Levels

Next, follow these steps to add the first sorting level:

• Click the Add Level button, to add the first sorting level.
• From the Sort by dropdown, select the first column you want to sort.
  • In this example, Gender will be the first column sorted.

Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.

Select Sort On Option

• Next, from the Sort On drop down, select the option that you want.
  • We're sorting on the values in the Gender column, so leave the default setting of Values.

• Next, from the Order drop down, select one of the options.
  • The list of Order options will depend on what you selected in the Sort On column.
  • Because we selected values, the Order options are A to Z, Z to A and Custom List.
  • We'll select A to Z, which will sort the items from smallest to largest.

Add More Levels

If you are sorting 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 Gender, State and BirthYr. 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 BirthYr column contains only numbers, its Order 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:

• Gender column is sorted first, so all the female names are at the top.
• Next, the State column is sorted, so females from Alabama are at the top of the list.
• Finally, the BirthYr is sorted, with the earliest birth years at the top of each state.

Sort in a Custom Order

In the Sort dialog box, or on the Excel Ribbon, you can select a sort order, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom order, such as month order, or weekday order. In this example, we'll sort a column with weekday names, using the Excel Ribbon command.

Watch the steps for doing a custom sort in the Sort Custom Order video, or follow the written instructions, below the video.

Sort in a Custom Order

In the Sort dialog box, or on the Excel Ribbon, you can select a sort order, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom order, such as month order, or weekday order.

• Tip: To see how to create your own custom list for sorting, go to the Create a Custom List section, below.

Sort by Weekday Names

In this example, we'll sort a column with weekday names, using the Excel Ribbon command.

To sort in a custom order, follow these steps:

1. Select one cell in the column you want to sort.
2. Press Ctrl + A, to select the entire region.
3. Check the selected area, to make sure that all the data is included.
4. On the Excel Ribbon, click the Home tab
5. In the Editing group, click the arrow on Sort & Filter.
6. Click Custom Order.



7. In the Sort dialog box, select the Day column in the Sort By box.
8. From the Order drop down, select Custom List.



9. In the Custom dialog box, select a custom list and then click OK, twice, to close the dialog boxes.

   

The Day column is sorted in weekday order, instead of alphabetical order, so Sunday appears at the top of the list.

Create a Custom List

In Excel, you can create custom lists, like the built-in lists of weekdays and months. For example, you could create a custom list of districts, department names, or reporting categories, and then use the custom lists to sort the items in a list or in a pivot table.

Open the Custom Lists Dialog

To open the Custom Lists dialog box, follow these steps, or use the keyboard shortcut in the next section:

• On the Ribbon, click the File tab, and click Excel Options
  • If you don't see Options, click More... and then click Options
• At the left, in the list of categories, click Advanced
• At the right, scroll almost to the bottom, to find the General section
• In that section, click button - Edit Custom Lists.

Keyboard Shortcut to Open Custom Lists Dialog

This is more of a "longcut" than a shortcut, but here's how you can open the Custom Lists dialog box with keystrokes, if you prefer to keep your hands on the keyboard:

• First, to open the Options window, press: Alt + F, T
• Next, to select the Advanced category: Type A (twice)
• Next, to select the Edit Custom List button, press: Alt + O (4 times)
• Finally, to open the Custom Lists dialog box, press: Enter

Create a New Custom List

The entries for the custom list can be imported from a worksheet list, or typed in the Custom Lists dialog box. In this example, the list of cities is typed.

To create a new custom list, follow these steps:

1. In the Custom Lists dialog box, under Custom Lists, select NEW LIST
2. Click in the List Entries section, and type your list, pressing the Enter key after each item, to separate the list items.

   

3. In this example, the list is New York, Boston, Chicago, Seattle, Los Angeles, Dallas, Miami
   Tip: Instead of typing a list in the List Entries box, you can import a list from the worksheet by selecting the list and clicking the Import button
4. Click OK twice, to close the dialog boxes.

Use the Custom List

You can use the custom lists when sorting, and you can also use them with the AutoFill feature.

To quickly create a list, based on a custom list:

1. Type any item from a custom list in a cell
2. Select the cell, and point to the Fill handle

   

3. Drag down, up, left or right, to complete the list.

Sorting a Row

Instead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu.

You can see the steps in this short Sort by Row video, and read the detailed instructions below.

Sorting a Row

Instead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu.

To sort by a row, follow these steps:

1. Select one cell in the row you want to sort.
2. Press Ctrl + A, to select the entire region.
3. Check the selected area, to make sure that all the data is included.
4. Right-click a cell in the row that you want to sort
5. In the popup menu, click Sort, then click Custom Sort.



6. In the Sort dialog box, select the Day column in the Sort By box.
7. From the Order drop down, select Custom List.
8. At the top of the Sort dialog box, click the Options button.



9. In the Sort Options dialog box, under Orientation, select Sort Left to Right.



10. Click OK, to close the Options dialog box.
11. From the Sort By drop down, select the row that you want to sort.
12. There are no headings available, so select the correct Row number.



13. Select the Sort On, and the Order options, then click OK.

The data is sorted by the values in the selected row.

Sort Data by Cell Icon

Watch this short video to see the steps for adding cell icons, and sorting by the selected cell's icon. There are written steps below the video.

When you create a named Excel table, or apply an AutoFilter to a list, each heading cell gets a drop down arrow. Click that arrow, and you'll see a variety of sorting and filtering options for the data.

If you add conditional formatting icons to one of the columns, you can also sort by those icons. In the screen shot below, Traffic light icons are being added to the Quantity column.

Sort by Selected Cell Icon

After adding icons, the quickest way to sort by a specific icon is:

1. Right-click on a cell that contains the icon you want at the top of the list
2. In the pop-up menu, click Sort
3. Click Put Selected Cell Icon On Top

The list is sorted, to move all items with the selected icon to the top of the list.

Other items are not sorted, and the items that were moved to the top of the list are left in their original order, within that group.

Sort With Heading Drop Down List

After you have added cell icons, you can also sort by icon from the drop down list in the heading.

1. Click the drop down arrow in the heading cell
2. Click Sort by Color
3. Click on an icon, to move it to the top of the list

VLOOKUP Sorting Problem

A VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example:

=VLOOKUP('Order Form'!B5, Products!$B$2:$C$6,2,FALSE)

NOTE: This problem can occur with other functions too, such as an INDEX/MATCH lookup formula.

Watch this video to see the steps for fixing the problem, and download the VLOOKUP Sorting problem sample file to follow along. The written instructions are on the How to Use Excel VLOOKUP - Examples page.

Sort Sample Workbook

To try the sorting techniques, get the Sort sample workbook. The file is in xlsx file format, and is zipped. It does not contain any macros.

More Tutorials

Sort Data With Macros

Sort Data - Excel 2003 Basics

Excel Add-in Advanced Sorting

Pivot Table Sorting

Sort a Row in Excel

Numbers Don't Sort Correctly