Cellwise Start free

Excel guide · 5 min read

Accounting vs Currency format in Excel: what's the difference?

Quick answerBoth show numbers as money and both keep them as real numbers. Currency puts the symbol right next to the number and lets you choose how negatives look. Accounting lines the symbol up at the left edge of the cell, lines up the decimals, shows zeros as a dash and (in US settings) puts negatives in parentheses. Use Accounting for columns in financial reports, Currency for single prices and invoices.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABC
1LineAmountAs text (TEXT)
2Software1249.5$1,249.50
3Travel386.2$386.20
4Refund-150-$150.00
5Training0$0.00
6Hardware12480$12,480.00
7Supplies74.35$74.35

Download this sheet as an Excel practice file (.xlsx)

What each format looks like

Take the amounts in B2:B7: a refund of -150, a zero for training, and values from 74.35 to 12,480.

  • Currency: $1,249.50 with the symbol right against the digits. Negatives can show as -$150.00, ($150.00) or in red, depending on the option you pick. Zero shows as $0.00.
  • Accounting: the $ sits at the left edge of every cell and the numbers line up on the decimal point at the right. Zero shows as a dash. In US English settings a negative shows as (150.00) in parentheses, with no option to change it in the dialog.
  • Both: the cell still holds the number. Formulas, sorting and SUM work exactly as before, which is the big difference from the TEXT function.

How to apply each one

Comma Style, next to Percent Style in the same group, is the Accounting format without a currency symbol: handy for big tables where a $ on every row is clutter. Increase Decimal and Decrease Decimal change what's shown, never the stored value.

  1. Select the cells.
  2. For Accounting: Home > Number group > Accounting Number Format (the button with the money icon). Click its arrow to pick another symbol such as £ or €, or More Accounting Formats.
  3. For Currency: Home > Number group > the Number Format box (it usually says General) > Currency. Or press Ctrl+Shift+$ (Ctrl+Shift+4 on a US keyboard), which applies Currency with two decimals.
  4. For full control, press Ctrl+1 to open Format Cells, Number tab. Currency lets you set decimal places, the symbol and the negative number style. Accounting only lets you set decimal places and the symbol.

Which to pick

Use Accounting for columns of money in reports, budgets and statements: aligned symbols and decimals make a long column easy to scan, and dashes make zeros stand out from small numbers. Use Currency for a single figure, a price list or an invoice total, where the symbol hugging the number reads naturally, and when you need negatives in red or with a minus sign.

Don't mix them in one column. And whichever you use, the total is the same, because only the display changes.

=SUM(B2:B7)

Result: 14040.05 shows as $14,040.05 with Currency format

14040.05: the total of the six amounts, shown as $14,040.05. The formula doesn't care which money format the cells use.

The TEXT function trap

Column C holds =TEXT(B2,"$#,##0.00"). It looks just like Currency format, but the result is text. Text doesn't add up: SUM skips it, COUNT doesn't count it, and it sorts alphabetically, so $74.35 sorts after $12,480.00.

Use TEXT only when you're building a sentence or a label, like ="Total: "&TEXT(SUM(B2:B7),"$#,##0.00"). For a cell that should look like money, use a number format. If you already have money stored as text, see numbers stored as text: VALUE(C4) turns "-$150.00" back into -150.

=SUM(C2:C7)

Result: 0

0: the SUM of the TEXT copies. The same amounts as numbers in column B total 14,040.05.

Custom formats: the best of both

If neither built-in format is quite right, write your own in Format Cells > Number > Custom. A number format has up to four sections separated by semicolons: positive; negative; zero; text.

#,##0.00;(#,##0.00);"-" shows positives normally, negatives in parentheses and zero as a dash, without a currency symbol. Add [Red] to the second section, like #,##0.00;[Red](#,##0.00);"-", to make negatives red too. The built-in Accounting format in US settings is _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_): the * repeats the space after the $ to fill the cell, which is what pushes the symbol to the left edge, and the _( and _) add a space the width of a parenthesis so positives and negatives line up. A comma at the end of a format scales the display, so $#,##0, shows 12,480,000 as $12,480: see show numbers in thousands and millions.

=TEXT(B4,"#,##0.00;(#,##0.00);""-""")

Result: (150.00)

(150.00): how the refund looks with the custom three-section format. A zero shows as a dash. As a number format the cell stays a number; TEXT is only used here to show the result.

Asked to explain this in an interview? See model answers to 30 Excel interview questions, or take the free Excel practice test.

Common mistakes

Using TEXT to make numbers look like money

Apply Currency or Accounting format instead; TEXT results don't sum, count or sort as numbers.

Looking for the red negatives option under Accounting

Accounting has no negative number choices; use Currency, or a custom format with [Red] in the second section.

Typing the $ sign into the cell yourself

Excel usually recognises $1,250 as a number in US settings, but a stray space or a different symbol can leave text; type the plain number and format it.

Mixing Accounting and Currency in one column

Pick one per column so the symbols and decimals line up.

FAQ

What is the difference between Currency and Accounting format in Excel?

Currency puts the symbol next to the number and offers several ways to show negatives. Accounting aligns the symbols at the left of the cell and the decimal points, shows zeros as dashes and uses one fixed negative style.

What is the shortcut for Currency format in Excel?

Ctrl+Shift+$ applies Currency format with two decimal places. There's no Ctrl shortcut for Accounting; use the Accounting Number Format button on the Home tab.

Why does Accounting format show a dash instead of 0?

The zero section of the Accounting format displays a dash, which makes true zeros easy to tell apart from small amounts. The cell still contains 0, and formulas treat it as 0.

How do I show negative numbers in red with Accounting format?

Use a custom format: open Format Cells > Custom, take the Accounting code and add [Red] at the start of the second section, after the first semicolon.

Does currency formatting change the value in the cell?

No. Number formats only change how the value is displayed. Decimals you hide are still there and still used in calculations, so round with ROUND if you need the stored value to match.

Functions used

Related guides

Updated