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, in-class 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:

Combination Charts

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.

 

Chart Four: Pie Chart with a 3-D Visual Effect

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 3-D pie chart in row 2.

3.       Right-click in the chart area (outside border.) Choose Move Chart. Select New Sheet and name it Pie. Click OK.

4.       Right-click 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 pop-up toolbar.

For Excel 2013 right-click in the chart area (outside border). Choose Font from the pop-up 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 left-clicks on the wedge. Drag it after the third click.)

9.       Fill the plot area with a light green solid fill color. For Excel 2013 right-click anywhere on the chart. From the Fill-Outline dialog box select plot area. Fill the plot area with a light green solid fill color. Close the task pane.

 

2010 Pie Chart

This is a picture of what the completed pie chart should look like.

 

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 2-D 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 Fill-Outline pop-up 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 D-G.

41.   Use the resize handle to make the chart about the same width as columns D-G 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. Right-click 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 right-click in the chart area (outside border). Choose Font from the pop-up menu. Choose Bold and a size 14 font. Close the task pane.

 

2010 Scatter Point Chart

This is a picture of what the X Y Scatter Plot chart should look like.

 

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

Combination Charts

 

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.