Create a line chart with bands [tutorial]

Posted on May 5th, 2014 in Charts and Graphs - 20 comments

Here is an interesting scenario.

Imagine you are responsible for customer satisfaction at ACME Inc. Every month you track customer satisfaction rate for the 3 products you sell which are conveniently named Product A, B & C.

You also have bands for the satisfaction rating.

  • Rating of 85% or below is Average
  • Rating between 85% & 95% is OK
  • Rating above 95% is good

At the end of the year, you want to visualize the ratings for last 12 months for 3 products along with bands.

Something like this:

Line Chart with bands to depict KPIs or goals - using Excel

Unfortunately, there is no “Insert Banded line chart” button in Excel. So what to do?

That is what we will learn today. Ready?

Create a line chart with KPI bands

The process of creating a line chart with background bands is simple. Follow these steps.

Step 1: Arrange your data

Lets say your data looks like this:

Original data for line chart with bands

Now, add the band information to it and make it look like this:

Final data - for line chart with bands

Use simple formulas to generate OK & Good columns from the bands.

Step 2: Create a line chart with all data

Select all 6 columns of data and insert a line chart.

This is how it looks.

Initial line char tiwht all the data - line chart with bands

Step 3: Convert Average, OK & Good lines to columns

This step changes depending on the version of Excel you are running.

Adjusting chart series settings - line chart with bands

In Excel 2013:

  1. Right click on any line and choose “Change series chart type”.
  2. Now, set up “Stacked Column” as chart type for Average, OK & Good series. (As shown in above picture)
  3. Done!

In earlier versions of Excel:

  1. Right click on Average line and choose “Change series chart type”.
  2. Change it to “Stacked Column Chart”
  3. Repeat the steps 1 & 2 for OK & Good lines too
  4. Done.

Step 4: Make columns wider by setting gap width to 0%

Adjust gap width to 0% - line chart with bandsRight click on any of the columns and choose format.

Change the gap width to 0%.

After adjusting column gap width - line chart with markers

Step 5: Adjust axis settings & column colors

Since our focus is to show variation in customer satisfaction ratings between the band of 80% to 100%, adjust axis min to 80%, axis max to 100% and major unit to 5%.

Also, fill columns with dull & subtle colors. This way, user’s attention shifts to the lines.

Adjust column colors and axis settings - line chart with bands in Excel

Step 6: Format the lines & axis labels

Now let’s make those lines stand out. You can add markers, adjust line thickness. While at it, fix the orientation of axis labels and remove year (as it is not needed).

Format line markers, axis settings - line chart with bands in Excel

Step 7: Add a title, apply labels to important points and annotate bands

This is the last step. Lets add a descriptive title to the chart.

Also, apply data labels (only to the last data point). To do this:

  1. Click on the line. All points will be selected.
  2. Click on the last point alone.
  3. Now only last point will be selected.
  4. Right click & add data label.
  5. Click on data label.
  6. Click on it again (this will select only that label, not the entire series)
  7. Format it by adding series name.

Annotate bands by either adding data labels or text boxes.

Our chart is ready.

Finalized line chart with markers to depict KPIs or goals in Excel

Download line chart with bands template

Click here to download the chart template. It has detailed images of all the steps. Examine the chart settings to understand this better.

 

Do you make banded line charts?

Although I make very few banded charts, I think they are great for several scenarios.

What about you? Do you make banded line charts to report this kind of data? What techniques you rely on? Please share your tips & ideas using comments.

More charts like this:

If you like banded line charts, you will surely enjoy these other charts too:

Your journey to Excel awesomeness – a banded line chart

Your journey to Excel Awesomenss - with Excel School program from Chandoo.org

Why stop at banded line charts? You want to learn how to create different types of charts, reports, dashboards & analytical models to supercharge your career. And this is where my Excel School program can help you most. It is an online course with more than 50 lessons on various aspects of Excel, right from basics to advanced concepts.

