Format Excel Cells as Currency with Python

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

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.

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.

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.

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.

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. This colouring is baked into the number format and reacts only to a value's sign. For rules driven by thresholds — flag any balance below zero, or shade the largest amounts — reach for conditional formatting on a range instead, which evaluates the value rather than just its sign.

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.

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

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.

Performance and scale notes

Setting number_format is a cheap assignment, but on large exports the per-cell loop adds up. A few things worth knowing when a report runs to tens of thousands of rows:

• Assigning the format string on every cell is fine for most reports. openpyxl deduplicates identical format codes internally, so looping cell.number_format = "$#,##0.00" over 50,000 cells does not store 50,000 copies of the string — the memory cost is small. The time cost is the Python loop itself.
• Reuse a NamedStyle when the same money look repeats across sheets. Register the code once and apply the style by name; it keeps the code, font, and alignment together and reads more cleanly than repeating the raw string. Assign the style to each cell in the loop rather than rebuilding a fresh style object per cell.

from openpyxl import Workbook
from openpyxl.styles import NamedStyle

money = NamedStyle(name="money", number_format="$#,##0.00")

wb = Workbook()
ws = wb.active
for row in range(1, 50001):
    ws.cell(row=row, column=1, value=row * 1.5).style = money

wb.save("currency_scale.xlsx")

• write_only mode does not support cell.number_format. The streaming writer used for very large files needs the format attached to a WriteOnlyCell (with a style) as you append rows — you cannot go back and set the format after the fact. If you are already in a two-pass pipeline (write with pandas, reopen to format), the reopen-and-loop pattern from Step 5 is simpler.
• Formatting is per cell, not per column. Excel has no "the whole column is currency" flag that openpyxl writes; the format lives on each cell you touch. Rows you add later are unformatted until you set the code on them too. For a formatted pivot table exported from pandas, apply the money code after the pivot's shape is known so you cover every data cell.

Symbol, separator, decimals

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.

Related

• Applying Number and Date Formats in Excel — up to the parent guide: separators, percentages, and the format-code reference.
• Format Dates in Excel Cells with Python — the date-formatting companion, which turns on the same display-versus-stored-value distinction.
• Apply Conditional Formatting to a Range with openpyxl — when you need value-driven rules rather than the sign-based [Red] colouring.
• Set Column Width and Row Height in openpyxl — widen the column so formatted amounts never collapse to #######.
• Write a Pandas DataFrame to Excel Without the Index — keep your money column where you expect it after export.