Calculation Issues

How to Fix Excel SUM Total Differing from Displayed Currency Values

Maira Mehtab Sep 20, 2026 868 views

Question details

The calculated SUM total does not match the sum of the visually displayed two-decimal currency values due to hidden decimal places in the underlying data.

Product: Excel
Device & OS: not provided
Scenario: Summing up a column of currency numbers that have been formatted to show only two decimal places, while the actual cell values contain more precision.
Observed behavior: The SUM function returns a mathematically correct total based on the raw data (e.g., $1,238.30) rather than the expected total of the values currently visible on screen (e.g., $1,238.32).

Before you start

Verify whether your financial report requires the exact mathematical total of the raw data or the total of the visually displayed two-decimal currency values.

Solution 1Recommended

Use the ROUND Function Inside the SUM Formula

Combine the SUM and ROUND functions to force Excel to round each cell value to two decimal places before calculating the total.

When Excel cells are formatted to show currency, it only changes the visual display, not the actual underlying value stored in memory. By nesting the ROUND function inside the SUM function, you instruct Excel to calculate using the exact two-decimal numbers you see on your screen.

Select the target cell

Click on the cell where you want the final, corrected total to appear.

Enter the nested formula

Type the formula =SUM(ROUND(B2:B9, 2)), making sure to replace 'B2:B9' with your actual data range.

Confirm the formula

Press Enter if you are using Microsoft 365, Office 2024, or Office 2021. If you are using an older version of Excel, you must press Ctrl+Shift+Enter to confirm it as an array formula.

Array Formulas in Older Excel Versions: In earlier versions of Excel, pressing Ctrl+Shift+Enter will surround your formula with curly braces {}, indicating it is being processed as an array formula.

Solution 2

Enable the 'Set Precision as Displayed' Option

Change Excel's advanced calculation settings to permanently use the displayed value for all calculations across the entire workbook.

Resolve calculation issues easily

Fix Rounding and Summing Errors in WPS Spreadsheet

WPS Spreadsheet handles complex array formulas and currency formatting flawlessly. You can easily fix summation discrepancies using the exact same ROUND and SUM functions, ensuring your financial reports remain perfectly accurate.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing the calculation discrepancy.
2. Input the rounding formula: Select your total cell and enter the formula =SUM(ROUND(your_range, 2)).
3. Execute the calculation: Press Enter to instantly get the exact sum matching your displayed currency values.

100% compatible with Microsoft Excel formulas like SUM and ROUNDFree and lightweight alternative for processing complex financial spreadsheetsIntuitive formatting tools for currency and decimal data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel seem to change my decimals when summing?

Excel doesn't actually change the decimals during summation. It calculates using the exact underlying values stored in the cell memory, which often have more decimal places than the visual cell formatting allows you to see.

Can I fix the sum without using an array formula?

Yes. You can add a new helper column next to your data where you use the formula =ROUND(B2, 2) for each individual row. Once all rows are rounded in the helper column, you can use a standard =SUM() formula on that new column.

Does formatting a cell as Currency or Accounting fix the rounding issue?

No, applying a Currency or Accounting format only changes how the number looks on your screen. It does not alter the actual precision of the underlying number used in background calculations.

Excel & Data Problems