14 Formatting Charts

Formatting Charts

There are a variety of formatting techniques to enhance the appearance of a chart once you have created it. Formatting commands are applied to a chart for the same reason they are applied to a worksheet: to make the chart easier to read. However, formatting techniques can help you qualify and explain the data in a chart. For example, you can add footnotes explaining the data source as well as notes that clarify the type of numbers being presented (i.e., if the numbers in a chart are truncated, you can state whether they are in thousands, millions, etc.). These notes are also helpful in answering questions if you are using charts in a live presentation. These formatting techniques will be demonstrated using the column chart and stacked column chart from the previous section.

Getting to Know the Elements of a Chart

A chart has many elements. Some of these elements are displayed by default, others can be added as needed. You can change the display of the chart elements by moving them to other locations in the chart, resizing them, or by changing the format. You can also remove chart elements that you do not want to display.

1. The chart area of the chart.

2. The plot area of the chart.

3. The data points of the data series that are plotted in the chart.

4. The horizontal (category) and vertical (value) axis along which the data is plotted in the chart.

5. The legend of the chart.

6. A chart and axis title that you can use in the chart.

7. A data label that you can use to identify the details of a data point in a data series.

X and Y Axis Formats

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.08 if starting here.)

There are numerous formatting commands that can be applied 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.

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. Note: you can access any of the formatting dialog boxes by right clicking on the area you wish to format. Then select format (whichever area you chose).

9. Click Number from the bottom of the Axis (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.

11. Click the X button at the top right 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

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.09 if starting here.)

The next items we will format on the Grade Distribution Comparison chart are the chart legend, axis titles and chart title. Similar to 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. We will use the Quick Layout button on the Chart Tools Design ribbon to add axis titles and move the legend. The following steps explain how to add these formats:

1. Activate the Grade Distribution chart on the Grade Distribution worksheet. From the Chart Tools Design ribbon click on the Quick Layout button. AS you scroll through the different layouts you will see your chart change. Select Layout 9. This will add your axis titles, keep the chart title and move the legend to the right side of the Plot area. (Note: if you accidently select a quick layout that eliminates the title, you will have to redo your title text.)

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-2 “Moving the Legend”).

3. Click and drag the lower center sizing handle so the bottom of the legend is aligned with the 25% line of the plot area.

4. Click and drag the left sizing handle so the legend is against the plot area.

5. Change the font style in the Home tab of the Ribbon to Arial.

6. Change the font size to 12 points.

7. Click the bold and italics commands in the Home tab of the Ribbon.

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.

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.30 “Chart Legend and Title Formatted”).

X and Y Axis Titles

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.10 if starting here.)

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. In the last section, we added axis titles by using the Quick Layout button. To add titles without using a Quick Layout, the following steps explain how to add titles to the X and Y axes to define these numbers and categories:

1. If you have axis titles already on your chart proceed to step 5.

2. Click anywhere on the Grade Distribution Comparison chart in the Grade Distribution worksheet to activate it.

3. Click the Add Chart Element button.

4. Click the Axis Titles button in the Labels group of commands and select Primary Vertical Axis. This adds a title next to the Y axis. Repeat the above to add the Primary Horizontal 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 and apply the bold format.

8. While the vertical Axis Title box is selected type Percent of Enrolled Students in the formula bar. Hit Enter when finished.

9. Click on the horizontal Axis Title.

10. Click the Format tab in the Chart Tools section of the Ribbon.

11. Click the Colored Outline – Blue, Accent 1 preset style option in the Shape Styles group of commands.

12. Change the font style in the Home tab to Arial. Change the font size to 11 points and apply the bold format.

13. While the vertical Axis Title box is selected type Final Course Grade in the formula bar. Hit Enter when finished.

Figure 4.31 “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

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.11 if starting here.)

Adding labels to the data series of a chart is a formatting feature that may aide in bringing clarity to a chart. However, caution should be used so the chart does not become cluttered and hard to read. A data series 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.32″ Changing the Color of a Data Series”). As you move the mouse pointer over colors on the palette, you can preview the change on the data bars.

5. Click the Design tab in the Chart Tools section of the Ribbon.

6. Click the Add Chart Element. button in the Labels group of commands. Select Data Labels, and then Outside End from the options in the drop-down list.

7. Right click on any of the labels and select Format Data Labels from the drop-down list.

8. Select Number in the Text Options Format window. Then, select Percentage in the Category box. Then change the decimal places to 0. (see Figure 4.33 “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.34 “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

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.12 if starting here.)

The next items to format on the Grade Distribution Comparison chart are the plot and chart areas. We can 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 Round 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

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.13 if starting here.)

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 lines are commonly used in stacked column charts to show the change from one stack to the next. Annotations 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 by Clicking on Add Chart Element for the Chart Tools Design ribbon and selecting Axis titles, then Primary Vertical Axis.

6. 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.

7. While the Axis Title is still selected, type Percent of Total Annual Spend in the Formula bar and then hit enter to save your changes.

8. Click and drag the vertical axis so it is closer to the plot area.

9. 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.

10. Activate the Chart Area by clicking on the down arrow next to the Format Selection box on the Format tab of the Chart Tools ribbon, and selecting Chart Area.

11. 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. Note: This will change the entire chart background to the color selected.

12. Activate the plot area using the method in step 10.

13. Change the plot area shape fill to white.

14. 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 “Round” bevel option.

15. Click and drag down the top center sizing handle of the plot area approximately 1/2 inch (see Figure 4.39 “Adjusting the Size of the Plot Area”).

16. Click and drag up the bottom center sizing handle approximately three-quarters of an inch This step and step 15 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. The remaining steps will focus on adding lines and annotations:

17. Click the Design tab in the Chart Tools section of the Ribbon.

18. Click the Add Chart Element, Lines, 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”).

19. Click any of the series lines added to the chart. Clicking one line will activate all lines on the chart. Note: you can select the series lines the way you did earlier. Chart Tools, Format, select Series Line 1 from the Format Selection box.

20. 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 on 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).

21. Click anywhere in the chart area of the Change in Health Care Spend Source chart.

22. Click the Text Box button in the Chart Tools Format Ribbon.

23. Position 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 “Adding Annotations to the Stacked Column Chart”).

24. 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.

25. 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 1975 is shown as $22,617 but is $22,617,000,000, or $22.6 billion.

26. 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 Complete the formatting changes in step 22 and select the Align Text Right command.

27. Type 100% = in the second text box.

28. Repeat steps 19–22 to add a third text box to the chart. Center this text box over the 1975 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.

29. Type $66,172 in the third text box.

30. Repeat steps 19–22 to add a fourth text box to the chart. Center this text box over the 2015 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.

31. Type $2,486,293 in the fourth textbox.

32. 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.

33. Type Source: CMS Centers for Medicaid and Medicare 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.