- Choosing a Chart Type
- Understanding a Date-Based Axis Versus a Category-Based Axis
- Using a Chart to Communicate Effectively
- 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
- Using a Scatter Plot to Show a Trend
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, you generally track sales every month. But in the big picture, you are interested in how 12 months add up to produce annual sales.
The top chart in Figure 3.36 is a poor attempt to show both monthly sales and the accumulated year-to-date (YTD) sales. The darker bars are the monthly results. The lighter bars are the accumulated YTD numbers through the current month. In order to show the large YTD number for November, the scale of the axis needs to extend to $400,000. This makes the individual monthly bars far too small for the reader to be able to discern any differences.
)
Figure 3.36 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. You follow these steps to create the bottom chart in Figure 3.36:
- Left-click one of the YTD bars to select the YTD series. Right-click the selected series and choose Format Data Series. Excel displays the Format Data Series dialog.
- In the Format Data Series dialog, choose 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, choose Shape Outline, Black to change the YTD line to black.
- Turn off the gridlines by selecting Gridlines, None from the Layout ribbon.
- Select Axes, Primary Vertical Axis, Show Axis in Thousands from the Layout ribbon.
- Select Axis Titles, Primary Vertical Axis Title, Rotated Title from the Layout ribbon. Type Monthly Sales and press Enter.
- Select Axis Titles, Secondary Vertical Axis Title, Rotated Title from the Layout ribbon. Type YTD and press Enter.
- Right-click the numbers on the secondary vertical axis. Choose Format Axis. In the Scaling section, choose 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.
- If you want 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 do so, click the numbers to select them. Use the Font Color drop-down on the Home ribbon to select this color (for example, 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.36. The chart illustrates both the monthly trend of each month's sales and the progress toward a final YTD revenue number.