- Choosing a Chart Type
- Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts
- Communicate Effectively with Charts
- Adding an Automatic Trendline to a Chart
- Showing a Trend of Monthly Sales and Year-to-Date Sales
- Understanding the Shortcomings of Stacked Column Charts
- Shortcomings of Showing Many Trends on a Single Chart
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
Showing a Trend of Monthly Sales and Year-to-Date Sales
In accounting, sales are generally tracked every month. However, in the big picture you are interested in how 12 months add up to produce annual sales.
The top chart in Figure 3.32 is a poor attempt to show both monthly sales and accumulated year-to-date (YTD) sales. The darker bars are the monthly results, while the lighter bars are the accumulated YTD numbers through the current month. To show the large YTD number for November, the scale of the axis needs to extend to $400,000. However, this makes the individual monthly bars far too small for the reader to be able to discern any differences.
)
Figure 3.32 The size of the YTD bars obscures the detail of the monthly bars.
The solution is to plot the YTD numbers against a secondary vertical axis. My preference is that after you change the axis for one series, you should also change the chart type for that series. Follow these steps to create the bottom chart in Figure 3.32:
- Left-click one of the YTD bars to select the YTD series. Right-click the selected series and select Format Data Series. Excel displays the Format Data Series dialog.
- In the Format Data Series dialog, select Secondary Axis in the Plot Series On section of the Series Options page. Click Close. Excel creates a confusing chart, where the YTD numbers appear directly on top of the monthly numbers, obscuring any monthly numbers beyond August.
- Excel deselects the series when you change the chart type. Reselect the YTD series by clicking the YTD line.
- On the Format dialog, select Shape Outline, Black to change the YTD line to black.
- From the Layout tab, turn off the gridlines by selecting Gridlines, None.
- From the Layout tab, select Axes, Primary Vertical Axis, Show Axis in Thousands.
- From the Layout tab, select Axis Titles, Primary Vertical Axis Title, Rotated Title. Type Monthly Sales and press Enter.
- From the Layout tab, select Axis Titles, Secondary Vertical Axis Title, Rotated Title. Type YTD and press Enter.
- Right-click the numbers on the secondary vertical axis. Select Format Axis. In the Scaling section, select 100,000.
- Click the legend and drag it to appear in the upper-left corner of the plot area.
- Click the plot area to select it. Drag one of the resizing handles on the right side of the plot area to drag it right to fill the space that used to be occupied by the title.
- To present your charts in color, change the color of text in the primary vertical axis to match the color of the monthly bars. To change the color, click the numbers to select them. Use the Font Color drop-down on the Home tab to select a color such as blue. This color cue helps the reader realize that the blue left axis corresponds to the blue bars.
The resulting chart is shown at the bottom of Figure 3.32. The chart illustrates both the monthly trend of each month's sales and the progress toward a final YTD revenue number.