Histogram with vertically divided zones

[ali_excelforum]

Hi,

I am trying to add some background color to a histogram plot to split it up into "Pass", "Minor", and "Fail" zones. You can see what I tried to do in the "Dash" tab in the attached spreadsheet. See also the screenshot. I was able to add the background colors for the time series plot (upper plot), but was not quite able to make it work for the histogram plot (lower plot). It is actually almost there. I can make the colors fill the plot area, but I just cannot figure out how to have the colors be in the background. Currently the colors are in the foreground.


Ali

[markw1974]

Go into format, then chart area. Select gradient fill, choose the appropriate colors and it will fill in the background of your chart.

[Andy Pope]

to your normal column chart add 2 new series
move both to secondary axis and change to stacked area chart.

add secondary horizontal axis and set cross on tickmarks.

format plot area to final band colour.

You just need to make the band values relevant to your data as I used fixed values

[ali_excelforum]

markw1974,

Thanks, but that is not the effect I am going for. I want the background for just the plot area (as opposed to the whole chart area) to be divided into vertical bands of different colors representing "Pass", "Minor", and "Fail" zones.



Andy,

That is close, but I want those horizontal bands in your sample to be vertical instead like the histogram in the "Dash" tab. Only I want them to be in the background rather than the foreground as they currently are. I already know how to make the bands fill the plot area. It is just that they are in the foreground so they cover up the histogram instead of remain in the background hence why I left them as not filling the plot area.

[Andy Pope]

This uses 3 series on the secondary axis plotted as area charts.
The secondary horizontal axis uses date format in order to get the accuracy.
Therefore the data values need to be factored up, in this case by 100.

[ali_excelforum]

Thanks!

I have been playing with it and I am wondering is it possible to make the green region always end at the right end of the plot? For example, if I keep refreshing the calculations (so as to change the random number computations) it is not long until I find that the green region does not go all the way to the end. At first, I thought it was just Excel's animations going slowly, but that did not pan out.

[Andy Pope]

The auto maximum scale must use a value greater than the max of the bin data.

You could simply set the fill colour for the plot area to match with the green

[ali_excelforum]

I tried to create the chart on my own. I cannot get it to work out nor figure out what is wrong. Here is what I did:

1. Create the histogram using D2:D12 as the values and B2:B12 as the category labels.
2. Special paste A15:D22 into the chart using columns as series, first row as series labels, and first column as category labels.
3. Convert each of the three newly added series to be of type stacked area and to be plotted on a secondary axis.
4. Adjusted the secondary horizontal axis to be of date format and have its vertical crosses between dates to match yours.
5. Performed sundry formatting such as colors and axes placement.

Screenshot: http://i.imgur.com/oxdTeVa.jpg
Worksheet: DynamicChartBackgrounds_AliTesting.xlsx