An Underrated Chart Type: The Band Chart

Written by

Charts, Microsoft Excel, Tableau, Visualization

Why band charts should be used more often and how to create them with Microsoft Excel and Tableau Software

Band Chart Intro Band chart, range chart, high-low line chart, corridor chart. I don’t know whether there is a standard term for this type of charts, so let me call it a band chart hereafter.

What is a band chart?

Basically a band chart is a standard line chart enhanced with a shaded area displaying the upper and lower boundaries of groups of data (e.g. the range between the minimum and the maximum of all members of the category). Band charts are very often supplemented by another line showing the arithmetic mean (the average).

What is the big whoop?

Band charts provide by far more context to your visualization and more insight into your data. Especially if you have to explore unknown data sets. They are easy to implement, very intuitive, very effective and do not require one pixel more of your dashboard real estate than a standard single line chart.

This being said, I have always been wondering why I do not see these charts more often in my professional environment. Are people underrating the benefits of band charts or am I overrating them?

Today’s article discusses the benefits and the use cases of band charts and provides tutorials of how-to implement this type of chart with Microsoft Excel and Tableau Software. As ever, including the Excel workbooks for free download and the Tableau visualization for direct access via Tableau Public.

The Benefits

Why is a band chart more efficient than a line chart? Because it provides more context. Unlike a single line chart displaying only the development of the selected data series over time, a band chart provides a variety of additional information at a glance. Let’s assume we are doing a competitive price analysis per product. Something like I did here: You can’t start a fire without a spark.

The line chart represents the development of the price of your selected product. Visualizing the competitive analysis as a band chart with an average line adds context and helps you to answer questions like

How do minimum and maximum prices in the market develop over time? Are the prices going up or down?
Is the spread of all product prices of all competitors staying stable, increasing or decreasing?
How price competitive is your product? Are you closer to the minimum or to the maximum? Did this change over time? Did you become more or less price competitive?
How close is your price to the average market price? How did this relationship change over time?
Where is the average line compared to minimum and maximum? An average line close to the minimum is an indication that only a few competitors offer the product for a relatively high price.

Just a small selection of data analysis questions you can answer at a glance with a band chart.

The Use Cases

Here is a generic description of a data analysis challenge you may use a band chart for:

The data includes a categorical dimension like regions, sales persons, companies, products, etc. and a numerical measure over time like monthly sales, revenues, profits, etc.
The purpose of the visualization is not a comprehensive reporting of all values for all members of the category, but rather one report per member or an analysis tool for interactively exploring the data

Here are just a few real life business examples you could use band charts for:

Sales performance of outlets or regions
Revenues per sales region
Your product prices compared to similar products of your competitors
Profit and loss of companies or profit centers

The Example Data

The data used in the Excel and Tableau workbooks below is taken from Sean Lahman’s Website. Sean is kind enough to provide “The Baseball Archive” for free download, a “free relational database of individual and team statistics that covers the game back to 1871”. I have taken only a very small fraction of data from the Lahman Baseball Archive: the salaries paid by the MLB teams from 1985 to 2010.

How to create a Band Chart with Microsoft Excel

As usual, there is more than one way to skin the cat:

1. Combination of a Line and a Stacked Area Chart

Consolidate and prepare your data: calculate the selected data series, the minimum, the maximum and the spread, i.e. maximum minus minimum
Create a line chart and add four data series to it: the selected series, the averages, the minimum and the spread between maximum and minimum
Change the chart type of the minimum and the spread series to a stacked area chart
Format the minimum series to be invisible (no fill color, no border)
Format all other series the way you want
Add a legend to explain the meaning of the data series

That’s it. Here is an example how this could look like:

2. A Standard Line Chart Line with High Low Lines

Prepare your data (see above)
Create a line chart and add the selected series, the averages, the minimum and the maximum
Add high-low-lines to the chart and format them the way you want

With this approach the band is less intense, but you still get the picture:

3. A Standard Line Chart Line with very thick High Low Lines

Excel 2007/2010 enables you to change the width of the high-low-lines. Setting the width to e.g. 30 pt. and formatting the maximum and minimum line with no line creates shaded reference bands like this:

Instead of high-low-lines, you can also use error bars or additional data series in an XY scatter chart. Jon Peltier has a great article on all the aspects of High-Low Line Alternatives in Excel Charts.

Here is my Microsoft Excel workbook including the three examples described above for free download:

Download Band Charts / MLB Salaries (Microsoft Excel 2003, 217K)

How to create a Band Chart with Tableau Software

Tableau Software has no built-in area chart type. Workarounds are possible by preprocessing the data, defining custom SQL data connections or using polygons.

