Invert text color when over dark highlight

[cglowe]

Is it possible to have color text invert based on cell highlight color? We have conditional formatting on some cells that create a rather dark highlight and makes the default dark text unreadable. We would like the text color to invert based on cell background color.

We see the same issue on some bar/column charts where the text is over a bar with a dark color and can't be read. Is there not a system setting or anything that will automatically invert colors?

[DRay]

Hi @cglowe,

Great question! I don't know of a way to do it with the UI, but should be able to do this with a widget script. 

This guide includes examples of changing cell styles based on values, which can be adapted to invert text color based on the cell's background color. If you need further assistance or a walkthrough, feel free to let me know!

Usage PivotAPI to Beautify Data On Pivot.

[Jake_Raz · Accepted solution]

I recently had to do this! After a lot of referencing the HighChart API and chatting with various AI's, I found a few things that worked, at least for us. Here's a script you could use for a Bar Chart:

// This script allows you to change the formatting of the data labels in a chart

widget.on('render', function(sender, se) {

    var series = sender.queryResult.series[0];  // if you have multiple series/values, change this number to specify which one to apply the below formatting to (starting from 0)
	series.dataLabels = series.dataLabels || {};
	series.dataLabels.allowOverlap = false;  // set to true to allow labels to overlap each other; will make more display but might be a mess
	
	// Text Highlighting
	series.dataLabels.style = {
        color: series.color,  // the color of the data label text; can set manually with a hex value like '#FF0000', or use series.color to have it inherit the color you set in the left-hand pane for the values
		textOutline: '2px contrast'  // adds an outline: first value sets the width, second sets the color; default is 'contrast' which automatically chooses for the most contrast; can set manually with a hex value or disabled by setting it to 'none'
    };	
	
	// Background Color
    series.dataLabels.backgroundColor = "rgba(255, 255, 255, 0.7)";   // sets background color using rgb values, plus the last number can be set from 0 (fully transparent) to 1 (fully opaque)
    series.dataLabels.borderWidth = 0;   // width of the border around the background, set to 0 to turn off
    series.dataLabels.borderColor = '#000000';   // sets the color of the border around the background, use hex values like '#FF0000'
	series.dataLabels.borderRadius = 5;   // controls how curved the borders are, set to 0 for square corners
	
	// Position & Alignment
	series.dataLabels.inside = true;   // set to true if you want the labels inside the bar
	series.dataLabels.align = 'right';  // can be 'left', 'right', or 'center'
	series.dataLabels.y = 0;  // optional: adjust the y position offset of the labels, in pixels; positive is up, negative is down
	series.dataLabels.x = 0;  // optional: adjust the x position offset of the labels, in pixels; positive is right, negative is left
	
});

This should also work on column charts, and I'm pretty sure with line charts as well. I've used it in combo charts (i.e. a line chart but with a second series formatted as a bar chart). Can't promise it'll work with other types of charts that have data labels, like area or pie charts; I haven't tested those.

This is assuming you just have one item under the "values" section, in the widget set up. If you have multiple values then I think it'll only apply to the formatting to the first one. If you're trying to format multiple different value series, I think you could just copy/paste the script again and change the "series[0]" bit to "series[1]", "series[2]", and so on, though that's a bit clumsy. There's a more elegant way to do it within the same script and to have it defined by the series name instead of it's index position, but I'd have to dig back through my AI chat logs to find that 😛 

Here's some examples with a bar chart:

Before:

After (using everything in that script I posted):

Here's an example with just the outline (no background) and default alignment:

I think there's more properties you could add to the script, if there's more text formatting you want to do. It should be possible to make the text bold or change its size. I'd recommend checking out the Highcharts API reference to see what other properties you can set (scroll the lefthand sidebar down to see the "dataLabels" section). I found the "try it!" demos to be really useful for testing out different options.

Hope this helps!

[DRay]

Hello @cglowe,

I’m following up to see if the solution offered by @Jake_Raz worked for you.

If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.