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:
AvoidSortingProblems_05b_B4_Sorting

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:
AvoidSortingProblems_10b_After_Incorrect_Sorting

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:

AvoidSortingProblems_15b_After_Correct_Sorting

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

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!

This entry was posted in Excel Sorting. Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>