Spreadsheets: Organize & Analyze Data

Learning Target:

• I can create a spreadsheet to organize, format and analyze data.

You will make a spreadsheet listing the first 10 U.S. presidents
including several facts about each president.

You will also be using functions to calculate some related data.

File Name: presidents_YourName.xls

Part 1: Setup Spreadsheet

1. Download, rename & open the spreadsheet file:
   presidents_template1.xls (right-click > Save Link as).
   
   
2. Be sure that the Formula Bar is showing.
   [View > check Formula Bar]
   
   
3. Type the following into each CELL listed:
   • A1: Last Name
   • B1: First Name
   • C1: Years Born
   • D1: Year Died
   • E1 (Function): Age at Death
   • F1: Year Inaugurated
   • G1: Year Presidency Ended
   • H1 (Function): Number of Years Served
   • I1: State Born
   • J1 (Function): Age at Inauguration
     
     
   • A2: Washington
   • B2: George
   • C2: 1732
   • D2: 1799
   • E2 (Insert Function): Clue: =D2-C2
   • F2: 1789
   • G2: 1797
   • H2 (Insert Function): Clue: subtract 'Year Presidency Ended' minus 'Year Inaugurated'
   • I2: VA (Use State postal abbreviations)
   • J2 (Insert Function): Clue: Subtract something...you can figure it out!
     
     
   • E12: Average
   • H12: Mode
   • J12: Average

Part 2: Collect Data & Calculate Using Functions

1. Collect Data:
Fill in the information for the first 10 presidents following the format started in Part 1.
Use the Web to do your research.

Do a key word search or try one of these websites:
http://www.ipl.org/div/potus/
http://en.wikipedia.org/wiki/List_of_Presidents_of_the_United_States
http://www.whitehouse.gov/history/presidents/index2.html



2. Calculate Using Functions:
Use cell E2 as your example to learn how to enter functions that will perform calculations.
Columns E, H, and J will require functions.

Save time by learning how to fill functions down a column.
http://www.uwec.edu/help/Excel07/ws-fills-m.htm#Mouse

Part 3: Format Spreadsheet

1. Create a Header with...
   ... the title “First 10 Presidents” (aligned center),
   ...Your Name, Date, and Class (aligned left).
   [View > Header and Footer > Customize Header > write in your information]
2. Scale page to fit on 1 page
   [File > Page Setup > Fit to 1 page wide by 1 tall]
3. Center all of the columns.
4. Bold the column headings.
5. Format all column headings to align center (vertical and horizontal) and to wrap text.
   
   How?
   1) Select header row.
   
   
   2) Open Format Cells menu.
   
   
   3) Choose the following highlighted options.
   
   
6. Adjust the column & row sizes to "fit" the information. No data should be hidden from view.
7. Adjust border(s) around the data table, if necessary.

Part 4: Analyze Data

1. Insert Functions: You will calculate average and mode to help you analyze the data.
   • In cell E13: Create a function that will calculate the average of the 'Age at Death' for the first 10 presidents.
     It will look like this -----> =AVERAGE(E2:E11)
   • In cell H13: Create a function that will calculate the mode of the 'Number of Years Served' for the first 10 presidents.
   • In cell J13: Create a function that will calculate the average of the 'Age at Inauguration' for the first 10 presidents.
     
     
2. Sort Data: You will COPY & PASTE your table to create a total of 4 identical tables, then sort each according to the following criteria:
   • List 1: Years Served, ascending (Chronologically)
   • List 2: Last Name(1), then First Name(2), ascending (Alphabetically)
   • List 3: State Born, descending
   • List 4: Age Elected, ascending
     
     
3. Answer the questions at the bottom of the spreadsheet.

Printing Guidelines:

• Remember to include the HEADER (see Part3 #1).
• Do a PRINT PREVIEW to ensure that all data fits onto one page!

Early Finishers

Create a multiplication table spreadsheet.