Round to 2 Decimal Places in Excel: ROUND vs Format
· 6 min read
To round to 2 decimal places, wrap the number or calculation in ROUND and use 2 as the second argument. This changes the actual value in the cell, not just how it looks:
=ROUND(number, 2)For example, =ROUND(7.6415, 2) returns 7.64, and =ROUND(B2*(1-C2), 2) rounds a discounted price to cents. Formatting a cell to show two decimals is different: the cell still holds every decimal, and calculations use them.
Worked example: discounted prices
List prices with a customer discount. Columns F and G hold ROUNDUP and ROUNDDOWN, covered further down:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | List price | Discount | Exact net price | Rounded | ROUNDUP | ROUNDDOWN |
| 2 | Stapler | 8.99 | 15.0% | 7.6415 | 7.64 | 7.65 | 7.64 |
| 3 | Kettle | 34.50 | 12.5% | 30.1875 | 30.19 | 30.19 | 30.18 |
| 4 | Paper ream | 6.49 | 7.5% | 6.00325 | 6.00 | 6.01 | 6.00 |
| 5 | Mop | 19.99 | 33.0% | 13.3933 | 13.39 | 13.40 | 13.39 |
D2 holds =B2*(1-C2). E2 holds the rounded version:
=ROUND(B2*(1-C2), 2)You can put a whole calculation inside ROUND, so there is no need for a helper column like D. It is shown here so you can see the values before rounding.
ROUND looks at the next digit. If it is 5 or more, it rounds up; otherwise it rounds down. Kettle: 30.1875, the third decimal is 7, so the result is 30.19. Paper ream: 6.00325, the third decimal is 3, so it is 6.00.
The excel formula round family
The second argument of ROUND sets the number of decimals, and it works in both directions:
| Formula | Result | What it does |
|---|---|---|
=ROUND(3.14159, 2) | 3.14 | 2 decimal places |
=ROUND(3.14159, 0) | 3 | nearest whole number |
=ROUND(1234.5, -2) | 1200 | nearest hundred |
=MROUND(7.6415, 0.05) | 7.65 | nearest multiple of 0.05 |
A negative number of digits rounds to the left of the decimal point: -1 to tens, -2 to hundreds, -3 to thousands. That is handy for summary numbers in a presentation.
Round up and round down
ROUND goes to the nearest value. Sometimes the business rule says always round up (shipping weights, number of boxes) or always down (commission caps, whole units in stock). Use ROUNDUP and ROUNDDOWN with the same arguments:
=ROUNDUP(B2*(1-C2), 2)
=ROUNDDOWN(B2*(1-C2), 2)On the same table, columns F and G of the sheet above show the results: the Stapler becomes 7.65 with ROUNDUP and 7.64 with ROUNDDOWN, the Kettle 30.19 and 30.18, the Paper ream 6.01 and 6.00, and the Mop 13.40 and 13.39.
ROUNDUP vs ROUND
ROUND can go either way, depending on the next digit. ROUNDUP always moves away from zero, even when the next digit is tiny: 6.00325 becomes 6.01. That also means ROUNDUP of a negative number gets more negative: =ROUNDUP(-2.341, 2) is -2.35. ROUNDDOWN always moves toward zero.
Pick based on the rule you are applying, not on which looks nicer. If the rule is "nearest cent", ROUND is right and ROUNDUP is wrong on three of the four rows above.
Round vs format
This is the part that catches people out. The Home tab has Increase Decimal and Decrease Decimal buttons, and Format Cells lets you choose two decimal places. Both change only the display. The cell still holds the full value, and every formula that uses it sees the full value.
Here is where it shows. Split a 10.00 delivery fee evenly across three regions with =10/3 in each cell:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Formatted | Rounded | Actual value in B |
| 2 | North | 3.33 | 3.33 | 3.333333333 |
| 3 | South | 3.33 | 3.33 | 3.333333333 |
| 4 | West | 3.33 | 3.33 | 3.333333333 |
| 5 | Total (SUM) | 10.00 | 9.99 | 10 |
The column shows 3.33 three times and a total of 10.00. Anyone adding the visible numbers gets 9.99, and the report looks wrong. If you use =ROUND(10/3, 2) instead, each cell really holds 3.33 and the SUM is 9.99. Now the total matches what people see, and you can see that one cent needs to go somewhere (often to the last line).
Which one to use:
- Format only when the decimals are just noise for the reader and every calculation should use the full precision. Most analysis falls here.
- ROUND when the rounded number is the real value: invoice lines, prices, payments, anything that must add up exactly as shown, or anything another system will compare to the cent.
There is also an Excel option called "Set precision as displayed" (File, Options, Advanced). It permanently cuts every value in the workbook to its displayed format. It cannot be undone, so avoid it unless you are sure.
Common mistakes
Formatting instead of rounding. When you ask an AI assistant to "round these to 2 decimal places", it will sometimes tell you to use Decrease Decimal or Format Cells. The sheet looks right, but the values are unchanged. Any check that compares the stored values (another system, a grader, a VLOOKUP on the price) will fail. To spot it, click the cell and look at the formula bar: if it still shows 7.6415, the value was not rounded.
Rounding too early. Round at the end, not in the middle. A pack of 12 costs 9.99, so one unit costs 0.8325. If you round that to 0.83 first and then multiply by 1,000 units, you get 830.00. Multiplying first and rounding at the end gives 832.50. The early rounding lost 2.50. If you need a rounded unit price for display, keep the exact one for calculations.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Pack price | Pack size | Units | Result | Version | |
| 2 | 9.99 | 12 | 1000 | 830.00 | Rounded too early | |
| 3 | 832.50 | Rounded at the end |
Rounding each row and the total separately. If lines are rounded, total the rounded lines. If you round an unrounded total instead, the total may not match the sum of the lines by a cent or two.
Using ROUNDUP when the rule says nearest. "Round to the nearest cent" means ROUND. ROUNDUP is for rules like "always charge at least".
Comparing calculated decimals with =. Computers store most decimals as tiny binary approximations. Excel hides this in the display, but two calculated values that look identical can occasionally fail an = test or an exact-match lookup. Wrapping both sides in ROUND makes the comparison reliable.
FAQ
How do I round to 2 decimal places in Excel?
Use =ROUND(A2, 2), or wrap a calculation: =ROUND(B2*(1-C2), 2). To only show two decimals without changing the value, use Format Cells, Number, 2 decimal places.
What is the difference between ROUND and ROUNDUP?
ROUND goes to the nearest value: 6.00325 becomes 6.00. ROUNDUP always goes away from zero: 6.00325 becomes 6.01. Use ROUNDUP only when a rule says to.
Does formatting a cell round the number?
No. Formatting changes only what you see. The cell keeps its full value, and SUM, AVERAGE and lookups use that full value.
How do I round to the nearest whole number?
Use 0 decimals: =ROUND(A2, 0). To always round up to a whole number, use =ROUNDUP(A2, 0).
Why does my total not match the numbers I see?
The cells are probably formatted to show fewer decimals than they hold. The SUM uses the hidden decimals. Round the individual values with ROUND if they need to add up exactly as shown. For a related case with percentages, see percentage of total in Excel, and for more rounding questions that come up in tests, our Excel interview questions for data analysts.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Average order value, in cents” →