Tom’s Tutorials For Excel: Formatting Numbers and Text With Custom Colors
Using the familiar Format Cells dialog box and your imagination, you can totally bypass Conditional Formatting to custom-color your numbers and text as you like.
In the above picture, I have formatted positive numbers to be colored orange; negative numbers to be colored pink; a zero to be colored gray; and text to be colored brown.
As you may know, when you custom format a cell’s Number property, there are four possible entry types, separated by semicolons, in this syntax:
PostiveNumber;NegativeNumber;Zero;Text
This is why you usually see three semicolons (separating the four Number components) in custom formatting scenarios. In the next picture, the Format Cells dialog box was called by first selecting the cell(s) you want to format, and then pressing Alt+O+E
Next, click the Number tab, select Custom in the Category pane, and in the Type field enter your desired custom format. The one I entered is
[Color45][>0]#,##0;[Color7][<0]-#,##0;[Color48]0;[Color53]@
Notice I used color index numbers (45, 38, 15, and 53)instead of color names. The reason is, I wanted to demonstrate how to implement custom formats with more flexibility for selectable colors than Excel's 8 basic colors of blue, black, white, cyan, red, green, magenta, and yellow. Had I used those basic colors for example, the colors would be specified by name not index number, and the custom format would have looked different:
[Green][ > 0]#,##0;[Magenta][ < 0]-#,##0;[Yellow]0;[Blue]@
Leave a Reply