Create a line chart with bands [tutorial]

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.

Step 1: Arrange your data

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.

Our chart is ready.

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:

• Bar chart with lower & upper bounds
• Shading above or below a line chart to depict boundaries
• Indexed charts to depict change from point in time
• Thermo-meter chart with last year value
• World education rankings visualization – a case study

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.

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