3.XLSX.2 Formatting Charts
Noreen Brown; Barbara Lave; Hallie Puncochar; Julie Romey; Mary Schatz; Art Schneider; Diane Shingledecker; and Jennifer Evans
Learning Objectives
- Apply formatting commands to the X and Y axes.
- Assign titles to the X and Y axes that clarify labels and numeric values for the reader.
- Apply labels and formatting techniques to the data series in the plot area of a chart.
- Apply formatting commands to the chart area and the plot area of 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.
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. Follow the below steps to make some changes to the percentage numbers on the Y (vertical) axis.
- In the Grade Distribution worksheet, click on the Grade Distribution Comparison chart. Double-click the vertical (value) axis. This opens the Format Axis pane.
- Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic.
- Click the Close button at the top of the Format Axis pane.
- 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:
- Click anywhere on the Grade Distribution Comparison chart in the Grade Distribution worksheet to activate it.
- 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”. - Click at the beginning of the Y-axis title and delete the generic title. Type Percent of Enrolled Excel Students.
- 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
- Click anywhere on the chart to activate it. Choose to open the Charts Element menu.
- Select one of the options from the second drop-down list.
- Click in the axis title to remove the generic title and type a new title.
Data Series Labels and Formats
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:
- 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. - From the menu, select Format Data Series.
- From the Format Data Series pane, click the Fill and Line (paint bucket) button to bring up the Fill and Border group of commands.
- Click the word Fill (if needed) to expand the list of Fill options.
- Select Pattern Fill. Then select 40% (last option in the top row). Change the Foreground to white, and the Background to Red.
- Close the Format Data Series pane.
Now we are going to add the Data Labels at the end of the columns.
- 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.
- On the Design tab select the Add Chart Element button, then Data Labels, then Outside End (see Figure 4.25.)
- Click on one of the Data Labels. Note that all of the data labels for that data series are selected.
- 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:
Adding Data Labels
- Click anywhere on the chart to activate it.
- Open the Add Chart Element group.
- Then, select Data Labels
- Select one of the preset positions from the drop-down list.
Key Takeaways
- Applying appropriate formatting techniques is critical for making a chart easier to read.
- Many formatting commands in the Home tab of the ribbon can be applied to a chart.
- To change the number format for an axis or data label, you must use the Number section in the Format Data Labels dialog box. You cannot use the Number format commands in the Home tab of the ribbon.
- Axis titles help the reader sees the most accurate representation of the information presented on a chart.
Attribution
Adapted by Hallie Puncochar and 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.