CATE Excel Charts Lesson Plan


ACP Lesson Plan 


Title: Creating Excel ChartsAuthor: Sue Craver
Subject(s): Computer Applications - Microsoft Excel 
Grade Level(s): 9-12Total Time: 80 minutes

Overview / Description: 

In this lesson, students will learn how to enter data and formulas into an Excel spreadsheet. They'll also learn how to use the data to create and format graphs and charts. 

Learning goals/objectives:

After completing this activity, students should be able to . . .

  • Enter data into a spreadsheet.
  • Use formulas to create totals and net profit.
  • Create three different types of charts using the spreadsheet data.
  • Add formatting attributes to the charts and graphs.

Workplace Readiness Skill: 

    Social Skills  x Communication 
   Teamwork     x    Critical Thinking
   Attitude and Initiative  x  Planning and Organization
 x  Professionalism    Media Etiquette

Content Standards:

Wisconsin Standards for Business and Information Technology

Business Calculations

BCA3.a: Create and analyze spreadsheet

BCA3.a.5.h: Construct formulas and equations.

BCA3.b: Illustrate and evaluate graphical data

        BCA3.b.5.h: Examine and interpret tables, charts and graphs to make comparisons, predictions and inferences.

Materials: 

Students will need access to Microsoft Excel.

Use this webpage for Excel instructions:

Use the data found on the following document:

Download: Spreadsheet_Data.docx


Learning Activities:

Part 1: Start with the formula section.

https://edu.gcfglobal.org/en/excel/intro-to-formulas/1/

Use the web page listed above to start this section.

First, have your students open Microsoft Excel. Open the web page for the formula section. Have them download the practice workbook found on the web page. Have the students follow along on the practice workbook as you walk through each step on the web page.

Then play the video so they can have a review of what they just did.

Once that is complete, have the students complete the “challenge” at the end of the web page. They will use the practice workbook they have already downloaded.  

Part 2: Next, you will work through the charts and graphs section.

https://edu.gcfglobal.org/en/excel/charts/1/

Use the web page listed above to start this section.

Make sure your students have Microsoft Excel opened. Open the web page for the Excel charts section. Have the students download the practice workbook found on the web page. Have the students follow along on the practice workbook as you walk through each step on the web page.

Then play the video so they can have a review of what they just did.

Once that is complete, have the students complete the “challenge” at the end of the web page. They will use the practice workbook they have already downloaded.  

Part 3: Students create spreadsheet with three charts.

Students create a workbook using the data provided below. The data must be entered, and formulas must be used in the totals and net area. The formula for the total sections should be a “sum”. The formula for the net section should be the sales total minus the expenses total.

Once the data and formulas are entered, three (3) charts need to be included. They should be put on the same page as the data.

One chart should be a 3-D column chart, and the two remaining chart types can be determined by the student. The students need to also include enhancements to the charts. The charts should include proper titles.  Each chart should include proper chart elements – a title, a legend, data labels and any other elements that properly enhance the chart.

 [the data needed for the spreadsheet is also included as an attachment above]

Marinette Hose and Coupling Mfg.

Sales and Expenses for 2019

Expenses

Quarter 1

Quarter 2

Quarter 3

Quarter 4

Total

Rent

        4,000.00

        4,000.00

        4,000.00

        4,200.00

 

Utilities

           355.12

           319.05

           287.98

           352.64

 

Payroll

      11,532.97

      12,299.87

        9,564.81

      14,226.47

 

Insurance

           358.98

           358.98

           358.98

           358.98

 

Supplies

        1,468.92

        1,740.84

        1,725.77

        1,728.13

 

Total

 

 

 

 

 

 

 

 

 

 

 

Sales

Quarter 1

Quarter 2

Quarter 3

Quarter 4

Total

Hose Assembly

      16,562.87

      17,247.72

      13,184.65

      21,895.60

 

Accessories

           423.90

           506.18

           480.01

           789.66

 

Coupling Assembly

        1,228.09

        1,287.16

        1,494.67

        1,982.64

 

Total

 

 

 

 

 

 

 

 

 

 

 

Net

 

 

 

 

 

 

WHO (T=Teacher Focus Lesson; WG=Whole Group\; SM=Small Group; I=Independent)

Learning Activity Task                                                WHO is responsible
for this step?
Approximate time
for task
Review Excel formula webpage with class.T25 minutes
Review Excel chart webpage with class.T25 minutes
Students complete assignment Excel assignment.I30 minutes

Assessment:

Summative Assessment: Students will be completing an Excel spreadsheet with three charts included. They need to include proper formulas and chart information that is appropriate for each chart. 

Wrap-Up:

This exercise will help students to learn the basics of formulas and charts in Excel. When the assignment is completed, students will have the data properly entered and three appropriate charts.

Extension Activity (for intervention or enrichment):

  • At the conclusion of the activity, students could share their spreadsheets with a partner to check each other's work before turning it in.
  • Teacher could share actual business spreadsheets and charts so students can see how these documents are used in a business setting.

ATTACHMENTS 

Example Solution - student responses will vary.    

Download: ExcelDataSolution.xlsx


Return to top