How to sort data in Excel

excel logoSo last weekend E takes the kids to Borders for a little reward for being good (books are big in our house), and the little guy gets a book called Heckedy Peg, written by Audrey Wood, with very cool illustrations by Don Wood. The story is about a poor mother with seven children, named Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. She tells the children that because they have been good she will go to town and get them whatever they want. Each child has a special request, with Monday asking for bread, Tuesday asking for a pocket knife, Wednesday asking for a china pitcher, and so on. While the mother is away a witch named Heckedy Peg comes by and tricks the children into letting her in. She then turns the children into various types of food, and takes them to her hut in the woods for her own little twisted feast. On returning and finding her children missing the mother is understandably distraught, but a blackbird takes pity on the mother and leads her to the witch's house. Once there she has to guess which child was turned into which food, and get it right the first time or else! Well, I don't want to give up the ending so I'll stop there. But I'm sure at this point you're asking 'so what on earth does this have to do with sorting data in Excel?'. Well, I'll tell you...

The following examples all refer to Microsoft Excel 2003. If you are using a version that is different than that your screens will look slightly different, but the basic functionality should be the same.

Nothing. But it gives me a good example to use to show you how you do it. So let's start by entering in the days of the week in column A, starting in row 1 (you can use the autofill feature in Excel, click here to find out how).


excel sort data 01

Oops, I really want to have a heading at the top of the columns, describing what's in them. So now click on the '1' on the very left of the first row to highlight the entire row:


excel sort data 02

With the row highlighted right click anywhere on the row (you have to keep your mouse somewhere over the selected row) and select 'Insert' from the pop up menu:


excel sort data 03

This will insert a blank row above the highlighted row, moving the entire spreadsheet down 1 row.
Tip - you can insert more than one row at a time by highlighting more than one row. For example, if you wanted to insert 3 rows you would highlight 3 rows, then select insert just like described above and you will get 3 rows inserted above the row you've selected

OK, so let's call the 1st column 'Child', just type it in the first cell, in the first column (A1). While we're at it, let's name the two other columns we're going to be using - Request (B1), and Food (C1):


excel sort data 04

Now here are the children's requests and the food they were turned into by that nasty old witch. I'll just type it in, you can just copy it from what you see here:


excel sort data 05

Now, let's highlight the header row by making the text bold. Highlight the rows A1-C1 (click here to see how). Now, you could go up to the toolbar and click on the B, or you could just press the Control key and the B key (Ctrl + B) at the same time.


excel sort data 06

We've got it looking the way we want it, so now let's do some sorting, how about alphabetically by Food. Place your mouse cursor over the C at the top of the sheet to highlight the column:


excel sort data 07

Once you've done that go to the main menu and select Data -> Sort, and you'll get a dialog box like this:


excel sort data 08

It's going to default to 'Expand the selection', but for this example select the option 'Continue with the current selection'. You're going to get the dialog box below:


excel sort data 09

A couple of things to notice here. First, you'll see that Excel has already figured out that you have a header row (see it selected at the bottom of the dialog) and has omitted that row from the sort field. Next, notice that you can also select Ascending or Descending order for the sort, for up to 3 fields. Now go ahead and select 'OK', to see the changes it will make:


excel sort data 10

So you see that you've ordered the Food column alphabetically, but as you've probably already guessed it didn't keep the rest of the fields in the correct order. It only sorted the Food column, it didn't sort the rest of the cells with it. When Excel sorts data, it will only keep the data together if you've got it selected. You have to keep this in mind, because in complicated spreadsheets this can get confusing. Let's back out of this and do it the right way. Hit the 'Undo' button from the main toolbar:


excel sort data 11

Once you've done that your sheet should be back in the original state. Now select Data -> Sort again, only this time leave the option 'Expand the selection' selected. You'll get the same dialog as before, except this time all of the data should be selected. I've also clicked on the drop-down box so that you can see that you can select and sort from any of the columns:


excel sort data 12

Leave it on Food, then click on 'OK'. You'll see that it will sort by the Food column, but will also keep the rest of the data in order:


excel sort data 13

As long as you remember to select all of the cells that you want sorted you won't have any problems with sort. And if you have kids, read them the book! How many of you have figured out how the mother was able to guess what food each child had been turned into?


Bytes: