Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Shading above or below a line in Excel charts [tutorial]

Posted on February 13th, 2013 in Charts and Graphs - 16 comments

When comparing 2 sets of data, one question we always ask is,

  • How is first set of numbers different from second set?

A classic example of this is, lets say you are comparing productivity figures of your company with industry averages. Merely seeing both your series as lines (or columns etc.) is not going to tell you the full story. But if we can shade our productivity line in red or green when it is under or above industry average… now that would be awesome! Something like below:

Shaded line charts - help us tell a better story when comparing one series with another

The above chart tells us where we are lagging and where we are good. It will let us ask poking questions about the gap and find answers (may be removing coffee machine from 2nd floor last May was a bad idea!)

So how do we create such a chart?

PS: This chart and article is inspired from a question asked by arobbins & excellent solution provided by Hui here.

Creating a shaded line chart in Excel – step by step tutorial

1. Place your data in Excel

Lay out your data like this.

Original Data - Shaded line chart in Excel

2. Add 3 extra columns – min, lower, upper

If you look at the chart closely, you will realize it is a collection of 4 sets of data. See this illustration to understand.

Anatomy of Shaded line chart made in Excel - 3 extra series explained

Write formulas to load values in to min, lower (green) & upper (red) series.

  • Min is minimum of productivity and ind. average
  • Lower (green) is difference between productivity and ind. average (or NA() if negative)
  • Upper (red) is difference between ind. average and productivity (or NA() if negative)

3. Create a stacked area chart from this data

Select all the 4 series (productivity, min, lower & upper) and create a stacked area chart.

This is how it looks.

Step 1 - create a stacked area chart - shaded line chart in Excel

4. Format the productivity series as line

Right click on productivity series and using “Change series chart type” option, change it to line chart.

Step 2 - Format Productivity series as line - Shaded line chart in Excel

5. Make the min series transparent

Select min series and fill it with “No color”

Step 3 - make the min series transperant - Shaded line chart in Excel

6. Format lower & upper in green & red colors respectively

Step 4 change the colors for lower & upper series - shaded line chart in Excel

And you are done!

Optional: adjust series formatting, add grid lines etc.

As a bonus, you can add vertical grid lines (so that we can understand the red green changes easily) and format the horizontal axis. You can also move around the legend and remove the words “min” from it.

This will make the chart look really awesome.

Shaded line charts - help us tell a better story when comparing one series with another

Is this the only way to compare productivity with industry averages?

Although our shaded line chart is an excellent way to visualize differences between 2 series of data, I kept thinking if there are other ways to compare this.

After a bit of doodling & drawing inspiration from various charts I have seen earlier, here are 4 more options we can consider.

Option 1 – Productivity vs. variance wrt Ind. average

Alternative 1 - shaded line chart in Excel

This chart shows the variance (industry average-productity) at bottom so that we can easily look at overall trend & understand how we fared with respect to industry.

To create this chart, you just have to calculate the variance in a separate column and create a column & line chart combination (column for variance & line for productivity). Once such a chart is ready, go to fill options for the column chart and check invert colors if negative option and set up green & red colors!

Option 2 – Productivity vs. better or worse indicators

Alternative 2 - Shaded line chart in Excel

This chart just shows whether productivity surpassed industry average or not in a boolean state (green for yes, red for no)

This chart is a combination of line & column chart with same principle as above (invert if negative option).

Option 2 (made using Excel 2010 Sparklines)

Alternative 2 - made with Sparklines - Shaded line chart in Excel

You can create this chart very easily with Excel 2010 sparklines. Line chart for productivity and win-loss chart for better or worse indicators.

Option 3 – Collapsed Productivity vs. variance wrt Ind. average

Alternative 3 - collapsed - Shaded line chart

Since the color is already telling us whether variance is negative or positive, we can collapse both to same side of axis (thus saving some space & reducing redundant information).

To create this chart, we need two series of data – positive variance & negative variance as 2 sets of areas on the chart.

Option 4 – Collapsed Productivity vs. better or worse indicators

Alternative 4 - Shaded line chart with collapsed indicators in Excel

Well, this is same as option 2 but collapsed.

Download Example workbook

Click here to download the Excel workbook containing all these examples. You can also see detailed steps for making the shaded line chart in it.

How do you compare one series with another?

