This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
See Detail on Large & Small Data Points
For our monthly sales and operations planning meeting, I plot the forecast and actual for 30 model lines. Some of the models sell 30,000 a month and some sell 300 a month. No one can make out the detail on 80% of the models.
Figure 1036 The first 12 models are too small to see.
Strategy: Use a Log scale. In a Log scale, the distance from 10 to 100 is the same as the distance from 1,000 to 10,000. This lets you zoom in on the smaller items.
1. Choose Layout, Axes, Primary Vertical Axis, Show Axis with a Log Scale.
Figure 1037 A log scale zooms in on the smaller points.
2. The first two gridlines on the chart include no data points. Because the purpose of the chart is to see if the forecast was within 15% of the actuals, it would help to zoom in. Double-click the numbers along the vertical axis to access the Format Axis dialog (right-click in Excel 2007).
3. Change the Minimum and Maximum from Automatic to Fixed. Enter 100 as the Minimum and 100000 as the Maximum. By the way, the Major Unit and Minor unit control where the gridlines will be drawn.
Figure 1038 Set the minimum to 100 to zoom in.
4. The markers are too large for this chart. Choose each series. In the Format Series dialog choose Marker Style and either None or a smaller size for the marker.
5. To help the reader’s eye travel from the label to the point, use Layout, Lines, Drop Lines. Select the drop lines. Use Format, Shape Outline. In this dropdown, choose Dashes and color to make the lines less prominent.
6. The Error Bars are showing ~+mn~15% from the forecast. To set these up, choose the Forecast series. Use Layout, Error Bars, More Error Bar Options. Choose Both for the direction. Choose Percentage, 15%.
Figure 1039 Draw error bars around each forecast point.
Result: You have a chart to review at the sales and operations planning meeting. Any time that the sales team’s forecast was not within 15%, have a discussion about what happened.
Figure 1040 See detail for small and large points with a log scale.