Title:By Golly, It's Gallon Man!

Adapted by Rhonda Yezak

Objective:The students will utilize spreadsheet software to create a concrete model that approximates the standard units for capacity (gallon, quart, pint, cup).

Timeline:Two 45 minute class periods

Content Area: Math / Grade Level: 3 or 4
Materials/Resources:Microsoft Excel, blank sheets of 8 1/2 X 11 paper, scissors, glue or glue stick / Technology Focus:
Spreadsheet
TEKS:
Subject Area - Third Grade -- §111.15.3.11E, §111.15.3.15A
Fourth Grade -- §111.16.4.11B, §111.16.4.15A
Technology Applications - Third and Fourth Grade -- §126.3.1(A, B, E), §126.3.2(A, B, D), §126.3.7(B,C), §126.3.10(A,B), §126.3.11A

Procedures:

Before Coming to the Computer Lab

Discuss the concept of gallon, quart, pint, and cup with students.

In the Computer Lab

Get Started

  1. Go to StartProgramsMicrosoftExcel.
  2. Go to FileSaveas.
  3. Have each student navigate to their folder on your school’s server
  4. In the Filename box, highlight Book1 and type GallonManStudentName.
  5. Click Save.
  6. If the Task Pane is open on the right, click the X to close it.

Page Setup

  1. Go to FilePage Setup.
  2. Click the Margins tab.
  3. Highlight the Top margin number and change it to 0.8.
  4. Highlight the Left margin number and change it to 0.3.
  5. Highlight the Bottom margin number and change it to 0.5.
  6. Highlight the Right margin number and change it to 0.3.
    Note: using the Up and Down arrows will not give these values - you must highlight the current value and retype the new value.
  7. Click OK.

Add a Header

  1. Go to ViewHeader and FooterCustom Header.
  2. In the Left section, have students type their first and last names.
  3. Click OK (upper right).
  4. Click OK (bottom).
    Note: The name will only show in Print Preview
  5. Go to FileSave.

Format Row Height and Column Width

  1. Highlight cells A1 through D16.
  2. Go to FormatRowHeight.
  3. Enter 20 and click OK.
  4. Select ColumnA by clicking on the A in the gray bar (top).
  5. Go to FormatColumnWidth.
  6. Enter 5 and click OK.
  7. Select ColumnB and go to FormatColumnWidth.
  8. Enter 10 and click OK.
  9. Select ColumnC and go to FormatColumnWidth.
  10. Enter 20 and click OK.
  11. Select ColumnD and go to FormatColumnWidth.
  12. Enter 30 and click OK.
  13. Go to File > Save.

Enter and Fill Data - Column A

  1. Click in cell A1.
  2. Type CUP in all capitals (use the CapsLock key).
  3. In the bottom right corner of cell A1, find the Fill Down "spot" (the cursor will change to a black plus symbol). Click and drag down to cell A16 - the word CUP will be copied to all of those cells.
  4. With cells A1 through A16 still highlighted, go to FormatCellsFont.
  5. Make the following selections:
    a. In the Font box, choose Arial.
    b. In the Fontstyle box, choose Regular.
    c. In the Size box, choose 12.
  6. Select the Alignment tab (at the top).
  7. In the Textalignment section, click the down arrow of the Horizontal box and choose Center.
  8. Click the down arrow of the Vertical box and choose Center.
  9. Select OK.
  10. Go to FileSave.

Merge Cells - Column B

  1. Highlight cells B1 and B2.
  2. In the top toolbar, select the MergeandCenter button .
  3. Highlight cells B3 and B4.
  4. Click the MergeandCenter button.
  5. Highlight cells B5 and B6.
  6. Click the MergeandCenter button.
  7. Highlight cells B7 and B8.
  8. Click the MergeandCenter button.
  9. Highlight cells B9 and B10.
  10. Click the MergeandCenter button.
  11. Highlight cells B11 and B12.
  12. Click the MergeandCenter button.
  13. Highlight cells B13 and B14.
  14. Click the MergeandCenter button.
  15. Highlight cells B15 and B16.
  16. Click the MergeandCenter button.
  17. Save.

Enter and Fill Data - Column B

  1. Click in cell B1/B2.
  2. Type PINT in all capitals (use the CapsLock key).
  3. In the bottom right corner of cell B1/B2, find the Fill Down "spot" (the cursor will change to a black plus symbol). Click and drag down to cell B16 - the word PINT will be copied to all of those cells.
  4. With cells B1 through B16 still highlighted, go to FormatCellsFont.
  5. Make the following selections:
    a. In the Font box, choose Arial.
    b. In the Fontstyle box, choose Regular.
    c. In the Size box, choose 14.
  6. Select the Alignment tab.
  7. In the Textalignment section, click the down arrow of the Horizontal box and choose Center.
  8. Click the down arrow of the Vertical box and choose Center.
  9. Select OK.
  10. Save.

Merge Cells - Column C

  1. Highlight cells C1 and C4.
  2. In the top toolbar, select the MergeandCenter button .
  3. Highlight cells C5 and C8.
  4. Click the MergeandCenter button.
  5. Highlight cells C9 and C12.
  6. Click the MergeandCenter button.
  7. Highlight cells C13 and C16.
  8. Click the MergeandCenter button.
  9. Save.

