- 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, whereas 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:
- Select the chart. In the Design tab, choose Change Chart Type.
- In the left panel of the Change Chart Type dialog, choose Combo. Change the YTD series to a Line chart. Choose the Secondary Axis box for YTD. Click OK. The YTD line is now plotted on the second axis, allowing the columns for months to fill most of the plot area.
- From the plus icon, uncheck gridlines.
- From the plus icon, choose Axis Titles. Excel adds the words “Axis Title” to the left, bottom, and right side of the chart.
- Double-click the numbers along the left axis to display the Format Axis task pane. At the top of the task pane, leave Axis Options selected. From the row of icons, choose the Chart icon. Expand the Axis Options category. Open the Display Units drop-down and choose Thousands. Uncheck the box for Show Display Units Label on Chart, because you will note the units when you edit the axis title.
- Without closing the task pane, single-click the numbers along the right axis. Open the Display Units drop-down and choose Thousands. Uncheck the box for Show Display Units Label on Chart.
- Click the bottom Axis Title and press the Delete key to delete that axis title.
- Double-click the left axis title. Type Monthly Sales ($000).
- Double-click the right axis title. Type YTD Sales ($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.