Microsoft Office Tutorials and References
In Depth Information
Formatting numbers as currency is a common need in Excel. The Format
Cells dialog box or the Currency Style button on the Number Formatting
options on the Home tab of the Ribbon are the usual places to go to format
cells as currency. Excel also has the DOLLAR function. On the surface,
DOLLAR seems to do the same thing as the similar currency formatting
options but has some key differences:
✓ DOLLARconvertsanumbertotext. Therefore, you cannot perform
math on a DOLLAR value. For example, a series of DOLLAR amounts
cannot be summed into a total.
✓ DOLLAR displays a value from another cell. As its first argument,
DOLLAR takes a cell address or a number entered directly into the
function. DOLLAR is handy when you want to preserve the original cell’s
formatting. In other words, you may need to present a value as currency
in one location but also to let the number display in its original format in
another location. DOLLAR lets you take the original number and present it
as currency in another cell — the one you place the DOLLAR function in.
✓ DOLLAR includes a rounding feature. DOLLAR has a bit more muscle
than the currency style. DOLLAR takes a second argument that specifies
how many decimal places to display. When negative values are entered
for the second argument, this serves to apply rounding to the digits on
the left side of the decimal.
Figure 16-4 shows how the DOLLAR function can display various numeric
values just the way you want. At the bottom of the worksheet is an area of
detailed revenues. At the top is a summary that uses DOLLAR.
Unless a cell has been formatted otherwise, you can tell the type of entry by
alignment. Text aligns to the left; numbers, to the right.
Specifically, the cells in the range C5:D7 use the DOLLAR function to present
values from the detail area and also round them down to no decimals. For
example, cell C5 contains =DOLLAR(G15,0). The grand total in cell C9 takes
advantage of DOLLAR to round to the nearest 1,000. In this case the second
argument is set to –3, like this: =DOLLAR(G25+G30,-3). Here are examples
of how the rounding feature works: