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

Round Numbers for Excel Output: float_format vs Number Formats

pandas offers three ways to get two decimal places into Excel — to_excel's float_format, DataFrame.round and a cell number format — and they do different things. See which one changes the stored value, which one changes only the display, and why they disagree on halves like 0.015.
Writing Excel filesNumber & date formatspandas

A report needs amounts with two decimals. There are three obvious ways to get there from pandas, and it is easy to assume they are interchangeable:

Text
df.to_excel(path, float_format="%.2f")        # an argument to to_excel
df.round(2).to_excel(path)                    # round the data first
cell.number_format = "0.00"                   # format the cells

They are not. Two of them change the stored value and one changes only the display; two of them round the same halves in opposite directions; and only one of them makes Excel show 2.50 rather than 2.5. Choosing the wrong one produces reports whose totals are off by a cent, or whose columns look ragged. This page, part of Writing DataFrames to Excel with Pandas, compares them on the same numbers.

What each rounding method changes A comparison of three methods. to_excel float_format changes the stored value, does not fix the number of displayed decimals, and rounds using string formatting. DataFrame.round changes the stored value, does not fix the display, and rounds half to even. A number format such as 0.00 leaves the stored value unchanged, fixes the displayed decimals, and rounds only on screen. Changes stored value Shows 2.50 for 2.5 Rounding rule to_excel(float_format="%.2f") string formatting df.round(2) half to even (NumPy) number_format = "0.00" display only

Prerequisites

Bash
pip install pandas openpyxl
Python
from decimal import Decimal, ROUND_HALF_UP
import pandas as pd
from openpyxl import load_workbook

amounts = pd.DataFrame({"amount": [0.12345, 2.5, 1234567.891, 0.005, 0.015, 1.005]})

def stored(path):
    ws = load_workbook(path).active
    return [(c.value, c.number_format) for (c,) in ws.iter_rows(min_row=2, max_col=1)]

Step 1 — float_format rounds the stored value

float_format takes a %-style pattern. pandas formats each float with it and converts the result back to a number, so the file holds rounded numbers, not text:

Python
amounts.to_excel("float_format.xlsx", index=False, float_format="%.2f")
print(stored("float_format.xlsx"))
# [(0.12, 'General'), (2.5, 'General'), (1234567.89, 'General'), (0.01, 'General'), (0.01, 'General'), (1, 'General')]

Two things stand out. The number format is still General, so Excel displays 2.5 and 1 rather than 2.50 and 1.00 — float_format cannot add trailing zeros because it produces a number, and numbers do not carry formatting. And the halves round unevenly: 0.005 became 0.01, 0.015 also became 0.01, and 1.005 became 1.00. That is Python's % formatting working on the exact binary values, which are slightly above or below the decimal you typed — 0.015 is stored as 0.01499999…, so it rounds down.

float_format affects only float columns; integers and nullable Float64 columns are written as they are.

Step 2 — DataFrame.round rounds differently

Rounding the data before writing gives rounded numbers too, but with a different rule:

Python
amounts.round(2).to_excel("rounded.xlsx", index=False)
print(stored("rounded.xlsx"))
# [(0.12, 'General'), (2.5, 'General'), (1234567.89, 'General'), (0, 'General'), (0.02, 'General'), (1, 'General')]

0.005 is now 0.0 and 0.015 is 0.02 — the opposite of float_format for both. DataFrame.round uses NumPy, which scales the value, rounds half to even and scales back, so its answers for near-halves depend on a different chain of floating-point steps. Neither method is wrong; they are different rules, and a report that mixes them (rounded in one script, float_format in another) will disagree with itself.

The same values rounded three ways A table of values near a half rounded to two decimals. For 0.005, float_format gives 0.01, DataFrame.round gives 0.00 and a spreadsheet ROUND gives 0.01. For 0.015, float_format gives 0.01, round gives 0.02 and ROUND gives 0.02. For 1.005, float_format gives 1.00, round gives 1.00 and ROUND gives 1.01. For 2.675, float_format gives 2.67, round gives 2.67 and ROUND gives 2.68. float_format="%.2f" df.round(2) spreadsheet ROUND(x, 2) 0.005 0.01 0.00 0.01 0.015 0.01 0.02 0.02 1.005 1.00 1.00 1.01 2.675 2.67 2.67 2.68

The third column comes from recalculating =ROUND(A1, 2) in LibreOffice; Excel documents the same rule, rounding halves away from zero as the number appears in decimal. Neither pandas method matches it for every value.

Step 3 — A number format changes only the display

Setting a number format leaves the value alone and tells Excel how to show it. It is the only way to get a fixed number of decimals on screen:

Python
amounts.to_excel("formatted.xlsx", index=False)
wb = load_workbook("formatted.xlsx")
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, max_col=1):
    cell.number_format = "#,##0.00"
wb.save("formatted.xlsx")
print(stored("formatted.xlsx")[:3])
# [(0.12345, '#,##0.00'), (2.5, '#,##0.00'), (1234567.891, '#,##0.00')]

Excel shows 0.12, 2.50 and 1,234,567.89, and the cells still hold 0.12345, 2.5 and 1234567.891. That matters in two directions. Readers who build formulas on the sheet get full precision, which is usually right. But a column of displayed amounts can appear not to add up: three cells showing 0.33 sum to a total cell showing 1.00, because the underlying values are thirds. When the printed numbers must add up exactly, round the values as well as formatting them.

Step 4 — Round like a spreadsheet when totals must match

