Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Show Negative Numbers in Red with Parentheses in Excel

Format negatives the way finance readers expect — (1,235) in red, aligned with the positives above them — from Python with openpyxl or XlsxWriter. Covers the _) alignment trick, zero as a dash, currency and accounting layouts, reversed colours for costs, the "(0)" trap and parsing parentheses back into numbers.
Number & date formatsopenpyxl

In financial reports a negative number is written in parentheses, often in red: (1,235) rather than -1,235. The convention exists because a minus sign is easy to miss in a column of figures, while brackets and colour are not — and brackets still work when the report is printed in black and white. Excel produces this with a number format, so the cell still holds −1234.5 for every formula. This guide, part of Applying Number and Date Formats in Excel, builds the formats from Python, explains the alignment characters that make them look right, and covers the edge cases. The general structure of format codes is covered in Write Custom Number Format Codes in Excel with Python.

A variance column with parenthesised negatives Revenue variance values of 1,235, negative 1,235, zero and 48,200 are displayed as 1,235, a red (1,235), a dash and 48,200. The positives carry a trailing space so their last digit lines up with the last digit of the negatives inside the parentheses. A B C 1 Line stored displayed 2 North 1234.5 1,235␠ 3 South -1234.5 (1,235) 4 East 0 –␠ 5 West 48200 48,200␠ the ␠ marks the space that _) reserves for the missing parenthesis

Prerequisites

Bash
pip install openpyxl pandas xlsxwriter

Step 1 — The two-section format

A number format has up to four sections separated by semicolons — positive; negative; zero; text. For red parenthesised negatives you need the first two:

Python
from openpyxl import Workbook

RED_PARENS = "#,##0_);[Red](#,##0)"

wb = Workbook()
ws = wb.active
ws.append(["Line", "Variance"])
for line, value in [("North", 1234.5), ("South", -1234.5), ("East", 0), ("West", 48200)]:
    ws.append([line, value])
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = RED_PARENS
wb.save("variance.xlsx")
print(ws["B3"].value, ws["B3"].number_format)       # -1234.5 #,##0_);[Red](#,##0)

The negative section supplies its own parentheses, so no minus sign appears — the section is used because the value is negative, and the sign is implied. [Red] at the start of the section colours it. The stored value stays −1234.5; rendering the file gives 1,235, (1,235) in red, 0 and 48,200.

Step 2 — Why the _) matters

_) in the positive section inserts a space exactly as wide as a closing parenthesis. Without it, the last digit of 1,235 sits under the ) of (1,235) instead of under its last digit, and a column of mixed signs looks ragged:

Python
for fmt in ["#,##0;[Red](#,##0)", RED_PARENS]:
    print(f"{fmt!r:26} positive ends with a pad: {fmt.split(';')[0].endswith('_)')}")

Exporting the rendered sheet as text shows the effect directly: with _), the positive 1,235 is followed by a space, so it right-aligns with (1,235). The pad is invisible on screen, which is why it is so often left out — the ragged edge only becomes obvious once a long column mixes positive and negative values. The underscore works with any character — _- reserves the width of a minus sign — which is how Excel's own accounting formats keep columns straight.

Positive numbers with and without the _) pad Without the pad, positive numbers end one character further right than the last digit of parenthesised negatives, so digits do not line up. With the _) pad, positives end with a space the width of a parenthesis and every digit column aligns. #,##0;[Red](#,##0) 1,235 ends under the ) digit columns drift looks ragged in long lists #,##0_);[Red](#,##0) 1,235 ends under the 5 digits line up Excel's own built-ins do this

Step 3 — Zeros, currency and the accounting layout

Add a third section for zero — a dash reads better than 0 in financial tables — and put currency inside the format so it sits inside the parentheses too:

Python
FORMATS = {
    "with dash for zero": '#,##0_);[Red](#,##0);"–"_)',
    "currency, 2 dp": '"€"#,##0.00_);[Red]("€"#,##0.00)',
    "accounting": '_("€"* #,##0.00_);_("€"* (#,##0.00);_("€"* "-"??_);_(@_)',
}
ws2 = wb.create_sheet("Formats")
ws2.append(["value", *FORMATS])
for value in [1234.5, -1234.5, 0]:
    ws2.append([value, *[value] * len(FORMATS)])
for row in ws2.iter_rows(min_row=2):
    for cell, fmt in zip(row[1:], FORMATS.values()):
        cell.number_format = fmt
wb.save("variance.xlsx")

The accounting layout adds two more tricks: * repeats a space to fill the cell, which pushes the currency symbol to the left edge while the number stays right-aligned, and the fourth section _(@_) pads text so labels in the same column line up too. openpyxl also knows Excel's built-in versions by number — BUILTIN_FORMATS[38] is #,##0_);[Red](#,##0) and [40] the two-decimal variant — which Excel shows under their localised names in the Format Cells dialog.

Step 4 — When negative is good

For a cost variance, spending less than budget is good, so the colours should be reversed: overspend in red, underspend in green. Colours are per section, so swap them:

Python
COST = '[Red]#,##0_);[Color10](#,##0);"–"_)'
ws["C1"] = "Cost variance"
for row, value in zip(range(2, 6), [1200, -800, 0, 300]):
    ws[f"C{row}"] = value
    ws[f"C{row}"].number_format = COST
wb.save("variance.xlsx")

