Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel Output

Sums such as 0.1 + 0.2 print as 0.30000000000000004, prices turn into 1998 cents instead of 1999, and threshold checks fail by a hair. See where binary floating point leaks into Excel reports, what the xlsx writers already hide, and how to round, compare and convert money safely.
Errors & fixesData cleaningpandas

A report calculates a total, and the output looks slightly wrong:

Text
>>> 0.1 + 0.2
0.30000000000000004

Usually that is only cosmetic. But the same effect has less visible consequences: a price of 19.99 converted to cents becomes 1998, a control check that should pass fails because the difference is 0.0000000000001, and a CSV handed to another system is full of 17-digit numbers. None of it is a pandas or Excel bug. It is how binary floating point works, and a report pipeline needs to deal with it deliberately at a few specific points. This page, part of Troubleshooting Common Python Excel Errors, shows those points and the fix for each.

Why 0.1 + 0.2 is not exactly 0.3 The decimal value 0.1 has no exact binary form, so Python stores the nearest double, slightly above 0.1. The same happens for 0.2. Adding the two stored values gives a double just above 0.3, which Python prints in full as 0.30000000000000004. You type 0.1 stored slightly above 0.1 You type 0.2 stored slightly above 0.2 Add the stored values nearest double to the true sum Printed result 0.30000000000000004

Prerequisites

Bash
pip install pandas openpyxl
Python
import math
import re
import zipfile
from decimal import Decimal, ROUND_HALF_UP

import numpy as np
import pandas as pd

Step 1 — Know what reaches the workbook

Before fixing anything, see what the file actually contains. openpyxl and xlsxwriter both format numbers with sixteen significant digits (%.16g), which is one digit short of what it takes to show every float's noise. Many artefacts therefore disappear on the way into the file — but not all of them:

Python
values = {"0.1 + 0.2": 0.1 + 0.2, "0.7 + 0.1": 0.7 + 0.1, "4.35 * 100": 4.35 * 100}
pd.DataFrame({"expr": list(values), "value": list(values.values())}).to_excel("noise.xlsx", index=False)

with zipfile.ZipFile("noise.xlsx") as z:
    xml = z.read("xl/worksheets/sheet1.xml").decode()
for expr, stored in zip(values, re.findall(r'<c r="B[2-4]"[^>]*><v>(.*?)</v>', xml)):
    print(f"{expr:12} python={values[expr]!r:22} file={stored}")

0.1 + 0.2 is written as 0.3. 0.7 + 0.1 is written as 0.7999999999999999 and 4.35 * 100 as 434.9999999999999. Excel displays at most fifteen significant digits, so a reader looking at the cell sees 0.8 and 435 — the noise is in the file but out of sight. Where it bites is in what code does with the values, before and after they are written.

Step 2 — Round at the edge, not in the middle

For display, the simplest fix is to round every monetary or percentage column to the precision the report shows, as the last step before writing. Rounding at the end keeps full precision through the calculation and gives clean values in the file:

Python
sales = pd.DataFrame({"unit_price": [19.99, 4.35, 0.7], "qty": [3, 100, 1]})
sales["revenue"] = sales["unit_price"] * sales["qty"]
print(sales["revenue"].tolist())
# [59.97, 434.99999999999994, 0.7]

report = sales.round({"revenue": 2})
report.to_excel("sales.xlsx", index=False)
print(pd.read_excel("sales.xlsx")["revenue"].tolist())
# [59.97, 435.0, 0.7]

Rounding in the middle of a calculation — every line of an invoice rounded before the total is computed, say — is a business rule, not a technical fix. Some documents need it so that the printed lines add up to the printed total; others must round only the total. Decide which your report needs and do it on purpose. Applying a number format such as #,##0.00 changes only how Excel shows the value; it does not remove the noise from calculations that read the cell. Match Excel Rounding in pandas covers why Python's round and Excel's ROUND disagree on halves.

Step 3 — Convert money to cents with rounding, never truncation

The most damaging artefact is not a long decimal but a lost cent. int() truncates towards zero, and a price multiplied by 100 is often a hair below the whole number:

Python
prices = pd.Series([19.99, 4.35, 0.07, 1.15])
print((prices * 100).tolist())
print((prices * 100).astype(int).tolist())
# [1998, 434, 7, 114]  <- three of four are wrong

print((prices * 100).round().astype(int).tolist())
# [1999, 435, 7, 115]

Round before converting to an integer — always. Code that stores amounts as integer cents to avoid float problems has to cross this boundary once, and that one crossing is where the problem lives. The same applies to math.floor, // and np.trunc applied to money.

Cents from int(price * 100) versus round first Bars comparing two conversions for the price 19.99. int(price * 100) gives 1998 because the product is stored as 1998.9999999999998. Rounding first gives the correct 1999. The one-cent loss repeats for 4.35 and 1.15. Price 19.99 → cents price * 100 (stored) 1998.9999999999998 cents int(price * 100) 1998 cents int(round(price * 100)) 1999 cents

Step 4 — Compare with a tolerance in validation checks

Checks that decide whether a report is correct — totals match control figures, a percentage column sums to 100 — must not use == on floats. The values are right; the comparison is too strict:

Python
parts = pd.Series([0.7, 0.1])
print(parts.sum(), parts.sum() == 0.8)
# 0.7999999999999999 False

print(math.isclose(parts.sum(), 0.8, abs_tol=1e-9))
print(np.isclose(parts.sum(), 0.8))
print(round(parts.sum(), 2) == 0.8)
# True True True

Choose the tolerance from the data, not from habit. A tolerance of 1e-9 is sensible for values around 1; for sums around 300 million, the gap between neighbouring floats is already about 6e-8 (np.spacing(3e8)), so a fixed absolute tolerance of 1e-9 can fail on correct numbers. For currency, rounding both sides to cents and comparing, or checking that the absolute difference is below half a cent, states the business rule directly. numpy.isclose uses a relative tolerance by default, which is sensible for large magnitudes but can be looser than you expect on totals in the millions. Reconcile Excel Totals Against Control Totals with Python builds a full reconciliation on this principle.

Step 5 — Use Decimal where every cent must reconcile

When a calculation has to match an accounting system exactly — invoice lines, tax per line, allocations that must sum to the original amount — do it in Decimal. Decimal arithmetic works in base ten, so 0.1 is exactly 0.1, and rounding follows the rule you name:

Python
lines = [Decimal("19.99"), Decimal("4.35"), Decimal("0.07")]
qty = [3, 100, 1]
totals = [(p * q).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP) for p, q in zip(lines, qty)]
print(totals, sum(totals))
# [Decimal('59.97'), Decimal('435.00'), Decimal('0.07')] 495.04

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.append(["line total"])
for t in totals:
    ws.append([t])
wb.save("decimal.xlsx")
print(pd.read_excel("decimal.xlsx")["line total"].tolist())
# [59.97, 435.0, 0.07]

Create Decimals from strings, not floats — Decimal(19.99) faithfully captures the float's noise. openpyxl writes Decimal values as ordinary numbers; the file does not remember that they were Decimals, and anything reading it gets floats again. That is fine: the point of Decimal is to get the arithmetic right before the values are written. Keep pandas in float for large analytical tables, where the speed matters and rounding at the end is enough.

Choosing a fix for float noise A decision chart. If the noise only looks bad in the output, round the column at the end before writing. If values are converted to integer cents, round before int. If a check compares floats, use isclose or compare rounded values. If every cent must reconcile with another system, calculate in Decimal created from strings. Where does the noise cause trouble? only in the output Round at the end df.round({"col": 2}) converting to cents Round before int (x * 100).round().astype(int) in a check Compare with tolerance isclose, or round both sides must reconcile exactly Use Decimal Decimal("19.99"), quantize

Step 6 — Watch CSV and text output

Unlike the xlsx writers, to_csv writes Python's shortest exact representation, so every artefact the workbook would have hidden appears in the CSV. Pass float_format for columns meant for people or for systems that expect fixed decimals:

Python
noisy = pd.DataFrame({"amount": [0.7 + 0.1, 4.35 * 100]})
noisy.to_csv("amounts.csv", index=False)
print(open("amounts.csv").read())
noisy.to_csv("amounts_2dp.csv", index=False, float_format="%.2f")
print(open("amounts_2dp.csv").read())

The same applies to f-strings in email bodies and log messages: format amounts with :.2f or :,.2f, never with plain str(). Email an Excel Report with an HTML Summary Body has an example of formatting figures for a message.

Common pitfalls

  • Rounding with a number format and assuming the value changed. number_format = "0.00" changes the display; formulas and readers still see the full value.
  • Decimal(0.1) instead of Decimal("0.1"). Constructing from a float imports its binary error.
  • int() on money. It truncates. Round first.
  • == in tests. Use pytest.approx, math.isclose or rounded comparisons.
  • Blaming Python's sum. Since Python 3.12, the built-in sum of floats uses compensated summation and gets sum([0.1] * 10) exactly right; pandas and NumPy use their own algorithms. The results can differ in the last digit, which is one more reason to compare with a tolerance.

Performance notes

DataFrame.round is vectorised and effectively free. Decimal arithmetic is much slower than float — tens of times on large arrays — because it runs in Python rather than in NumPy, which is why it belongs in the narrow calculations that need exactness rather than across a whole analytical pipeline.

Conclusion

Float noise comes from binary storage, not from pandas or Excel. The xlsx writers' sixteen significant digits hide some of it, and Excel's fifteen-digit display hides most of the rest from readers; the real damage happens in code. Round columns to their display precision as the last step before writing, round before converting money to integers, compare with a tolerance, use Decimal built from strings where every cent must reconcile, and pass float_format when writing CSV.

Frequently asked questions

Why does Python show 0.30000000000000004 for 0.1 + 0.2? Floats are stored in binary, and 0.1 and 0.2 have no exact binary representation, just as 1/3 has no exact decimal one. The sum lands on the nearest representable number, which prints as 0.30000000000000004.

Will the noise appear in the Excel file? Often not. openpyxl and xlsxwriter write numbers with 16 significant digits, which rounds 0.30000000000000004 to 0.3. Values like 0.7999999999999999 keep their noise, though, and CSV output keeps all of it.

Should I store money as Decimal? Use Decimal for calculations where every cent must reconcile, such as invoices and allocations. openpyxl writes Decimal values as numbers. For analysis on large tables, floats rounded to cents at the end are usually enough.

How should I compare floats in validation checks? Never with ==. Use math.isclose or numpy.isclose with a tolerance that fits the data, or round both sides to the precision that matters — two decimal places for currency — before comparing.

More on errors & fixes

More on errors & fixes →