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

Format Numbers in Thousands and Millions in Excel with Python

Show 1,234,567 as 1,235K or 1.2M in an Excel report without changing the stored value — scaling commas in number formats, units in headers or suffixes, auto-switching K and M with conditional sections, negatives and small values, and totals that seem not to add up.
Number & date formatsopenpyxl

A finance pack with revenue written as 1,234,567.00 and 987,654.00 is hard to scan; the same figures as 1.2M and 1.0M, or as 1,235 and 988 under a heading "€ thousands", are read in a glance. Excel can do this purely in the display — the cell keeps 1,234,567 for every formula — through a detail of number formats: commas after the last digit scale the value. This guide, part of Applying Number and Date Formats in Excel, applies thousands and millions formats from Python and covers the places they surprise people: negatives, small values and totals. The general format-code syntax is covered in Write Custom Number Format Codes in Excel with Python.

The same values under three formats With the format #,##0,"K" #,##0.0,,"M" auto K/M 1,234,567 1,235K 1.2M 1.2M 987,654 988K 1.0M 988K 45,210 45K 0.0M 45K 812 1K 0.0M 812

Prerequisites

Bash
pip install openpyxl pandas xlsxwriter

Step 1 — Scale with trailing commas

A comma between digit placeholders is the thousands separator; a comma after the last placeholder divides the display by 1,000. Two commas divide by a million:

Python
from openpyxl import Workbook

values = [1234567, 987654, 45210, 812, -1234567]
wb = Workbook()
ws = wb.active
ws.append(["Revenue", "In K", "In M"])
for v in values:
    ws.append([v, v, v])
for row in ws.iter_rows(min_row=2):
    row[0].number_format = "#,##0"
    row[1].number_format = '#,##0,"K"'
    row[2].number_format = '#,##0.0,,"M"'
wb.save("scaled.xlsx")
print(ws["B2"].value, ws["B2"].number_format)       # 1234567 #,##0,"K"

Rendered, row 2 reads 1,234,567 — 1,235K — 1.2M, and the stored value in every cell is still 1234567. The text after the number must be quoted; a bare K would be read as part of the code. Because the value is untouched, SUM, charts and pivot tables all use the full figure.

Step 2 — Put the unit in the header or in the cell

There are two conventions, and a report should use one. A suffix in every cell — 1.2M — is self-explanatory when figures are quoted out of context. A unit in the header — "Revenue (€ thousands)" with plain #,##0, in the cells — is the finance convention: cleaner columns, and the unit is stated once:

Python
ws2 = wb.create_sheet("Pack")
ws2.append(["Region", "Revenue (€ thousands)"])
for region, value in [("North", 1234567), ("South", 987654), ("East", 45210)]:
    ws2.append([region, value])
for (cell,) in ws2.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = "#,##0,"
wb.save("scaled.xlsx")

Whichever you choose, keep it consistent across sheets: a pack where one table is in thousands and the next in millions, both unlabelled, invites a factor-of-a-thousand misreading.

Unit in every cell or unit in the header A suffix in every cell, such as 1.2M, is clear when a single figure is copied elsewhere but adds clutter to dense tables. A unit in the header, such as Revenue in euro thousands with plain scaled numbers, keeps tables clean and is the usual finance convention, but a copied number loses its unit. Suffix: 1.2M each figure explains itself good for dashboards and cards clutter in dense tables one unit per cell Header: (€ thousands) clean columns finance convention copied figures lose the unit label every table

Step 3 — Auto-switching between K and M

Number formats can have conditions in square brackets, which lets one format show large values in millions and smaller ones in thousands:

Python
AUTO = '[>=1000000]#,##0.0,,"M";[>=1000]#,##0,"K";0'
ws3 = wb.create_sheet("Auto")
for v in [1234567, 987654, 45210, 812]:
    ws3.append([v])
for (cell,) in ws3.iter_rows():
    cell.number_format = AUTO
wb.save("scaled.xlsx")

The four values display as 1.2M, 988K, 45K and 812. The catch is that a format allows at most two conditions plus a final section, and the final section catches everything else — including negative numbers. Rendered in LibreOffice, −1,234,567 falls into that last 0 section and shows as an unscaled 1234567, with no sign. Use auto-switching only for columns that cannot be negative, such as counts and gross sales; for anything that can go below zero, pick one scale per column.

Step 4 — Negatives and small values

With a single scale, a two-section format handles negatives explicitly — here in red parentheses, the finance convention:

Python
SIGNED_M = '#,##0.0,,"M";[Red](#,##0.0,,"M")'
for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    cell.number_format = SIGNED_M
wb.save("scaled.xlsx")

−1,234,567 now reads (1.2M) in red. The other trap is at the small end: 45,210 in millions with one decimal is 0.0M, and 812 is 0.0M too — the figure is not zero, it is just too small for the scale. If a column mixes magnitudes that widely, show it in thousands, add a decimal, or split the small items into an "other" line. Red parentheses and their variants are covered in detail in Show Negative Numbers in Red with Parentheses in Excel.

Step 5 — The total that does not add up

