Chapter 4: Presenting Data with Charts

Time Series Trend: Line Chart 1

The first chart we will demonstrate is a line chart. Figure 4.1 “52 Week Data for the S&P 500 and Microsoft” shows part of the data that will be used to create two line charts. The first line chart will show the trend of the S&P 500An aggregate price index for five hundred of the largest publicly traded US companies. stock index. This is an aggregate price index of five hundred of the largest publicly traded companies. This chart will be used to communicate a simple message: to show how the index has performed over a fifty-two-week period. We can use this chart in a presentation to show whether stock prices have been increasing, decreasing, or remaining constant over the designated period of time.

Before we create the line chart, it is important to identify why it is an appropriate chart type given the message we wish to communicate and the data we have. When presenting the trend for any data over a designated period of time, the most commonly used chart types are the line chart and the column chart. With the column chart, you are limited to a certain number of bars or data points. As you increase the number of bars on a column chart, it becomes increasingly difficult to read. As you scroll through the data on the worksheet shown in Figure 4.1 “52 Week Data for the S&P 500 and Microsoft”, you will see that there are fifty-two points of data used to construct the chart. This is generally too many data points to put on a column chart, which is why we are using a line chart. Our line chart will show the closing price for the S&P 500 on the Y axisThe vertical axis of a chart. and the week number on the X axisThe horizontal axis of a chart.. The following steps explain how to construct this chart:

1. Highlight the range A3:B55 on the Stock Trend worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Line button in the Charts group of commands (see Figure 4.2 “Selecting the Basic Line Chart”).
4. Click the first option from the list, which is a basic line chart (see Figure 4.2 “Selecting the Basic Line Chart”). This adds, or embeds, the line chart to the worksheet, as shown in Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet”.

Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet” shows the embedded line chart in the Stock Trend worksheet. Notice that three additional tabs, or contextual tabsHidden tabs on the Ribbon that contain commands related to a specific object. Contextual tabs become visible when the related object is added or activated., are added to the Ribbon. We will demonstrate the commands in these tabs throughout this chapter. These tabs appear only when the chart is activated.

As shown in Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet”, the embedded chartAny chart that is created and placed within a worksheet. is not placed in an ideal location on the worksheet since it is covering several cell locations that contain data. The following steps demonstrate common adjustments that are made when working with embedded charts:

1. Moving a chart: Click and drag the upper left corner of the chart to the center of cell H2.
2. Resizing a chart: Place the mouse pointer over the left middle sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the left side of Column H.
3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 2, the bottom “snaps” to the bottom of Row 17, and the right side “snaps” to the right side of Column P.
4. Adjusting the chart title: Click the chart title once. Then click in front of the letter S. You should see a blinking cursor in front of the letter S. This allows you to modify the title of the chart.
5. Type the following in front of the letter S in the chart title: 52 Week Trend for the.
6. Removing the legend: Click the legend once and press the DELETE key on your keyboard. This removes the legend from the chart. Since the chart contains only one data series, the legend is not necessary. Once you remove the legend, the plot area automatically expands.

Figure 4.4 “Line Chart Moved and Resized” shows the line chart after it is moved and resized. You can also see that the title of the chart has been edited to read 52 Week Trend for the S&P 500. Also notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.

Adjusting the Y Axis Scale

After creating an Excel chart, you may find it necessary to adjust the scale of the Y axis. Excel automatically sets the maximum value for the Y axis based on the data used to create the chart. However, the minimum value is usually set to zero. Depending on the data you are using to create the chart, setting the minimum value to zero can substantially minimize the graphical presentation of a trend. For example, the trend shown in Figure 4.4 “Line Chart Moved and Resized” appears to be increasing slightly. However, the S&P 500 increased by over 20% during this period, which is substantial. The presentation of this trend can be improved if the minimum value started at eight hundred. While it is certainly possible for the S&P 500 to fall below eight hundred, it is most likely remote. The following steps explain how to make this adjustment to the Y axis:

1. Click anywhere on the Y axis on the 52 Week Trend for the S&P 500 line chart (Stock Trend worksheet).
2. Click the Format tab in the Chart Tools section of the Ribbon.

3. Click the Format Selection button in the Current Selection group of commands (see Figure 4.5 “Format Axis Dialog Box”). This opens the Format Axis dialog box.

   Figure 4.5 Format Axis Dialog Box

   

4. Click the Fixed option next to the “Minimum” axis option in the Format Axis dialog box.
5. Click the input box for the “Minimum” axis option and delete the zero. Then type the number 800. As soon as you make this change, the Y axis on the chart adjusts.
6. Click the Close button at the bottom of the Format Axis dialog box.

Figure 4.6 “Adjusted Y Axis for the S&P 500 Chart” shows the change in the presentation of the trendline. Notice that with the Y axis starting at 800, the trend for the S&P 500 is more pronounced and reflects the substantial increase over the 52-week period. This adjustment makes it easier for the audience to see the magnitude of the trend.

Trend Comparisons: Line Chart 2

We will now create a second line chart using the data in the Stock Trend worksheet. The purpose of this chart is to compare two trends: the change in value for the S&P 500 and the Microsoft common stock. Chapter 3 “Logical and Lookup Functions” presented a personal investment portfolio where the investments were compared to a benchmark. The S&P 500 is a benchmark that is commonly used to judge the performance of individual stocks. The purpose and message of this chart is to show whether Microsoft is performing better or worse than the S&P 500 index. This type of analysis can be used to determine whether a stock should be sold, purchased, or held.

Before creating the chart to compare the S&P 500 and Microsoft, it is important to review the data in the range E4:F55 on the Stock Trend worksheet. We cannot use the price data for Microsoft and the S&P 500 because the values are not comparable. That is, the data for Microsoft is in a range of $22.00 to $28.00, but the data for the S&P 500 is in a range of 1,022 to 1,363. If we used these values to create a chart, we would not be able to see any substantial change in the trend for either the S&P 500 or Microsoft. Therefore, formulas were used to calculate the percent change in value for the S&P 500 and Microsoft for each week. For example, looking at cells E5 and F5 on the Stock Trend worksheet, you see that the S&P 500 increased 2.4% in week 2, whereas Microsoft increased 3.1%. The percent change calculations now provide an appropriate method of comparison. This is a very important step to consider when comparing trends.

