Excel color scales are not linear and inconsistent

Question

I set up a status tracker for reporting progress and noticed two things:

1. The colours are not linearly dependent on the cell values
2. The colours are not consistently determined by value in different sets of cells.

Here is a screenshot to illustrate 3 experiments I did:

Experiment 1 (see the 6 cells in the top left):

• If I select these cells and set a color scale, everything looks fine.

Experiment 2 (see the 6 cells in the top right):

• If I select these cells and set a color scale, the scale is not linear—the value 3% is closer to 100% than to 0%. I expect 3% to be a dark orange similar to that in experiment 1.

Experiment 3 (see the 12 cells in the bottom):

• If I select all these cells and set a color scale I get these results. Now the color of 3% is consistent among the selection, but it is still not linearly dependent on the cell value.

(Note, the problem is not related to the fact that these are percentage numbers. It is the same with any numbers).

My question is:

• What determines the color value?
• How do I set up a color scale that is linearly dependent on the values in the cells, and consistent in all places?

Answer 1

Hi

My name is André. I am an independent consultant.

This formatting is conditioned to these rules found here in this menu.

Where you have some more tweaking options.

To get here, just click on CONDITIONAL FORMATTING MANAGE RULES and then double-click on your Rule.

I believe that by adjusting some parameters, you can achieve your result.

Answer here so I can continue helping you.

André.

Answer 2

Thank you. This explains the problem. The way to get a consistent, linear color scale is to manually set the minimum, maximum and midpoint values. E.g. to 0, 1, and 0.5 for percentages.