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

By DPI Integration Strategies

Learn more

• Integrating Spreadsheets in the Classroom Tutorials and guidelines

Related pages

• Introduction to spreadsheets: In this two-week unit the students will use a teacher made spreadsheet to learn how calculations work. Then student teams will work together to create their own "store" order form on a spreadsheet. The final part of the lesson is a simulated game show where students "shop" at each others stores, trying to spend an exact amount of money without going over, getting closer to their required amount than any other team.
• 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.
• 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, column, and width. Type 18, and click OK.)
   • Columns B, C, D, E, G, and H: 11 (Highlight any cell in columns B–H, click on format, column, and width. Type 11, and click OK.)
   • Column F: 5 (Highlight any cell in column F, click on format, column, and width. Type 5, and click OK.)
7. Format the 1st three rows of numeric data including columns G and H (Operating Revenue-Net Income) for commas with no decimal places. (Highlight the cells named above, click on format, cells, number, use 1000 separator, decimal equals 0, and OK.)
8. Format the Earnings Per Share values (B13:H13) for two decimal places. (Highlight the cells then click on format, cells, number, decimal equals 2.)
9. Use the fill handle to enter the next two quarterly labels. Remember to select both quarter labels first. (Highlight cells B8:E8, and click on edit, fill, series, autofill, and OK.)
10. Skip one column, and enter the following new column headings: TOTALS, AVERAGES on row 8.
11. Enter the formulas shown in the illustration for totals and averages. Enter other missing data if needed.
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 tool on the tool bar. Highlight cells A5:H5, click on the merge and center tool on the tool bar. Highlight cells A6:H6, click on the merge, and center tool on the tool bar.)
14. Copy your 1998 report, and paste it four rows below. (Highlight cells A6:H13; click on the copy tool on the tool bar, select cell A17, and click on the paste tool on the tool bar.)
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. (Click on view, 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 file, page setup, page tab, and landscape.)
20. Set the print area to print the worksheet data. (Click on file, page setup, page tab, fit to box, type 1 in the first box, and delete the number from the second box. Click OK.)

Print the workbook; save the changes.

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 Illustration
• 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.