Excel – Charts 2
Continuing the Charts 1
Lesson
This lesson covers Pie, Combination, and Scatter
Point Charts. It also introduces statistical functions.
Instructions:
Read through the
set of learning objectives. This is what you should know when you finish the
lesson.
Watch the
YouTube videos before starting the lesson.
Complete every
step in the assignment, in the order given. Do not skip steps.
When you have
done all of the steps, fill out the Check for Understanding (CFU) form.
A completed
CFU for every lesson, inclass and homework, is due to be turned in during your
next class.
The CFU forms
are not to be submitted by email.
Where there
are differences between software versions, the instructions for Excel 2010 are
in Black, for the Mac are in green and for Excel 2013 are in blue.
Summary of Key Points:
Create a pie chart.
Combine two chart types to make a complex chart.
Create range names
Create and customize a Scatter Plot chart.
Create and use an advanced filter.
Use statistical functions.
Videos:
Videos – for MAC users:
https://www.youtube.com/watch?v=kiOYTpGHyac
https://www.youtube.com/watch?v=64RH0Hmon3Q
Creating a 3D visual pie chart. https://www.youtube.com/watch?v=azHNZbhu79A
Key Terms 

Function 
Definition 
Example 
Count 
The total number of
values in a list. Every entry in
the list is counted, even if a value is repeated in the list. 
In the set of numbers
1,2,3,4,4, the Count would be 5. 
Max 
The largest number in a
list of numbers. 
In
the set of numbers 1,2,3,4,4, the Max would be 4 
Mean 
The sum of the values in
a list of numbers divided by the total number of values in the list, commonly
called the average. Mean, Mode,
and Median are different ways to estimate the average value in a list of numbers. 
In
the set of numbers 1,2,3,4,4, the Mean would be The
sum (14) divided by the count (5). 14/5 = 2.8 
Median 
The middle number in a
list of numbers. Half the values in the list will be larger than the median,
half will be smaller. 
In
the set of numbers 1,2,3,4,4, the Median would be 3. 
Min 
The smallest number in a
list of numbers. 
In
the set of numbers 1,2,3,4,4, the Min would be 1. 
Mode 
The number that appears
most often in a list of numbers. 
In
the set of numbers 1,2,3,4,4, the Mode would be 4. 
StDev 
Standard Deviation  A measure of the dispersion of a set of data from
its mean. The more spread apart the data, the higher the deviation. 
In
the set of numbers 1,2,3,4,4, the Standard Deviation would be 1.30384. 
Var 
Variance  The variance (σ2) is a
measure of how far each value in the data set is from the mean. 
In
the set of numbers 1,2,3,4,4, the Variance would be 1.7 
If
you have the charts worksheet you created in the Charts 1 lesson, open it. If not, download and save the file
http://bus91L.altervista.org/Data/2013Data.xlsx Open it. Select the Sales
worksheet. It contains the information we will base our charts on.
Show Food Ingredient Costs by month.
1.
Use the mouse to select cells A3 to G3 in the
Sales worksheet. Hold down the control key and mouse over cells A19 to G19. 
2. From the Insert tab, click on the Pie icon
[ALT N Q]. Select the first chart in row 3. For Excel 2013
select the 3D pie chart in row 2. 
3. Rightclick in the chart area (outside
border.) Choose Move Chart. Select New Sheet and name it Pie. Click OK. 
4. Rightclick on the legend and choose
delete. 
5. Click on the pie. From the Chart Tools
Layout tab click on the Add Data Labels icon. Select Best Fit. For Excel 2013, Click on the pie. From the Chart Tools Design tab
click on the Add Chart Elements icon. Click on the arrow to the right of the
Data Labels option. Select Best Fit. 
6. Click on the pie. From the Chart Tools
Layout tab click on the Add Data Labels icon. Select More Data Label Options.
Change the label options to show the category instead of the value. Click on
Close. For Excel 2013, Click on the pie. From the Chart Tools Design tab
click on the Add Chart Elements icon. Click on the arrow to the right of the
Data Labels option. Select More Data label Options. Change the label options to show the category instead of the
value. Close the task pane. 
7.
Click
in the chart area (outside border). Choose Bold and a size 14 font from the
popup toolbar. For Excel 2013 rightclick in the chart area (outside border). Choose
Font from the popup menu. Choose Bold and a size 14 font. Close the task
pane. 
8. Drag the June wedge away from the rest of
the pie. (Hint, this requires three leftclicks on the wedge. Drag it after
the third click.) 
9. Fill the plot area with a light green
solid fill color. For Excel 2013 rightclick anywhere on the chart. From the
FillOutline dialog box select plot area. Fill the plot area with a light
green solid fill color. Close the task pane. 
2010 Pie Chart
2013 Pie Chart
Chart Five: Combining Charts
Video: Combination
Charts
We can combine
chart types. Here’s an example combining a line with a column chart.

