I would like to use excel's conditional formatting (ger.: bedingte Formattierung) tool to color multiple rows and columns, depending on the value entered into a row above, corresponding to each column.

enter image description here

More precisely: the data points in B5:B14 shall be marked red, if the value is larger than the one in B3 (28,5). So far so easy. I go ahead and select all cells (B5:B14), klick the formatting tool, select the rule to mark up cells, and choose greater than. Then I select B3. Now I would like to drag the cells over to apply this to all following columns. Choosing "only take over formatting" it does it, however the reference B3 will still be the same and not chang up to C3, D3,...

Does anybody know how to include changing it up to C3, D3, etc.?

Thank you!

Kind regards, Timon


1 Answer 1


Select Conditional formatting > manage rules (Regeln Verwalten). Find the rule you created and change the "Applies to" reference from $B$4:$B$14 to $B$4:$Z$14 (replace Z with the last column you need).

The formula in your conditional formatting wants to be something like


So that each cell is compared to the value in the same column as itself, but always row 3.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.