How to change the cell colors of a column according to the cell's value distance to the column average?

Question

I am building a spreadsheet with a column with values that I would like to create a more visual way to identify discrepancies. So I would like to change the colors of the cells in a column according to the distance from the cell value to the column average.

For example, in this column I have the following values:

• 8
• 14
• 1
• 12
• 6
• 19

Their average is 10, I would like the cells to automatically have the colors:

• 8 (lighter red)
• 14 (green)
• 1 (darker red)
• 12 (lighter green)
• 6 (red)
• 19 (darker green)

I thought about averaging this column in a cell and putting color conditional formatting for values larger and smaller than this column mean. But in this case, my cells would have only two colors: red for below-average values and green for above-average values. Is there any way to change the color of these columns according to the discrepancy with the average of that column? Thanks in advance.

Answer 1

in Google Sheets it's like this:

Answer 2

Assuming you have excel 2016 or newer, simply highlight the data you want to have included, and then click Conditional Formatting (from the "Home" ribbon), then click "Color Scales" and pick the desired one.

After setting the color scale, you can then modify it by selecting the same data, and then clicking "modify rules". What you've stated is a 2-color scale, and that is one of the options you can choose if you go back and edit the rules.

Answer 3

You can use Color Scales in Conditional Formatting for this: Select a New Rule With your Desired colors for Minimum & Maximum Value

Result:

Same Goes if you are using Google Sheets. Goto Format>Conditional Formatting> Color Scales