Round Numbers in Excel: Stored Values Versus What You See

A cell showing two decimal places can still contain more digits. This explains why a total may not match the sum of displayed line items. Number formatting and ROUND solve different problems, and you should choose based on the reporting rule.

Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.

Build a small example

In A1 type Raw amount. Enter =10/3 in A2:A4 and format those cells to display two decimal places. Each shows 3.33, while the underlying calculation retains more precision.

In A6 enter =SUM(A2:A4). It returns 10, even though adding the displayed values suggests 9.99. Formatting changed how the values appear, not the numbers used by SUM.

Round the calculation explicitly

Put Rounded amount in B1. In B2 enter =ROUND(A2,2) and fill down. Each result is rounded to two decimal places. =SUM(B2:B4) returns 9.99.

The second argument specifies decimal places. Positive values round after the decimal point; zero rounds to a whole number; negative values round to positions left of the decimal point. Test a small example before applying the rule across a report.

Compare line rounding and final rounding

=ROUND(SUM(A2:A4),2) returns 10.00 when displayed to two decimals. Summing B2:B4 gives 9.99. The first rounds the combined total; the second combines individually rounded lines.

Neither is the correct rule for every process. In invoicing, tax or other controlled reporting, use the applicable agreed policy. This exercise demonstrates the arithmetic difference, not an accounting requirement.

Keep useful precision until it is needed

Rounding an intermediate rate can change many later results. Keep raw inputs and calculations available, then round at the specified stage. Label rounded columns so another person knows which values were changed by the formula.

Do not casually enable a workbook-wide precision-as-displayed setting to fix one discrepancy. It can change stored values throughout the workbook. A local, documented ROUND formula is easier to inspect in this exercise.

Do not substitute rounding directions casually

ROUNDUP and ROUNDDOWN have defined behaviours, including for negative values. They are not simply safer versions of ROUND. Rounding a required container count upward may make sense, while rounding a refund needs a different rule.

Test positive, negative and boundary inputs when the application requires them. Computer number representation can also matter near rounding boundaries. Strict precision requirements need an appropriately designed numeric method, not just attractive cell formatting.

Try it yourself

Enter 2.345 in C2 and 2.344 in C3. Round both to two decimal places. Compare a merely formatted source cell with the formula bar and a rounded result.

Keep exploring: Excel learning path.