Enter and Fill Data - Column C

  1. Click in cell C1/C4.
  2. Type QUART in all capitals (use the CapsLock key).
  3. In the bottom right corner of cell C1/C4, find the Fill Down "spot" (black plus symbol). Click and drag down to cell C16 - the word QUART will be copied to all of the cells.
  4. With cells C1 through C16 still highlighted, go to FormatCellsFont.
  5. Make the following selections:
    a. In the Font box, choose Arial.
    b. In the Fontstyle box, choose Regular.
    c. In the Size box, choose 16.
  6. Select the Alignment tab.
  7. In the Textalignment section, click the down arrow of the Horizontal box and choose Center.
  8. Click the down arrow of the Vertical box and choose Center.
  9. Select OK.
  10. Save.

Merge Cells - Column D

  1. Highlight cells D1 and D16.
  2. Save.

Enter and Format Data - Column D

  1. Click in cell D1/D16.
  2. Type GALLON in all capitals.
  3. Click in a blank cell.
  4. Click on cell D1/D16 again and go to FormatCellsFont.
  5. Make the following selections:
    a. In the Font box, choose Arial.
    b. In the Fontstyle box, choose Regular.
    c. In the Size box, choose 48.
  6. Select the Alignment tab and make the following choices:
    a. In the Textalignment section, click the down arrow of the Horizontal box and choose Center.
    b. Click the down arrow of the Vertical box and choose Center.
    c. In the Orientation section, change the Degrees box to 90.
  7. Select OK.
  8. Save.

Add a Border

  1. Highlight cells A1 through D16.
  2. Click the down arrow to the right of the Borders button on the top toolbar.
  3. Click the AllBorders option (third row, second button).
  4. Save.

Copy and Paste Cells

  1. Highlight cells A1 through D16 if they are not already selected.
  2. Go to EditCopy.
  3. Scroll down and click in cell A30.
  4. Press the Enter key one time - the data should now be pasted to cells A30 through D45.
  5. Save.

Create a Face with Drawing Tools

  1. Go to ViewToolbars and make sure Drawing is checked - check it if it is not checked.
  2. Highlight cells A17 through D29.
  3. Click the MergeandCenter button (top toolbar).
  4. In the bottom toolbar, click the Oval button one time.
  5. Move to the A17 through D29 area and click and drag a circle.
    Note: For a perfect circle, hold down the Shift key while drawing the circle.
  6. Move the circle to the middle of the area if necessary.
  7. Use the Oval tool to draw one "eye" on the face.
  8. With the "eye" still selected, go to the bottom toolbar and click the down arrow to the right of the FillColor button . Select a color for the "eye".
  9. With the "eye" selected, go to EditCopyEditPaste.
  10. Move the second "eye" to the desired location.
  11. Save.
  12. In the bottom toolbar, go to AutoShapesBasicShapes and choose the BlockArc button (fifth row, last choice).
  13. Move to the "mouth" area of the "face" and click and drag a "frown".
  14. With the "mouth" still selected, go to the bottom toolbar and click the down arrow to the right of the FillColor button . Select a color for the "mouth".
  15. Make sure the "mouth" is still selected and go to Draw (bottom toolbar) > RotateorFlipFlipVertical.
  16. Move the "smile" to the desired location.
  17. Save.
  18. Add a nose if desired and save.

Grouping

  1. Click on a white section of the "face".
  2. Hold the Shift key down and click on the remaining parts of the face until all are selected.
  3. Go to DrawGroup - the face should now move as one unit.
  4. Save.

Add Cell Color (Optional)

  1. Highlight cells A1 through A16.
  2. In the top toolbar, click the down arrow to the right of the FillColor button .
  3. Choose a light color for the background.
  4. Highlight cells B1 through B16.
  5. In the top toolbar, click the down arrow to the right of the FillColor button .
  6. Choose another light color for the background that is different from the first color choice.
  7. Repeat steps 4 - 6 for columns C and D.
  8. Save.
  9. Scroll down to the second chart and add background color to those cells as well.

Set Print Area and Print

  1. Highlight cells A1 through D46.
  2. Go to FilePrintAreaSetPrintArea.
  3. Go to FilePrintPreview.
  4. Make sure that Page 1 of 1 is showing on the bottom left of the screen. If correct, click Print. See NOTE below if it does not fit on one page.
  5. Select the color printer and choose OK.

NOTE: If the information does not fit on one page, follow these steps before printing.
a. Go to ViewPageBreakPreview.
b. Find the dashedblueline.
c. Left click, hold and drag the line down to the solidblueline. Release the mouse.
d. Go to ViewNormal.
e.Save.
f. Go to FilePrint.
g. Choose the color printer and click OK.

In the Classroom

  1. Have students cut the bottom chart into its separate pieces.
  2. Cut out the "face".
  3. Have students glue the pieces and the face on a clean sheet of paper to create Gallon Man (see sample). Make sure the "parts" are in the correct order.
  4. Have students trim the remaining chart and save it to use for reference.

Assessment:Correctly assembled Gallon Man; Teacher observation and questioning

Modifications:Provide a partially completed Microsoft Excel template; work in pairs
Extensions:Have students include ounces in the Gallon Man
Have students write an essay or poem which explains Gallon Man
Additional Resources:Discovering Gallon Man --
Meet Mr. Gallon --
teachnet.com - Remembering Units --

Skills:

Manage a spreadsheet / 
the skills utilized in the project
Create a new file / I / X
Identify parts of spreadsheet / I / X
Print a worksheet / I / X
Enter/Edit Data
Enter and edit data / I / X
Delete data / I / X
Performing Calculations
Create a simple formula / I
Use Sum function / I
Format Data
Change Column width/row height / I
Charting Data
Create a chart / I
Print a chart / I
Select the type of chart / I
Add titles / I
Add a legend / I
Choose data labels / I
Change font, size, and style of text / I
Change pattern/color of chart background / I
Change the pattern color of a series / I