Scaled display rounds each cell independently, while a SUM works on the full values. Two cells of 1,449,000 display as 1.4M each, and their total of 2,898,000 displays as 2.9M. Readers notice:

Python
ws4 = wb.create_sheet("Totals")
ws4.append(["Item", "Amount"])
ws4.append(["A", 1449000])
ws4.append(["B", 1449000])
ws4.append(["Total", "=SUM(B2:B3)"])
for (cell,) in ws4.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = '#,##0.0,,"M"'
wb.save("scaled.xlsx")
print(round(1449000 / 1e6, 1) * 2, round(2898000 / 1e6, 1))   # 2.8 2.9

There are three honest responses. Add a footnote — "figures may not add due to rounding" — which is standard in published accounts. Show one more decimal, which usually makes the effect disappear. Or, when the report must add up visually, round the stored values in Python before writing and sum those — accepting that the total now differs slightly from the true one. The last option changes the data, so choose it deliberately; rounding rules are covered in Match Excel Rounding in pandas.

Rounded parts and an unrounded total Items A and B each hold 1,449,000 and display as 1.4M. The total formula sums the full values, 2,898,000, and displays 2.9M, so the displayed parts appear to add up to 2.8M while the total says 2.9M. A B C 1 Item Amount stored 2 A 1.4M 1,449,000 3 B 1.4M 1,449,000 4 Total 2.9M 2,898,000 the total is right; the parts are rounded for display

Step 6 — The same formats from pandas with XlsxWriter

When the report is written from a DataFrame, set the format per column with XlsxWriter's set_column, which applies it without touching each cell:

Python
import pandas as pd

df = pd.DataFrame({"Region": ["North", "South", "East"], "Revenue": [1234567, 987654, 45210],
                   "Change": [150300, -48200, 2100]})
with pd.ExcelWriter("pack.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Pack", index=False)
    book, sheet = writer.book, writer.sheets["Pack"]
    sheet.set_column("B:B", 14, book.add_format({"num_format": '#,##0,"K"'}))
    sheet.set_column("C:C", 14, book.add_format({"num_format": '#,##0,"K";[Red](#,##0,"K")'}))
print(pd.read_excel("pack.xlsx")["Revenue"].tolist())      # [1234567, 987654, 45210]

Reading the file back returns the full values, confirming that only the display changed. To check what readers will actually see, export the displayed text — covered in Read Cell Values as Displayed in Excel with Python.

Step 7 — Check what readers will see

Format codes are easy to get subtly wrong, and openpyxl only stores them — it never shows the result. LibreOffice can export every sheet as CSV with the text as displayed, which turns "does it look right?" into something a script can check:

Python
import subprocess

subprocess.run(["soffice", "--headless", "--convert-to",
                "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true,false,false,-1",
                "scaled.xlsx"], check=True, capture_output=True, timeout=120)
print(open("scaled-Totals.csv").read())
# Item,Amount
# A,1.4M
# B,1.4M
# Total,2.9M

The long filter string asks for comma-separated output of the formatted text, one file per sheet (the final -1). Every value shown in this guide was checked this way. Put a few such assertions in the report's tests — "revenue for North shows as 1,235" — and a format change that breaks the display fails the build instead of reaching readers.

Common pitfalls

  • Dividing in Python. Writing value / 1000 changes the data; every formula that uses the cell is then off by a factor of a thousand. Scale in the format.
  • Locale expectations. Format codes are stored with a comma as the thousands separator and a point as the decimal. Excel displays them in the reader's locale, so a German reader sees 1.234K — the code itself should not be localised.
  • Charts inherit the format. A chart built from a scaled column shows axis labels in the same format when it links to the source format, which is usually what you want; if the axis shows full numbers, set the axis number format to match.
  • Scaling percentages or ratios. Trailing commas apply to any number; putting a thousands format on a ratio column shows 0K everywhere.

Conclusion

Trailing commas in a number format scale the display by thousands or millions while the cell keeps its full value. Choose one convention — suffix in the cell or unit in the header — use two-section formats for columns that can be negative, keep auto-switching K/M formats for positive-only data, watch for small values that round to zero, and explain or eliminate totals that do not add up visually.

Frequently asked questions

How do I show numbers in thousands in Excel without dividing them? Add a comma after the last digit placeholder in the number format, such as #,##0,. Each trailing comma divides the displayed value by 1,000 while the cell keeps the full number for formulas.

How do I show millions with one decimal and an M? Use #,##0.0,,"M". Two trailing commas divide by a million, .0 keeps one decimal and the quoted M is shown as text after the number.

Can one format switch between K and M automatically? Yes, with conditional sections such as >=1000000#,##0.0,,"M";>=1000#,##0,"K";0, but only for positive numbers — a format allows two conditions, so there is no section left for negatives. Use one scale per column when values can be negative.

Why don't the displayed values add up to the displayed total? Each cell is rounded for display on its own, while the total is calculated from the unrounded values. 1.4M plus 1.4M can show a total of 2.9M. Say so in a note, or round the stored values if the report must add up visually.

More on number & date formats

More on number & date formats →