Round to the cent in Excel: make the stored value match the money

Money moves in whole cents. Spreadsheets do not. Multiply a quantity by a unit price, split a bill by headcount, or work out a few days of interest, and the result usually runs past the second decimal place. Leave those extra digits in, and your totals drift from the invoice, the bank and the bill by a cent. This page shows how to round to the cent on purpose, which function fits which job, and the mistakes that leave a stray cent in the books.

Why a single cent matters to an accountant

A cent sounds too small to care about. In practice, a one-cent difference is often the thing that stops a reconciliation from tying out. To reconcile means to prove that two records of the same thing agree, such as your ledger and the bank statement. When they are a cent apart, someone has to find out why, and that search costs far more than the cent.

Most of these differences come from one source. The spreadsheet stores more digits than you can see. A line total of 836.672 looks like $836.67 when the cell is formatted as currency, but the full value is still there, and every formula that refers to the cell uses it.

Rounding properly fixes this at the source. You decide where each amount becomes whole cents, which direction it rounds, and where any leftover cent goes.

How rounding works in the spreadsheet

Three functions cover nearly every money task. Each takes the value to round and the number of decimal places to keep. For money, that second argument is 2, which means cents.

• =ROUND(value, 2) rounds to the nearest cent. A fraction of half a cent or more goes up; anything less goes down. This is the usual choice for invoice lines and allocations.
• =ROUNDDOWN(value, 2) always cuts toward zero. Every fraction of a cent is dropped, however large. Use it when a rule says an amount must never be overstated, such as interest a bank pays.
• =ROUNDUP(value, 2) always moves away from zero. Any fraction of a cent becomes a full cent. Use it when a rule says an amount must never be understated, such as a fee your policy says is always collected in full.

2 keeps cents, 0 keeps whole dollars, and -2 rounds to the nearest hundred.

Format is not the same as value

This is the most important idea on the page. A number format changes how a cell looks. It does not change what the cell holds. If you format a cell to two decimals, the spreadsheet displays a rounded amount, but sums, lookups and comparisons still use the full stored value. The ROUND function is different: it changes the value itself, so =ROUND(836.672, 2) stores exactly 836.67.

Worked example 1: invoice line totals

Copperleaf Studio receives a supplier invoice. Some unit prices are quoted to a tenth of a cent. Each line total is quantity times unit price, and each must be stored in whole cents. In D5 the formula is =ROUND(B5*C5,2), filled down to D14. The invoice total in D15 is =SUM(D5:D14).

Look at the last row. With rounded lines, the total is $16,267.30, and it equals the sum of the ten amounts printed on the invoice. Without rounding, the total is 16,267.294, which a currency format shows as $16,267.29. Here is the trap: in that unrounded version, every line is also formatted to two decimals, so the column displays exactly the same ten amounts as the rounded column. Add them up by hand and you get $16,267.30. The sheet shows $16,267.29 underneath them. The column looks right line by line, and the total is a cent short.

The cause is the hidden thousandths. Rounding each line in the formula removes them, so what you see is what gets added.

Worked example 2: allocating a bill and the rounding difference

Summit Bike Repair shares a bill of $19,475.32 across four departments in proportion to headcount. The bill sits in B2, the headcounts in B5:B8, and their total of 43 in B9. Each share is the bill times the department's headcount divided by the total headcount.

If you round all four shares to the nearest cent, they add up to $19,475.33. That is one cent more than the bill. Small rounding gains on three lines added up to a full cent. This gap is called the rounding difference, and it is normal whenever you split an amount into rounded parts.

The fix is a plug: a figure calculated as whatever is left over, so the total is forced to agree. Round the first three shares with =ROUND($B$2*B5/$B$9,2) filled down to C7. Then give the last department the rest: =$B$2-SUM(C5:C7) in C8. Admin receives $7,246.62, one cent less than its rounded share, and the four allocations add up to the bill exactly.

Notice the dollar signs. The bill and the total headcount are the same for every row, so they are locked as $B$2 and $B$9. The headcount in column B changes on each row, so it stays relative.

Worked example 3: interest cut down to the cent

Harbor Bakery holds ten deposit accounts and earns interest at an example annual rate of 1.25% in B2. Interest for each account is the balance times the rate times the days held, divided by a 365-day year. The bank never pays part of a cent, and it never rounds up in your favor. It drops the fraction. So the formula in D5 is =ROUNDDOWN(B5*$B$2*C5/365,2), filled down.

The table shows five of the ten accounts. On Escrow, both functions give $14.96, because the fraction is below half a cent. On the other four, the fraction is more than half a cent, so ROUND goes up and ROUNDDOWN does not. Across all ten accounts, the correct total in D15 is $323.68. Rounding to the nearest cent would give $323.72, four cents more than the bank will actually credit.

ROUNDUP is the mirror image. Use it when a rule says the amount must never fall short, for example a service charge your policy says is always billed in full cents upward. Read the rule, then pick the direction it describes.

Common mistakes and how to fix them

The exercises in this lesson catch each of these and explain the fix.

Relying on the number format instead of ROUND

Formatting cells to two decimals makes them look right but leaves the hidden digits in place. In the invoice example, this is exactly how the total came out at $16,267.29 instead of $16,267.30. Put ROUND in the formula so the stored value is a whole number of cents.

Rounding to 0 decimals

=ROUND(B5*C5,0) rounds to whole dollars. The invoice total becomes $16,267, and every line loses its cents. The second argument counts decimal places, so cents need 2.

Rounding every allocation, including the last

When all four shares are rounded, the allocations total $19,475.33 against a bill of $19,475.32. Round all but one, and make the last one the bill minus the others.

Leaving the bill reference unlocked

If the first formula is =ROUND(B2*B5/$B$9,2), filling it down turns B2 into B3 and then B4. Those cells are blank or hold a label, so the second share comes out as zero and the third as an error. Lock the bill as $B$2 before you fill down.

Using ROUND where the rule says round down

For interest that is cut down to the cent, ROUND overstates any amount whose fraction is half a cent or more. In the interest example, that adds four cents in total. "Never pays part of a cent" means drop the fraction: ROUNDDOWN.

Cutting down to whole dollars

=ROUNDDOWN(value,0) drops the cents along with the fraction. The interest total falls to $318, and the Project fund account, which earned 52 cents, shows nothing at all. Use 2 decimal places.

Rounding and MO-220 practice

The MO-220 exam is a 50-minute, performance-based exam [Official]. Its tasks describe the purpose instead of naming the function [Official], so you will not be told "use ROUNDDOWN". You will be told that an amount must never be overstated, or that values should be stored to two decimal places. Matching the description to the function is the skill. Accounting Exercises is not affiliated with Microsoft or Certiport.