For creating a band chart, however, we do not need one of those workarounds. Tableau’s built-in reference lines combined with a little trick of replacing the built-in filter by a Parameter and a Calculated Field will do the job.

Here is the step-by-step tutorial:

Create a Parameter based on the list of teams
Create a Calculated Field assigning the salaries if the selected Parameter is equal to the team, otherwise Null
Drag the year to the column shelf and the calculated field to the row shelf
Drag [Salaries] and [Team] to the level of detail shelf
Right click on the horizontal axis and add a reference line, select per cell for the scope and average of [Salaries] as the line
Add another reference line, select “Band”, choose per cell as the scope and define the band from minimum of [Salaries] to maximum of [Salaries]
Finally do the formatting the way you like

That’s it.

Here is the visualization on Tableau Public:

The Power of Band Charts

I confess, I do not know anything about Baseball. Of course “I don’t memorize who played third base for … Pittsburgh in … 1960” (remember the film?). I do not even know the names of all the teams and I have no clue how much a MLB team costs. However, the band chart helped me to understand at least the following facts within a couple of minutes:

The New York Yankees are the all time big spenders. Their salaries have been the highest until 1988 and again every season since 1996 until today. Even in the 1990s the Yankee’s salaries have always been way above the league’s average.
The Houston Astros seem to be the average Joe in terms of salaries. Except for 1991/92 and 2009, their salaries are closely dancing around the leagues average line.
The Montreal Expos have been the scrimpers. No team has been as close to the minimum line of the salary band as they were.

I bet most of you know this and I guess I am stating the obvious here. But the topic and the data were completely new to me and it took me only one very simple interactive chart and a few mouse clicks to get some meaningful insights.

So, next time you have to explore an unknown data set, I highly recommend giving the band chart a try.

Your thoughts?

As I mentioned in the introduction, from my point of view the band chart is totally underrated. In my professional environment I have barely seen these charts, Actually I think in my small world, I am one of the few using them at all.

What do you think? Is it just a coincidence that people I am working with are not using this chart type? Or am I overrating its importance? Please leave me a comment and let me know what you think about band charts.

Acknowledgement

Many thanks go to Dan L for all the discussions we had on band charts, for pointing me to the Baseball example data and in general for providing me with the idea to this post. Thanks, Dan!

Many thanks go also to Sean Lahman for compiling and sharing his great Baseball Archive. Thanks, Sean!

I am having some more things in my pipeline on data analysis, visualization and optimization with Microsoft Excel and Tableau. More to come soon.

Stay tuned.

area charts with tableau band chart band charts with microsoft excel band charts with tableau software corridor chart high-low line chart microsoft excel range chart tableau software

Comments

31 responses to “An Underrated Chart Type: The Band Chart”

April 14, 2011

Joe Mako

I liked your Excel version better than your Tableau version, so I remade the chart in Tableau to look more like your Excel version. I used custom SQL when connecting to the data source, and multiple mark types to get the view at http://public.tableausoftware.com/views/MLB_salariesjmedit/MLBSalariesjmedit (the tool-tip does not behave as I would like it, but if you select the line, you can get the value to display)

Reply
April 14, 2011

Dan Murray

I use a similar technique in combination with breaks for quarter/month/etc….and de-emphasize the data marks so that the banding itself is the primary visual. This works well for long-term trending analysis. Nice post.

Reply
April 14, 2011

dan l

Robert,
Thank you for the thanks. Truthfully, I should be thanking you as
I’ve made a ton of hay with variations of this chart. It’s one of the
best items in my tool box, so I owe you a great deal.
I think what makes this technique so useful is that you can freely place a
variety of additional series alongside the data and it never really
gets too noisy. Historic highs/lows that are beyond the scope of
the actual selected data, for example, plays nicely. Point tagging
doesn’t work out terribly either.
I should add that while I make use of this chart with dashboards,
it does make one for one really good presentation piece.

Reply
April 14, 2011

Markus

Thank you for the great article! I wonder if it’s somehow possible to do a band chart in Excel by using your method if the data has negative values? For instance if the maximum value is positive and the minimum value negative?

Reply
April 14, 2011

Robert

Joe,
great work. Thanks for revamping the Tableau visualization using a Custom SQL data connection.
I agree, your “stacked area” solution looks much better than the one I posted using the reference line / band.
Well, there is always more than one way to get to results. With Excel and with Tableau.
Your implementation looks better, no doubt about it. But it requires more experience and SQL knowledge. My version looks a bit bumpy, but in my humble opinion you still get the big picture and it is very easy to implement, even if you don’t know SQL.

Reply
April 14, 2011

Robert

