Format Excel Cells as Currency with Python

Format Excel cells as currency with openpyxl: set $#,##0.00, apply it across a column, use €/£ symbols, accounting parentheses for negatives, and post-process pandas output.

A column of bare floats like 1234.5 reads as raw data, not money. Currency formatting adds the symbol, thousands separators, and a fixed two decimals — without changing the stored number. This guide, part of Applying Number and Date Formats in Excel, walks through formatting single cells, whole columns, foreign symbols, accounting negatives, and a pandas-exported column reopened with openpyxl. Every snippet is runnable.

Prerequisites and install

Bash

pip install openpyxl

For the pandas section: pip install pandas openpyxl. You need a working Python 3.8+ and the ability to write a file in the current directory. Nothing else — openpyxl does not need Excel installed.

Step 1: Format a single cell as currency

Assign the format code $#,##0.00 to cell.number_format. The $ is a literal symbol, #,##0 groups thousands, and .00 forces two decimals. The value must be numeric for the format to apply.

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 1234.5
ws["A1"].number_format = "$#,##0.00"   # displays $1,234.50

print("Stored:", ws["A1"].value)        # 1234.5 — unchanged
wb.save("currency_single.xlsx")

Excel renders $1,234.50, but cell.value is still 1234.5. The format is purely visual.

Step 2: Apply currency down a column

Reports format an entire column of amounts. Loop the column's cells and set the same code on each, skipping the header. ws["C"] yields every cell in column C; [1:] drops the header cell.

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Date", "Region", "Revenue"])
ws.append(["2024-01-05", "North", 23990.5])
ws.append(["2024-01-06", "South", 12475.0])
ws.append(["2024-01-07", "West", 1599.2])

for cell in ws["C"][1:]:               # column C, skip header
    cell.number_format = "$#,##0.00"

wb.save("currency_column.xlsx")
print("Formatted", ws.max_row - 1, "amounts")

To format an arbitrary range instead of a full column, iterate ws.iter_rows(min_row=2, min_col=3, max_col=3) and set the format on each cell.

Step 3: Other currency symbols (€, £) and locale notes

Swap the literal symbol in the format string. For currencies that trail the amount, put the symbol after the number with a non-breaking space.

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Currency", "Amount"])
ws.append(["USD", 1234.5])
ws.append(["EUR", 1234.5])
ws.append(["GBP", 1234.5])

ws["B2"].number_format = "$#,##0.00"      # $1,234.50
ws["B3"].number_format = "€#,##0.00"      # €1,234.50
ws["B4"].number_format = "£#,##0.00"      # £1,234.50

wb.save("currency_symbols.xlsx")
print("Applied $, €, and £ formats")

The symbol is just text inside the format string, so any Unicode currency glyph works. Note that the format does not convert exchange rates or follow a system locale — it only displays the symbol you type. If you need European-style separators (. for thousands, , for decimals), that depends on the reader's Excel regional settings; the stored format code uses , and . as the structural placeholders regardless.

Step 4: Accounting format with parentheses for negatives

Accounting layouts show losses in parentheses and often align the symbol to the left. Use a two-section code separated by a semicolon: positive section, then negative section. Add [Red] to color negatives.

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Account", "Balance"])
ws.append(["Operating", 8200.40])
ws.append(["Overdraft", -1530.75])

code = "$#,##0.00;[Red]($#,##0.00)"
for cell in ws["B"][1:]:
    cell.number_format = code

wb.save("currency_accounting.xlsx")
print("8200.40 → $8,200.40 ; -1530.75 → red ($1,530.75)")

The positive value shows as $8,200.40; the negative shows as a red ($1,530.75). The stored value of the second cell is still -1530.75, so totals compute correctly.

Step 5: Format a pandas-exported column

df.to_excel() writes values but no currency formatting. The clean pattern is: export with pandas, reopen the file with openpyxl, format the money column, and save. This keeps pandas for the data and openpyxl for presentation.

Python

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({
    "Region": ["North", "South", "West"],
    "Revenue": [23990.5, 12475.0, 1599.2],
})
df.to_excel("pandas_report.xlsx", index=False, sheet_name="Sales")

wb = load_workbook("pandas_report.xlsx")
ws = wb["Sales"]
for cell in ws["B"][1:]:               # Revenue is column B; header in row 1
    cell.number_format = "$#,##0.00"

wb.save("pandas_report.xlsx")
print("Reopened pandas output and formatted the Revenue column")

Exporting without the index (here index=False) keeps the money column at B; see Write a Pandas DataFrame to Excel Without the Index for why the index column shifts everything right if you leave it in.

Common pitfalls

Symptom	Cause	Fix
Format appears to do nothing	The cell holds text, e.g. `"1234.5"`, not a number	Write a real `float`/`int`, or convert with `float(value)` before assigning
Value changed when I formatted it	It did not — you are reading the rendered display	`cell.value` still returns the raw number; the format is display-only
Negatives show a minus, not parentheses	Single-section format code	Add a negative section: `$#,##0.00;($#,##0.00)`
Symbol missing after a pandas export	`to_excel` writes no formatting	Reopen with `openpyxl` and set `number_format`
Thousands separator absent	Used `$0.00` instead of `$#,##0.00`	Include the `#,##0` grouping in the code

A note on the underlying value

Currency formatting never rounds or alters the stored number. A cell showing $1,234.50 may hold 1234.4999; the display rounds, the value does not. If you need the value itself rounded, do it in Python with round(value, 2) before writing — formatting alone will not change what =SUM() or a later read returns.

Frequently asked questions

Why does my currency format have no effect?

The cell contains a string, not a number. Currency codes only format numeric values. Convert the value to float or int before assigning it to the cell.

Does formatting as currency change the stored amount?

No. The number is untouched. Excel rounds the display to two decimals, but cell.value and all formulas use the full-precision number.

How do I format an entire column at once?

Loop for cell in ws["B"][1:]: and set cell.number_format on each cell, skipping the header. There is no single "format this column" call; you assign the code per cell.

Can I show negative amounts in red parentheses?

Yes. Use a two-section code like $#,##0.00;[Red]($#,##0.00). The part after the semicolon styles negatives.

How do I format money in a pandas export?

Write the DataFrame with to_excel, then reopen the file with openpyxl.load_workbook, loop the money column, set number_format, and save again.

Conclusion

Currency formatting in openpyxl is one assignment: cell.number_format = "$#,##0.00". Loop a column to format a report, swap the literal symbol for € or £, and add a negative section for accounting parentheses. The value never changes — so reopen pandas output and format freely, knowing your totals and exports still see the real numbers.

Where to go next

Applying Number and Date Formats in Excel — the parent cluster: separators, percentages, and format-code reference.
Format Dates in Excel Cells with Python — the date-formatting companion to this guide.
Write a Pandas DataFrame to Excel Without the Index — keep your money column where you expect it after export.