How to Fix Rounding Errors in Excel

To fix rounding errors in Excel, wrap your calculations in the ROUND function — for example =ROUND(A1*1.075, 2) — so the stored value is actually rounded, not just shown with fewer decimals. Most “rounding errors” are really display issues (the cell shows two decimals but stores the full-precision number) or floating-point quirks, both of which ROUND resolves by forcing the value Excel keeps to match the value you see.

If you work with numbers in Microsoft Excel, then you have likely encountered rounding errors. These errors occur when Excel rounds numbers to a certain number of decimal places, which can result in inaccurate calculations and discrepancies between data. Fixing rounding errors is essential for ensuring the accuracy of your Excel spreadsheets. In this blog post, we will provide you with a step-by-step guide on how to fix rounding errors in Excel. By the end of this post, you will have the knowledge and skills needed to prevent rounding errors from impacting your work and improve the overall accuracy of your Excel spreadsheets.

Understanding Rounding Errors in Excel

Before we dive into how to fix rounding errors in Excel, it’s important to understand what they are and why they occur. Rounding errors happen when a number is rounded to a certain number of decimal places, and then later used in a calculation. The rounding can lead to small inaccuracies that can accumulate and cause significant discrepancies in your data.

For example, let’s say you have a column of numbers that you want to sum up. Some of these numbers have decimal places that extend beyond the two decimal places you want to round to. When you try to sum up these numbers, the result may not be accurate due to rounding errors.

Display Rounding vs. Actual Rounding (the key distinction)

Most “Excel rounding errors” are not really errors at all — they are a mismatch between what a cell shows and what it stores. These are two completely different things:

Display rounding happens when you change the number format (Home → decimal buttons, or Format Cells → Number → Decimal places). The cell looks like 1.23, but Excel still stores the full-precision value, e.g. 1.23456. Any formula that references the cell uses the full stored value, not the displayed one. This is why a column that visibly reads 0.33 + 0.33 + 0.34 can sum to a number that displays as 1.01 — the hidden decimals added up.
Actual rounding happens only when you use a function like ROUND. =ROUND(1.23456, 2) changes the stored value to exactly 1.23. From that point on, every formula sees 1.23.

The rule of thumb: format changes how a number looks; ROUND changes what the number is. If your totals are off by a cent, you almost always need actual rounding, not formatting. For more on this, see our guides on how to format cells in Excel and how to round numbers in Excel.

The Rounding Functions and When to Use Each

Excel has several functions that perform actual rounding. Choosing the right one matters — they behave differently at the halfway point and with negative numbers. All of these work identically on Windows and Mac.

ROUND(number, num_digits) — rounds to the nearest value at the given number of digits, using “round half away from zero” (so 2.5 → 3, -2.5 → -3). Use positive num_digits for decimals (ROUND(x, 2)), 0 for whole numbers, and negative digits to round to tens, hundreds, etc. (ROUND(1234, -2) → 1200). See the Excel ROUND function for a full walkthrough.
ROUNDUP(number, num_digits) — always rounds away from zero (ROUNDUP(2.1, 0) → 3). Useful for things like billing units or packaging where you can’t round down. Details in how to ROUNDUP in Excel.
ROUNDDOWN(number, num_digits) — always rounds toward zero (ROUNDDOWN(2.9, 0) → 2), regardless of the fractional part.
MROUND(number, multiple) — rounds to the nearest multiple (MROUND(17, 5) → 15, MROUND(18, 5) → 20). Handy for rounding times to the nearest 15 minutes or prices to the nearest 5 cents.
INT(number) — rounds down to the nearest integer. For positive numbers this drops the decimals; for negatives it rounds more negative (INT(-2.1) → -3). See the Excel INT function or using the INT formula in Excel.
TRUNC(number, [num_digits]) — simply chops off digits without rounding (TRUNC(-2.9) → -2, TRUNC(3.14159, 2) → 3.14). Unlike INT, it never rounds away from zero. See the Excel TRUNC function.

The difference between INT and TRUNC only shows up with negative numbers: INT(-2.5) is -3, but TRUNC(-2.5) is -2.

Step-by-Step Guide to Fix Rounding Errors in Excel

Step 1: Identify the Cells with Rounding Errors

