CATE Excel Charts Lesson Plan
ACP Lesson Plan
| Title: Creating Excel Charts | Author: Sue Craver |
| Subject(s): Computer Applications - Microsoft Excel | |
| Grade Level(s): 9-12 | Total 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:
- For MS Excel formulas:
- Excel Formulas
- https://edu.gcfglobal.org/en/excel/intro-to-formulas/1/
- For creating MS Excel charts and graphs:
- Excel Charts
- https://edu.gcfglobal.org/en/excel/charts/1/
Use the data found on the following document:
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. | T | 25 minutes |
| Review Excel chart webpage with class. | T | 25 minutes |
| Students complete assignment Excel assignment. | I | 30 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.