Set Conditional Coloring on Data Bars in Pivot table
Introduction
The following article describes the steps needed to set conditional colors in the bars in pivot table.
For example red bars for negative values and green bars for positive.
Implementation Steps
-
Create a pivot chart with at least 1 value
-
Enable data bars for 1 or more values
-
Edit the widget’s script, and paste in the below JavaScript code
widget.on('ready', function(sender, ev){
//get an array of bar elements
var AllCells = $('.databar');
//find how many bars exist in total including invisible
var L = AllCells.length;
//init variables
var MyCell;
var MyBar;
var MyCellVal;
//loop through each bar
for (i=0; i<L; i++)
{
//look at a specific bar
MyCell = AllCells[i];
//get the value
MyCellVal =$(MyCell).attr('val');
MyBar = $(MyCell).find('.bar');
switch(true) {
case (MyCellVal<10000):
$(MyBar).css('background-color','#01DF3A');
break;
case (MyCellVal==10000):
$(MyBar).css('background-color','#FFBF00');
break;
case (MyCellVal>10000):
$(MyBar).css('background-color','#FE642E');
break;
default:
break;
}
}
})
-
Edit the desired numeric ranges in the script, and HTML color codes
-
Save the script, refresh the widget and click apply
-
Hi, I am having problem keeping data bars in a pivot where the colors are calculated in a script, maintain the colors when the dashboard is refreshed. At times, colors revert to the default blue color, and at times all bars are displayed as the first color appearing in the script. It almost seems like at times either part or all of the widget script is not executed. Any ideas?
Thanks, Rachel
Please sign in to leave a comment.
Comments
1 comment