ARGB vs. ColorMix for Export Transparency

 

I'm a fan of using colors consistently in my applications. If blue represents a particular data point in one chart, I like to use that same blue to represent that same data point in other charts, particularly on the same sheet. But colors need to be treated differently when they are used in graphs versus tables. Graphs can require deeper saturated colors to look good.

 

But using those same saturated colors in tables often results in text that is hard to read:

 

My go-to tool to make text more readable in these tables used to be QlikView's ARGB function. This function is similar to RGB, except that you can also include an "alpha" value that represents how opaque a color should be. Here's what the above table looks like with the same RGB color, but at 50% alpha:

 

Not too shabby. But ARGB has one critical shortcoming. When exporting an ARGB color from QlikView to Excel, the "A" part gets dropped, leaving just the RGB at full opacity:

 

A slick workaround to make colors less saturated in both QlikView and Excel is the ColorMix1 function. This function uses a sliding scale to return a color somewhere between two specified colors. It's a highly useful function for creating heatmaps, and that has been its typical and traditional use. However, this function can also be leveraged to "wash out" colors (i.e. make them more transparent) by mixing in white with another arbitrary color. Here's the above chart with ColorMix1, using 20% as the first parameter, white as the first color, and each dimension's regular RGB color as the second color:

 

Most importantly, this color is preserved on export, because it's not actually transparent, it's just diluted with white:

 

Feel free to download the following QVW to play with all of the above functions on your own. And please let me know in the comments if the above tip helped you!