Data Analysis - Sorting in Excel 2007

Have you ever made a long list of things and thought it would have been better if it were in a different order?

Basically, what you are doing there is called Data Analysis.  Don't let that big word scare you, we're going to learn how to do a sort in Excel.   You see, I had a client over and we were mangling big lists of emails for an invite to a party.  After deleting most of the emails, we had to put them into order to make sure that things weren't repeated.  If you sort them in A To Z order, that makes life a lot easier.

You'll be able to see quickly that you have 2 "Ramblingmoose" in the list, and while you can never have enough Ramblingmoose, there are times that two of that would be a problem.

So for this discussion I went to grab a list of foods.  It is actually from Amazon.com's food lists.  I didn't know you could order food from them but hey, everyone gets hungry.

As I was creating the list, I gave them all the "invalidemail.com" address.  We all have to have an address right? 

I also added in a couple duplicate items so they would show up quickly.  You will see that there is more "food" than just one when I sort the list.

The list is in column A.  When you have added all the items that you will need, click on the "Data Tab" in the Control Ribbon. The Control Ribbon will change to give you all the items that Microsoft believes are appropriate, and you will see that there is a "Sort" button there.   I helpfully hovered over that so it was lit up orange for you to spot it easily.

Select the columns you want to include in your sort.  You can do that by clicking the Blue Box above the 1 in the first column to select your entire spreadsheet or just Click on the individual columns while holding down the Shift Key.

Click on the Sort Button and you will find a new box pop up.   This will allow you to select the way you want to see your data.  It's called the Sort Criteria, and you can play with all the data that way to get it just right.

In the sort box, you can "Add Level" if you want more than one sort order.  If this were a real life example I may have a City, State, Zip list.  You may want to sort that on State first, then City order, then Zip to make it easier to spot the zip code of the city you want to mail something to.

Postal code for those out of the US.

You would add a Level for City, State, and Zip, then tell it to sort on Values, and Order would be A To Z.   Normally that is, because you can add many kinds of sorts in Excel.   I'll be keeping this one basic since you can explore that on your own.  The Sort On box has things like sorting on Color.  That will allow you to tag your data so that you can have all the "red" boxes sort to the top. 

In this case, since there is only one column, my sort box came up correct.   This picture is what happened when I clicked OK.

Excel happily sorted all the Food together into a block so I can work with it.  What I did was highlight all three rows of Food so that I can show you, however I will delete out two of them.  Simply highlight the two rows that are extra by clicking once on the row you want to delete - Row 15, and moving the mouse down to select Row 16.  Then hit the delete key.

No, you hit it too hard, showoff.  Since the data is gone, I won't chide you too much.

The result is a hole in your data. A big blank spot of two missing rows.

The way to fix it to get a "nice clean list" is to re-sort the data using the method you just tried:

• Select your columns,
• Select Data tab from the Control Ribbon,
• Select Sort button from the Control Ribbon,
• Make sure that you have your criterion correct,
• Click OK.