Format Numbers as Percentages in Excel with Python

A percentage cell in Excel is a number with a display rule: the format multiplies the stored value by 100 and appends a sign. That single fact explains the most common bug in generated reports — a margin column computed as 12.5 and formatted as a percentage, which Excel dutifully shows as 1250.0%. Store the fraction, format the display, and everything works, including the arithmetic readers do on top. This guide covers the format strings, the conversion, coloured negatives, and writing percentages from pandas. It extends Applying Number and Date Formats in Excel.

Prerequisites

pip install openpyxl pandas xlsxwriter

Some rates to format, expressed as fractions:

import pandas as pd

margins = pd.DataFrame({
    "region": ["North", "South", "West", "East"],
    "revenue": [5150.00, 4268.50, 3511.25, 2980.10],
    "cost": [4120.00, 3841.65, 3862.38, 2384.08],
})
margins["margin"] = (margins["revenue"] - margins["cost"]) / margins["revenue"]
print(margins["margin"].round(4).tolist())
# [0.2, 0.1, -0.1, 0.2]

Computing the rate as a fraction is the natural result of a division, which is why the bug appears only when somebody adds a * 100 to "make it a percentage".

Step 1 — Set the format with openpyxl

from openpyxl import load_workbook

margins.to_excel("margins.xlsx", index=False)

wb = load_workbook("margins.xlsx")
ws = wb.active

for (cell,) in ws.iter_rows(min_row=2, min_col=4, max_col=4):
    cell.number_format = "0.0%"

ws.column_dimensions["D"].width = 12
wb.save("margins_formatted.xlsx")

The format strings you will use:

The zeros after the decimal point set the displayed precision only. The stored value is untouched, so a cell showing 12.5% may hold 0.1249876 — which matters when a reader sums a column and the total does not match the sum of what they can see. Round the values themselves when that reconciliation matters:

margins["margin"] = margins["margin"].round(3)      # display and value agree

Step 2 — Colour and sign the variance columns

A variance column reads far better with signed, coloured values. Excel's format string has up to four sections separated by semicolons — positive, negative, zero and text:

from openpyxl import load_workbook

wb = load_workbook("margins.xlsx")
ws = wb.active

# Positive in green with a plus, negative in red with a minus, zero plain.
VARIANCE = '[Color10]+0.0%;[Red]-0.0%;0.0%'

for (cell,) in ws.iter_rows(min_row=2, min_col=4, max_col=4):
    cell.number_format = VARIANCE

wb.save("margins_variance.xlsx")

Two notes on the colour syntax. Only a small set of named colours works — [Red], [Blue], [Green], [Black], [White], [Cyan], [Magenta], [Yellow] — and beyond those you use [ColorN] with an index into Excel's palette. Number-format colours are also independent of the font colour: a cell whose font is set to grey still renders red under [Red], because the format wins.

The negative section carries its own sign, which is why it reads -0.0% rather than 0.0%. Omitting the minus produces negatives that display as positives — a subtle and expensive formatting bug in a variance report.

Step 3 — Convert when the source is already multiplied

Data arriving from elsewhere is often already in percentage points. Convert on import, and be explicit about which convention each column uses.

import pandas as pd

def to_fraction(series, already_multiplied=None, sample=200):
    """Return a rate column as fractions, converting if it is in percent points."""
    numbers = pd.to_numeric(series, errors="coerce")

    if already_multiplied is None:
        head = numbers.dropna().head(sample)
        # A column of fractions rarely exceeds 1 in absolute value.
        already_multiplied = bool(len(head)) and (head.abs() > 1).mean() > 0.5

    return numbers / 100 if already_multiplied else numbers

The heuristic is a convenience, not a decision procedure. A column of small rates — churn of 0.4%, a fee of 0.25% — sits entirely below 1 either way, and guessing wrong is a hundredfold error in a published number. Pass already_multiplied explicitly whenever you know, and treat the heuristic as something to confirm:

rates = pd.read_excel("rates.xlsx")
rates["churn"] = to_fraction(rates["churn"], already_multiplied=True)

Text percentages need the sign stripping first, which is the same cleaning path as any other formatted number:

import pandas as pd

def parse_percent_text(series):
    """Turn '12.5%' into 0.125."""
    text = series.astype("string").str.strip()
    is_percent = text.str.endswith("%", na=False)
    numbers = pd.to_numeric(text.str.rstrip("%"), errors="coerce")
    return numbers.where(~is_percent, numbers / 100)

The wider treatment is in converting Excel text columns to numbers.

Step 4 — Write percentages from pandas

Through ExcelWriter, set the format per column rather than per cell:

import pandas as pd

