Threshold Chart in Excel

The reason why I call this as a Threshold Chart is because the lower point can only be equal to the threshold and not exceed it!

Although I am using Asset Depreciation Data for this chart but a threshold chart can be applied to multiple places. I’ll talk about its applications in a bit more detail in a while… but first the chart

 

The Charting Idea..

The idea is to focus on the difference between the threshold and current level.

 

Lets assume this data!

FYI :

1. Assume the Asset Value is the price of the asset at which it was purchased.
2. Depreciation is the normal wear and tear of the asset during the course of its use!

 

In this case our chart will be like this

When the blue dot touches the ground (x axis) the asset is fully depreciated!

 

We also need to calculate the Net Asset Balance = Asset Value – Depreciation

 

Now let’s make the Chart!

Note that I have

1. Chosen the chart type as Line Chart with Markers
2. The Blue Line denotes the Asset Value
3. The Orange Line denotes the Net Asset Value

 

The Next Step is to remove the Line by choosing no outline on each of the data series

 

Add the Error Bars for the connectors between the 2 dots

1. Select the Bot Dot
2. Go to the Plus sign on the side of the chart
3. Check the Error Bars Option
4. Although we have got the error bars but they are no where close to the way we want them

 

So let’s Format the Error Bars

1. Select the error bars and press Ctrl+1 to format it
2. In the formatting dialogue box change the direction to Minus and set the style as No Caps
3. Now specify a Custom height of the error by clicking Custom and Specify the Value
4. Link the negative error values to the Depreciation data from the spreadsheet

With a bit of formatting (Applying Legends, Title, Data Labels, Removing the Axis etc..) our chart is done!

 

Applications of this Chart

1. Chart for Training / Department Budget Balance : The longer the connector the more the budget balance

 

2. Occupancy of an event in different Cities : In this case the longer the connector the more seats are vacant

 

Caution for Threshold Charts..

1. This Chart will become difficult to read where the values can be more than the threshold
2. This is particular good where the values will be less or equal to the threshold

 

Other Interesting Chart Types

1. Highlight any Bar in a Column Chart with a Drop Down
2. Use Drop Downs to highlight Line in a Line Chart
3. Timeline Chart in Excel
4. Dot Chart in Excel
5. Men & Women – Funnel Chart Visualization

 

Let me know your thoughts and how will you use the threshold chart in your data!