You can use 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: they make the chart easier to read. However, formatting techniques also 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. We will demonstrate these formatting techniques using the column chart and stacked column chart from the previous section.

Download and open FILE: CH 6.2

X and Y Axis Formats

There are numerous formatting commands we can apply to the X and Y axes of a chart. Although adjusting the font size, style, and color are common, many more options are available through the Format Axis pane. The following steps demonstrate a few of these formatting techniques on the Grade Distribution Comparison chart:

1. Switch to the Grade Distribution worksheet and click anywhere along the X axis (horizontal axis) of the Grade Distribution Comparison chart.
2. Right click and select Font.
3. Change the font to Arial, the Font Style to Bold, and the Size to 11 (see Figure 6.7).

4. Click anywhere along the Y axis to activate it and repeat steps 2 and 3.
5. Click on the chart title and repeat steps 2 and 3 but set the Size to 14.
6. The final appearance of the axes is shown in Figure 6.8 Formatted X & Y Axes.

Next, we want to make some changes to the percentage numbers on the Y (vertical) axis.

1. Right click the vertical (value) axis. Select Format Axis. This opens the Format Axis pane.
2. Click Number from the list of options. The commands in this section of the Format Axis pane are used to format numbers that appear on the selected axis of the chart.
3. Click in the Decimal places input box and change the value to 1.
4. Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic. The Format Axis pane should match Figure 6.9.
5. Click the Close button at the top of the Format Axis pane.
6. Save your work.

Note: Experiment!  You can also change font styling using shortcut keys and the buttons on the Home tab.

Chart Legend and Title Formats

The next items we will format on the Grade Distribution Comparison chart are the chart legend and title. Like 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 pane. The following steps explain how to add these formats:

1. Right click the legend on the Grade Distribution Comparison chart and select Format Legend.
2. Select Right in the Legend Position options. Close the Format Legend pane.
3. Move the legend by placing your cursor – shaped like a little plus sign with four arrows – on the edge of the selection box.  Click and drag the legend so the top of the legend aligns with the 35% line next to the plot area (see Figure 6.10).

1. While the legend is still selected, change the font style in the Home tab of the ribbon to Arial.
2. Change the font size to 12 points.
3. Click the bold and italics commands in the Home tab of the ribbon.
4. Click and drag the left sizing handle so the legend is against the plot area (see Figure 6.11).

5. Click the chart title to activate it.
6. Right click on the chart title and select Format Chart Title to open the Format Chart Title pane.
7. Under Title Options, in the Effects group (the option in the middle) give your title one of the Preset shadows. Change the color if you like.
8. Close the Format Chart Title pane.
9. Save your work.

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. On the Chart Design tab on the ribbon select the Add Chart Element button, then Axis Titles, then Primary Vertical. (See Figure 6.13)

3. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.
4. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Excel Students. (see Figure 6.14).

Next, we will add the title for the X axis:

1. On the Design tab select the Add Chart Element button, then Axis Titles, then Primary Horizontal.
2. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.
3. Click in the beginning of the X axis title and delete the generic title. Type Final Course Grade. Figure 6.15 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.
4. Save your work.

Data Series Labels and Formats

Adding labels to the data series of a chart is a key formatting feature. 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 on any of the red bars representing the All Excel Classes 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. Right click and select Format Data Series to open the Format Data Series pane.
3. Click the Fill and Line (paint bucket) button to bring up the Fill and Border group of commands.
4. Click the word Fill (if needed) to expand the list of Fill options.
5. Select Pattern Fill. Then select 30% (fifth column, top row). Changing your fill pattern to a pattern makes it easier to distinguish between the data series when you print or view your chart in black and white. While you are there, make changes to the fill by experimenting with different foreground and background colors.
6. Close the Format Data Series pane.

Now we are going to add the Data Labels at the end of the columns:

1. Be sure that your entire chart is selected, not just one of the data series. Click the Design tab in the Chart Tools section of the ribbon.
2. On the Chart Design tab select the Add Chart Element button, then Data Labels, then Outside End (see Figure 6.17.)
3. Click on one of the Data Labels. Note that all of the data labels for that data series are selected.
4. Using the Home ribbon, change the font to Arial, Bold, size 9.
5. Click on one of the data labels for the other data series. Format those data labels as Arial, Bold, size 9 as well.
6. Save your work.

Figure 6.18 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.

7. Format the Legend and move it to the bottom. The data labels overlap and do not make for a well-crafted presentation of the chart.
8. Your chart should now look like Figure 6.19.

Add text boxes to include additional information in the chart.

1. Click the Text Box button in the Text group on the Insert tab of the ribbon (see Figure 6.20).
2. Place the mouse pointer on the right side of the chart area approximately where the legend was. Click and drag a rectangle approximately one and a half inches wide and one-quarter inch high (see Figure 6.20). Do not worry if it is not exact – you can move and resize text boxes at any time.
3. Type Prepared By: Then enter your initials. This tells the audience who created the chart.
4. Select all the text in the text box. Using the Home tab of the ribbon, change the font to Arial, size 11.
5. Save your work.