Why band charts should be used more often and how to create them with Microsoft Excel and Tableau Software
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.
Comments
31 responses to “An Underrated Chart Type: The Band Chart”
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)
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.
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.
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?
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.
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.
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.
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.
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.
and we have the Bollinger Band if you want to make some money in the market!!! No guarantees though!
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)
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?
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.
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 🙂
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. 🙂
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.
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.
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
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.
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.
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
Robert,
I sent you a packaged workbook. Thanks for your assistance.
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.
What was the custom SQL you used on this? I’m interested in learning more.
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.
Thanks Robert,
There are so many little nuances.
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.
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]
I missed this post before. Thanks Robert and Joe for the post!
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.
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