Whisker Chart w. Tails

[Number7stunner]

Hello,

I was checking to see if someone knew how to make a type of whisker chart, horizontally, and with a tail to it. I have an example chart attached. Disregard the colors and stuff.

I have data. I have the max and min, the current data point, and a prior data point. So with that I want to see where we are now relative to where we came from, within a stated range. Here's example data.

Current: 2..1
Max: 2.8
Min: -3.0
Prior Data point: 1.5

Example.png

[MrShorty]

I do not understand how your data relates to the chart in your picture, so I don't think I understand all of what you are after. Are you talking about the red circle data series with the "horizontal error bars" in your picture? If those are the "whiskers" you are referring to, I use error bars to add that sort of thing to a chart https://support.office.com/en-us/art...5-864049a145f0

[Number7stunner]

Thanks for the reply. The red dot is the current reading (color coded based on the side of the black vertical line). The line coming from the red dot is where the prior data point is coming from.

[Number7stunner]

Im still having trouble. I want to make a chart using 4 data points. The current reading. Lets call that 2.0. Last month the reading was 1.0. So I want the current reading to be a dot, with a tail from the prior reading. I want the range to be -2 and +2. I want it to be horizontally displayed. I know it has something to do with error charts, but cant seem to get it.

[MrShorty]

If you have not already, I would add a cell/row/column to compute the difference between current and prior, then add horizontal error bars to the series, using this helper cell/column/row as the values for those error bars.

[MrShorty]

I am not sure what you need help with. Before, I had assumed that you already had the scatter chart plotted, and you just needed help adding error bars to the scatter chart data. Was I correct before, or do you really need help with the whole chart creation? Starting from scratch (and disregarding the background colors), here's how I would build a simple scatter chart with horizontal error bars:

1) Enter data into spreadsheet. Probably arranged something like:

previous (x) -- current (x) -- y value -- difference
1.5 -- 2.1 -- ?? -- =A2-B2
next entries copy D2 down

2) Insert chart
2a) Select current (x) and yvalue columns
2b) Insert -> Charts -> XY scatter chart -> markers without lines
2c) Hopefully, excel will correctly see that the data is arranged in rows. If not, Clock "switch row/column". If Excel completely fails to see the x and y columns correctly, then enter the source data dialog and fix the data series.
3) Add error bars (see help file in post #2)
3a) Select data series
3b) Layout->Error bars
3c) delete the vertical error bars
3d) Format horizontal error bars -> specify value -> use column D for the positive error bar values.

Where do you get stuck?

[Number7stunner]

I got it! Thanks. I have a follow up. If I wanted to then add shaded areas. Like -2 to -3 was bright green, and 2-3 was dark red. What would I do there? Here's what I got so far!

[MrShorty]

This tutorial shows how to add a shaded background to an XY scatter chart using a combination XY scatter + stacked area: http://peltiertech.com/shaded-quadra...scatter-chart/
This one uses a stacked bar chart to add vertical bands: http://peltiertech.com/Excel/Charts/...ticalBand.html

I would recommend trying either of those.