Graded color scale to ignore 0's in Excel

1
votes

I'm trying to use the graded color scale in a pivot table in excel, but I'd like to ignore 0's from the scale. I can easily mask the values with an additional rule, but this doesn't remove them from the scale.

I've tried setting the minimum value to the minimum I'd like to use, but it doesn't seem to register. I've searched all morning and found plenty of resources for ignoring 0's in other forms of conditional formatting, but not for the graded color scale.

1
excelconditional-formatting

1 Answers

1
votes

You are probably really close to solving it by having two conditional formatting rules apply to the same range.

Create an array formula somewhere else in sheet to find:

(1) The minimum > 0, and;

(2) The median if excluding 0 (assuming you are using a 3-color scale).

My data is in column F.

    min formula: =MIN(IF($F$2:$F$22<>0,$F$2:$F$22,FALSE))

    median formula: =MEDIAN(IF($F$2:$F$22 <> 0, F2:F22,FALSE))

Press CTRL+SHIFT+ENTER to make them array formulas.

Then, edit your conditional format rules as follows:

1: Set the color scale:

set color scale

  1. Gray cells = zero:

ignore 0 value

  1. Arrange format rules and stop at true for the ignore formula.

arrange rules

And voila, column F looks correct.

enter image description here