def write_with_percentages(df, path, percent_columns, sheet_name="Report"):
    """Write a report, formatting the named columns as percentages."""
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
        book, sheet = writer.book, writer.sheets[sheet_name]

        header = book.add_format({"bold": True, "bg_color": "#EEF2FF",
                                  "border": 1, "align": "center"})
        money = book.add_format({"num_format": "#,##0.00"})
        percent = book.add_format({"num_format": "0.0%"})
        variance = book.add_format({"num_format": '[Color10]+0.0%;[Red]-0.0%;0.0%'})

        for position, name in enumerate(df.columns):
            sheet.write(0, position, str(name), header)

            if name in percent_columns:
                fmt = variance if percent_columns[name] == "variance" else percent
                sheet.set_column(position, position, 13, fmt)
            elif pd.api.types.is_numeric_dtype(df[name]):
                sheet.set_column(position, position, 14, money)
            else:
                sheet.set_column(position, position, 16)

        sheet.freeze_panes(1, 0)
    return path

write_with_percentages(margins, "margins.xlsx",
                       percent_columns={"margin": "variance"})

Widths matter more for percentages than for most columns, because ##### appears the moment a formatted value does not fit — and a signed, coloured percentage is wider than the bare number suggests. The sizing helper in auto-fitting column widths sizes from the format string for exactly this reason.

Step 5 — Basis points and other scaled units

There is no basis-point format. A basis point is a hundredth of a percent, so scale by ten thousand and use a plain number format with a literal suffix:

from openpyxl import load_workbook

wb = load_workbook("rates.xlsx")
ws = wb.active

for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    if isinstance(cell.value, (int, float)):
        cell.value = cell.value * 10_000        # 0.0125 -> 125
        cell.number_format = '0" bp"'           # displays: 125 bp

wb.save("rates_bp.xlsx")

Note the quoted literal in the format — 0" bp" appends the text without affecting the value. Using 0.0% here would rescale by a further hundred, which is the same trap in a different unit.

The general rule holds across every scaled unit: the value in the cell should be in the unit the format expects. A percentage format expects a fraction; a 0" bp" format expects the already-scaled basis-point number; a thousands format like #,##0, expects the raw value and divides for display.

Common pitfalls and fixes

Performance and scale notes

Number formats are style entries, so the guidance is the same as for any other formatting: assign one format object to many cells rather than constructing one per cell, and prefer a whole-column call where the engine offers it.

import pandas as pd

# Constant cost, whatever the row count.
with pd.ExcelWriter("big.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Rates", index=False)
    fmt = writer.book.add_format({"num_format": "0.0%"})
    writer.sheets["Rates"].set_column("D:D", 13, fmt)

Two arithmetic points worth knowing at scale. Compute the rate once, vectorised — a division over a column is a single pass, where a per-row loop is two orders of magnitude slower:

# Fast
df["margin"] = (df["revenue"] - df["cost"]) / df["revenue"]

# Slow, and it also fails on a zero denominator
df["margin"] = df.apply(lambda r: (r["revenue"] - r["cost"]) / r["revenue"], axis=1)

Guard the denominator. A zero revenue produces inf rather than an exception, and inf formatted as a percentage displays as #DIV/0!-looking noise that readers report as a bug:

import numpy as np

df["margin"] = np.where(
    df["revenue"] != 0,
    (df["revenue"] - df["cost"]) / df["revenue"].replace(0, np.nan),
    np.nan,
)

Leaving those rows as NaN writes an empty cell, which reads correctly as "not applicable" — far better than a spurious zero, and consistent with the missing-value handling in finding and reporting missing values.

Conclusion

A percentage format is a display multiplier, so the cell must hold the fraction — 0.125, not 12.5. Set 0.0% for the everyday case, and use the multi-section form [Color10]+0.0%;[Red]-0.0%;0.0% for variance columns so sign and direction read at a glance. Convert incoming percentage-point columns explicitly rather than relying on a heuristic, because a column of small rates is genuinely ambiguous and the error is a factor of a hundred. Round the values when readers will sum them, widen the column so a signed percentage does not turn into #####, and remember that basis points need a scaled value with a literal suffix rather than a percent sign.

Frequently asked questions

Why does my 12.5 display as 1250.0%? A percentage number format multiplies the stored value by 100 for display. Store the fraction — 0.125 — and the cell shows 12.5%. Storing the already-multiplied number multiplies it again.

Which format string should I use?"0.0%" for one decimal place, "0%" for whole percentages, and "0.00%" for two. The number of zeros after the point sets the displayed precision; the value itself is never rounded.

How do I show negatives in red with a sign? Use a two-section format separated by a semicolon, such as "0.0%;[Red]-0.0%". The first section formats positives and zero, the second formats negatives.

Should percentages be stored as fractions everywhere? In the spreadsheet, yes — Excel's percentage formatting depends on it, and any arithmetic in the sheet works correctly with fractions. Convert to a display number only when writing to a system that expects 12.5.

What about basis points? There is no built-in basis-point format. Multiply the fraction by ten thousand and use a plain number format with a "bp" suffix, such as 0" bp", since the percent sign would rescale it again.

Related

• Up to the parent: Applying Number and Date Formats in Excel — the wider format-string vocabulary.
• Format Excel Cells as Currency with Python — the same mechanism applied to money.
• Convert Excel Text Columns to Numbers with pandas — parsing "12.5%" on the way in.
• Auto-Fit Column Widths When Writing with pandas — sizing so percentages do not show as #####.
• Apply a Reusable Style Theme Across an Excel Report — where a percent role belongs.