Excel is a great tool when you know how to use it. There are many powerful visualizations that can be created with Excel. The band chart is one of them.
What is a Band Chart?
A Band Chart is simply a line chart with shaded areas (bands) that display the upper and lower thresholds of defined data ranges.
When to use a Band Chart?
When monitoring a metric such as profit or performance. They can be used to show capacity as well.
Where to start?
Proper preparation of data is the most important element of creating any type of data visualization in Excel.
Example: Show Capacity % each day for Plant 1 vs Plant 2
Step 1.
Prepare the data. In this example we will show 3 bands demonstrating “low” “medium” and “high.”Start with the time range, then data inputs and finally show percentage values where you want your band ranges in the format depicted below.
Step 2.
Hit “control T” and turn the data into a table. Why make the data into a table? Later on if you change a value it will automatically reflect in your chart. You will find this useful later… Make sure the box is checked for “My table has headers”
Step 3.
Click anywhere into the data table and select “insert” for “column chart.”
Step 4.
Select “2D Stacked Column.”
Step 5.
This is the time to properly visualize your “plant 1” and “plant 2” data points. Change each data series to line charts. Left click on the blue bar graph symbolizing “plant 1.”
Step 6.
Right click and select “Change Chart Type.” You will see a series of different chart types.
Step 7.
Select “line chart” Your data series will change from part of the stacked column to a line.
Step 8.
Repeat the cycle by left clicking the “plant 2” data series and then right click to “Change Chart Type” and select line chart.
Step 9.
Right click on one of your percentage thresholds. Select “Low” (< 50%) then select “Format Data Series.”
Step 10.
Move “Gap Width” to “No gap” Then you will see your “bands” created
Customize colors to your liking by right clicking on the bands and labels. Any other feature rich customization can be applied at this time!