I must confess that I never made shaded line chart until today. For smaller data sets (<15 items), I usually compare by making column charts or thermo-meter charts. These are easy to make and easy to understand. For larger data sets, I try to make dynamic charts so that I can choose which series to include in comparison or make indexed charts.

Now that I learned how to set up shaded line charts, I will try them in my upcoming projects & consulting assignments to see how they fare.

What about you? Which types of charts do you use to compare one series with another? Please share your techniques & implementations using comments. I would love to learn more from you.

Compare often? Check out these charts

If you compare apples to apples (or to an occasional  bushel of oranges) for living, then check out these charting tutorials & techniques.

WARNING: After learning these techniques, Suddenly you will become incomparably awesome in your office.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Shading above or below a line in Excel charts [tutorial]”

  1. Meic Goodyear says:

    Great topic.
    Your Option 1 is one of my preferred methods, subject to a change of colour scheme. I know RAG (Red/Amber/Green) is a de facto standard, but red and green is the worst colour pairing of all for showing opposites, as 10% of males are red-green colour blind. Especially when using similar colour densities your options 3 and 4 would be quite unreadable to that section of the population. Unless mandated by management I also try to avoid using pairs of complementary colours for aesthetic reasons.
    My other preferred method is similar, but shows the variation as a percentage of the actual productivity rather than the plain value. Ideally, the two would be combined in a small multiple.

  2. Jasmine says:

    I was just looking on how to do something like this for work. Oh the irony! This is great, thanks

  3. zurman says:

    EVEN THIS IS VERY GOOD TIP

  4. Adam says:

    This is fantastic.
     
    I love it when a chandoo post happens to show a perfect solution for what you’re going to be working on that day.
     
    I was using daily performance data & found that the chart jumped from above to below average too frequently to make any real sense.
     
    Once I changed the data to look at a rolling 7 day average, the graph was perfect.
     
    Thank you!

  5. SomeintPhia says:

     
    Thank you for this nice training. Just one minor thing a coundn’t handle: how disapear the -50 value in the axis? .. strange
    Kind regards, SomeintPhia
     

  6. Denice Longshaw says:

    Once again Chandoo shows us how we can doll up a chart.  Thanks Chandoo.
     

  7. Snook says:

    Superb tip!

    Out of interest would it be possible to have just the productivity line and colour code it red/green as appropriate?

    • Hui... says:

      @Snook
      Yes
      To do that create a new Chart using just the Date and Column N & O for the series
      In Column N & O delete all the cells contents that have a #N/A error
      Reformat as required:
      Acme

  8. Chris says:

    I produce a weekly labor utilization graph that compares current year actual to target and two prior year actuals. The target utilization is represented by a medium density area graph (kind of mud colored). 
    The currently week actual is a dense line. Comparison is easy, if a week is below target it dips into “the mud”. Favorable weeks show the line above “the mud”.
    The prior years are progressively lighter density lines. It is very easy to see what is going on currently and where the prior year cycles are.

  9. Sebastian says:

    The chart is nice, but there are some errors if you look in it with high definition!
    Please try the main chart with x-values: Feb 22nd-28th and y-values 40-70. The important days are the 25th and 26th. The productivity changes from upper to lower, but there are both (red and green) areas.
    The problem is, that the color have to switch between two points.
    Bigger and often changes makes the problem more visibly.

    A solution maybe possible with a (not stacked) area chart, but that means, that the area below “min” get the color white and the grid is not visible.

  10. Brian says:

    This does look pretty cool. I am having a small issue though with the 1st chart. I discovered the issue when I tried to add a line that would follow allong the “ind. average”. When I did this I saw that in cases when we switch from green to red or red to green the the area chart was not in-line with my newly added “ind. average” line.

    For example if ind. average goes from being less than productivity to greater than, the 1st point of the red area starts on where productivity was on the last date before the switch which makes the slope of the top of the area less steep than the “ind. average” would.

    What I think would be ideal is in the case where there is a change in area color, that the chart should show a vertical cliff on both on the last point befor the change and the 1st point after. Excel 2010 wont let me do this.

    Does my issue make sense?

  11. […] Credit to both Peltiertech.com and Chandoo.org for their logic on how to fill areas with color in Excel. Thank you, […]

  12. […] Shading above or below a line chart to depict boundaries […]

Leave a Reply