How to Avoid Disaster When Sorting

Sorting is another powerful feature of Excel, but if we’re not careful, we can create a problem from which only Undo can rescue us, and if we can’t Undo, we’re sunk. Let me explain.

Click this link to open an Excel 2007 file (zipped, no macros) that contains a list of some of the largest cities in the world, along with their associated country, in no particular order. Here’s what the file looks like before we sort the data:

Let’s see what happens when we sort the data by City (Hint: This is the incorrect way):

  • Click Cell A1.
  • In the Home tab, in the Editing group, click the Sort & Filter icon.
  • In the menu that appears, click the item labeled Sort A to Z.

Here’s what the file should look like:

Did you notice what happened? The cities were sorted, but the associated countries were not. Worse, there is no systematic way of getting the cities back to their original order, other than Undo, because the cities were not originally sorted in any particular order. Why did this happen? Because the hidden Column B was blank, so Excel assumed we only wanted to sort the data in Column A.

Can you imagine if this happened, and we couldn’t Undo it, especially if we were dealing with large amounts of data? Let’s Undo this, and use an approach that will avoid the problem we just experienced.

  • In the Quick Access Toolbar, click the Undo icon.
  • Click, hold and drag Rows 1 through 12.
  • In the Home tab, in the Editing group, click the Sort & Filter icon.
  • In the menu that appears, click the item labeled Sort A to Z.
  • Click Cell A1 to remove the highlights from the worksheet.

Here’s what the file should look like:


Note that the sort now worked properly, with the city properly being sorted along with it’s associated country.

For a more complete discussion of how to safely sort data, please see Chapter 5, “Sorting Data, Wrapping Text” in the book “Simply Learning Excel 2007” available at

I hope this topic will prevent you from experiencing the problem with sorting we talked about. In two weeks, we’ll talk about how to avoid problems when copying data with filters on. See you then!

About Robert Ryan

Robert F. Ryan, MBA, CPA, is an independent business consultant, educator, writer, and finance leader in a Fortune 50 multinational company. He has a passion for simplicity, quality, efficiency, and a unique ability to explain things to people, so they understand quickly and thoroughly. He's worked with spreadsheets extensively for almost thirty years, including VisiCalc, Lotus 1-2-3, and Excel.
This entry was posted in Excel Sorting. Bookmark the permalink.

Leave a Reply