Dan,
thanks for your comment. I couldn’t agree more.
Band charts are very helpful for trend analysis or forecasting. They have a very broad array of applications. That’s why I am still wondering why people don’t use them more often.

Reply
April 14, 2011

Robert

Dan,
you are welcome. I am happy to hear that band charts helped you improving your visualizations.
I fully agree with you. Band charts are not only a great visualization in the standard version, but are also open to a lot of possible variations.

Reply
April 14, 2011

Robert

Markus,
thanks for your comment and question.
Actually I do not see a problem with negative values in the implementations provided in the article. The only thing you would have to change is the formula calculating the minimum on the worksheet [calculations]. The formula is excluding zeros from the minimum calculation. If you have negative values, simply remove the IF-clause from the formula (i.e. include all values) or replace “>0” by “<>0″ (i.e. still excluding zeros, but not the negative values). Second thing you would have to do is changing the axis label positions to “low” instead of “next to axis”.
Other than that, the charts should work fine with negative values, even if minimum and maximum are negative.

Reply
April 14, 2011

Acotgreave

Nice post, Robert – this is a useful chart. Joe’s additional method and your comment make perfect sense to me: there’s the quick/dirty/effective way and the more complex but cleaner method. Both are effective and depend on the time/skill you have to dedicate to the solution.

Reply
April 14, 2011

paresh

and we have the Bollinger Band if you want to make some money in the market!!! No guarantees though!

Reply
April 14, 2011

SteveT

Great Post!
One interesting comparison would be to put a vertical band for when the team reached the World Series. That way you could see how the spending related to a shot at the title. (Column M=”Y” by year by team of file:Teams.csv)
Or One put a vertical band for when the team won the World Series. (Column N=”Y” by year by team of file:Teams.csv)

Reply
April 14, 2011

Robert

Steve,
many thanks for your comment.
It is an interesting idea to add the correlation of salaries and success to the visualization.
Vertical bands would be one option to do this. Yet, I am a bit afraid additional vertical bands might clutter the visualization.
Another alternative would be color coding the line chart, respectively the line markers, e.g. grey markers as the default, blue for the years the team reached the World Series and red for the years they won it. Or using different marker types instead of different colors.
This would put the message across without cluttering the chart. What do you think?

Reply
April 14, 2011

SteveT

Yes, i like that option. if you go that route, then you could also add two more colors (or are we going to far :)) one for if they reached the playoffs and one if they reached the Championship series as all build on eachother.
Thanks for the link to the data!!! Way cool.

Reply
April 15, 2011

dan l

That was sort of the goal of the project to begin with.
Well. Actually. The goal of the project was to make a dashboard. The purpose of the dashboard was to show salary against a variety of standard baseball team metrics: wins, losses, runs, runs against.
In that execution, you could come away with some interesting observations. Every year, of your top 10 winning teams 2 or 3 of which will be below average spenders for that year.
For as much as I’d love to tell you that money isn’t a huge factor, the Yankees prove otherwise. Apparently outspending the number 2 team by 60 or 70 million dollars might get you a few wins 🙂

Reply
April 15, 2011

dan l

Try this:
https://rapidshare.com/files/457492174/salaryplayoffs.xlsx
I added markers in varying shades of blue to indicate play off entry, winning the league, and winning the world series.
A few words, as you look at it:
1. I know that’s the saddest legend in the history of sad legends. I’m really not that fail, just in a hurry 🙂
2. I’ve experimented with the markers quite a bit since Robert taught me this chart. I’ve not had a lot of success with using different shapes. Using colors seems to work quite well – though these might not be the best color choices. In this case, light blue for a playoff birth, darker blue for a league win, darkest blue for a world series win.
At first I was a little put off by using the oversized marker. It took me a little while to rationalize it: I’m putting the marker there to highlight something. The marker at about an 8 point font seems about right. Smaller it can lose it’s effect.
3. Check the yankees and redsox to see what a big salary buys you. Check the Oakland A’s to see what you can do on a budget.
4. Point and laugh at the Chicago Cubs. Note the lack of World Series wins. You can go back 100 years and not find one. Tufte couldn’t make them look good on a chart. 🙂

Reply
April 21, 2011

Thomas

What you call “Band chart” is a contextual information heavily used in stock charts by traders. The most famous one was created by John Bollinger in the early 1980s : the “Bollinger bands”.
It’s much more flexible than the “control chart” where min and max are horizontal lines.

Reply
April 21, 2011

Robert

