Search

Create a line chart with bands [tutorial]

Share

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:

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.

Lets say your data looks like this:

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

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.

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

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

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%

Right click on any of the columns and choose format.

Change the gap width to 0%.

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.

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).

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.

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

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.

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

Related Tips

Charts and Graphs

Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Charts and Graphs

Make an Impressive Interactive Map Chart in Excel

Charts and Graphs

How to Create a Dynamic Excel Dashboard in Just 5 Steps

Charts and Graphs

How to create a fully interactive Project Dashboard with Excel – Tutorial

Charts and Graphs

9 Box grid for talent mapping – HR for Excel – Template & Explanation

Charts and Graphs

24 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.

• maniratnam says:

Hi Chandoo & Others,

Please help me in creating line chart with bands (number range). Unlike percentage, my data is numbers and count. Following is my data.

Function Number of days - projects closed Days
100 200 300
0 - 100 101 -200 201 - 300 Good OK Bad
Comme 5 5 2 100 100 100
Customer 8 51 1 100 100 100
F&A 7 28 3 100 100 100
OP Support 4 38 1 100 100 100
Support 3 24 5 100 100 100

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

• Carlos Goncalves says:

Great, but I still have problems
If I have numbers instead of percentages, and values close to zero, I do not get what I expected
Try this numbers instead with only one product, the result is not, O tryed but the lower limit does not draw correctly

1,35
1,28
1,13
0,99
0,94
0,89
0,89
0,88
0,88
0,82
0,82
0,76
0,75
0,71
0,71
0,65
0,64
0,63
0,63
0,62
0,61
0,60
0,59
0,57
0,57
0,56
0,56
0,54
0,49
0,47
0,46
0,44
0,44
0,33
0,30
0,28

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

• @Roberto,

Nice site of this Fernando Cinquegrani . It is a pity that Excel sites in Italian are hard to find.

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!

14. find low-priced auto says:

This blog was... how do I say it? Relevant!! Finally I've found something which helped me.
Thanks a lot!

15. ANGELO says:

Hi, Chandoo

I follow your blog since 6 moths ago, & I want to thank you for your website, because it helps me in all the things that i need to do.

Your website is awesome, when I grow up in excel, I want to be like you.

Greetings form Mexico.

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.Â