Accounting number format in Excel: make money look like money

A column of plain numbers such as 15819 and -6354.91 is hard to read and easy to misread. Accountants expect money to look a certain way: dollar signs, a comma every three digits, two decimal places, and negative amounts in parentheses. This page explains the number formats the spreadsheet offers, when to use Currency and when to use Accounting, how to show negatives and percentages, and the small formatting mistakes that make a report look careless.

What a number format does (and what it does not do)

A number format is a set of display rules attached to a cell. It decides whether the cell shows a dollar sign, how many decimal places appear, whether thousands are separated with commas, and how negative numbers look. It does not change the number stored in the cell.

That second point matters. If a cell holds 0.96800 and you format it as a percentage with one decimal, it shows 1.0%, but every formula that refers to it still uses 0.968. If a cell holds 6354.91 and you format it with no decimals, it shows 6,355, but a total still adds 6354.91. Formatting is about how a figure reads, not about its value. When you need the stored value itself to change, for example to whole cents, you round it with a function. The page on rounding to the cent in Excel covers that side of the story.

The formats you will use most

The number format list in the toolbar offers a handful of choices. For accounting work, five of them do almost everything.

The last row is a reminder that Percentage expects a fraction. A cell that holds 0.125 shows 12.5%. If you type 12.5 and format it as a percentage, it shows 1250.0%, because the format multiplies by 100 for display.

Number

Number fixes the decimal places and usually adds a thousands separator, the comma between every group of three digits. It has no currency symbol. It suits quantities, headcounts and whole-dollar tables where a dollar sign on every line would be clutter. It can also show negatives in parentheses, which is common in budget reports.

Currency

Currency is Number plus a dollar sign placed right against the digits. Because the sign sits next to the number, the signs in a column do not line up: a short amount has its sign further right than a long one. Negatives usually show with a minus sign in front.

Accounting

Accounting is the format most financial statements use. It differs from Currency in three ways:

• Dollar signs line up. The sign is pinned to the left edge of the cell and the number to the right edge, so every sign in the column sits in the same place.
• Negatives go in parentheses. -1,234.50 appears as (1,234.50). The parentheses are hard to miss, while a small minus sign is easy to overlook in a long column.
• Zero shows as a dash. A cell holding exactly 0 shows a dash instead of 0.00, which makes empty lines easy to spot.

Percentage

Percentage multiplies the stored fraction by 100 and adds a % sign. For ratios like a variance percentage or a margin, one decimal place is a common choice: it is precise enough to tell 0.6% from 1.4%, without the noise of four decimals.

How to apply a format

The steps are the same in our practice grid and in the desktop spreadsheet:

1. Select every cell that should share the format, including the total row. Formatting applies to the whole selection at once.
2. Pick the format from the number format list: Currency, Accounting, Number or Percentage.
3. Use the increase decimals and decrease decimals buttons until the cells show the number of decimal places you want: two for money, none for whole dollars, one for percentages.
4. For negatives in parentheses with the Number format, use the negative-number option, the button that shows (1). Accounting already uses parentheses.

Worked example: a cash summary in Accounting format

Summit Bike Repair keeps a simple cash summary for March. Money coming in is positive and money going out is negative. The amounts arrive in General format in B5:B12, and the net change in cash goes in B13 with =SUM(B5:B12). Because the outflows are already negative, a plain sum gives the net change; there is no need to subtract anything.

Check the total by hand. Money in is 15,819.00 + 14,524.58 + 7,032.44 = 37,376.02. Money out is 6,354.91 + 5,472.75 + 6,299.69 + 7,665.94 + 6,071.68 = 31,864.97. The net change is 37,376.02 - 31,864.97 = 5,511.05.

Now compare the three columns. The General column is hard to scan: 15819 has no decimals while its neighbours have two, and nothing lines up. The Currency column reads as money, but the minus signs are small and the dollar signs drift left and right with the length of each number. The Accounting column lines every dollar sign up at the left of the cell, puts each outflow in parentheses so it stands out, and keeps the decimal points in a straight line. That is why Accounting is the format to choose for a cash summary or any report that mixes money in and money out.

The stored value in B13 is 5511.05 in all three versions; only its appearance changes.

A second example: budget against actual

Budget reports often use whole dollars with no symbol, and a percentage for the variance. Here are four lines from a budget report, where the variance is =C5-B5 and the variance percentage is =D5/B5, filled down. Amounts use Number with no decimals and negatives in parentheses; percentages use Percentage with one decimal.

With no decimals, the percentages would read -17%, 1%, 12% and 13%. Rent and Utilities would look almost the same, and the cost of goods sold line, really 0.968%, would round up to a flat 1%. One decimal keeps those differences visible.

Keep formats consistent

A report looks trustworthy when every number of the same kind looks the same:

• Format totals like their amounts. A total in General under a column in Accounting is the most common slip, and it is the first thing a reviewer notices.
• Pick one format per column. Don't mix Currency and Accounting in the same list of amounts.
• Match decimals to the data. If the amounts have cents, show two decimals. If the report is in whole dollars, show none, on every line.
• Choose one style for negatives. Parentheses or minus signs, but not both in the same report.

Common mistakes and how to fix them

These are the mistakes our exercises check for. Each one leaves the values correct but makes the report wrong to the eye.

Number instead of Currency

The amounts show commas and cents but no dollar sign. When the task asks for amounts that read as money, choose Currency or Accounting, not Number.

Currency with no decimals

Pressing decrease decimals twice too many turns $6,354.91 into $6,355. The cents are still stored, but nobody can see them. Keep two decimals whenever the amounts have cents.

Currency instead of Accounting

Currency and Accounting look similar for positive numbers, which makes this an easy mix-up. If the task asks for lined-up dollar signs or negatives in parentheses, it wants Accounting.

Total row left unformatted

Selecting B5:B12 instead of B5:B13 formats the amounts but leaves the net change in General, showing 5511.05 under a column of $ signs. Include the total in your selection.

Percentages with no decimals

A variance of 0.968% shown as 1% hides detail the reader may need. If the task asks for one decimal, press increase decimals once after choosing Percentage.

Negatives with a minus sign

In a budget table that calls for parentheses, -3,849 should appear as (3,849). With the Number format, turn on the parentheses option for negatives.

Where this fits in MO-220 practice

Formatting cells so others can read them is part of preparing data. Our MO-220 practice tasks describe the result you want, such as "dollar signs aligned at the edge of the column", rather than naming the format, so you learn to recognise which format produces it. Accounting Exercises is not affiliated with Microsoft or Certiport.