"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"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...

  1. Select the range of cells you want to apply this format to.
  2. Select Format, Cells, and click the Number tab.
  3. In the Category field, select Custom.
  4. 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.
  5. Click OK.
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.

Want more great Excel tips just like this one?
Subscribe to my FREE
"Spreadsheet Tips From An Excel Addict"
newsletter at the www.TheExcelAddict.com.
Copyright by Francis Hayes (The Excel Addict) All Rights Reserved

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.