The construction of this second line chart will be similar to the first line chart. The X axis will be the 52 weeks in the range A4:A55. However, the Y axis will be the percentages in the range E4:F55. This creates a problem because Columns B, C, and D will not be used in this chart. Therefore, we cannot simply highlight one contiguous range of cells to create the chart. In this chapter we will demonstrate two options for charting data that is not in a contiguous range. The following steps demonstrate the first option:

1. Highlight the range A3:A55 on the Stock Trend worksheet.
2. Hold down the CTRL key on your keyboard and highlight the range E3:F55.
3. Click the Insert tab of the Ribbon.
4. Click the Line button in the Charts group of commands.

5. Click the first option from the list, which is a basic line chart.

   Figure 4.7 “Trend Comparison Line Chart” shows the appearance of the line chart comparing the S&P 500 and Microsoft before it is moved and resized. Notice that Excel does not add a title to the chart.

   Figure 4.7 Trend Comparison Line Chart

   

6. Move the chart so the upper left corner is in the middle of cell H20.
7. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column P, the top is locked to the top of Row 20, and the bottom is locked to the bottom of Row 35.
8. Click the Layout tab in the Chart Tools section of the Ribbon.
9. Click the Chart Title button in the Labels group of commands. Select the Above Chart option from the drop-down list (see Figure 4.8 “Adding a Title to a Chart”). This adds a generic title above the plot area of the chart.
10. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: 52 Week Trend Comparison.

Figure 4.9 “Final Trend Comparison Line Chart” shows that Microsoft has not performed as well as the S&P 500 benchmark. From week 31 to week 52, Microsoft is showing a significant decline compared to the S&P 500, which continues to grow. What makes this chart effective is that an audience can quickly see how Microsoft compares with the S&P 500 over the 52-week period.

Frequency Distribution: Column Chart 1

A column chart is commonly used to show trends over time so long as the data are limited to approximately twenty points or less. For example, in Chapter 1 “Fundamental Skills” we showed a sales trend over a twelve-month period. Another common use for column charts is frequency distributions. A frequency distributionThe number of occurrences for an established set of categories. shows the number of occurrences by established categories. For example, a common frequency distribution used in most academic institutions is a grade distribution. A grade distribution shows the number of students that achieve each level of a typical grading scale (A, A−, B+, B, etc.). The Grade Distribution worksheet contains final grades for a hypothetical academic class. To show the grade frequency distribution, the numbers of students appear on the Y axis and the grade categories appear on the X axis. The following steps explain how to create this chart:

1. Highlight the range A3:B8 on the Grade Distribution worksheet. Column B shows the number of students that achieved a grade within the grade category shown in Column A.
2. Click the Column button in the Charts group section on the Insert tab of the Ribbon. Select the first format from the drop-down list of options, which is the Clustered Column format.
3. Click and drag the chart so the upper left corner is in the middle of cell H2.
4. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column P, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
5. Click the legend one time and press the DELETE key on your keyboard. Since the chart presents only one data series, the legend is not necessary.
6. Click the title of the chart twice so the cursor is placed in front of the word Class.
7. Type the following in front of the word Class: Final Grades for the.
8. Click any cell location on the Grade Distribution worksheet to deactivate the chart.

Figure 4.10 “Grade Frequency Distribution Chart” shows the completed grade frequency distribution chart. By looking at the chart, you can immediately see that the greatest number of students earned a final grade in the B+ to B− or the C+ to C− categories.

Creating a Chart Sheet

The charts we have created up to this point have been added to, or embedded in, an existing worksheet. Charts can also be placed in a dedicated worksheet called a chart sheetAn Excel worksheet that is designated for a chart.. It is called a chart sheet because it can contain only an Excel chart. Chart sheets are useful if you need to create several charts using the data in a single worksheet. If you embed several charts in one worksheet, it can be cumbersome to navigate and browse through the charts. It is easier to browse through charts when they are moved to a chart sheet because a separate sheet tab is added to the workbook for each chart. The following steps explain how to move the grade frequency distribution chart to a dedicated chart sheet:

1. Click anywhere on the Final Grades for the Class chart on the Grade Distribution worksheet.
2. Click the Move Chart button in the Design tab of the Chart Tools set of commands. This opens the Move Chart dialog box. You can use this dialog box to move the chart to a different worksheet or create a dedicated chart sheet.
3. Click the New sheet option on the Move Chart dialog box.
4. The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option (see Figure 4.11 “Moving a Chart to a Chart Sheet”). Type Class Grades. This replaces the generic name in the input box.
5. Click the OK button at the bottom of the Move Chart dialog box. This adds a new chart sheet to the workbook with the name Class Grades.

Figure 4.12 “Chart Sheet Added to the Workbook” shows the Final Grades for the Class column chart in a separate chart sheet. Notice the new sheet tab added to the workbook matches the tab name entered into the Move Chart dialog box. Since the chart is moved to a separate chart sheet, it no longer is displayed in the Grade Distribution worksheet.

Frequency Comparison: Column Chart 2

We will create a second column chart to show a comparison between two frequency distributions. Column C on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the entire college. We will use a column chart to compare the grade distribution for the class (Column B) with the overall grade distribution for the college (Column C). However, since the number of students in the class is significantly different from the total number of students in the college, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:

1. Highlight the range B9:C9 on the Grade Distribution worksheet.
2. Click the AutoSum button in the Editing group of commands on the Home tab of the Ribbon. This automatically adds SUM functions that sum the values in the range B4:B8 and C4:C8.
3. Activate cell E4 on the Grade Distribution worksheet.
4. Enter a formula that divides the value in cell B4 by the total in cell B9. Add an absolute reference to cell B9 in the formula =B4/$B$9.
5. Copy the formula in cell E4 and paste it into the range E5:E8 using the Paste Formulas command.
6. Activate cell F4 on the Grade Distribution worksheet.
7. Enter a formula that divides the value in cell C4 by the total in cell C9. Add an absolute reference to cell C9 in the formula =C4/$C$9.
8. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste Formulas command.

Figure 4.13 “Completed Grade Distribution Percentages” shows the completed percentages added to the Grade Distribution worksheet. The column chart uses the grade categories in the range A4:A8 on the X axis and the percentages in the range E4:F8 on the Y axis. Similar to the trend comparison line chart, this chart uses data that is not in a contiguous range. The following steps explain a second method for creating charts with data that is not in a contiguous range:

1. Activate cell H2 on the Grade Distribution worksheet. It is important to note that this is a blank cell that is not adjacent to any data on the worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Column button in the Charts group of commands. Select the first option from the drop-down list of chart formats, which is the Clustered Column. This adds a blank chart to the worksheet.
4. Click and drag the blank chart so the upper left corner is in the middle of cell H2.
5. Resize the blank chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column P, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
6. Click the Select Data button in the Design tab of the Chart Tools section of the Ribbon. This opens the Select Data Source dialog box.
7. Click the Add button on the Select Data Source dialog box. This opens the Edit Series dialog box.
8. In the Series name input box on the Edit Series dialog box, type the word Class.
9. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
10. Highlight the range E4:E8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart (see Figure 4.14 “Completed Data Series for the Class Grade Distribution”).

11. Click the OK button on the Edit Series dialog box.

    Figure 4.14 Completed Data Series for the Class Grade Distribution

    

12. Click the Add button on the Select Data Source dialog box.
13. In the Series name input box on the Edit Series dialog box, type the word College.
14. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
15. Highlight the range F4:F8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart.
16. Click the OK button on the Edit Series dialog box.
17. Click the Edit button on the right side of the Select Data Source dialog box under the Horizontal (Category) Axis Labels section. This is used to define the labels that will appear on the X axis of the chart and opens the Axis Labels dialog box.
18. Highlight the range A4:A8 on the Grade Distribution worksheet. This adds the range to the Axis Labels dialog box, and the labels appear on the X axis on the column chart (see Figure 4.15 “Final Settings for the Select Data Source Dialog Box”).
19. Click the OK button on the Axis Labels dialog box.

20. Click the OK button on the Select Data Source dialog box.

    Figure 4.15 Final Settings for the Select Data Source Dialog Box

    

21. Click the Chart Title button on the Layout tab of the Chart Tools section of the Ribbon. Select the Above Chart option from the drop-down list.
22. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: Grade Distribution Comparison.

Figure 4.16 “Completed Grade Distribution Column Chart” shows the final appearance of the column chart. The column chart is an appropriate type for this data because there are fewer than twenty data points and we can easily see the comparison for each category. An audience can quickly see that the class issued fewer As compared to the college. However, the class had more Bs and Cs compared with the college population.

Percent of Total: Pie Chart

The next chart we will demonstrate is a pie chart. A pie chartA chart used to show the percent of total for each component of a data set. is used to show a percent of total for a data set at a specific point in time. The data we will use to demonstrate a pie chart is related to the overall spending activity in the health-care industry. The Health Care worksheet contains data that shows total spending in the United States for the years 1969 and 2009. In 1969, the total amount spent in the United States for health-related expenses was over $66 billion. The pie chart shows how this $66 billion was funded. The following steps explain how to accomplish this:

1. Highlight the range A2:B7 on the Health Care worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the “Exploded pie in 3-D” option from the drop-down list of options.
5. Click and drag the pie chart so the upper left corner is in the middle of cell E2.

6. Resize the pie chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column M, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 17 (see Figure 4.18 “Pie Chart Moved and Resized”).

   Figure 4.18 Pie Chart Moved and Resized

   

7. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each wedge. Therefore, the legend is not needed.
8. Click the Data Labels button in the Layout tab of the Chart Tools section of the Ribbon.
9. Select More Data Label Options from the drop-down list. This opens the Format Data Labels dialog box.
10. Click the box next to the Value option under the Label Options section in the Format Data Labels dialog box. This removes the check mark (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
11. Click the Percentage option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
12. Click the Category Name option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
13. Click the Close button at the bottom of the Format Data Labels dialog box.

14. Click the Home tab of the Ribbon and then click the Bold button. This should bold the data labels on the pie chart.

    Figure 4.19 Final Settings in the Format Data Labels Dialog Box

    

15. Click the chart title twice.
16. Click in front of the year 1969 and type Health Care Spending by Source:.

Figure 4.20 “Final Health Care Pie Chart” shows the completed pie chart. You can quickly see that Health Insurance and Out of Pocket made up the majority of health-care spending in 1969. Similar to the column chart, the key to creating an effective pie chart is the number of categories presented on the chart. Although there are no specific limits for the number of categories you can use on a pie chart, a good rule of thumb is ten or less. As the number of categories exceeds ten, it becomes more difficult to identify key categories that make up the majority of the total. In this example, it is easy to see that two categories compose 75% of the total.

Percent of Total Trend: Stacked Column Chart

The last chart type we will demonstrate is the stacked column chartA chart used to show how a percent of total changes over time or between two or more entities.. We use a stacked column chart to show how a percent of total changes over time. For example, the data on the Health Care worksheet shows spending by source for 1969 and 2009. A stacked column chart can show whether there is any change in the percent of total for each source between the two years. The Y axis of the chart shows the percentage from 0% to 100%. The X axis shows the two years: 1969 and 2009. The following steps explain how to create this chart:

1. Highlight the range A2:C7 on the Health Care worksheet.
2. Click the Insert tab of the Ribbon.

3. Click the Column button in the Charts group of commands. Select the 100% Stacked Column format option from the drop-down list (see Figure 4.21 “Selecting the 100% Stacked Column Format”).

   Figure 4.21 Selecting the 100% Stacked Column Format

   

   Figure 4.22 “Initial Construction of the 100% Stacked Column Chart” shows the column chart that is created after selecting the 100% Stacked Column format option. As mentioned, the goal of this chart is to show the percentages on the Y axis and the years 1969 and 2009 on the X axis. However, notice that Excel places the spend sources on the X axis. The remaining steps explain how to correct this problem and complete the chart:

   Figure 4.22 Initial Construction of the 100% Stacked Column Chart

   

4. Click the Switch Row/Column button in the Design tab on the Chart Tools section of the Ribbon. This reverses the legend and current X axis categories (see Figure 4.22 “Initial Construction of the 100% Stacked Column Chart”).
5. Click and drag the chart so the upper left corner is in the middle of cell E19.
6. Resize the chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column N, the top is locked to the top of Row 19, and the bottom is locked to the bottom of Row 37.
7. Click the legend one time and press the DELETE key on your keyboard.
8. Click the Layout tab on the Chart Tools section of the Ribbon.
9. Click the Data Table button in the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.
10. Click the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon.
11. Select the Above Chart option for the drop-down menu.
12. Click the chart title two times. Delete the generic chart title name and type Change in Health Care Spend Source.

Figure 4.23 “Final 100% Stacked Column Chart” shows the final stacked column chart. Notice that the Out of Pocket category, or the amount of cash people paid for health-care expenses, decreased significantly from 1969 to 2009. However, the Health Insurance category increased significantly from 1969 to 2009. Overall, the chart shows that the total out-of-pocket and health insurance expense increased significantly from 1969 to 2009. These two categories made up approximately 75% of total health-care spending in 1969. By 2009, these two categories increased to over 80% of total health-care spending.

X and Y Axis Formats

There are numerous formatting commands we can apply to the X and Y axes of the chart. Although adjusting the font size, style, and color are common, many more options are available through the Format Axis dialog box (see Figure 4.5 “Format Axis Dialog Box”). The following steps demonstrate a few of these formatting techniques on the Grade Distribution Comparison chart:

1. Click anywhere along the X axis (horizontal axis) of the Grade Distribution Comparison chart on the Grade Distribution worksheet.
2. Click the Home tab of the Ribbon.
3. Change the font style to Arial. Notice that as the mouse pointer hovers over a font style, you can preview the change on the chart before you make a selection.
4. Change the font size to 11 points and bold the font. The final appearance of the X axis is shown in Figure 4.24 “Formatted X Axis”.
5. Click anywhere along the Y axis to activate it.

6. Repeat steps 3 and 4.

   Figure 4.24 Formatted X Axis

   

7. Click the Format tab in the Chart Tools section of the Ribbon.
8. Click the Format Selection button in the Current Selection group of commands. This opens the Format Axis dialog box.
9. Click Number from the list of options on the left side of the Format Axis dialog box (see Figure 4.25 “Formatting Numbers on the Y Axis”). The commands in this section of the Format Axis dialog box are used to format numbers that appear on the X and Y axes of a chart.
10. Click in the Decimal places input box and change the value to 0 (see Figure 4.25 “Formatting Numbers on the Y Axis”).
11. Click the Close button at the bottom of the Format Axis dialog box. The formatting adjustments are shown in Figure 4.26 “Completed X and Y Axis Formats”.

Chart Legend and Title Formats

The next items we will format on the Grade Distribution Comparison chart are the chart legend and title. Similar to the how we formatted the X and Y axes, we can format these items by activating them and using the formatting commands in the Home tab or the Format tab of the Ribbon. The following steps explain how to add these formats:

1. Click the legend on the Grade Distribution Comparison chart in the Grade Distribution worksheet.

2. Click and drag the legend so the top of the legend aligns with the 35% line next to the plot area (see Figure 4.27 “Moving the Legend”).

   Figure 4.27 Moving the Legend

   

3. Change the font style in the Home tab of the Ribbon to Arial.
4. Change the font size to 12 points.
5. Click the bold and italics commands in the Home tab of the Ribbon.
6. Click and drag the left sizing handle so the legend is against the plot area (see Figure 4.28 “Legend Formatted and Resized”).

7. Click and drag the lower center sizing handle so the bottom of the legend is aligned with the 25% line of the plot area (see Figure 4.28 “Legend Formatted and Resized”).

   Figure 4.28 Legend Formatted and Resized

   

8. Click the chart title to activate it.
9. Click the Format tab in the Chart Tools section of the Ribbon.
10. Click the More down arrow in the Shape Styles group of commands to open the complete set of preset format styles (see Figure 4.28 “Legend Formatted and Resized”).
11. Click the Subtle Effect – Blue, Accent 1 option, which is in the fourth row, second style from the left. As the mouse hovers over a style, you can preview the appearance on the chart.
12. In the Home tab of the Ribbon, change the font style to Arial and reduce the font size to 14 points (see Figure 4.29 “Chart Legend and Title Formatted”).

X and Y Axis Titles

Titles for the X and Y axes are necessary for defining the numbers and categories presented on a chart. For example, by looking at the Grade Distribution Comparison chart, it is not clear what the percentages along the Y axis represent. The following steps explain how to add titles to the X and Y axes to define these numbers and categories:

1. Click anywhere on the Grade Distribution Comparison chart in the Grade Distribution worksheet to activate it.
2. Click the Layout tab in the Chart Tools section of the Ribbon.
3. Click the Axis Titles button in the Labels group of commands.

4. Place the mouse pointer over the Primary Vertical Axis TitleCommand used to add a title to the Y axis of a chart. option from the drop-down list. This opens a second drop-down list. Select the Rotated Title option from the second drop-down list. This adds a title next to the Y axis (see Figure 4.30 “Selecting a Title for the Y Axis”).

   Figure 4.30 Selecting a Title for the Y Axis

   

5. Click the Format tab in the Chart Tools section of the Ribbon.
6. Click the Colored Outline – Blue, Accent 1 preset style option in the Shape Styles group of commands.
7. Change the font style in the Home tab to Arial. Change the font size to 11 points.
8. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Students.

9. Click and drag the Y axis title so it is between 0% and 30% in the plot area (see Figure 4.31 “Adding and Formatting the Y Axis Title”).

   Figure 4.31 Adding and Formatting the Y Axis Title

   

10. Click the Layout tab in the Chart Tools section of the Ribbon.
11. Click the Axis Titles button in the Labels group of commands.
12. Place the mouse pointer over the Primary Horizontal Axis TitleCommand used to add a title to the X axis of a chart. option. Select Title Below Axis from the second drop-down list.
13. Click the Format tab in the Chart Tools section of the Ribbon.
14. Click the Colored Outline – Blue, Accent 1 preset style option in the Shape Styles group of commands.
15. Change the font style in the Home tab to Arial. Change the font size to 11 points.
16. Click in the beginning of the X axis title and delete the generic title. Type Final Course Grade.

Figure 4.32 “X and Y Axis Titles Added” shows the added titles for the X and Y axes. The titles provide definitions for the grade categories along the X axis as well as the percentages on the Y axis.

Data Series Labels and Formats

Adding labels to the data series of a chart is a key formatting feature. A data seriesA quantitative data set that is displayed graphically on a chart. Data sets are typically displayed in the form of columns or lines on a chart. is the item that is being displayed graphically on a chart. For example, the blue bars on the Grade Distribution Comparison chart represent one data series. We can add labels at the end of each bar to show the exact percentage the bar represents. In addition, we can add other formatting enhancements to the data series, such as changing the color of the bars or adding an effect. The following steps explain how to add these labels and formats to the chart:

1. Click any red bar representing the College data series on the Grade Distribution Comparison chart in the Grade Distribution worksheet. Clicking one bar automatically activates all bars in the data series. If you click a bar a second time, only that bar is activated.
2. Click the Format tab in the Chart Tools section of the Ribbon.
3. Click the down arrow on the Shape Fill button in the Shape Styles group of commands.

4. Click the orange color square from the drop-down color palette (see Figure 4.33 “Changing the Color of a Data Series”). As you move the mouse pointer over other colors on the palette, you can preview the change on the data bars.

   Figure 4.33 Changing the Color of a Data Series

   

5. Click the Layout tab in the Chart Tools section of the Ribbon.
6. Click the Data Labels button in the Labels group of commands. Select More Data Label Options at the bottom of the drop-down list to open the Format Data Labels dialog box.
7. Click the Number option from the list on the left side of the Format Data Labels dialog box.
8. Select Percentage on the right side of the Format Data Labels dialog box (see Figure 4.34 “Adding Labels to a Data Series”).
9. Click in the Decimal Places input box and change the number of decimal places to zero.
10. Click the Close button at the bottom of the Format Data Labels dialog box.
11. Click the Home tab of the Ribbon.
12. Change the font style to Arial, change the font size to 9 points, and select the Bold command.
13. Click any blue bar in the Class data series.
14. Repeat steps 5 through 12.

Figure 4.35 “Completed Formatting Adjustments for the Data Series” shows the Grade Distribution Comparison chart with the completed formatting adjustments and labels added to the data series. Note that we can move each individual data label. This might be necessary if two data labels overlap or if a data label falls in the middle of a grid line. To move an individual data label, click it twice, then click and drag.

Formatting the Plot and Chart Areas

The last items we will format on the Grade Distribution Comparison chart are the plot and chart areas. We format these areas primarily to enhance the visibility of the data series. The following steps explain how to add these formatting enhancements to the chart:

1. Click anywhere in the chart area of the Grade Distribution Comparison chart in the Grade Distribution worksheet.
2. Click the Format tab in the Chart Tools section of the Ribbon.
3. Click the down arrow on the Shape Fill button in the Shape Styles group of commands.
4. Select the Tan, Background 2, Darker 25% option from the color palette (see Figure 4.36 “Formatting the Chart Area”).
5. Click anywhere in the plot area to activate it. Be sure not to click a grid line or one of the data series.
6. Click the Format tab in the Chart Tools section of the Ribbon.
7. Click the Shape Effects button in the Shape Styles group of commands.
8. Place the mouse pointer over the Bevel option from the drop-down list. Then select the Circle bevel option from the second drop-down list (see Figure 4.37 “Putting a Bevel Effect on the Plot Area”).

Figure 4.38 “Grade Distribution Comparison Chart with Formats Applied” shows the completed Grade Distribution Comparison chart. The darker shade on the chart area along with the bevel effect on the plot area make the data series the main focal point of the chart.

Adding Series Lines and Annotations to a Chart

The last formatting features we will demonstrate are adding series lines and annotations to a chart. To demonstrate these skills, we will use the Change in Health Care Spend Source stacked column chart. Series linesLines that are typically used in a stacked column chart to connect the data series between two or more stacks. are commonly used in stacked column charts to show the change from one stack to the next. AnnotationsNotes or comments that explain the nature and source of the data presented on a chart. are useful for clarifying the data presented in a chart or for identifying data sources. In addition to demonstrating these skills, we will review several of the formatting skills that were covered in this section. The following steps include the skills review as well as the new formatting features:

1. Locate the Change in Health Care Spend Source chart on the Health Care worksheet. Activate the chart by clicking anywhere inside the chart perimeter.
2. Move the chart to a separate chart sheet by clicking the Move Chart button in the Design tab of the Ribbon. Type the following sheet tab label in the New sheet input box: Health Spending Chart. Click the OK button.
3. Click anywhere on the X axis to activate it. In the Home tab of the Ribbon, change the font style to Arial, change the font size to 12 points, and select the bold command.
4. Activate the Y axis and apply the same formatting adjustments as stated in step 3.
5. Add a Y axis title using the Rotated Title option. In the Format tab under the Chart Tools section of the Ribbon, select the first preset style option, Colored Outline – Black, Dark 1, in the Shape Styles group of commands. Then, in the Home tab of the Ribbon, change the font style to Arial and the font size to 14 points.
6. Change the wording of the Y axis title to read Percent of Total Annual Spend.
7. Activate the title of the chart by clicking it once. In the Format tab under the Chart Tools section of the Ribbon, select the first preset style option, Colored Outline – Black, Dark 1, in the Shape Styles group of commands. Then, in the Home tab of the Ribbon, change the font style to Arial.
8. Click anywhere in the chart area to activate it.
9. Click the Format tab in the Chart Tools section of the Ribbon and click the down arrow on the Shape Fill button. Select the Olive Green, Accent 3, Lighter 60% option on the color palette.
10. Click anywhere on the plot area to activate it. Be sure not to click on a grid line.
11. Click the Shape Effects button in the Format tab of the Ribbon. Place the mouse pointer over the Bevel option from the drop-down menu. Select the first option from the Bevel format list, which is the “Circle” bevel option.
12. Click and drag down the top center sizing handle of the plot area approximately one inch (see Figure 4.39 “Adjusting the Size of the Plot Area”).

13. Click and drag up the bottom center sizing handle approximately three-quarters of an inch (see Figure 4.39 “Adjusting the Size of the Plot Area”). This step and step 12 are necessary to create space at the top and bottom of the chart to add annotations.

    Figure 4.39 “Adjusting the Size of the Plot Area” shows the Change in Health Care Spend Source chart prior to adding the series lines and annotations. Notice that the Ribbon has been minimized to improve the visibility of the chart. The remaining steps will focus on adding lines and annotations:

    Figure 4.39 Adjusting the Size of the Plot Area

    

14. Click the Layout tab in the Chart Tools section of the Ribbon.
15. Click the Lines button in the Analysis group of commands.

16. Click the Series Lines option from the drop-down list. This adds lines to the chart, connecting each data series between the two stacks (see Figure 4.40 “Selecting the Series Lines Option”).

    Figure 4.40 Selecting the Series Lines Option

    

17. Click any of the series lines added to the chart. Clicking one line will activate all lines on the chart (see Figure 4.41 “Activating the Series Lines”).

    Figure 4.41 Activating the Series Lines

    

18. Click the Shape Outline button in the Format tab of the Ribbon. Place the mouse pointer over the Weight option and select the “2¼ line weight” option.

    Figure 4.42 “Series Lines Added to the Stacked Column Chart” shows the appearance of the chart with the series lines connecting the two stacks. This formatting enhancement is common for stacked column charts. The lines help focus the audience’s attention to changes in the percent of total trend. In this case, the audience can quickly see the decline in the Out of Pocket category (blue) and the increase in the Health Insurance category (red).

    Figure 4.42 Series Lines Added to the Stacked Column Chart

    

19. Click anywhere in the chart area of the Change in Health Care Spend Source chart.
20. Click the Text Box button in the Insert tab of the Ribbon (see Figure 4.43 “Lines Added to the Stacked Column Chart”).
21. Place the mouse pointer on the left edge of the chart area approximately one-quarter inch from the top. Click and drag a rectangle approximately one and a half inches wide and one-quarter inch high (see Figure 4.43 “Lines Added to the Stacked Column Chart”).
22. Click the Home tab of the Ribbon and change the font style to Arial, change the font size to 10 points, and select the bold and italics commands.

23. Type Dollars in Millions. This tells the audience that the numbers have been truncated and represent denominations in millions. This means you would add six zeros to the end of each number on the chart. Therefore, the Out of Pocket value for 1969 is shown as $22,617 but is actually $22,617,000,000, or $22.6 billion.

    Figure 4.43 Lines Added to the Stacked Column Chart

    

24. Repeat steps 19–22 to add a second text box to the chart. Begin drawing this text box below the first box approximately one inch in from the left edge of the chart (see Figure 4.43 “Lines Added to the Stacked Column Chart”). Complete the formatting changes in step 22 and select the Align Text Right command.
25. Type 100% = in the second text box.
26. Repeat steps 19–22 to add a third text box to the chart. Center this text box over the 1969 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
27. Type $66,172 in the third text box.
28. Repeat steps 19–22 to add a fourth text box to the chart. Center this text box over the 2009 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
29. Type $2,486,293 in the fourth text box.
30. Repeat steps 19–22 to add a fifth text box to the chart. Begin drawing this text box at the bottom left edge of the chart, just below the data table. The text box will need to be at least four inches wide.
31. Type Source: US Department of Health and Human Services in the fifth text box.

Figure 4.44 “Completed Stacked Column Chart with Annotations” shows the completed Change in Health Care Spend Source stacked column chart. The lines and annotations provide key information for understanding the data and interpreting the trends presented on the chart.

Supply and Demand: The Scatter Chart

A common use for a scatter chart is the study of supply and demand curves. This is because the data points for both the supply and demand lines require quantitative values on both the X and Y axes. The Y axis contains the price of a certain good or item; the X axis contains the quantity sold for that good or item. Fundamental economic laws state that as prices rise, sellers are willing to increase supply and sell more goods. However, the reverse is true for consumers. As prices rise, consumers purchase fewer goods. The Supply & Demand worksheet contains hypothetical data for the supply and demand of breakfast cereal. There are ten data points to show the change in supply and demand as the price changes in Column A. The values you see in Columns A through C are formula outputs that are driven by the percentage in cell C14. For example, if the percentage in cell C14 is changed to 10, each price listed in Column A will increase, as shown in Figure 4.45 “Hypothetical Supply and Demand Data”.

We will use the scatter chart to study the change in quantity supplied and demanded as the price increases over ten data points, as shown in Figure 4.45 “Hypothetical Supply and Demand Data”. For many of the charts demonstrated in this chapter, we were able to highlight a range of cells and insert the chart type we needed. This was especially the case when the data was in a contiguous range of cells. However, this method rarely works when creating a scatter chart, even if the data are in a contiguous range. As a result, the method we present here starts with a blank chart and demonstrates how each data series is added to the chart individually. The following steps explain how we create this chart:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Activate cell E1 on the Supply & Demand worksheet. It is important to note that this cell location is not adjacent to any data on the worksheet.
3. Click the Scatter button from the Charts group of commands on the Insert tab of the Ribbon.

4. Select the Scatter with Smooth Lines and Markers format from the drop-down list of options (see Figure 4.46 “Selecting a Scatter Chart Format”). This adds a blank chart to the worksheet.

   Figure 4.46 Selecting a Scatter Chart Format

   

5. Click and drag the chart so the upper left corner is in the center of cell E2.
6. Resize the chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column M, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 17.
7. Click the Design tab in the Chart Tools section of the Ribbon. Then click the Select Data button in the Data group of commands. This opens the Select Data Source dialog box.
8. Click the Add button on the left side of the Select Data Source dialog box. This opens the Edit Series dialog box. Notice on this dialog box there are inputs for defining values for both the X and Y axes. Charts that we previously created using this method only had an input for putting values on the Y axis.
9. Type the series name Demand. This should appear in the Series name input box.
10. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
11. Highlight the range B3:B12 on the Supply & Demand worksheet. You will see this range appear in the Series X values input box after it is highlighted.
12. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.

13. Highlight the range A3:A12 on the Supply & Demand worksheet.

    Figure 4.47 “Defining the Demand Data Series” shows the final settings in the Edit Series dialog box for the Demand data series. You will see that as the X and Y axis values are defined in the dialog box, they appear on the chart. The chart in this figure shows the price along the Y axis and quantity along the X axis.

    Figure 4.47 Defining the Demand Data Series

    

14. Click the OK button at the bottom of the Edit Series dialog box.
15. Click the Add button on the left side of the Select Data Source dialog box.
16. Type the series name Supply. This should appear in the Series name input box.
17. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
18. Highlight the range C3:C12 on the Supply & Demand worksheet. This range appears in the Series X values input box after it is highlighted.
19. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.
20. Highlight the range A3:A12 on the Supply & Demand worksheet.
21. Click the OK button at the bottom of the Edit Series dialog box.
22. Click the OK button at the bottom of the Select Data Source dialog box.

Figure 4.48 “Scatter Chart Showing One Price” shows the appearance of the scatter chart before any formatting enhancements are applied. Notice only two plot points are located on the chart. This is because the price change value in cell C14 is still zero. Therefore, the data are not reflecting any change in price, quantity demanded, or quantity supplied. The chart shows that at the current price of $1.50, suppliers are willing to provide fewer units compared with the number of units consumers are willing to buy.

The following steps explain the formatting enhancements we will apply to the scatter chart shown in Figure 4.48 “Scatter Chart Showing One Price”:

1. Add a title to the chart by clicking the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon. Use the Above Chart option from the drop-down list.
2. Select Subtle Effect – Orange, Accent 6 from the preset style list in the Shape Styles group of commands on the Format tab of the Ribbon.
3. Change the font style of the chart title to Arial and the font size to 14 points.
4. Change the wording of the chart title as follows: Supply and Demand for Breakfast Cereal.
5. Add a title to the Y axis. Use the Rotated Title option from the Primary Vertical Axis Title drop-down list after clicking the Axis Titles button in the Layout tab of the Ribbon.
6. Repeat steps 2 and 3 to format the Y axis title. However, change the font size to 12 points.
7. Change the wording of the Y axis title as follows: Price per Unit.
8. Add a title to the X axis.
9. Repeat steps 2 and 3 to format the X axis title. However, change the font size to 12 points.
10. Change the wording of the X axis title as follows: Quantity in Units.
11. Make the following format changes to the X and Y axis values: font style Arial, font size 11 points, and bold.

12. Change the color of the chart area to Aqua, Accent 5, Lighter 40% (see Figure 4.49 “Formatting Enhancements Added to the Scatter Chart”).

    Figure 4.49 Formatting Enhancements Added to the Scatter Chart

    

13. Apply a bevel effect to the plot area. Use the Circle format option from the Bevel drop-down list of options.
14. Change the font style of the legend to Arial and bold the font.
15. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, you will see the lines change on the chart.

Figure 4.50 “Scatter Chart with Price Change at 2%” shows the completed scatter chart when the Price Change is set to 2%, and Figure 4.51 “Scatter Chart with Price Change at 14%” shows the same chart when the Price Change is set to 14%. The point at which the demand and supply lines intersect on Figure 4.51 “Scatter Chart with Price Change at 14%” is known as the market equilibrium point. The market equilibriumA state in which the quantity demanded equals the quantity supplied at a specific price. is where the quantity demanded equals the quantity supplied at a specific price. The price where quantity demanded equals quantity supplied is referred to as the equilibrium priceThe price where quantity demanded equals quantity supplied..

Changing the Scale of the X and Y Axes

For all the charts demonstrated in this chapter, Excel has automatically established the scale for the Y axis. For scatter charts, Excel has also established the scale for the X axis. The axis scaleThe minimum and maximum value that appears on the X or Y axis of a chart. is the minimum and maximum value that appears on an axis. For example, in Figure 4.51 “Scatter Chart with Price Change at 14%”, the Y axis scale is set to a minimum value of zero and a maximum value of 6.00. Although this is a very convenient feature of Excel, you may want to change the scale in some instances. If you change the value in cell C14 on the Supply & Demand worksheet, the lines jump or shift on the plot area of the chart. This is because Excel keeps rearranging the scale of both the X and Y axes. When studying the shape of lines, it is best to set the scale so it does not change. The following steps explain how to accomplish this:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Click anywhere on the Y axis of the chart.
3. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box.
4. Click the Fixed option next to the Minimum setting under the Axis Options in the Format Axis dialog box. This ensures that the minimum value for the Y axis will always be zero.
5. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
6. Click in the input box next to the Maximum setting. Remove the 1.6 and enter the number 5.0. We will not be studying the behavior of supply and demand beyond a $5.00 price point, so there is no need to extend the Y axis beyond this point.
7. Click the Fixed option next to the Major Unit setting under the Axis Options in the Format Axis dialog box.
8. Click in the input box next to the Major Unit setting and change the value from 0.2 to 0.5 (see Figure 4.52 “Setting the Y Axis Scale”). This allows us to measure the plot points in $0.50 intervals along the Y axis. When the axis extends to $5.00, $0.20 intervals may place too many values along the Y axis, making it difficult to read.

9. Click the Close button at the bottom of the Format Axis dialog box.

   Figure 4.52 Setting the Y Axis Scale

   

10. Click anywhere along the X axis of the chart.
11. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box for the X axis.
12. Click the Fixed option next to the Minimum setting under the Axis Options in the Format Axis dialog box. This ensures that the minimum value for the X axis will always be zero.
13. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
14. Click in the input box next to the Maximum setting. Remove the 500.0 and enter the number 450.0. The number of units supplied or demanded will not exceed 450 based on the price points in our study. There is no need to extend the X axis to 500.
15. Click the Fixed option next to the Major Unit setting under the Axis Options in the Format Axis dialog box.
16. Click in the input box next to the Major Unit setting and change the value from 100.0 to 50.0. This allows us to measure the plot points in 50-unit intervals along the X axis.
17. Click the Close button at the bottom of the Format Axis dialog box.
18. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, the lines change but they no longer jump or shift since the scale of both axes is fixed.

Figure 4.53 “Final Appearance of the Scatter Chart” shows the final appearance of the scatter chart after the scale is set for both the X and Y axes. Notice that market equilibrium is achieved at a price of approximately $2.50.

Adding a Trendline and Equation

A trendline can be applied to a chart to estimate or predict where plot points may occur at various points along the X and Y axes. Excel enables you to add a trendline to a chart and also provides the equation you can use to plot additional points. The following steps explain how to accomplish this:

1. Set the value in cell C14 on the Supply & Demand worksheet to 14.
2. Click anywhere in the chart area of the scatter chart to activate it.
3. Click the Trendline button in the Layout tab of the Ribbon. Select the Linear Trendline option from the drop-down list.

4. Select the Demand option from the Add Trendline dialog box and click the OK button. This adds a new line to the plot area of the chart as well as the legend.

   Figure 4.54 “Adding a Linear Trendline” shows the scatter chart after adding a linear trendline. Notice that the line goes through only two points on the demand line. This indicates that this trendline may not be a good fit for the line that has been created on the chart.

   Figure 4.54 Adding a Linear Trendline

   

   Finding the right shape for a trendline may require trying a few different options. As shown in Figure 4.54 “Adding a Linear Trendline”, the linear trendline is not a good fit for the shape of the demand line. The remaining steps will demonstrate how to remove a trendline and access more trendline options:

5. Click the Trendline button in the Layout tab of the Ribbon. Select the None option from the drop-down list. This removes the trendline from the chart.
6. Click the Trendline button in the Layout tab of the Ribbon again. This time, select More Trendline Options from the drop-down list.
7. Select the Demand option from the Add Trendline dialog box and click OK. This opens the Format Trendline dialog box.
8. Select the Power option from the Format Trendline dialog box.
9. Click the “Display Equation on chart” option at the bottom of the Format Trendline dialog box (see Figure 4.55 “The Format Trendline Dialog Box”).
10. Click the Close button at the bottom of the Format Trendline dialog box.

Figure 4.56 “Scatter Chart with a Power Trendline” shows the scatter chart with the Power trendline added for the demand series. Notice that the line fits perfectly over the demand series in the plot area. In fact, it may be difficult to see the line in the figure. This indicates that the trendline is an excellent fit for the demand line. As a result, we can be confident in using this line to predict other demand values along the X and Y axes. You can also see that the equation for this trendline has been added to the plot area of the chart. We can use the equation to calculate the price for each quantity value substituted for X. For example, if the number 150 is substituted for X in the equation, the result is a price of $2.59. Based on the values used to create the chart, this result appears to be accurate.

Pasting a Chart Image into Word

Excel charts can be valuable tools for explaining quantitative data in a written report. Reports that address business plans, public policies, budgets, and so on all involve quantitative data. For this example, we will assume that the Change in Health Care Spend Source stacked column chart (see Figure 4.44 “Completed Stacked Column Chart with Annotations”) is being used in a written policy report. The following steps demonstrate how to paste an image, or picture, of this chart into a Word document:

1. Click below the figure heading in the Word document that reads: Figure 6: Health Care Spending in the U.S. The image of the stacked column chart will be placed below this heading.
2. Open the Excel Objective 4.16 follow-along file.
3. Activate the Change in Health Care Spend Source chart in the Health Spending Chart worksheet.
4. Click the Copy button in the Home tab of the Ribbon.
5. Go back to the Excel Objective 4.17 Word document by clicking the file in the taskbar.
6. Click the drop-down arrow below the Paste button in the Home tab of the Ribbon. Click the Picture option from the drop-down list, which is the last option on the far right (see Figure 4.57 “Paste Picture Option for Word”).
7. Click anywhere on the picture of the chart to activate it.

8. Click the Format tab under the Picture Tools section of the Ribbon (see Figure 4.58 “Changing the Size of a Picture in Word”).

   Figure 4.57 Paste Picture Option for Word

   

9. Click the down arrow on the Shape Width button in the Size group of commands (see Figure 4.58 “Changing the Size of a Picture in Word”). Continue to click the down arrow until the width of the picture is 5.5. As you reduce the width of the picture, the height is automatically reduced as well.

Figure 4.59 “Final Appearance of Pasting a Chart Image into Word” shows the final appearance of the Change in Health Care Spend Source chart pasted into a Word document. It is best to use either the Shape Width or Shape Height buttons to reduce the size of the chart. Using either button automatically reduces the height and width of the chart in proper proportion. If you choose to use the sizing handles to resize the chart, holding the SHIFT key while clicking and dragging on a corner sizing handle will also keep the chart in proper proportion.

Pasting a Linked Chart Image into PowerPoint

Microsoft PowerPoint is perhaps the most commonly used tool for delivering live presentations. The charts used in a live presentation are critical for efficiently delivering your ideas to an audience. Similar to written documents, a wide range of presentations may require the explanation of quantitative data. This demonstration includes a PowerPoint slide that could be used in a presentation for setting prices for a hypothetical breakfast cereal company. We will paste the scatter chart showing the supply and demand for breakfast cereal into this PowerPoint slide. However, instead of pasting an image, as demonstrated in the Word document, we will establish a linkA dynamic connection between two files, worksheets, or cell locations. Any change in the source file will be reflected in the file, worksheet, or cell that contains the link. to the Excel file. As a result, if we change the chart in the Excel file, the change will be reflected in the PowerPoint file. The following steps explain how to accomplish this:

1. Open the Excel Objective 4.16 follow-along file.
2. Activate the scatter chart in the Supply & Demand worksheet.
3. Click the Copy button in the Home tab of the Ribbon.
4. Go back to the Excel Objective 4.18 PowerPoint file by clicking the file in the taskbar.
5. Click the down arrow below the Paste button in the Home tab of the Ribbon in the PowerPoint file.

6. Select the Keep Source Formatting & Link Data option from the drop-down list (see Figure 4.60 “Creating a Link to an Excel Chart in PowerPoint”). This pastes an image of the Excel chart into the PowerPoint slide. In addition, a link is created so that any changes made to the chart appear on the PowerPoint slide.

   Figure 4.60 Creating a Link to an Excel Chart in PowerPoint

   

7. Click anywhere in the plot area of the scatter chart pasted into the PowerPoint slide. You will see the same Excel Chart Tools tabs added to the Ribbon (see Figure 4.61 “Modifying an Excel Chart Pasted into a PowerPoint Slide”).

   Figure 4.61 Modifying an Excel Chart Pasted into a PowerPoint Slide

   

8. Click the down arrow next to the Shape Fill button in the Format tab of the Ribbon. Select the white color block from the palette (see Figure 4.61 “Modifying an Excel Chart Pasted into a PowerPoint Slide”).
9. Go back to the Excel Objective 4.16 file by clicking it in the taskbar.
10. In the Supply & Demand worksheet, change the value in cell C14 to 6.
11. Go back to the Excel Objective 4.18 PowerPoint file by clicking it in the taskbar.
12. Click the Design tab in the Chart Tools section of the Ribbon. Click the Refresh Data button (see Figure 4.62 “Refreshing a Linked Excel Chart Pasted into a PowerPoint Slide”). The change made in the Excel workbook is now reflected on the PowerPoint slide.

Figure 4.62 “Refreshing a Linked Excel Chart Pasted into a PowerPoint Slide” shows the appearance of the scatter chart in the PowerPoint slide after the color of the plot area was changed back to white. Figure 4.63 “Final Chart Linked to a PowerPoint Slide” shows the appearance of the scatter plot after the change was made in the Supply & Demand worksheet in the Excel file. The change that was made in the Excel file will appear in the PowerPoint file after clicking the Refresh Data button.