Spreadsheet formulas

This lesson will provide practice using formulas, print options, functions, and adjusting column widths.

A lesson plan for grades 6–8 Computer/Technology Skills and Information Skills

By DPI Integration Strategies

Learn more

• Integrating Spreadsheets in the Classroom Tutorials and guidelines

Related pages

• Interdisciplinary Integrated Unit on DNA/Genetics Part B: Math: The second lesson of an interdisciplinary integrated unit on DNA and genetics, focusing on math. The other lessons in the unit focus on science and language arts.
• Planets' relative gravity spreadsheet: The students use online resources to find information about the eight planets in our solar system. Then, using the relative gravity of each planet, they will create a spreadsheet to calculate what the weight of an average man and woman (possibly their own weight or the weight of other objects) would be on each of the planets.
• Collecting and organizing data: In this lesson students will learn to collect data and organize the data into a chart using computer technology.

Related topics

• Learn more about spreadsheets and technology skills.

Help

Please read our disclaimer for lesson plans.

Legal

Learning outcomes

Modify and use spreadsheets to solve problems.

Teacher planning

Time required for lesson

30 minutes

Technology resources

• Microsoft Excel
• Carson Excel file

Pre-activities

Students should know how to format a spreadsheet.

Activities

Use Microsoft Excel to complete the exercise below. Carefully follow all directions and proofread your work.

Teachers note: Point Scale: -5 per error. These instructions may be printed from the Carson Instructions attachment.

Scenario:

You are to prepare a financial analysis for 1998 and 1999 Accounts Department of Carson Interiors. You already have the data for 1998; Tiawana, an 8th grade student from a local middle school, will provide you with the 1999 figures that you need.

1. Start Excel.
2. Open the file called Carson.xls.
3. Enter your first name in cell A2, your last name in cell B2, grade level in cell C2, and homeroom teacher’s name in cell D2.
4. Save the workbook as “Carson” along with your name on it in the student folder on your hard drive.
5. Bold the name Carson Interiors.
6. Change column widths as follows:
   • Column A: 18 (Highlight any cell in column A, click on format [in the “Cells” section of the “Home” tab] then select “column width.” Type 18, and click OK.)
   • Columns B, C, D, E, G, and H: 11 (Highlight any cell in columns B–H, follow the same directions as the previous step to get to the column width box. Type 11, and click OK.)
   • Column F: 5
7. Format the 1st three rows of numeric data including columns G and H (Operating Revenue-Net Income) for commas with no decimal places. (Click on “format” in the “Cells” section of the “Home” tab, click on “format cells,” when the box appears, click on “number.” Select the box for “Use 1000 separator” and change the Decimal places to 0.)
8. Format the Earnings Per Share values (B13:H13) for two decimal places. (Highlight the cells then right-click. Select “format cells,” “number,” and change the Decimal places value to 2.)
9. Use the fill handle to enter the next two quarterly labels. Remember to select both quarter labels first. (select the cell that says “June.” You will see a little black square in the bottom right corner. Click and hold on that black square and drag to the right. This will automatically fill in the next cell following the pattern that has already been established. You can do this to continue the pattern to the next square over, as well. )
10. Skip one column, and enter the following new column headings: TOTALS, AVERAGES on row 8.
11. Enter the formulas for totals and averages. (Click on the “Formulas” tab, Select “AutoSum.” In cell G10, insert the “Sum” function. To include the correct data, make sure the formula says “=SUM(B10:E10)” Hit enter. Select that cell. You will see a little black square in the bottom right corner. Click and hold on that black square and drag down to row 12. This will apply that same formula to the other rows, as well. Follow the same process to find the averages. Click on cell H10, then choose “Average” from the AutoSum button. To include the correct data, make sure the formula says “=AVERAGE(B10:E10)”
12. Center and bold all column headings.
13. Use the Merge and Center button on the toolbar to center the title lines over columns A-H. (Highlight cells A4:H4, click on the “Merge and Center” on the “Home” tab. Highlight cells A5:H5, click on the “Merge and Center” on the “Home” tab. Highlight cells A6:H6, click on the “Merge and Center” on the “Home” tab)
14. Copy your 1998 report, and paste it four rows below. (Highlight cells A6:H13; click on the Copy button on the “Home” tab, select cell A17, and click on the Paste button on the “Home” tab.)
15. Edit the copy that you pasted below to show the changes displayed in Tiawana’ 1999 report.
16. Create a header with the words Financial Information appearing on the left side and the date appearing on the right. (On the “Insert” tab, click on “Header and Footer,” and custom header to key in the information. Click on OK.)
17. Create a footer with the page number in the center.
18. Preview the report.
19. Change the Page Setup to print Landscape. (Click on the “Page Layout” tab, select “Orientation,” and select “Landscape.” )
20. Set the print area to print the worksheet data. (Select the cells you wish to include when printing. Click on “Print Area” under the “Page Layout” tab, and click “Set Print Area.”)

Save all changes. Print the workbook (under the “File” tab).

21. Close the workbook and exit Excel.

March June TOTALS AVERAGES

• Operating Revenues 1926722 1325238 1257613 980148 =SUM(B10:E10) =AVERAGE(B10:E10)
• Operating Income 263903 215326 257591 205328 =SUM(B11:E11) =AVERAGE(B11:E11)
• Net Income 147850 96478 154156 131419 =SUM(B12:E12) =AVERAGE(B12:E12)
• Earnings Per Share 0.8 0.52 0.83 0.7 =SUM(B13:E13) =AVERAGE(B13:E13)

QUARTERLY PERIODS ENDED 1999

March June September December TOTALS AVERAGES

• Operating Revenues 3449877 4598112 3664767 2445067 =SUM(Operating Revenues)
• Operating Income 454900 678345 855098 171564 =SUM(Operating Income)
• Net Income 645100 888121 202498 349111 =SUM(Net Income)
• Earnings Per Share 0.79 0.88 0.55 0.76 =SUM(Earnings Per Share)
• Tiawana’s 1999 figures

Assessment

Have students review the files of their peers to compare the printed information to the set of instructions to make sure the assignment is complete. Students should be provided a copy of the attached answer sheet. They will be required to deduct 5 points for each item that does not match the answer sheet then a classmate will double check the paper to see if all errors were detected. Sometimes students recognize what they did wrong during the process of evaluating the work of a peer.

Supplemental information

Attachments:

• Carson Spreadsheet
• Carson Description
• Carson Answer Sheet
• List of Assignment Titles for Computer Skills Course

Comments

This lesson was developed to help students understand how to use formulas to solve math problems.