4.2 Formatting Charts

Learning Objectives

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.

X ANDAnd Y-AXISAxis FORMATSFormats

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. Follow the below steps to make some changes to the percentage numbers on the Y (vertical) axis.

1. In the Grade Distribution worksheet, click on the Grade Distribution Comparison chart.  Double-click the vertical (value) axis.  This opens the Format Axis pane.
2. Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic.
3. Click the Close button at the top of the Format Axis pane.
4. Save your work.

X ANDAnd Y-AXISAxis TITLESTitles

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. In the upper right corner of the graph, choose the Charts Element plus sign.  Select the Axis Titles, then Primary Horizontal and Primary Vertical. This inserts the place holders that you will type text in.
    Mac Users click the “Add Chart Element” button in the Design tab, point to “Axis Titles” and click on “Primary Horizontal”. Do this one more time and click on “Primary Vertical”.
3. Click at the beginning of the Y-axis title and delete the generic title. Type Percent of Enrolled Excel Students.
4. Click at the beginning of the X-axis title and delete the generic title. Type Final Course Grade.   

Skill Refresher

X and Y Axis Titles

DATAData SERIESSeries LABELSLabels ANDAnd FORMATSFormats

Adding labels to the data series of a chart is a key formatting feature. A data series is an 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 columns representing the All Excel Classes data series, then Right-Click to open the menu.
    Mac Users should hold down the CTRL key and click on any of the red columns.
2. From the menu, select Format Data Series.
3. From the Format Data Series pane, 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 40% (last option in the top row). Change the Foreground to white, and the Background to Red.
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 Design tab select the Add Chart Element button, then Data Labels, then Outside End (see Figure 4.25.)
3. Click on one of the Data Labels. Note that all of the data labels for that data series are selected.
4. Check the spelling on all of the worksheets and make any necessary changes. Save your work.

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

Skill Refresher:Refresher

Adding Data Labels

Key Takeaways