"How To Excel" Mini-Tutorials|
"Helping Average Spreadsheet Users Become Local Excel Experts"
Display Values With Dollars And Cents Symbols
There may be times when you want to show a cents symbol (¢) for
values that are less than $1.00 and a dollar symbol($) for values
that are a dollar or more. To do this you need to use a
"conditional number format" (i.e. a number format that depends on
the condition). If the condition is that the value in the cell is
less than a dollar, the cents symbol will be displayed (i.e.
.87¢) otherwise, the dollar symbol will be displayed.
To create this conditional number format...
Caveat: Technically .87¢ is not 87 cents, it's 87 one hundredths
of a cent. So use this with caution. No, you can't get rid of the
decimal - I tried it.
- Select the range of cells you want to apply this format to.
- Select Format, Cells, and click the Number tab.
- In the Category field, select Custom.
- In the Type field, enter [<1].00¢;$0.00_¢
Note: You can copy this format directly from this web page or, if you
enter it yourself, remember that to get the cents symbol you need
to hold down the ALT key and enter 155 (or 0162),then release the ALT key.
- Click OK.