[home] 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.
  1. 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.
  2. 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.
  3. 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 select Start Value:1 and Increment:1. Notice that you have filled C1..E26 with ordered values: C1=1, C2=2, C3=3... C26=26, D1=27, etc.
  4. 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.
  5. 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.
  6. 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 we started.