[Color10] is green in Excel's default palette; the named colours are limited to [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White] and [Yellow], and the bright [Green] is hard to read on white. When the exact shade matters — a brand red such as #B91C1C — use conditional formatting for the colour and keep the number format for the parentheses; see Highlight Cells Above a Threshold with openpyxl.

Step 5 — The "(0)" trap

A value of −0.4 is negative, so it uses the negative section, and with no decimals it displays as (0) — a red zero in brackets that looks like an error. It appears whenever small negative remainders survive a calculation:

Python
ws["B7"] = -0.4
ws["B7"].number_format = RED_PARENS                  # displays (0)
cleaned = round(-0.4) + 0.0                          # round in Python before writing
print(cleaned)                                       # 0.0

Round the stored values to the precision you display before writing — adding 0.0 turns Python's negative zero into a plain zero — or show one more decimal so (0.4) is honest. Rounding that matches Excel's rules is covered in Match Excel Rounding in pandas.

Choosing the negative format For general financial figures use red parentheses with the _) pad. For printed or colour-blind-friendly reports keep parentheses without relying on colour. For costs where negative is good, reverse the colours. When an exact brand colour is needed, use conditional formatting for colour and the number format for parentheses. What should a negative number communicate? bad, general figures _);[Red](…) finance default printed or colour-blind parentheses only _);(…) good (costs) reversed colours [Red]…;[Color10](…) exact colour needed conditional format + parentheses

Step 6 — From pandas with XlsxWriter, and back again

With XlsxWriter, create the format once and apply it to whole columns:

Python
import pandas as pd

df = pd.DataFrame({"Line": ["North", "South", "East"], "Variance": [1234.5, -1234.5, 0]})
with pd.ExcelWriter("variance_pd.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Variance", index=False)
    fmt = writer.book.add_format({"num_format": '#,##0_);[Red](#,##0);"–"_)'})
    writer.sheets["Variance"].set_column("B:B", 12, fmt)

text = pd.Series(["1,235", "(1,235)", "–", "(48,200.50)"])
parsed = pd.to_numeric(text.str.replace(",", "").str.replace(r"^\((.*)\)$", r"-\1", regex=True)
                       .replace("–", "0"), errors="coerce")
print(parsed.tolist())                               # [1235.0, -1235.0, 0.0, -48200.5]

The second half handles the reverse problem: exports and PDFs converted to Excel often contain the displayed text, (1,235), rather than the number. Strip separators, turn surrounding parentheses into a leading minus and convert. More on text that should be numbers in Convert Excel Text Columns to Numbers with pandas.

Step 7 — One named style for the whole report

A report with a dozen variance columns should not repeat the format string a dozen times. Register it once as a named style; every cell that uses the style shares the definition, and it appears in Excel's Cell Styles gallery so readers can apply it to their own additions:

Python
from openpyxl.styles import Alignment, NamedStyle

variance_style = NamedStyle(name="Variance", number_format='#,##0_);[Red](#,##0);"–"_)',
                            alignment=Alignment(horizontal="right"))
wb.add_named_style(variance_style)
for (cell,) in ws.iter_rows(min_row=2, max_row=5, min_col=2, max_col=2):
    cell.style = "Variance"
wb.save("variance.xlsx")
print(ws["B3"].style, ws["B3"].number_format)       # Variance #,##0_);[Red](#,##0);"–"_)

Changing the variance convention later — adding a decimal, switching to accounting layout — is then a one-line edit in the script that builds the style. Named styles and the alternative of copying styles between cells are compared in Copy Cell Styles from One Cell to Another with openpyxl.

Common pitfalls

  • A minus sign inside the negative section. #,##0;[Red](-#,##0) shows (-1,235) — the section already implies the sign.
  • Formats on text. A number stored as text ignores its number format entirely. Write real numbers.
  • Totals that change sign. A total row using the same format flips to red parentheses when the sum goes negative — which is correct, but readers scanning for red may take a small negative total for an error. Label total rows clearly.
  • Different conventions per team. Some teams expect a leading minus, others parentheses. Agree the convention for a report pack once and apply it through one named style.
  • Relying on colour alone. Red and green are indistinguishable for many readers and vanish in print. Parentheses carry the meaning; colour reinforces it.

Conclusion

Red parenthesised negatives are a number format, not a change to the data: #,##0_);[Red](#,##0) with _) padding the positives so every digit lines up. Add a dash for zero, put currency symbols inside the format, reverse the colours where negative is good, and round values before writing so small negatives do not show as (0). When text like (1,235) arrives from elsewhere, convert it back to a signed number before calculating.

Frequently asked questions

What number format shows negatives in red parentheses? Use a two-section number format: #,##0_); for positives, then Red followed by (#,##0) for negatives, written together with no space. Red colours the text and the parentheses replace the minus sign; the _) in the first section leaves room so positives line up with negatives. Add .00 in both sections for two decimals.

What does _) do in a number format? It adds a space as wide as a closing parenthesis after positive numbers, so positives line up with negatives that end in ")". Without it, the digits of positive and negative numbers are offset by one character.

Why does a small negative number show as (0)? A value such as -0.4 is negative, so it uses the negative section, and rounding to whole numbers displays 0 inside parentheses. Round the stored values before writing, or show a decimal.

How do I turn "(1,234)" text back into a number in Python? Strip thousands separators, replace the surrounding parentheses with a leading minus sign, and convert with pd.to_numeric. A regular expression such as ^((.*))$ captures the inner value.

More on number & date formats

More on number & date formats →