1. Go back to the original data. Insert a new series for each gridline. These series will be stacked. The first series of 200 will run from 0 to 200. The second series of 200 will be on top of series 1 and will run from 200 to 400. Have your Sales series be the last series.
Figure 1058 One rogue series for each band of alternate shading.
2. Choose Insert, Area, Stacked Area chart. Don’t worry that the initial chart looks completely wrong.
Figure 1059 It is typical for these trick charts to look wrong at first.
3. Select the Sales series. Use Design, Change Chart Type. Change it to a Line chart.
4. Format the vertical axis. Go back to the settings in Figure 1057.
5. Click on one of the gridlines to select all the gridlines. Press Delete.
6. Select series One. Use Format, Shape Fill and choose a light color.
7. Repeat step 6 for the remaining area series, choosing alternating dark and light colors.
At this point, the effect is complete, but the legend is giving away your secret. You can delete individual entries in the legend.
Figure 1060 You need to delete 80% of the legend.
8. Click once on the legend to select it.
9. Click a second time on Four. This selects the one legend entry.
10. Press Delete. That one entry in the legend is deleted.
11. Repeat steps 8-10 for series Three, Two, and One.
There are a number of special charts where extra rogue series are used to create some formatting. For more examples, check out:
● Mario Garcia’s amazing five rogue charts in order from Learn Excel Podcast episode 1026.
● Andy Pope’s charting tutorials at http://www.andypope.info/.
● Jon Peltier’s charting tutorials at http://peltiertech.com/Excel/Charts/.