The last error amount option the More Options… window is Custom.
We’ll use this to create dynamic error bars for sales projection data.
Open the second sheet of the example workbook.
Click the plus button and select Error Bars > Custom > Specify Values to bring up a new window.
You can now specify different positive and negative error values.
So how can we use this to create error bars that Excel automatically updates?
By entering a cell reference.
Now, Excel looks for any changes to that cell. 👀
And if it sees that you’ve made a change, it updates the error bars.
I’ve included a table of possible sales fluctuations that we’ll use to create these error bars.
Let’s assume you want to build error bars to show what happens if you sign a large new client or lose your current largest client.
To do so, update the cell reference in the Positive and Negative Error Value fields:
Now change the values in those cells and watch the error bars update automatically!
You can also link the positive and negative error bars to the same cell.
Try linking them to the Market Fluctuations cell. Then change the value to see what happens.