Anguilla Library Computer Club
Spreadsheet Lesson #4: Sorting
In the last lesson you
learned how to turn numbers into graphs and charts.
In this lesson you learn how to sort your data.
Sorting is the process of putting things into an
orderly sequence based on some key value.
In this lesson we will create a spreadsheet with 26 rows
of data, then sort those rows by different values.
Activity: Sort with the Spreadsheet
Start a fresh spreadsheet and go to cell A1. We will fill it like the
sample shown above.
- Fill A1..A26 By Hand: Fill column A with the letters of the keyword, one
letter per cell. Start with
Q,W,E,R,T,Y,U,I,O,P along the top row, then A,S,D,F,G,H,J,K,L on the second
and ending with Z,X,C,V,B,N,M. You should stop in cell A26.
Hint: after pressing a letter, press the CURSOR DOWN
key to save that letter and move down to the next cell.
- Fill B1..B26 By Hand: Row 1 should have rolled off the screen when
you went to row 21. Jump back to A1 by pressing the HOME key.
Fill column B with single-digit numbers such as 3, 7, and 2,
from cell B1 to B26. Try not to enter the digits in numeric
sequence, like the screen above.
- Fill C1..E26 Automatically: Make three columns of orderly data.
Go to cell C1 by pressing HOME and CURSOR RIGHT twice. You will
now fill the range C1..E26 with incremented data using
a special function. Press the SLASH "/" key to pull up the
menu, D for Data, F for Fill. You are now prompted for the
range to fill. In cell C1 press ".", then CURSOR
RIGHT to E1, CURSOR DOWN to E26 and press ENTER. Press ENTER also to
Start Value:1 and
Notice that you have filled C1..E26 with ordered values: C1=1, C2=2, C3=3... C26=26, D1=27, etc.
- Sort by Column A: Press /DSD for Data, Sort,
and Data Range. You can type the Data Range as a1..e26
or cursor over it as you did in the last step.
To sort rows 1 to 26 by column A, type P for Primary Key
from the Sort menu, then press HOME to go to cell
A1 and ENTER to sort
on column A1..A26. Press ENTER again to sort in Ascending order (A,B,C,D...).
We are now ready to sort. Select Go from the Sort menu.
You should see column A sorted as A,B,C,... with the other columns
moved as well.
- Sort by Column B: Now sort the same range A1..E26 by the column B1..B26, but in
Descending order. The highest number should end up in row 1.
- Return to Original Order: To put the rows back in their original order, sort by
C1..C26 which were filled by the computer with 1,2,3...26 when
- What if the sort key values are names such as "Elmira"?
Does Bo sort before or after Bob?
Use the F2 edit key to modify the values in
column A to be names, then resort A1..E26 by column A to
observe the results.
- What if two rows have the same value for the
"primary sort key"? How do you decide which row comes first? Try
the Secondary Sort Key. Resort by Column B (Primary) and by
Column A (Secondary).