You can login and access the lessons from anywhere at anytime. You can learn at your own pace and become awesome in Excel.

Please click here to learn about Excel School program & join us.

Your email address is safe with us. Our policies

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

20 Responses to “Create a line chart with bands [tutorial]”

  1. nir says:

    save time: skip step 4 by using stacked area instead of stacked columns.

  2. Hi Chandoo,

    Your example on http://chandoo.org/wp/2014/05/05/line-chart-with-bands-excel-tutorial/ looks very much the same as a chart, published by Hessel de Walle, somewhere half of april: http://hesseldewalle.blogspot.nl/2014/04/excel-chart-with-background-indicating.html
    I made a kind of copy on my own site: http://www.fhvzelm.com/files/DiversenGrafiekenAanvulling.xlsm, sheet LijnWaardenBovenOnder.

    Is this a coincidence, a copy or inspiration? Or is it a commonly known old trick? It was new to me.

    By the way: your charts and other Excel info look good!

    Mvg, Frans

    • Chandoo says:

      Hi Frans…

      Thanks for the comments. It is not a copy. Just a coincidence.

      I have not heard about Hessel’s website until today. As you can see from my reply to him below, I got inspiration for this from a reader’s email.

  3. Remarkable, I published this exact same idea on April 22, 2014, using exactly the same method. Two souls one thought.

    You can find it in my blog http://hesseldewalle.blogspot.nl/2014/04/excel-chart-with-background-indicating.html

    • Chandoo says:

      @Hessel,

      Thanks for your comments. My post is a mere coincidence. I did not even know about your website until I saw your comment. But as you can imagine, chart with background bands is a common request by many people working in service / manufacturing areas where such monitoring is required. I got an email from a reader last week with similar request (but a little more complicated) and I used that as an inspiration for this post.

      Btw, very good blog you have. I am adding to my reading list.

  4. Carlos Goncalves says:

    How can we add bands based on the Average and 1; 2 and 3 Standard Deviation?

    The idea is to have one green band, 2 yellow bands and 2 red bands

  5. Chris Macro says:

    I image you could use this same methodology to create a four quadrant colored analysis chart as well. Would just need another stacked bar graph series for the additional two colors :)

  6. Excellent file by Hessel (I follow his blog already) and good explanation by Chandoo (as always) …
    I found now from here:
    http://www.prodomosua.eu/ppage05.html#contributi a file by Fernando Cinquegrani was the 9 December 2003 … http://www.prodomosua.eu/zips/bands.xls
    of course, as you, he is a great expert in Excel chart too

  7. Youngy says:

    I had created something similar last year and it was cool. X Axis was time Y axis was value. I created vertical tiles which showed BEFORE and AFTER. I wanted to highlight change in trend in terms of BEFORE vs AFTER. Of course I used Bar Chart instead of Column Chart as suggested here) and it worked quite pretty well :)

  8. Eric Lind says:

    I found using gradient fill in the plot area works reasonably well.

    You simply have to define your stops. No extra columns required. :)

  9. Swetha says:

    Your tutorial is awesome creating line chart with bands.
    Thanks for sharing.

  10. Sarah says:

    Nice looking line chart tutorial
    Thanks

  11. Jeffrey Lebowski says:

    Beautiful work. How did you get the lines from last data point to data label? Are these just inserted shapes?

  12. Dinesh K Takyar says:

    Excellent! Thanks for sharing!

  13. Oz says:

    Very cool, I’m going to be making a lot of use of this!

    If it helps; you can save on having to calculate the numbers for the different bands by not using a stacked chart:

    Set the numbers in the last three columns to the actual numbers (85%, 95% and so on) and change the column order so the highest number is first and the lowest is last.

    Instead of using a stacked column use a clustered column, set the spacing to zero as shown but set the Series Overlap to 100%.

    This has the same effect but you don’t need a risky formula to find the right values for the bands. You could even have your banding columns point at a table of fixed values, so if your measures change all you need to do is update three lines in another table!

Leave a Reply