MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Rogue Series for Shading

July 25, 2023 - by

Use Rogue Series for Shading

Problem: I want to shade the areas between the gridlines in this chart.

A line chart showing Sales. Jan is below 400, Feb is above 400, March peaks close to 700, April is below 600, May and June just above 600 and July is just above 400.
Figure 1211. Add alternating shading between the gridlines.

Strategy: Use four series as stacked area charts.

To use this method, you need to take control of the vertical axis. Format the vertical axis. Figure out the minimum, maximum, and major unit that you will be using.

In the Axis options, set Minimum to Fixed=0. Maximum to Fixed = 800. Major Unit to Fixed = 200. Minor Unit is left at Automatic.
Figure 1212. Make sure the scale won’t change.

  • 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.

There will be five series in the chart. The first four series are fake. Labeled One, Two, Three, Four, they all have 200 as the value for each month. The fifth series is the actual Sales.
Figure 1213. 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.

The initial chart is a stacked area chart. The first four series provide 200-tall bands of alternating color: One is blue and goes from 0 to 200. Two is red and goes from 200 to 400. Three is green and goes from 400 to 600. Four is purple and goes from 600 to 800. The Sales series then shows on top of that.
Figure 1214. 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 1212.

  • 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.

Change the Sales series to a line. Re-color series one and three as light green. Re-color series two and four as dark green.
Figure 1215. 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:

This article is an excerpt from Power Excel With MrExcel

Title photo by vackground.com on Unsplash