Sorting Records

Modules >>
Excel II >>
Sorting Records

Now that you have some data to investigate, you may think about some questions that you can answer by sorting data. We have some numerical information in this spreadsheet, but we might be curious to know which cause of death, sex, or province reports the highest value or the lowest value. For your assignment, you are asked to sort the "Sort" spreadsheet in your workbook. To do this,

Make sure you are viewing your "Sort" spreadsheet by clicking on its tab.
Before we get started, there is a fix to be made. Delete the year "1996" from cell E5 and change it to "PYLL". This is a better descriptor of the data in column E.
If your spreadsheet contains blank rows between each row of data, this would interrupt Excel's ability to interpret the data as one list. Now, it's no fun to go through and delete each blank row, so you could skip that. You could create a macro (a mini-program) in Excel to record and repeat some of the steps you take that are necessary. Fortunately, there is a shortcut. If you have blank rows, please do the following steps:
- Please select the entire spreadsheet (use Ctrl + A)..
- Go to the Edit menu and select Go To
- Click on the Special button
- Select Blanks and click OK
- All the blank lines will be selected. To remove these lines, go to the Edit menu and select Delete. From the window that follows, select Entire Row
- The data is now nicely compacted.

It is helpful to create a column label before doing any sorting, so Excel can distinguish between what is data and what is a heading. Excel recommends using a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the list. Since we want to separate labels from data, let's choose to use cell borders (not blank rows or dashed lines) to insert lines below the labels. Also, inserting a blank row between data and a heading is recommended. In our "Sort" spreadsheet, rows 1 to 5 do not contain any data, so we will have to distinguish these cells as separate. To do this,

Insert a row between the header lines and the data. Click on cell A5 then go to the Insert menu and select Row. Don't worry that the "Geography/Sex/etc." text isn't included. We're going to use those as a header row later.
Select rows 1 through 4 by putting your pointer over the number "1" in row one, then click and drag down until your pointer is over the number "4" in row 4.
Go to the Format menu and select Cells
Go to the Border tab and click on Outline.
Click OK
Click anywhere to de-select rows 1-4 (otherwise, only those cells will be sorted).
Resize the width of your columns so you can see the data better. Your sheet should look something like the following:

Now, before we sort the data, let's get rid of some of the footnotes, which we don't want included with the data as we sort it.
Scroll down to the bottom, select rows with this text and delete their contents by pressing the delete key on your keyboard. Alternatively, you could delete them by going to the Edit menu and selecting Delete. In my version of the sheet, they are rows 163 through 179 as shown below:

Now, we're ready to sort our data. Go to the Data menu and choose Sort (you may need to click somewhere on the data list for this to work)
Next, in the Sort pop-up window, click on the bullet "Header Row" to select it. This will tell Excel not to include the "Geography/Sex/etc." column headings in the sort. It will also use these headings in your "Sort by" drop-down menus, so it's easier to understand (e.g., sort by "Geography" instead of "Column A").
It may be our interest to look at the levels of Potential Years of Life Lost (PYLL) by province, then by Sex, and then by PYLL. Therefore, select Geography for provinces and ascending, then Sex and ascending, and then PYLL for Potential Years of Life Lost and ascending. This is shown in the image below.

click OK to activate the sort.
As you can see, the sort tells us that the greatest potential years of life lost (age 75) in Alberta males involves unintentional injuries, whereas in British Columbia, the greatest PYLL in males involves malignant neoplasms (cancers). We can answer other questions by changing the sort to include Sex and PYLL, etc. This is not the only dataset available off the web. StatsCan and other agencies may have data available for your students to analyze and report on.
There are various data tables available to you that you can use with your students, which include a variety of topics - from the environment to people. Contact information (1-800 number, TTY, fax, and email) is also available to help you find the material needed. Don't forget about the learning resources webpage mentioned in the first lesson, where lesson plans are also provided.

Please continue to the next section on Extracting Records.