Large, undifferentiated sheets of numbers in Excel tend to be hard-to-follow; to make easier-to-read spreadsheets, format the numbers in meaningful ways!

Of course, Excel offers a variety of tools, such as foreground and background colors, bold, italic, and underlined; but sometimes, you want to make a number stand out depending on what the content of that number is.

To apply custom formatting to a number, you can use the formatting wizard, by selecting Format > Conditional Format and completing the form. This is a good way to learn the syntax for new formats, but can be slow if applying a format you already know. Below are a few common formats to get you started.

Negative Numbers

One common use for conditional formatting is to make negative numbers stand out on even a quick scan. A good way to do this is with the use of color, since most Excel values are plain black text. The general format for negative number formatting is:

postive format ''';''' negative format ''';''' zero ''';''' text

So, if you want to give a negative number a minus sign in front, then you'd use: 0;-0

If you wanted to display dollar values, with cents, you'd use: $0.00;-$#0.00 (Notice how the number of pound signs after the period signifies the exact precision of the number.)

To move that negative sign try: $0.00;$-0.00

I bet you can guess how to apply accounting formatting: $0.00;($-0.00);--

So, color. Color is applied by specifying the name for the color. Names include:

• black
• blue
• green
• cyan
• red
• magenta
• yellow
• white

To make a red negative percentage value, you'd do: 0%;[red]-0%

Easy, huh?

Other kinds of conditional formatting are possible:

Keeping or Dropping Signifigcant Digits

There are two number formats:

• 0 always displays the digit, even if it's insignificant (a zero)
• # only displays the digit if it's not a zero

So if we have 47.502 then #.## will display 47.5, while 0.00 will display 47.50.

Excel will always display as many digits as necessary to the left of the decimal point, but will attend to your specified precision to the right of the decimal point.

Including Commas

Simple -- put the commas where you'd want them:

###,###.##

Fractions!

Yes, it's possible: # ##/## gives 4 3/16.

Text labels in the number

Want to call out "Profit!" and "Loss :("? Then try: $0.00" Profit!";-$0.00" Loss :("

Custom Conditions

Yes, you're not just stuck with the positive;negative;zero;text conditions specified as default. Just show a comparison operator to add a custom condition:

$0.00;[red]-$0.00;$0.00" woohoo!"[>1000000]