Thread: Gradient Colored bar Graph

I am trying to create a bar graph in excel that has a gradient color
scheme that goes from yellow to red as the predefined severity of a
risk increases from low to high. The count of each value that is being
risk ranked should also be displayed on the graph.

Count Risk (1 = L, 6 = H)
Type A 2 1
Type B 2 2
Type C 4 3
Type D 5 4
Type E 1 5
Type F 7 6

Bsically, I'm looking for one horizontal bar graph to look as follows:
L M H
2 2 4 5 1 7

Any suggestions?

If I understand you correctly you want (1) ONE bar, gradually changing colour
from red to yellow, and (2) along its length the frequency count.
(1) is standard available in Excel. Make the bar, rightclick it as and
choose Format Data Series/Patterns/Area-Fill Effects. Choose Two Colors in
the Gradient pane, and select the apropriate direction (shading style) for yr
gradient.
(2) requires a lot of work around, and is pretty complicated. It involves
making a dummy series. Add this series to the bar chart, temporarily showing
as bars of another colour. Next change this new series to XY scatter y get a
second x-axis and a second y-axis. (It may also work as stacked bar, but
this is even more complicated I fear). It needs some compatibilisation to
get the axes in line. Make them invisible by choosing transparancy (or
"white" if that's yr chart colour) to hide the axess you don't want to show.
Hide the added data points likewise, and add the data labels to them (your
frequency count, formatted as text).
For H-M-L you need to add another dummy series, and repeat the formatting as
described above.
I hope you can make sense out all of this.

"Uninvisible" wrote:

>
> I am trying to create a bar graph in excel that has a gradient color
> scheme that goes from yellow to red as the predefined severity of a
> risk increases from low to high. The count of each value that is being
> risk ranked should also be displayed on the graph.
>
> Count Risk (1 = L, 6 = H)
> Type A 2 1
> Type B 2 2
> Type C 4 3
> Type D 5 4
> Type E 1 5
> Type F 7 6
>
> Bsically, I'm looking for one horizontal bar graph to look as follows:
> L M H
> 2 2 4 5 1 7
>
> Any suggestions?
>
>

Here is another approach.

Assuming you data is in the range A1:B7
B1: ="Risk"
A2: =2 B2: =1
A3: =2 B3: =1
A4: =4 B4: =1
A5: =5 B5: =1
A6: =1 B6: =1
A7: =27 B7: =1

Create a line chart on the range A1:B7

Format the plot area to use a special fill effect of 2 colours, Yellow
and Red with a vertical fill. This will give you the gradient colour fill.

Format the line series to have no line or markers. Add data labels
displaying category labels.

Set the minimum Y axis value to 0 and the maximum to 2. This will place
the counts in the center of the bar vertically.

Format the x axis, using the scale tab, and uncheck Crosses between
categories. This will move the first and last value to the edge of the bar.

Set both x and y axis to not plot border or tick marks and tick labels.

If you need to label the bottom of the chart with Low and High use the X
and Y titles. Position this as required.

Cheers
Andy


Uninvisible wrote:
> I am trying to create a bar graph in excel that has a gradient color
> scheme that goes from yellow to red as the predefined severity of a
> risk increases from low to high. The count of each value that is being
> risk ranked should also be displayed on the graph.
>
> Count Risk (1 = L, 6 = H)
> Type A 2 1
> Type B 2 2
> Type C 4 3
> Type D 5 4
> Type E 1 5
> Type F 7 6
>
> Bsically, I'm looking for one horizontal bar graph to look as follows:
> L M H
> 2 2 4 5 1 7
>
> Any suggestions?
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I have a macro that does this for Scatter charts. That is, it plots the X,Y values as normal and colours the points or line by a 3rd value.

I'm not familiar with bar charts, but guess that it can be modified for them.

Mike