Excel Exercise #5: Sorting Data and Printing Files

Printing a Spreadsheet

1. Enter the data on states provided below.

A B C D E F
1 State Crime Data Set
2 STATE URBAN MURDER POLICE ROBBERY REGION
3 CT 78.8 47 260 2180 North
4 MA 83.8 41 290 2355 North
5 ME 47.5 28 200 308 North
6 NH 52.2 25 240 420 North
7 NJ 89 69 350 3037 North
8 NY 84.6 127 370 6413 North
9 PA 69.3 68 240 1779 North
10 RI 87 44 280 1186 North
11 VT 33.8 22 200 389 North
12 IL 83 106 320 2170 Midwest
13 IN 64.2 89 210 1414 Midwest
14 IO 58.6 22 200 549 Midwest
15 KS 66.7 69 230 1131 Midwest
16 MI 70.7 102 250 2440 Midwest
17 MN 66.8 26 190 991 Midwest
18 MO 68.1 111 280 2236 Midwest
19 NB 62.7 44 220 822 Midwest
20 ND 48.8 12 180 77 Midwest
21 OH 73.3 81 210 2237 Midwest
22 SD 46.4 7 200 201 Midwest
23 WI 64.2 29 240 707 Midwest
24 AL 60 132 230 1321 South
25 AR 51.6 92 190 809 South
26 DE 70.7 69 280 1370 South
27 FL 84.3 145 300 3555 South
28 GA 62.3 138 240 1976 South
29 KY 50.8 88 200 952 South
30 LA 68.6 157 290 1970 South
31 MD 80.3 95 310 3927 South
32 MS 47.3 145 200 810 South
33 NC 48 106 220 823 South
34 OK 67.3 51 230 1049 South
35 SC 54.1 114 240 1181 South
36 TN 60.4 108 210 1806 South
37 TX 79.6 169 240 2085 South
38 VA 66 86 230 1201 South
39 WV 36.2 71 180 485 South
40 AK 64.5 97 350 900 West
41 AZ 83.8 103 310 1936 West
42 CA 91.3 143 270 3842 West
 43 CO 80.6 69 270 1601 West
44 HI 86.5 87 290 1902 West
45 ID 54 31 240 468 West
46 MT 52.9 40 240 340 West
47 NM 72.2 131 280 1279 West
48 NV 85.3 20 360 4606 West
49 OR 67.9 51 240 1524 West
50 UT 84.4 38 240 802 West
51 WA 73.6 55 210 1351 West
52 WY 62.8 62 310 444 West

2. If you were interested in printing this data, you would open the office button and select the
Print command. Let’s see how the spreadsheet would look like if we printed it in its current
form.
• Open the office button and select the Print option then Print Preview command. This
shows you what the file would look like if you printed it in its current form.
3. Click on the Next button to see the second page of the print out.
4. To view the entire page on the screen the text must be rather small.
• To get a better look at the data, click on the Zoom button.
• To return back to the full page view, click on the Zoom button a second time.
5. Click on the Close button to get out of Print Preview.
6. To dress up this print out. Open the office button and select the Print option then Print
Preview command.
• Select the Page Setup tab on the print group.
7. The Page options should appear on your screen.
• Click on the tab labeled Sheet.
8. In the space next to the Print Titles:
• Rows to Repeat at Top enter the cell range A1:F1.
9. Click on Margins tab and select the option Center on Page: Horizontally.
10. Click on the Header/Footer tab. The default header for the spreadsheet is the title of the
spreadsheet.
• To create a more description title on the print out, click on the Custom Header button.
In the center section type the title Selected State Data.
11. When you are finished making all of the changes you would like to make, simply click on
the OK button.
• To see what the printed spreadsheet would now look like, click on the office button and
select the Print option then Print Preview command.
12. Only two states appear on the second page which is a waste of paper.
• To help save a tree, or at least a twig or two, the spreadsheet can be fit on to one
page. To do this:
o Open the Page Setup option.
o Under the Page options click on Fit to 1 page(s) wide by 1 tall option.
o Now click on the OK button to accept this change.
13. Print Preview again. Notice the data for all the states now fits on one page.
• Display your version of the changes made above.
Sorting Data

1. Highlight the column headings for columns A through H.

• Click on the Data tab and select the Sort… option.
• Note: Since the first row of the spreadsheet has data labels we do not want these
labels to be sorted with the data, select My Data has Headers.
2. Click on the down arrow button in the Sort By option.
• This provides a list of all the variable labels in the first row of the spreadsheet.
• Select the MURDER label,
• Select the Descending option, then click on the OK button.
• The data in the spreadsheet is should now be arranged from the highest to the
lowest value for the welfare variable (which is the number of murders per million
residents).
3. For a different sort, highlight the column headings, open the Data tab, and select the
Sort… option.
4. In the Sort By… option select REGION and the Ascending option. In the Then Sort By…
option select STATE and the Ascending option. Then click on the OK button.
• The data should now be sorted by region, and within each region is arranged in
alphabetical order by state abbreviation.
5. Scroll down to the southern states. Highlight the range of cells from A23 to F38.
• The data for the southern states should be highlighted.
• Select the Print Area… command.
• Select the Set Print Area option. Preview the printout of this spreadsheet. You will
notice that the set print area command defines the range of a spreadsheet that you
want to have printed. Remember that earlier in this exercise the header for printouts
was set using options in the Page Setup command.

To print the entire spreadsheet, click on the office button - print preview – page set up - sheet
tab - on the Print Area command, select the Clear Print Area option.

6. Save your spreadsheet.