Excel Charts

Read this Microsoft Excel tutorial to learn tips for formatting various elements of your chart, such as the axes, title and legend, data series labels, plot area, and annotations. Each section provides step-by-step guidance with screenshots on how to perform the formatting.

You can use various 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 help you qualify and explain the data in a chart. For example, you can add footnotes explaining the data source and 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 previous section's column chart and stacked column chart.


X- and Y-Axis Formats

We can apply numerous formatting commands 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.


    Image of grade distribution comparison chart: on the home ribbon any of the formatting commands for text can be applied to the x-and y-axis, additional formatting comands can be found in the format tab in the chart tools, the border around the x-axis indicates it is activiated.

    Figure 4.24 Formatted X-Axis

  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.
  9. Click Number from the list of options on the left side of the Format Axis dialog box (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 (see Figure 4.25, "Formatting Numbers on the Y-Axis").
  11. Click the Close button at the bottom of the Format Axis dialog box. The formatting adjustments are shown in Figure 4.26, "Completed X- and Y-Axis Formats."


In the formatting button under chart tools: click on number to format numbers on the activated axis, click on decimal places to change the decimal places for numbers on the activiated axis, select an option to change the number format.

Figure 4.25 Formatting Numbers on the Y-Axis

In the grade distribution comparison chart: shows the decimal places reduced to zero, font style and size changed.

Figure 4.26 Completed X- and Y-Axis Formats


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 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. The following steps explain how to add these formats:

  1. Click the legend on the Grade Distribution Comparison chart in the Grade Distribution worksheet.
  2. Click and drag the legend so the top of the legend aligns with the 35 percent line next to the plot area (see Figure 4.27, "Moving the Legend").

    In the grade distribution comparison chart: the legend is being moved to this location, Click and drag the sizing handles to increase the size of the legend.

    Figure 4.27 Moving the Legend

  3. Change the font style in the Home tab of the Ribbon to Arial.
  4. Change the font size to 12 points.
  5. Click the bold and italics commands in the Home tab of the Ribbon.
  6. Click and drag the left sizing handle so the legend is against the plot area (see Figure 4.28, "Legend Formatted and Resized").
  7. Click and drag the lower center sizing handle so the bottom of the legend is aligned with the 25 percent line of the plot area (see Figure 4.28, "Legend Formatted and Resized").

    The format button under chart tools: Click on the equal sign to see a comlete list of preset formats. The legend was resized to align with the plot area here.

    Figure 4.28 Legend Formatted and Resized

  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 (see Figure 4.28, "Legend Formatted and Resized").
  11. Click the Subtle Effect – Blue, Accent 1 option 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 Ribbon Home tab, change the font style to Arial and reduce the font size to 14 points (see Figure 4.29, "Chart Legend and Title Formatted").

Chart Legend and Title Formatted: shows a preset shape style added to the title.

Figure 4.29 Chart Legend and Title Formatted

X- and Y-Axis Titles

Titles for the X- and Y-axes are necessary to define the numbers and categories on a chart. For example, the grade distribution comparison chart does not clearly show the percentages along the Y-axis. 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. Click the Layout tab in the Chart Tools section of the Ribbon.
  3. Click the Axis Titles button in the Labels group of commands.
  4. Place the mouse pointer over the Primary Vertical Axis Title option from the drop-down list. This opens a second drop-down list. Select the Rotated Title option from the second drop-down list. This adds a title next to the Y-axis (see Figure 4.30, "Selecting a Title for the Y-Axis").

    In the grade distribution comparison chart: in the layout tab under chart tools, click on axis titles to add a title to the x or y-axis, place the mouse pointer on primary vertical axis title to see a list of y-axis title formats (none, rotated title, verticle title, horizontal title).

    Figure 4.30 Selecting a Title for the Y-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.
  8. Click at the beginning of the Y-axis title and delete the generic title. Type Percent of Enrolled Students.
  9. Click and drag the Y-axis title so it is between 0% and 30% in the plot area (see Figure 4.31, "Adding and Formatting the Y-Axis Title").

    Image of grade distribution comparison: shows y-axis title added to chart.

    Figure 4.31 Adding and Formatting the Y-Axis Title

  10. Click the Layout tab in the Chart Tools section of the Ribbon.
  11. Click the Axis Titles button in the Labels group of commands.
  12. Place the mouse pointer over the Primary Horizontal Axis Title option. From the second drop-down list, select Title Below Axis.
  13. Click the Format tab in the Chart Tools section of the Ribbon.
  14. Click the Colored Outline - Blue, Accent 1 preset style option in the Shape Styles group of commands.
  15. Change the font style in the Home tab to Arial. Change the font size to 11 points.
  16. Click in the beginning of the X-axis title and delete the generic title. Type Final Course Grade.

Figure 4.32, "X- and Y-Axis Titles Added," shows the added titles for the X- and Y-axes. The titles define the grade categories along the X-axis and the percentages on the Y-axis.

Image of grade distribution comparison: shows x- and y-axis titles added to chart.

Figure 4.32 X- and Y-Axis Titles Added


Data Series Labels and Formats

Adding labels to a chart's data series is a key formatting feature. data series is an item that is 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. Only that bar is activated if you click a bar a second time.
  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 square from the drop-down color palette (see Figure 4.33, "Changing the Color of a Data Series"). As you move the mouse pointer over other colors on the palette, you can preview the change on the data bars.

    Image of grade distribution spreadsheet: shows different colors to choose from in Shape Fill dialog box if choose from Format button in Chart Tools, clicking a bar one time will activate the entire data series, the color orange was selected. Series name in upper left hand corner – series name that is currently activated appears here.

    Figure 4.33 Changing the Color of a Data Series

  5. Click the Layout tab in the Chart Tools section of the Ribbon.
  6. Click the Data Labels button in the Labels group of commands. Select More Data Label Options at the bottom of the drop-down list to open the Format Data Labels dialog box.
  7. Click the Number option from the list on the left side of the Format Data Labels dialog box.
  8. Select Percentage on the right side of the Format Data Labels dialog box (see Figure 4.34, "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.

Image shows open Format data labels box - click here to format numeric data labels. Put a number in the decimal places to change the decimal places for the data labels.

Figure 4.34 Adding Labels to a Data Series

Figure 4.35, "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.

Completed Formatting Adjustments for the Data Series: the color of this data series was changed. This label can be moved from the grid line by clicking it twice and then clicking and dragging.

Figure 4.35 Completed Formatting Adjustments for the Data Series


Formatting the Plot and Chart Areas

The last items we will format on the Grade Distribution Comparison chart are the plot and chart areas. We 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 percent 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 Circle bevel option from the second drop-down list (see Figure 4.37, "Putting a Bevel Effect on the Plot Area").


    Image shows the format tab under the chart tools selected, shape and fill with theme colors, chart area in upper left - this indicates the chart area is activated.

    Figure 4.36 Formatting the Chart Area


    Image shows shape effects pulldown options under format and chart tools buttons (preset, shadow, reflection, glow, soft edges, bevel, 3-d rotation).

    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, makes the data series the main focal point of the chart.

Image shows final grade distribution comparison after formatting is applied

Figure 4.38 Grade Distribution Comparison Chart with Formats Applied


Adding Series Lines and Annotations to a Chart

The last formatting feature we will demonstrate is adding series lines and annotations to a chart. We will use the Change in Healthcare Spend Source stacked column chart to demonstrate these skills. 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 identifying data sources. In addition to demonstrating these skills, we will review several formatting skills 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 Healthcare worksheet. Activate the chart by clicking anywhere inside the chart perimeter.
  2. Click the Move Chart button in the Design tab of the Ribbon to move the chart to a separate sheet. In the New sheet input box, type the following sheet tab label: Health Spending Chart. Click the OK button.
  3. Click anywhere on the X-axis to activate it. In the Ribbon Home tab, 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 using the Rotated Title option. 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 Ribbon Home tab, change the font style to Arial and the font size to 14 points.
  6. Change the wording of the Y-axis title to read Percent of Total Annual Spend.
  7. 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 Ribbon Home tab, change the font style to Arial.
  8. Click anywhere in the chart area to activate it.
  9. Click the Format tab in the Chart Tools section of the Ribbon and click the down arrow on the Shape Fill button. On the color palette, select the Olive Green, Accent 3, Lighter 60% option.
  10. Click anywhere on the plot area to activate it. Be sure not to click on a grid line.
  11. 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, the "Circle" bevel option.
  12. Click and drag down the top center sizing handle of the plot area approximately one inch (see Figure 4.39, "Adjusting the Size of the Plot Area").
  13. Click and drag up the bottom center sizing handle approximately three-quarters of an inch (see Figure 4.39, "Adjusting the Size of the Plot Area"). This step and step 12 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 before adding the series lines and annotations. Notice that the Ribbon has been minimized to improve the visibility of the chart. The remaining steps will focus on adding lines and annotations:

    Image shows chart tools. Notes the ribon has been minimized. Click and drag the sizing handle down to reduce the plot area size, this space was created by reduing the size of the plot area, click and drag the sizing handle at the bottom, to reduce the plot area size. Label for this chart sheet which was added to the workbook.

    Figure 4.39 Adjusting the Size of the Plot Area

  14. Click the Layout tab in the Chart Tools section of the Ribbon.
  15. Click the Lines button in the Analysis group of commands.
  16. 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").

    Image of stacked column chart: shows the layout tab under chart tools. Line button options include none and series lines - click here to add lines connecting each data series between the two stacks, a line will be added to connect the points between the bars.

    Figure 4.40 Selecting the Series Lines Option

  17. Click any of the series lines added to the chart. Clicking one line will activate all lines on the chart (see Figure 4.41, "Activating the Series Lines").

    Image of stacked column chart: the dots around the bars indicates the lines are activated, click here to activate all series lines ion the chart.

    Figure 4.41 Activating the Series Lines

  18. 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 chart's appearance with 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 percentage of the 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).

    Image of stacked column chart: the lines bring clarity to changes in the percent total.

    Figure 4.42 Series Lines Added to the Stacked Column Chart

  19. Click anywhere in the chart area of the Change in Health Care Spend Source chart.
  20. Click the Text Box button in the Insert tab of the Ribbon (see Figure 4.43, "Lines Added to the Stacked Column Chart").
  21. Place 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 "Lines Added to the Stacked Column Chart").
  22. Click the Ribbon Home tab, change the font style to Arial, change the font size to 10 points, and select the bold and italics commands.
  23. Type Dollars in Millions. This tells the audience that the numbers have been truncated and represent denominations in millions. To do this, you would add six zeros to the end of each number on the chart. Therefore, the Out-of-Pocket value for 1969 is shown as $22,617 but is actually $22,617,000,000, or $22.6 billion.

    Image of stacked column chart: text box button, text box added to the chart area, the secont box will begin here.

    Figure 4.43 Lines Added to the Stacked Column Chart

  24. 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 (see Figure 4.43, "Lines Added to the Stacked Column Chart"). Complete the formatting changes in step 22 and select the Align Text Right command.
  25. Type 100% = in the second text box.
  26. Repeat steps 19–22 to add a third text box to the chart. Center this text box over the 1969 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
  27. Type $66,172 in the third text box.
  28. Repeat steps 19–22 to add a fourth text box to the chart. Center this text box over the 2009 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
  29. Type $2,486,293 in the fourth text box.
  30. 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.
  31. Type Source: US Department of Health and Human 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.

Image of stacked column chart: text boxes above each stack show the total spend on healthcare for each year, text box added at the bottom to show the data source for the values in this chart (Source: U.S. Department of Health and Human Services).

Figure 4.44 Completed Stacked Column Chart with Annotations

Integrity Check: Annotations and Axis Titles

Although adding annotations and axis titles can be a tedious process, doing so maintains a high level of integrity for your charts. People can misinterpret the message being conveyed by the chart if they make inaccurate assumptions about the values displayed. Axis titles and annotations help prevent readers from making false assumptions and ensure that readers see the most accurate representation of the message being conveyed by the chart.

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

  • To change the number format for the values on the Y-axis, and the X-axis in the case of a scatter chart, you must use the Number section of the Format Axis dialog box. You cannot use the Number format commands in the Home tab of the Ribbon.

  • Axis titles and annotations help prevent false assumptions from being made and ensure that the reader sees the most accurate representation of the information presented on a chart.

Exercises

  1. You need to format the numbers along the Y-axis of a column chart to US dollars with zero decimal places. Which of the following describes the method that would allow you to accomplish this?

    1. Activate the Y-axis and use any of the number formatting commands in the Home tab of the Ribbon.
    2. Activate the Y-axis and click the Data Labels button in the Layout tab of the Ribbon.
    3. Activate the Y-axis and click the Format Selection button in the Layout tab of the Ribbon.
    4. Activate the Y-axis and click the Axis Titles button in the Layout tab of the Ribbon.

  2. Which of the following statements is accurate with regard to changing the color of a data series on a column chart?

    1. Click one bar on the column chart plot area to activate all bars for that data series. Click the Fill Color button in the Home tab of the Ribbon and select a color.
    2. Click one bar on the column chart plot area twice to activate all bars for that data series. Click the Shape Fill button in the Format tab of the Ribbon and select a color.
    3. Click the Legend one time and then click the name of the data series to activate it. Click the Shape Fill button in the Format tab of the Ribbon and select a color.
    4. Both A and C are valid methods for changing the color of a data series.

  3. Which of the following methods is accurate with respect to formatting the legend?

    1. Click the legend one time and use any of the available formatting commands in the Home tab of the Ribbon.
    2. Click the Legend button in the Layout tab of the Ribbon and select from the drop-down list of commands.
    3. Click the legend one time to activate it and use any of the formatting commands in the Design tab of the Ribbon.
    4. None of the above.

  4. Which of the following is the most efficient way to add a title to the Y-axis of a chart?

    1. Add a text box to the plot area and drag it over to the Y-axis.
    2. Type the title into the formula bar. This adds a text box to the plot area that can be dragged over to the Y-axis.
    3. Select the vertical axis option from the Axis Titles button in the Layout tab of the Ribbon.
    4. Select the axis title option in the Select Data Source dialog box after clicking the Select Data button in the Design tab of the Ribbon.

Creative Commons License This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.

Last modified: Monday, August 26, 2024, 2:42 PM