Conditional formatting in charts
Add Conditional formatting in charts to control the colour of e.g. a bar or line, depending on the value or content of the data labels. Green if appels, Yellow if bananas and so on.
Eric Kinser commented
I'm new to this particular forum. This is a great idea - I even cover this concept in a VBA course I teach. I have a small file that works out some of the logic for a simple version of conditionally formatting a column chart based on both highest/lowest values and values above or below a user defined value.
Folks, there's nothing happening in Excel Chart development. It appears that Power BI visuals will be the new charts in Excel.
+1 @Carl - using a separate data range to drive color indicators, absolutely should be part of this solution. Conditional formatting just based on the values won't always solve the problem, sometimes the coloring needs to be based on a different set of data (including non-numeric)
Although there are workarounds (stacked bars if only one data element is being shown), conditional formatting based on user formulas would be awesome.
If those formulas that drive the conditional graph formatting could include UDFs, that would be even better.
I'm also wondering if exposing this property in VBA would be helpful; so if you needed to change the default color (for example, low scores were red, now the boss wants them all changed to yellow across all graphs and all reports) to be able to change that with just a snippet of VBA would be nice.
Karen Price commented
I create lots of charts of survey data, ie, here is your current patient satisfaction ratings by medical office, so they are percentage scores. We then do a significance test calculation in another column to test the score for each medical office to the regional average. Based on the value of that significance test, I will shade the bar green if the score is significantly higher than average (a ttest value >=1.96), red if the score is significantly lower than average (a ttest value <=-1.96), or the default bar color (usually a light grey) if the ttest value is within that range. Right now I create a chart with a green series, a red series, and a grey series with formulas to populate the scores in the correct series and a 100% overlap. It works, but having some chart functionality to base the bar color off of a formula or off of the value of another series would be helpful.
Carl Kirstein commented
you could also allow chart element values to be defined by ranges... for instance marker size =Sheet1!A1:A21
Yes! A simple scenario that I encountered recently was the desire to create different point colors in a Scatter Plot based on deviation from a specified value. My bootleg solution was to create multiple data sets, but I'd love to be able to do this at the chart format level instead of cluttering my worksheet with many additional columns.
I agree with the above comment that both a "Value" and a "Category" condition would be helpful.
Jon Wittwer commented
If the only type of conditional formatting is a gradient based on the series value, then color will just be a redundant dimension (because the relative sizes of bars or positions of data points already show the value).
However, if you can create a rule set that uses a formula where references are relative to the series value range, then you can do some powerful things, limited only by what you can do with a formula.
Consider the following series for a column chart:
The following might be a rule to color the bars of the chart a certain color if the category name contains the letter "g"
The following rule might be used to color the bars red if the value in column C is "high" (for example, if column C represented a priority level)
An idea for the GUI: In the Conditional Formatting Rules Manager, the "Show formatting fules for:" field could include a list of the valid chart series in the worksheet such as "Chart 2:Series1" ... which would allow you to easily add and edit multiple rules for that series.
I think there are two very different but both very useful things being discussed here -
1. Stylesheet based on a defined name (eg set bar colour = blue where series name = "Microsoft")
2. Conditional formatting based on defined ranges of values (eg set bar colour = red where value <100%)
Love the sunburst chart! Now values are used to define the radius of the area, but if value is 0 this field doesn't exist in the chart. For specific cases a quick insight in values per sunbeam, colouring would be helpful. In this case equally devided sunbeams (per node in hierarchy) is best. The values can be used for colouring (conditional format or predefined). For instance a chart for risks. If in a specific area no risks are defined (value=0) that part will be green, if the value of risks in an area are high (value=10), that part will be red. Because this will replace the colouring by first node hierarchy name as it is now, use of linetypes can help. For example first node can have a solid line that goes from center till end of total sunburst. lines between 2nd nodes can start in 2nd ring till end of total sunbust and can be dotted etc. Instead of solid and dotted lines, you can also think of difference in thickness of lines or range of color for lines. Using this type of layout also makes it possible to use layout not only by 1st node name but all nodes in hierarchy and still be intuitive.
David Robinson commented
Yes, useful to allow data points to have differetn proporties based on EITHER their value OR their category.
e.g. (value) bar height < 4 red, < 7 amber, else green
e.g. (category) all bars blue except "Company total", which is red.
Both of these can be done by having extra data series that get overlaid, but it's a bit of a hassle.
a scale of colors between 2 points.
Point 1 green cell green point on chart
point 2 red cell red point on chart
And a scale of colors between the 2 points, between green and red.
i do it in VBA but it's harder.
Dan Golding commented
Adding to this, it would be great to be able to apply a 'color map' to a chart (for example what is being done here but without the VBA: http://stackoverflow.com/questions/35624585/colouring-the-points-of-a-scatter-plot-based-on-continuous-sequential-data)
Gail Watson commented
At present, I can't even select a specific line in the plot area of a chart and turn it a different color to show the boundary between 2 categories of data. I manually overlay a separate line! Conditional formatting would be fantastic in charts.
This is related to what I posted here - I suggested a different solution though anything to address this type of problem would help.
Yes this if the value of the bars is over a certain value it is green and the opposite. But also if the text label for the bar is "USA" then the bar is blue and if it is “Denmark” the bar is red.