Thomas,
thanks for your comment and the additional information.
Agreed, band charts are often used in stock chart analysis and they are much more intuitive than simple high-low-lines.
That’s why I was wondering why I don’t see them more often in other business reports / dashboards. I am using them quite often and most of the time I see an aha-reaction on my clients’ side.
Band charts are easy to implement, easy to understand and a very powerful visualization technique. From my point of view they do not deserve the wallflower existence they seem to have at the moment.

Reply
May 31, 2011

RTKaushik

Making the Y axis logarithmic, would give a better visual idea of the growth of the High and Low lines, as well as a comparison of the spread in 1985 vs 2010

Reply
May 31, 2011

Robert

RTKaushik,
many thanks for your comment and suggestion.
Well, yes and no.
If you are more interested in the development of the data (and the range) over time, a logarithmic scale is a viable alternative.
On the other hand, if you are more interested in comparing one data point (year) of one team within the context of the maximum, the minimum and the average, a linear scale is more helpful. Furthermore, many people are not familiar with logarithmic axes.
From my point of view, it always depends on what you are interested in and whom you are presenting the visualization to.

Reply
June 23, 2011

sean otto

Robert,
I am trying to duplicate what you did in Tableau and I am running into a problem. When I move the “Sum(Salaries)” and the “Team” on to the level of detail shelf I get a solid line at the bottom of the chart. It disappears when I remove “Team”, but then the banded lines don’t work. How did you get rid of the solid line at the bottom? I get this when I try to duplicate what you did on my own data file.

Reply
June 23, 2011

Robert

Sean,
many thanks for your comment
What do you exactly mean by “a solid line at the bottom”? Do you see a line at the bottom (and the top) of the reference band? If so, did you try to format the reference line / band and set the reference line in the Format Reference Line window to none?
If this doesn’t solve your problem, can you please elaborate a little bit more on what the issue is? Or – even better – can you send me a packaged workbook (see email link on the left)?
Thanks
Robert

Reply
June 24, 2011

sean

Robert,
I sent you a packaged workbook. Thanks for your assistance.

Reply
June 24, 2011

Robert

Sean,
thanks for your workbook. This is no problem.
You have to hide the marks for the Null values:
1. Right click on the calculated field on the row shelf and select Format
2. Go to the Pane tab of the Format window
3. At the bottom in the section Special Values you have to select Marks: Hide (instead of Marks: Show)
This way the Null values (i.e. the teams that are not selected) will not be shown and the line at the bottom of the chart disappears.
I hope this will be helpful.

Reply
June 24, 2011

Sean

What was the custom SQL you used on this? I’m interested in learning more.

Reply
June 24, 2011

Robert

Sean,
I am not sure if Joe is still around, so I will try to answer your question:
Joe’s Custom SQL statement isn’t too hard to understand, but maybe too long to explain it in a comment here.
Simply download the workbook Joe provided with his link above, go to the sheet [Band Chart jm edit] and click on Data | Data Connection | Edit and you can have a look for yourself.

Reply
June 24, 2011

sean

Thanks Robert,
There are so many little nuances.

Reply
June 27, 2011

sean

Robert,
I gave that a try, but it doesn’t let me scroll down to see all of the SQL. I am only able to see the beginning of the SQL.

Reply
June 27, 2011

Robert

Sean,
hmm. I had no issues scrolling down and copying the statement. I am wondering why this is not working for you.
Anyway, here is Joe’s full SQL statement:
SELECT [Sheet1$].[Salaries] AS [Salaries],
[Sheet1$].[Team] AS [Team],
[Sheet1$].[Year] AS [Year],
“Data” AS [Type]
FROM [Sheet1$]
UNION ALL
SELECT MAX([Sheet1$].[Salaries]) AS [Salaries],
NULL AS [Team],
[Sheet1$].[Year] AS [Year],
“Band Max” AS [Type]
FROM [Sheet1$]
GROUP BY [Sheet1$].[Year]
UNION ALL
SELECT MIN([Sheet1$].[Salaries]) AS [Salaries],
NULL AS [Team],
[Sheet1$].[Year] AS [Year],
“Band Min” AS [Type]
FROM [Sheet1$]
GROUP BY [Sheet1$].[Year]

Reply
September 23, 2012

Dan Murray

I missed this post before. Thanks Robert and Joe for the post!

Reply
January 18, 2021

Microsoft Excel Recalc or Die

Hi Robert, I am coming from the future (2021), bit under 10 years later.. and still this is an underrated chart!
Of course, across the business analytics settings with the exception of stock trading, as I am reading in the comments above.
Voilá mate! Amazing post. It really has inspired to implement this on couple projects with historical sales data.

Reply
March 15, 2024

qnovate

Making the Y axis logarithmic, would give a better visual idea of the growth of the High and Low lines, as well as a comparison of the spread in 1985 vs 2010

Reply