Forum Discussion

PeterVelschow's avatar
PeterVelschow
Copper Contributor
Oct 31, 2020
Solved

Conditional number color format

#Office365 #Office2016 (If conditional formats)   Hi All. Is there a way that I can make a returned sum of numbers a specific color? I would like negative numbers to be red, positive numbers green ...
excel
Formulas and Functions
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Oct 31, 2020

    Hi PeterVelschow 

     

    The simplest and most flexible approach is to use Conditional Formatting

     

    Just apply 3 rules

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 31, 2020

PeterVelschow Riny_van_Eekelen Wyn Hopkins 

Two good solutions but which is the better?

1. Conditional formatting has a proper user interface supporting the definition of complex formatting rules.  On the other hand, there is a maintenance burden as drag-and-drop gradually scrambles the region to which the  formatting is applied (I wonder why CF does not support defined ranges properly?).

2. Number formatting is an arcane skill conducted requiring obscure codes to be applied within a user-hostile development context.  That said, the functionality is an exact match for the OP's stated requirement.  Number formatting can also be introduced as part of an applied Style which, at least,, make its definition a one-off task (I wonder what proportion of users employ Styles routinely?).

 

The only change I would make to Riny's solution is to use [Color10] rather than [Green] if it is to be viewed against a white background.

Resources