Search Contextures Sites

 

Contextures
Excel news
by email

We respect your email privacy

 

 

Learn how to create Excel dashboards.

 

 

Sorting Data in Excel

Quick Sort with Sort Buttons
Problems with Sorting Excel Data
Sort Two or More Columns
Video: Sort in Custom Order
Sort in Custom Order
Video: Sort Data by Row
Sort Data by Row
Download the Sample Sort Workbook

For Excel 2003 and earlier, see the Excel 2003 Sort Data Basics page

Quick Sort With Sort Buttons

In Excel,you can quickly sort your data by using the A-Z and Z-A Sort buttons on the Ribbon's Data tab. But, be careful, or one column may be sorted, while others are not.

Only use this technique if there are no blank rows or columns within the data.

  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)

    4. data tab sort a-z

  4. Before you do anything else, check the data, to ensure that the rows have sorted correctly. If things look wrong, immediately click the Undo button on the toolbar.

Problems with Sorting Excel Data

When you quickly sort data with the A-Z or Z-A button, things can go horribly wrong. If there is a blank row or blank columns within the data, part of the data might be sorted, while other data is ignored. Imagine the mess you'll have, if names and phone number no longer match, or if orders go to the wrong customers!

Follow these steps to help prevent problems when sorting 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, so columns at the left are not selected.

    4. sort hidden column

  4. If all the data was not selected, fix any blank columns or rows, and try again. Or, use the Sort Dialog box, as described in the next section.
  5. If all the data is selected, click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)
  6. Before you do anything else, check the data, to ensure that the rows have sorted correctly. If things look wrong, click the Undo button on the toolbar.

Sort Two or More Columns

If you want to sort 2 or more columns in an Excel table, you can use the Sort dialog box. In this example, we'll sort a table with personal data. First, the data will be sorted by Gender, then by State, and then by Birth Year.

  1. 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.
  2. On the Excel Ribbon, click the Data tab.
  3. In the Sort & Filter group, click the Sort button.

    4. data tab sort

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

    6. sort by drop down

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

    my data has headers

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

    7. sort on values

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

    8. sort order options

  8. If you are sorting on multiple columns, click the Add Level button, to add the next level, and select options from its drop down boxes.

    Here we have selected Gender, State and BirthYr as the sort fields, and all are sorted on Values. Because the BirthYr column contains only numbers, its Order options are slightly different from the text column options.

    sort order numbers

  9. After you have selected all the Sort levels, and their options, click OK.

The data will be sorted in the order that you specified. In the screen shot below:

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

    list sorted by 3 columns

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. 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. In this example, we'll sort a column with weekday names, using the Excel Ribbon comman

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. custom sort command

  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. select custom list

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

    select weekday custom list

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

sorted by weekday

Video: 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. custom sort popup menu

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

    9. sort options button

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

    10. 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. There are no headings available, so select the correct Row number.

    12. select row to sort by

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

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

sorted by total row

Sort Sample Workbook

To try the sorting techniques, you can download the Sort sample workbook. The file is in Excel 2007 format, and is zipped. It does not contain any macros.

Excel Sort Data Tutorials

1. Sort Data - Excel 2007/2010 - Basics
2. Sort Data - Excel 2003 - Basics
3. Sort Data -- VBA

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.