Monday 
Tuesday 
Wednesday 
SUM 
Average 
Breakfast 
275 
385 
261 
=SUM(B2:D2) 
=AVERAGE(B2:D2) 
Lunch 
240 
246 
300 
=SUM(B3:D3) 
=AVERAGE(B3:D3) 
Dinner 
205 
260 
210 
=SUM(B4:D4) 
=AVERAGE(B4:D4) 
10.
Copy this table. Select
the range A1:F4 in a blank Excel worksheet. Paste the table into the range. 
11.
Resize the columns and rows so all of the data is
showing. 
12.
Select the range A1:F4. On the Insert tab of the
ribbon, click the column chart icon [ALT N C]. From the 2D options select clustered
column chart. 
13.
On the Chart Tools Design tab, click on the
Switch Row/Column icon. 
14.
Click on one of the average columns (the fifth
one in any meal period) right click, and select “Change Series Chart
Type … and then select Line with Markers. This will convert the Average column
into a line. For Excel 2013
Click on one of the average columns (the fifth one in any meal period) right
click, and select “Change Series Chart Type … and then select
Combo Chart. Click on the icon
above the word Custom. This will convert the Average and Sum columns into
lines. Click OK. 
15.
Hide column E, the column with the Sum values.
Save the file. Name it Charts2. 
2010 Chart
2013 Chart
Chart Six: Scatter Plot Chart
16. Sort the data in the first two columns of
the Attendance worksheet using the Sort icon [ALT A S S]. For the first
level use the Attendance column. For the second level use the Exam Score
column. Use the “smallest to largest” order for each column. Now
cells A2 and B2 should show 6 and 25. 
Note: If you are not seeing those numbers you are probably using an older version of the Testscores.xlsx file. Download the file from the link and try again.
Create Range Names
Add range names in the worksheet to use as arguments for the functions
you will be creating.
17. Type the range notation A1:B130 into the
Name Box to select the cells. Press Enter. Right click and choose Define
Name. Use DataList for the range name. Click OK. 
18. Type the range notation A2:A130 into the Name Box to select the cells. Press Enter. Right click and choose Define Name. Use Class for the range name. Click OK. 
19. Type
the range notation B2:B130 into the Name Box to select the cells. Press Enter. Right click and choose Define Name. Use Score for
the range name. Click OK. 
Add labels
20. In cell D1 enter the word Attended. 
21. In cells D2:D6 enter the numbers 6 through 10. 
22. In cell E1 enter the word SUMIF. 
23. In cell F1 enter the word COUNTIF. 
24. In cell G1 enter the words Average Score. 
25. In cell i1 enter the word Attendance. This must be identical to the label in cell A1. 
26. Select the labels in the first row. Apply a Bold font. [ALT H 1] 
27. Select columns A to J and
use the centering icon to format all the cells in the columns. [ALT H A C] 
Set up your
functions and formulas to find the values
28. In cell E2 enter the function =SUMIF(Class,D2,Score). Copy the function into
cells E3:E6. 
29. In cell F2 enter the function =COUNTIF(Class,D2). Copy the function into cells F3:F6. 
30. In cell G2 enter the formula =E2/F2. Copy the formula into cells G3:G6. 
Build the XY chart
31. Drag the mouse over the contents of cells
D1:D6. Hold down the CTRL key (Command key for
MACs) and drag the mouse over cells G1:G6. 
32. On the Insert tab, click on the Scatter Chart icon [ALT N D]. Select the first option, “Scatter With Only Markers.” In Excel 2013 choose the first chart: “Scatter.” 
33. From the Charts Tools Layout Tab, click on the Legend icon [ALT J A L] and choose None. In Excel 2013 the legend is not shown, so you can skip this step. 
34. From the Charts Tools Layout Tab, click on the Trendline icon [ALT J A N] and choose “More Options.” From the Trendline options, select the second one from the top, “Linear,” and the second one from the bottom, “Display Equation on Chart.” Click Close. The MAC should automatically show the Trendline. Some students have told me the Trendline feature is not available in a MacBook. For Excel 2013 From the Charts Tools Design Tab, click on the Add Chart Element icon. Click on the arrow to the right of Trendline and select More Options. From the Trendline options, select the second one from the top, “Linear,” and the second one from the bottom, “Display Equation on Chart.” Close the task pane. 
35. From the Charts Tools Layout Tab, click on the Gridline icon [ALT J A G] and choose Primary Vertical Gridlines. Select Major Gridlines. For Excel 2013 From the Charts Tools Design Tab, click on the Add Chart Element icon. Click on the arrow to the right of gridlines and select Primary Vertical Gridlines. Close the task pane. 
36. Right click on one of the numbers below the X Axis and select Format Axis. For the Axis Options change the Minimum setting to Fixed at 5, the Maximum setting to Fixed at 10 and the Major Units setting to Fixed at 1. Click on Close. 37. For Excel 2013 Right click on one of the numbers below the X Axis and select Format Axis. For the Axis Options change the Minimum setting to 5, the Maximum setting to 10 and the Major Units setting to 1. Close the task pane. 
38. From the Chart Tools Design Tab, change the chart style [ALT J C S] to “Style 12”, using the slightly larger red markers, in row two. 
39. Click somewhere inside the lined area of the chart. From the Chart Tools Format Tab, click on the Shape Styles dropdown menu [ALT J O S S] and change the chart background color to a pink gradient. This is the option called “Subtle Effect, Accent 2” and is the third option in row 4. If you can’t find this color effect, create your own background color effect. For Excel 2013 Right click anywhere on the chart. From the FillOutline popup box select “Plot Area.” Click on Fill, then Gradient, then More Gradients then Preset gradient. Choose the top value in the red gradients, 20%. Close the task pane. 
40. Click and drag to move the chart below the data in columns DG. 
41. Use the resize handle to make the chart about the same width as columns DG and change the height to make it a square shape. The handles are the eight sets of four dots scattered on the outside frame of the chart. 
42. Click and drag on the equation and move it to the center of the chart, above the Trendline. If you don’t see the equation, go back and look at step 34. Did you create it? 
43. Improve the visibility of
the numbers and labels. Rightclick in the outside part of the chart, next to
the title. A popup formatting toolbar will appear. Select Bold with a font
size of 12 or 14. Click on the chart to apply the changes. For Excel 2013 rightclick in the chart area (outside border).
Choose Font from the popup menu. Choose Bold and a size 14 font. Close the
task pane. 
2010 Scatter Point Chart
2013 Scatter Point Chart
Test the chart.
44. Change the value in cell B2 from 25 to 40.
The chart should change and give you a new Trendline
and a new equation. Change the value back to 25 to return to the original
chart. 
Find the Mode, Median, and Standard Deviation values
for the students who attended 8 classes. Start by extracting the data for that
set of students, using an Advanced Filter.
45. In cell i2 enter =”8” The quote
marks tell Excel to treat the 8 as a text character, not as a number. Be sure
to put the = sign outside of the quotation marks. 
46. Click in cell A2 or B2. On the Data tab of the ribbon click on the Advanced Filter icon [ALT A Q]. 
47. In the dialog box, choose “Copy to another location.” 
48.
The List range
should already be selected, A1:B130. 
49. Set the Criteria range to cells i1 and i2. 
50. Choose cells i4:j4 as the Copy to location. 
51. Click OK. You should see records in cells
i5:J15. 
52. In cell i16 enter the word Mode. In cell J16
enter the function =Mode(J5:J15). You should see 38. 
53. In cell i17 enter the word Median. In cell
J17 enter the function =Median(J5:J15) You should see 38. 
54. In cell i18 enter the word StDev. In cell J18 enter the function =STDEV(J5:J15) You
should see 4.480… 
55.
Change the value in
cell B24 from 27 to 40. The chart is interactive and will change but the
filtered data is not, so you will see the same Mode Median and StDev values in column J. 
56.
Change the value in
cell B24 back to 27. Save the file. If you see a loss of functionality
warning, ignore it. 
Review – Fine Points
Combination Chart 5:07
Combine
Two Graph Types in one Chart 1:23
Linking Chart to PowerPoint Slide 3:30
Videos – for MAC users:
https://www.youtube.com/watch?v=kiOYTpGHyac
https://www.youtube.com/watch?v=64RH0Hmon3Q
Creating a 3D visual pie chart. https://www.youtube.com/watch?v=azHNZbhu79A
Instructions for the Check for Understanding (CFU) form.
A CFU form is turned in for every assignment.
Starting with the line for your name, copy and paste the CFU form into a Word document.
Fill
in your name, date, section number
Circle
(or indicate by highlighting) a Y or N response for each statement. I do not
subtract points for No answers.
Below the list
of Y/N statements, insert ONE screenshot
of any part of your work. I just need to see something to prove that you did
the assignment. I don’t need to see all of the assignment. Resize the
screenshot, if necessary, so both the form and graphic can fit on one page.
A copy of the CFU is due to be turned in during the next
class session. I do not accept
CFU’s through email.
Name _______________________________
Date___________ Section
_______
Check for Understanding
– Excel Charts 2
Y/N I can create a pie chart.
Y/N I can combine two chart types to make a complex chart.
Y/N I can create range names.
Y/N I can create and customize a Scatter Plot chart.
Y/N I can create and use an advanced filter.
Y/N I can use statistical functions.