The first step in fixing rounding errors is to identify the cells that are affected. Look for cells that have a significant difference compared to what you were expecting. In some cases, you may need to manually compare the rounded number with the actual number to identify any discrepancies.

Step 2: Adjust the Decimal Places

The next step is to adjust the number of decimal places for the cells that have rounding errors. To do this, select the affected cells, right-click, and select “Format Cells.” In the “Format Cells” dialog box, select the “Number” tab and change the decimal places according to your preference.

Step 3: Use the ROUND Function

If adjusting the decimal places doesn’t solve the rounding errors, you can use the ROUND function in Excel. This function allows you to round a number to a certain number of decimal places. To use the ROUND function, enter “=ROUND(number, num_digits)” in a cell, where “number” is the value you want to round and “num_digits” is the number of digits you want to round to.

Step 4: Use the SUM Function with ROUND

If you’re summing up a column of numbers, use the SUM function with the ROUND function to fix any rounding errors. To do this, enter “=SUM(ROUND(range, num_digits))” where “range” is the range of cells you want to sum up and “num_digits” is the number of digits you want to round to. Note that this is an array formula: in older Excel versions you may need to confirm it with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac); in Microsoft 365 it spills automatically. The cleaner, non-array approach is to round each value in its own cell first, then SUM the rounded cells.

Why Sums Look Off by a Cent — and the One-Line Fix

The classic symptom: you have a list of dollar amounts that display to two decimals, and the total is one cent higher or lower than a hand calculation. This is display rounding biting you — the cells store more decimals than they show, and those hidden fractions accumulate.

The fix is to round at the point where precision matters. Two reliable patterns:

Round each line item as it is created: =ROUND(quantity*price, 2). Then a plain =SUM(...) over those cells can never drift.
If you only need the total to be correct, round the total: =ROUND(SUM(B2:B20), 2).

For a deeper look at totals, see how to add and SUM in Excel.

Floating-Point (IEEE 754) Quirks: Why 0.1 + 0.2 Isn’t Exactly 0.3

Some rounding errors are not about formatting at all — they come from how computers store numbers. Excel follows the IEEE 754 double-precision standard, which stores numbers in binary. Many decimal fractions (like 0.1, 0.2, or 0.3) have no exact binary representation, the same way 1/3 has no exact decimal representation. So 0.1 + 0.2 is stored as something like 0.30000000000000004, and a test like =(0.1+0.2)=0.3 can return FALSE.

Excel hides most of this by displaying only about 15 significant digits, and it applies internal heuristics so everyday math looks correct. But the underlying imprecision can surface in comparisons (=), in IF tests, or when subtracting two nearly equal numbers. Related to this is the 15-significant-digit limit: Excel stores at most 15 significant figures of precision. Type a 20-digit number or a long credit-card number and Excel converts the trailing digits to zeros. (To keep long digit strings intact, format the cells as Text first or prefix with an apostrophe — see how to stop Excel from changing numbers.)

How to handle floating-point issues:

For comparisons, round both sides before testing: =ROUND(0.1+0.2, 10)=ROUND(0.3, 10) returns TRUE.
For money, store and compute values already rounded to the cent with ROUND(..., 2).
Never test two floating-point results for exact equality; compare within a small tolerance instead.

The “Set Precision as Displayed” Option (Use With Caution)

Excel has a workbook-level setting that forces stored values to match displayed values. On Windows: File → Options → Advanced → “When calculating this workbook” → check “Set precision as displayed.” On Mac: Excel → Preferences → Calculation → “Set precision as displayed.”

When you turn this on, every number in the workbook is permanently truncated to the precision currently shown by its cell format. A cell displaying 1.23 that actually stored 1.23456 is changed to 1.23 forever — the extra digits are gone even if you uncheck the option later.

This is a destructive, irreversible change to your data, so use it sparingly:

It affects the entire workbook, not the selected cells.
There is no undo for the precision loss; the original full-precision values cannot be recovered.
Save a backup copy of the file before enabling it.

For most situations, wrapping calculations in ROUND is safer because it changes only the cells you choose and leaves the rest of your data untouched.

Preventing Rounding Errors in Excel

Now that you know how to fix rounding errors in Excel, it’s important to take steps to prevent them from happening in the future. Here are some tips:

