Highlight a time period on a line chart

So today I thought I would post the “how to” for the third in the series of line charts I posted a while back.


Let’s imagine that you want to highlight a poor period during your year. This chart is ideal for that purpose, using a combination line / area chart with drop lines.  You can download my sample worksheet here.

Step 1: Setup your data table

Let’s imagine that you have some data for the year set out as follows:

original data table

This table houses the data for your basic line chart.  For your next step, you need to create a new column which will house the data for your area chart.  You can do this by copying and pasting the data you want to shade, or use a calculation to make the process automatic should the data change.  I vote for automatic.

In cell C2 then, type in the formula below:


This calculation will return a value of #N/A unless the value of B2 is less than 10, in which case it will show the value in B2.

Copy and paste the calculation down the cells until your table looks like this:

finished data table with calculations

Step 2: Create the Basic Line chart

Now that your data is set up, highlight the whole table and click on Insert > Charts > Line > 2-D line > Line.

insert line chartA chart should appear similar to the one below:

line chart

Delete your legend and gridlines.

Then right click on the red series and choose Change series chart type.

change series chart type

Scroll down the choices and choose an Area chart:

Area chart

Click on the OK button and your chart should update as shown below:

combination chart

You can format the area below your line as you would wish by clicking on the area and pressing Ctrl+1.  This will bring up the formatting menu box.  I chose to shade the fill area a light blue to match the line above it.

awesome chart

Step 3: Add the drop lines

The final step involves adding the black drop lines to the line series. Simply select on the blue line and then click on Chart Tools > Layout > Lines > Drop Lines.

drop lines

Your chart should update as shown below:

cool chart

And that is it.  Hope you find it useful!

+Alesandra Blakeston

One thought on “Highlight a time period on a line chart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s