4.2 Formatting Charts

Learning Objectives

  1. Apply formatting commands to the X and Y axes.
  2. Enhance the visual appearance of the chart title and chart legend by using various formatting techniques.
  3. Assign titles to the X and Y axes that clarify labels and numeric values for the reader.
  4. Apply labels and formatting techniques to the data series in the plot area of a chart.
  5. Apply formatting commands to the chart area and the plot area of a chart.
  6. Employ series lines and annotations to enhance trends and provide additional information on a chart.

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.

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 4.26).
    Font Dialog box with Regular Font Style and Size 11 selected.
    Figure 4.26 Font Dialog Box
  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 4.27 Formatted X & Y Axes.
Formatted X and Y Axes. Note the bold text.
Figure 4.27 Formatted X and 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 4.28.
  5. Click the Close button at the top of the Format Axis pane.
  6. Save your work.
Figure 4.28 Format Axis Pane changes
Note: Experiment!  You can also change font styling using shortcut keys and the buttons on the Home tab.

 

Skill Refresher

Formatting the X and Y Axes

  1. Click anywhere along the X or Y axis to activate it.
  2. Click either the Home tab or Design tab of the ribbon.
  3. Select any of the available formatting commands in these tabs.

 

Skill Refresher

X and Y Axis Number Formats

  1. Click anywhere along the X or Y axis to activate it.
  2. Click the Layout tab in the Chart Tools section of the ribbon.
  3. Click the Format Selection button in the Current Selection group of commands.
  4. Click Number from the list of options on the left side of the Format Axis dialog box.
  5. Select a number format and set decimal places on the right side of the Format Axis dialog box.
  6. Click the Close button in the Format Axis pane.

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 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 4.29).
Legend has been moved and top of box is aligned with 35% line.
Figure 4.29 Moving the Legend
  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 4.30).
Legend resized and font changed to 12, bold, italic.
Figure 4.30 Legend Formatted and Resized
  1. Click the chart title to activate it.
  2. Right click on the chart title and select Format Chart Title to open the Format Chart Title pane.
  3. 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.
  4. Close the Format Chart Title pane.
  5. Save your work.
Format Chart Title pane open with Title Options selected for Shadow and Color.
Figure 4.31 Format Chart Title Pane

Skill Refresher

Formatting the Chart Legend

  1. Click the Legend to activate it.
  2. Click either the Home tab or right click to activate the appropriate formatting pane.
  3. Select any of the available formatting commands.
  4. Click and drag the legend to move it.
  5. Click and drag any of the sizing handles to adjust the size of the legend.

 

Skill Refresher

 Formatting the Chart Title

  1. Click anywhere on the chart title.
  2. Click either the Home tab or right click to activate the appropriate formatting pane.
  3. Select any of the available formatting commands.

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 Design tab on the ribbon select the Add Chart Element button, then Axis Titles, then Primary Vertical. (See Figure 4.32)
Design Tab to "Add Chart Element" drop-down menu, "Axis Titles" option open, and "Primary Vertical" selected.
Figure 4.32 Selecting a Title for the Y Axis
  1. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.
  2. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Excel Students.(see Figure 4.33).
Y axis title is now "Percent of enrolled Excel Students".
Figure 4.33 Adding and Formatting the Y Axis Title

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 4.34 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.
Y axis new title and X axis title "Final Course Grade" added.
Figure 4.34 X and Y Axis Titles Added

Skill Refresher

X and Y Axis Titles

  1. On the Design tab select the Add Chart Element button.
  2. Click anywhere on the chart to activate it.
  3. Select one of the options from the second drop-down list.
  4. Click in the axis title to remove the generic title and type a new title.

Attribution

Adapted by Noreen Brown from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

Media Attributions

  • Figure 4-26
  • Figure 4-27
  • Figure 4-28
  • figure-4-30-selecting-a-title-for-the-y-axis

License

Icon for the Creative Commons Attribution 4.0 International License

4.2 Formatting Charts by Emese Felvegi, Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker, and Robert McCarn is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.