If figures must agree with an accounting system or with a colleague's ROUND formulas, round with the spreadsheet's rule. Decimal built from the value's shortest decimal representation, with ROUND_HALF_UP, gives the same answers as the ROUND column above:

Python
def round_like_excel(value, places=2):
    if pd.isna(value):
        return value
    step = Decimal(1).scaleb(-places)
    return float(Decimal(str(value)).quantize(step, rounding=ROUND_HALF_UP))

amounts["excel_round"] = amounts["amount"].map(round_like_excel)
print(amounts["excel_round"].tolist())
# [0.12, 2.5, 1234567.89, 0.01, 0.02, 1.01]

str(value) is the key step: it gives the short decimal a person would read ('1.005'), not the binary value underneath, so halves are treated as halves. ROUND_HALF_UP in decimal rounds away from zero for negatives too, which matches Excel. Match Excel Rounding in pandas covers ROUNDUP, ROUNDDOWN and MROUND as well.

Step 5 — Combine rounding with a format

The dependable pattern for a money column is both: round the values with the rule the business uses, then apply a number format so every cell shows the same number of decimals:

Python
report = pd.DataFrame({"item": ["A", "B", "C"], "amount": [10.005, 2.5, 7.125]})
report["amount"] = report["amount"].map(round_like_excel)
report.to_excel("report.xlsx", index=False)

wb = load_workbook("report.xlsx")
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = "#,##0.00"
wb.save("report.xlsx")
print([(c.value, c.number_format) for (c,) in ws.iter_rows(min_row=2, min_col=2, max_col=2)])

The stored values are exactly what the report claims, and the display is consistent. Format Excel Cells as Currency with Python extends this to currency symbols and negative numbers in brackets.

A money column that is both correct and consistent Three steps. Calculate at full precision. Round the values with the business rule, for example half up with Decimal. Write the rounded values and apply a #,##0.00 number format so every cell shows two decimals. Calculate full float precision Round the values business rule, e.g. half up Write rounded numbers Format the cells "#,##0.00" — 2.50 shows as 2.50

Step 6 — Check that rounded lines still add up

Rounding each line and rounding the total are different operations, and on real data they disagree by a cent or two. Three equal shares of 100 show the problem at its smallest:

Python
shares = pd.Series([100 / 3] * 3)
lines = shares.map(round_like_excel)
print(lines.tolist(), round(lines.sum(), 2), round_like_excel(shares.sum()))
# [33.33, 33.33, 33.33] 99.99 100.0

A report that prints the three lines and a total of 100.00 looks wrong to anyone who adds them up; one that prints 99.99 disagrees with the source. Decide which figure is authoritative. If the lines must sum to the total — an allocation, an invoice split — give the rounding difference to one line, usually the largest, and assert the result:

Python
target = round_like_excel(shares.sum())
i = lines.idxmax()
lines.iloc[i] = round_like_excel(lines.iloc[i] + (target - lines.sum()))
assert round(lines.sum(), 2) == target
print(lines.tolist())
# [33.34, 33.33, 33.33]

Re-rounding the adjusted line matters: adding a difference of 0.01 in floating point gives 33.339999999999996, the very noise this page is about. Writing the assertion into the job means a change in the data or the rounding rule that breaks the totals stops the run, instead of reaching a reader.

Which to use when

  • Analysis workbooks others will calculate with: keep full precision and use a number format only.
  • Printed or emailed figures that must add up: round the values (Step 4) and format them (Step 5).
  • Quick exports where exact halves do not matter: df.round(2) or float_format are fine — pick one and use it everywhere.
  • CSV output: float_format is the right tool there, because a CSV has no number formats; to_csv(float_format="%.2f") writes text with exactly two decimals.

Common pitfalls

  • Expecting float_format to show trailing zeros. It writes numbers; use a number format.
  • Formatting and assuming the value is rounded. The display says 0.12; the cell holds 0.12345.
  • Mixing rounding methods across scripts. Pick one rule per report.
  • Rounding before aggregating when the business rule says otherwise. Sum-then-round and round-then-sum differ; the choice is an accounting policy, not a style preference.

Performance notes

df.round and float_format are vectorised or applied during writing and cost nothing noticeable. The Decimal approach calls Python once per value — around a microsecond each — which is fine for report-sized data and worth replacing with vectorised rounding only for exports of many millions of values. Setting number formats with openpyxl is a per-cell pass; with the xlsxwriter engine you can instead set a column format once with worksheet.set_column.

Conclusion

float_format and df.round both change the stored values, with different rules for halves, and neither makes Excel show a fixed number of decimals. A number format fixes the display and leaves the value untouched. For money in reports, round the values with the rule your business uses — Decimal(str(x)) with ROUND_HALF_UP to match a spreadsheet's ROUND — and then apply a #,##0.00 format.

Frequently asked questions

Does float_format write text or numbers? Numbers. pandas formats each float with the pattern, then converts the result back to a float, so the cell holds the rounded number with the General format. Excel will still display 2.5 rather than 2.50.

Why do float_format and DataFrame.round give different results? float_format uses Python string formatting on the binary value, while DataFrame.round uses NumPy's round-half-to-even on a scaled value. For numbers near a half, such as 0.005 and 0.015, the two can land on different sides.

How do I show exactly two decimals, like 2.50? Apply a number format such as 0.00 or #,##0.00 to the cells. Only the format controls how many decimals are displayed; rounding the value cannot add a trailing zero.

How do I round the way Excel's ROUND does? Excel rounds halves away from zero based on the decimal value you see. In Python, convert each value with Decimal(str(value)) and quantize with ROUND_HALF_UP.

More on writing excel files

More on writing excel files →