Highlighting Periods in Excel Charts

Shading or highlighting periods in Excel charts can help users more quickly interpret them and identify patterns. In the chart below I’ve highlighted every second month to give a quick visual indication of each period, which allows the user to focus on the line instead of having to refer back and forth to the horizontal axis.

Or even highlight date ranges, like the (Southern Hemisphere) winter months shown below:

You can also use this easy technique to highlight specific dates, like that of the maximum temperature, with a line:

Or a dot marker:

Watch the Video

Download Workbook

Step by Step Highlighting Periods in Excel Charts

The secret to this technique is to plot another series on the secondary axis for the highlighting/shading. I’ll step through the first chart example and you can download the Excel file and or watch the video to learn how to create the others.

Step 1: Set up the chart source data.

Notice the horizontal axis uses helper columns C and D for the month and day which will form a nested axis. I’ve used formulas to list only the first day of the month in column C and every 7^th date in column D to avoid the axis getting cluttered.

Column F contains a formula that specifies which months are shaded; in my example the even months are shaded. The formula returns TRUE for even months and when plotted in a chart the TRUE is equivalent to 1.

Step 2: Insert the Chart.

Select the data in columns C:F and insert a line chart.

Step 3: Change Chart Type.

Right-click the chart > Change Chart Type… Select Combo. Set the series you want to plot as a line, in my example it’s the temperatures. The series for shading should be a clustered column on the secondary axis:

Step 4: Remove Gap Width.

Select the column series “Shade Month” > CTRL+1 to open the format pane. Here set the gap width to 0%

Step 5: Format Colour.

Change the column series fill colour to something subtle:

Step 6: Secondary Axis Scale.

Set the secondary axis maximum to 1 so that the columns to go to the top of the chart:

Step 7: Hide the secondary axis.

Go to the Tick Marks and Labels settings and set to ‘None’:

Step 8: Apply further formatting as desired.

For example, a chart title, legend, remove gridlines etc.:

More Ideas for Highlighting Periods in Excel Charts

Labelling Events in Excel Charts - Plotting data over time can reveal patterns and trends, but often blips in the data require further explanation. We can help our user by labelling events to explain those blips or patterns revealed in the data.

Label Chart Minimum and Maximum Points - Automate highlighting the minimum and maximum in an Excel chart to help focus your readers’ attention.