Threshold Chart in Excel 1

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..

Threshold Chart in Excel 2

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

 

Lets assume this data!

Threshold chart in excel 3

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

Threshold Chart in Excel 4

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

Threshold Chart in Excel 5

 

Now let’s make the Chart!

Threshold Chart in Excel 6

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

Threshold Chart in Excel 7

 

Add the Error Bars for the connectors between the 2 dots

Threshold Chart in Excel 8

  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

Threshold Chart in Excel 9

  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

Threshold Chart in Excel 10

 

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

Threshold Chart in Excel 11

 

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

 

DOWNLOAD THE THRESHOLD CHART + APPLICATIONS

 

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!

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI