Gradient Conditional Formatting In Pivot Tables
Conditional formatting of background colors in pivot tables is one of the best ways to draw attention to numbers, but still provide a clean visual. The use of data bars creates a lot of visual noise, whereas background colors are subtle and act more like a heatmap.
The code below can be added to any pivot widget to conditionally format cells based on their values from a solid color for the max value down to white for the min value.
In order for this code to work, the pivot table must have the "data bars" feature enabled. This conditional formatting simply swaps the data bars for background colors.
Here is the code:
var colors = ['77, 197, 255', '226, 112, 255'];
var startColIdx = 2;
widget.on('ready',function(e,ev) {
for (var i in colors){
var color = colors[i];
$(element).find(".databar[fidx=" + (parseInt(i,10)+startColIdx) + "] .bar-container .bar").each(function(x,e){
$(e).css('background-color', 'rgba(0,0,0,0)');
$(e).closest('.databar').css('background-color','rgba(' + color + ',' + ($(e).width()/$(e).parent().width()) + ')');
});
}
});The code has 2 variables
- The colors array, these are the RGB colors for the max value for each of the value columns
- The startColIdx is the column number that the pivot values start at. If you have 1 "Rows" item, the startColIdx is 2. If you have 2 rows, the startColIdx is 3 and so on.
Below is an image showing the result of applying this code to a pivot widget.
Updated 03-02-2023