Keep the number of decimal places consistent throughout your spreadsheet
Use the ROUND function for any calculations involving numbers with decimal places
Use the SUM function with the ROUND function when summing up numbers with decimal places

By following these tips, you can ensure the accuracy of your Excel spreadsheets and avoid any rounding errors.

Dealing with Complex Calculations

In some cases, you may be dealing with more complex calculations that involve rounding errors. One common example is when calculating percentages. Excel rounds the result of any percentage calculation, which can result in inaccuracies. To fix this, you can use the ROUND function along with the formula for percentage calculations.

The formula for calculating percentage in Excel is: “=(part/total)*100”. To use the ROUND function with this formula, enter “=ROUND((part/total)*100, num_digits)” where “part/total” is the calculation for the percentage and “num_digits” is the number of digits you want to round to. See how to round percentages in Excel for percentage-specific examples.

Alternative Ways to Fix Rounding Errors

Aside from adjusting decimal places and using the ROUND function, there are other ways to fix rounding errors in Excel. One option is to use the “ROUND UP” or “ROUND DOWN” functions instead of the standard ROUND function. These functions round up or down to the nearest whole number or specified number of decimal places.

Another option is to use the “IF” function with the ROUND function to round any numbers that are close to a whole number. For example, if a number is within 0.5 of the previous whole number, you can use the IF function to round it appropriately.

Excel is a powerful tool for working with data, but rounding errors can lead to inaccuracies and discrepancies in your calculations. By following the steps outlined in this blog post, you can fix rounding errors and ensure the accuracy of your Excel spreadsheets. Remember to keep the number of decimal places consistent throughout your spreadsheet, use the ROUND function for any calculations involving numbers with decimal places, and use the SUM function with the ROUND function when summing up numbers with decimal places. With these tips and tricks, you can make sure that your Excel spreadsheets are always accurate and reliable.

Frequently Asked Questions

Below are some frequently asked questions regarding rounding errors in Excel:

What Causes Rounding Errors in Excel?

Rounding errors in Excel occur when a number is rounded to a certain number of decimal places, and then later used in a calculation. This can cause small inaccuracies that can accumulate and cause significant discrepancies in your data.

How Do I Identify Cells with Rounding Errors?

The easiest way to identify cells with rounding errors is to manually compare the rounded number with the actual number to identify any discrepancies. Cells with a significant difference compared to what you were expecting are usually affected by rounding errors.

What Is the Best Way to Fix Rounding Errors?

There are several ways to fix rounding errors in Excel, including adjusting decimal places, using the ROUND function, or using the SUM function with the ROUND function. The best way to fix rounding errors depends on the specific situation, but using the ROUND function is usually an effective way to fix rounding errors.

How Can I Prevent Rounding Errors in Excel?

To prevent rounding errors in Excel, it is important to keep the number of decimal places consistent throughout your spreadsheet, use the ROUND function for any calculations involving numbers with decimal places, and use the SUM function with the ROUND function when summing up numbers with decimal places.

Are There Any Alternative Ways to Fix Rounding Errors?

Aside from adjusting decimal places and using the ROUND function, you can use the ROUNDUP or ROUNDDOWN functions instead of the standard ROUND function, MROUND to round to the nearest multiple, or INT/TRUNC to drop decimals. Alternatively, you can use the IF function with ROUND to round any numbers that are close to a whole number.

Why Does Excel Round Wrong or Show 0.1 + 0.2 ≠ 0.3?

This is not a bug — it is floating-point arithmetic. Excel stores numbers in binary using the IEEE 754 standard, and decimals like 0.1 and 0.2 have no exact binary form, so their sum stores as 0.30000000000000004. Round both sides before comparing (=ROUND(0.1+0.2,10)=ROUND(0.3,10)) and round monetary results to the cent to avoid the issue.

Should I Use “Set Precision as Displayed” to Fix Rounding?

Usually no. The “Set precision as displayed” option (File → Options → Advanced on Windows, or Excel → Preferences → Calculation on Mac) permanently truncates every value in the workbook to the precision currently shown, with no way to recover the original digits. Wrapping calculations in ROUND is safer because it changes only the cells you choose. If you do use the option, back up the file first.