Safely Sorting Data in Excel

It seems simple enough, but sorting data in Excel can go horribly wrong, if you aren’t careful. For example, you could sort a list of names, but miss the phone numbers in a nearby column. When you’re finished, all the names are associated with the wrong phone number.

Here are some tips for trouble free sorting:

1. Make a backup copy of your file before you sort the list. If there are problems with the sorted list, you can use a copy of your backup file instead.

2. Save the file before you sort the list. Then, if the list get scrambled, you can close the list without saving it again.

3. Before you sort, 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 not always. If there are blank rows or columns, some of the data may not be included in the sort, and the list will be scrambled.

Sort Excel Data by One Column

In some lists, you might want to sort by one column. For example, in a list of sales orders, you could sort by Order Number.

  1. Select all the cells in the list. Tip: Use the keyboard shortcut Ctrl + A.
  2. On the Ribbon, click the Data tab, then click Sort.
    DataSortCommand
  3. From the Sort by dropdown, select the column you want to sort.
    Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.
    SortHeaders
  4. From the Sort On drop down, select Values.
  5. From the Order drop down, select A to Z, or Z to A.
  6. Click OK

Sort Excel Data by Two or More Columns

Sometimes you need to sort by multiple columns. For example, in a list of sales orders, you could sort by Customer Name and then by Order Date. The customers would be sorted alphabetically, and if there are multiple orders for a customer, those orders would be sorted by date.

  1. Follow steps 1 to 5 in the Sort Excel Data by One Column instructions above, to sort by Customer Name.
  2. Click Add Level
    SortAddLevel
  3. From the Then By drop down, select the second column that you want to sort – Order Date in this example.
    SortTwoLevels
  4. Add other levels, if necessary.
  5. Click OK

_______________________

You may also like...

11 Responses

  1. Tim Mayes says:

    Deb, these are some good tips and I agree that sorting can be dangerous if you aren’t paying careful attention. I’m sure that most of us (me, at least) have been burned by this in the past.

    I recently got an email from a guy who had re-purposed a formula from one of my posts to create a checksum for each row in his worksheet. He was using an array formula to add up the Ascii codes of every character in the row and then using DEC2HEX to convert it to Hex for the checksum.

    I thought that was a fascinating way to make sure that his sorting didn’t get screwed up. I guess he had been burned one too many times by end users sorting incorrectly.

  2. Jon Peltier says:

    I rarely use the sort dialog. Dialogs keep me away from my work for precious moments.

    I always use headers on my working ranges, and I usually convert them to Lists (Excel 2003). In a list the sort options on the dropdowns are very reliable, and if you have headers on a range, the commandbar buttons are pretty reliable as well.

    To do multiple sorts, I will sort by each column needed, in reverse order: If I want to sort states in Column A, and within each state’s list I want cities in column B sorted, I sort first by column B then by column A. (This is perfectly logical, as it’s the protocol I used back in the day when I had stacks of punchcards encoded via Hollerith.)

  3. Tim, thanks, that’s a novel solution to the sorting problem. Of course, if the users can’t sort any more, they’ll find other creative ways to do what they want.. ;-)

    Jon, you’re right, the Lists feature helps prevent sorting problems. Lots of my clients are still using older versions though, so they can’t benefit from Lists. Most have stopped using punchcards though!

  4. Ken Puls says:

    Ugh… that brings back a bad memory. I built a nice solution for maintaining a customer database and a year later found out that our staff had stopped using it. Upon investigation, it seems that someone sorted only the name column so did exactly what you mentioned. All the names and addresses were mis-aligned. Rather than tell anyone, they saved it and walked away. By the time I found out, they’d already started correcting some entries, so the entire thing was suspect. It had all the headers, no blank columns… it never occurred to me that someone might do that. No wonder I’m so jaded now!

  5. Sorry Ken, I didn’t mean to dredge up bad memories. ;-) That’s one advantage of records in Access — they stick together, no matter how you sort them.

  6. Charles Urban says:

    I like to do what Jon Peltier commented about. In 2007, on occasion, more so than I remember with 2003, Excel will not recognize my header in my list. This happens more often after I have sorted then inserted a row above the table and tried to sort again.

    Maybe it goes without saying, but I need to remind my self often enough. There should be no blank items in the header row.

    When I copy data that has blank rows, I like to use the sort option to eliminate the blank rows. To do so, I create a dummy column with an item in each row. Then I sort the list.

  7. John Franco says:

    I agree with Jon Peltier

    The sorting is very reliable if you have column headers.

    To trust Excel 2003 Sorting just follow the Microsoft recommendations to work with lists (data regions):

    1. Use only one list per worksheet
    2. Put similar items in one column
    3. Keep the list separate
    4. Position critical data above or below the list
    5. Show rows and columns

    Enhance your Sorting efficiency with new Excel.

    Excel 2007 Table treat data region as a whole, you filter and sort more consistently

    Excel table manage blanks consistently too.

  8. Jeffrey weir says:

    I used to add a numeric column to the far right of whatever I was sorting, just in case I needed an easy way to get it back in the original order.

    THese days most of my data comes from SQL Server presorted, and has keys associated with it.

  9. Jeffrey, adding a column of numbers is a great idea, and something that I often do when sorting a list. Thanks for the reminder.

  10. Vic says:

    I have an index list that is a little over 8000 lines and 4 columns. When I add to the list and then sort, it scrambles some of the data. Last time it was just the D’s. What could be causing this?

  11. MK says:

    I always put headers on top of my columns, and am careful about how I sort, but apparently not careful enough. I’ve discovered that the right-hand third of my “source data” columns are scrambled. SHIT. This really sucks, and I think the Excel team could have done a better job of this. going forward I will convert my solution to store source data in Access, instead. I may go directly from Excel to Access, or I may just convert my existing Excel “source data” sheet to pull from Access. This is what I get for using Excel as a database–BAD.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *