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.