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:
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:
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.
Delete your legend and gridlines.
Then right click on the red series and choose Change series chart type.
Scroll down the choices and choose an Area chart:
Click on the OK button and your chart should update as shown below:
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.
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.
Your chart should update as shown below:
And that is it. Hope you find it useful!