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

Show Units Like kg or Hours with Number Formats

Display units such as kg, t, h, °C, m² or pcs next to numbers in an Excel report while the cells stay real numbers that sum, sort and chart: number format codes with quoted unit text, a column-to-unit mapping applied from pandas, kilograms shown as tonnes with the thousands scaling comma, singular and plural with conditions, and the displayed text checked in LibreOffice.
Number & date formats

A column headed Weight with values like 1,240.5 leaves the reader to guess the unit, so reports often write the unit into each cell: "1,240.5 kg". If that is done by building strings in Python, the cells become text — they no longer add up, sort numerically or plot on a chart, and Excel marks each one with a green "number stored as text" triangle. A number format can add the unit to the display while the cell keeps its numeric value: the format #,##0.0" kg" shows 1240.5 as 1,240.5 kg, and =SUM() over the column still works. This page shows how to write unit formats from Python, apply them to a pandas export column by column, display a value stored in kilograms as tonnes, handle singular and plural, and check what Excel will actually show. It belongs to Applying Number and Date Formats in Excel.

Prerequisites

Bash
pip install pandas openpyxl

LibreOffice is used in step 4 to check the displayed text without opening Excel; it is optional.

Step 1 — A unit is literal text inside the format

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Value", "Format", "Shown as"])
examples = [(1240.5, '#,##0.0" kg"'), (37.5, '0.0" h"'), (21.4, '0.0"°C"'),
            (86, '#,##0" m²"'), (-3.2, '+0.0" pp";-0.0" pp";0.0" pp"'), (0.85, '0" %"')]
for r, (value, fmt) in enumerate(examples, start=2):
    ws.cell(r, 1, value).number_format = fmt
    ws.cell(r, 2, fmt)
wb.save("units_examples.xlsx")
print([ws.cell(r, 1).value for r in range(2, 8)], "-> all still numbers")

Anything inside double quotes in a format code is printed as it is, so " kg" appends a space and the unit. The number part before it — #,##0.0 — controls separators and decimals as usual. Most symbols, including °, ² and non-Latin text, are safe inside quotes. The four-part format for percentage points shows how units combine with sections: positive, negative and zero values each get their own pattern, and each repeats the unit. The last example is a warning: a % sign inside quotes is just text, so 0.85 is shown as 1 %, not 85 % — to scale the value, use the % symbol outside the quotes, as described in Format Numbers as Percentages in Excel with Python.

Unit in the text or in the format Writing 1,240.5 kg as a string makes the cell text, which cannot be summed, sorted numerically or charted. Keeping 1240.5 as a number with the format hash comma zero point zero kg shows the same text while the cell stays a number. "1,240.5 kg" as a string cell is text SUM ignores it sorts 100 before 20 green warning triangle 1240.5 with #,##0.0" kg" cell is a number sums and averages work sorts numerically charts use the value

Step 2 — A unit for each column of a pandas export

Python
import pandas as pd
from openpyxl import load_workbook

shipments = pd.DataFrame({
    "Shipment": ["S-1001", "S-1002", "S-1003", "S-1004"],
    "Weight_kg": [1240.5, 18250.0, 980.25, 4410.0],
    "Volume_m3": [3.2, 41.0, 2.75, 11.5],
    "Transit_h": [26.5, 71.0, 18.25, 40.0],
    "Cartons": [1, 48, 3, 12],
})
UNIT_FORMATS = {
    "Weight_kg": '#,##0.0" kg"',
    "Volume_m3": '#,##0.00" m³"',
    "Transit_h": '0.0" h"',
    "Cartons": '[=1]0" carton";#,##0" cartons"',
}
HEADERS = {"Weight_kg": "Weight", "Volume_m3": "Volume", "Transit_h": "Transit time"}

path = "shipments.xlsx"
shipments.rename(columns=HEADERS).to_excel(path, index=False)
wb = load_workbook(path)
ws = wb.active
for col_idx, name in enumerate(shipments.columns, start=1):
    fmt = UNIT_FORMATS.get(name)
    if fmt:
        for (cell,) in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx):
            cell.number_format = fmt
ws.column_dimensions["B"].width = ws.column_dimensions["E"].width = 14
wb.save(path)
print({n: f for n, f in UNIT_FORMATS.items()})

Keeping the unit in the column name in pandas — Weight_kg, Transit_h — is a good habit: the code always knows what unit a value is in. The export then turns that into a clean heading (Weight) and moves the unit into the cell format, so the reader sees it next to every value. The format map is one dictionary, so a new column needs one line. Cells are formatted after pandas has written them, because pandas' own writer does not set per-column number formats; for a large sheet, formatting whole columns is faster, as covered in Style Whole Columns and Rows Efficiently with openpyxl.

The Cartons format has two sections with conditions: [=1] applies to a value of exactly one, and the second section to everything else, so the column reads 1 carton and 48 cartons. Conditions in brackets can test =, <, >, <=, >= and <>, with at most two conditional sections in one format.

Step 3 — Show kilograms as tonnes without changing the value

Python
ws.cell(1, 6, "Weight (t)")
for r in range(2, ws.max_row + 1):
    c = ws.cell(r, 6, f"=B{r}")
    c.number_format = '#,##0.00," t"'
total_row = ws.max_row + 1
ws.cell(total_row, 1, "Total")
ws.cell(total_row, 2, f"=SUM(B2:B{total_row - 1})").number_format = '#,##0.0" kg"'
wb.save(path)

A comma at the end of the number part of a format — just before the quoted unit — divides the displayed value by 1,000, and two commas by a million. So #,##0.00," t" shows 18250 (kilograms) as 18.25 t, while the cell still holds 18250. This is the same mechanism as showing thousands with a "K", described in Format Numbers in Thousands and Millions in Excel. It is handy for a summary that wants tonnes while the detail stays in kilograms, but remember the stored unit is still kilograms: a formula that multiplies that cell by a price per tonne must divide by 1,000 itself. The total row at the bottom sums the weight column, which works because every cell in it is a number.

Reading the format The format has three parts. The digits with a thousands separator and two decimals set how the number looks. The trailing comma divides the displayed value by one thousand. The quoted text adds a space and the unit t. #,##0.00 separators and two decimals , display divided by 1,000 " t" literal unit text

Read left to right, each part of the format does one job, and the three combine freely: two trailing commas would give thousands of tonnes, and any other quoted text could replace the unit.

Step 4 — Check what Excel will show

Bash
soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' shipments.xlsx > /dev/null 2>&1
cat shipments.csv

The CSV filter options ask LibreOffice to write each cell as shown, after formats are applied and formulas calculated, which makes a quick check of what a reader will see. Every unit appears where expected: kilograms with thousands separators, cubic metres with two decimals, hours, 1 carton against 48 cartons, the tonnes column scaled by the trailing comma, and the total of 24,880.8 kg — proof that the column still sums. For a check inside Python without LibreOffice, see Read Cell Values as Displayed in Excel with Python.

The shipments sheet as displayed Weight is shown in kilograms with one decimal, volume in cubic metres, transit time in hours, cartons with singular and plural, and an extra column showing the same weights in tonnes through the scaling comma. The weight column totals 24,880.8 kilograms. A B C D E F 1 Shipment Weight Volume Transit time Cartons Weight (t) 2 S-1001 1,240.5 kg 3.20 m³ 26.5 h 1 carton 1.24 t 3 S-1002 18,250.0 kg 41.00 m³ 71.0 h 48 cartons 18.25 t 4 S-1003 980.3 kg 2.75 m³ 18.3 h 3 cartons 0.98 t 5 S-1004 4,410.0 kg 11.50 m³ 40.0 h 12 cartons 4.41 t 6 Total 24,880.8 kg

Units in the heading or in every cell?

Both are valid; they suit different tables. A unit in the heading — Weight (kg) — keeps cells short and is the norm for dense tables with many columns, where a repeated unit in every cell is visual noise. A unit in the cell format suits tables with few columns, mixed units in one column (a KPI list where one row is hours and the next is kilograms), or values that will be copied out one at a time into an email or slide, where the unit should travel with the number. Copying a cell in Excel copies the displayed text, including the unit, which is often exactly what people want. Whichever you choose, use it consistently across a report, and never do both at once — Weight (kg) above cells that also say kg reads as a mistake. Screen readers announce the displayed text, so a unit in the format is also read aloud with each value.

Hours deserve one more note. A format like 0.0" h" is for a decimal number of hours, such as 37.5. If the cell holds an Excel time or duration — a fraction of a day — use a time format such as [h]:mm instead; mixing the two gives values that are 24 times too small or too large. Durations are covered in Format Durations Over 24 Hours in Excel with Python.

Common pitfalls

  • Units added in Python strings. The cells become text; put the unit in the number format.
  • % inside quotes. It is literal text and does not scale the value.
  • Forgetting the trailing comma scales only the display. Formulas still see kilograms.
  • A unit on only one section. In a multi-section format, repeat the unit in each section.
  • Decimal hours with a time format. Use 0.0" h" for 37.5, [h]:mm for durations.

Conclusion

To show units in an Excel report without turning numbers into text, put the unit in quotes inside the number format — #,##0.0" kg", 0.0" h", #,##0" m²" — and apply the format to each column from a mapping after pandas writes the sheet. Use a trailing comma to display kilograms as tonnes, conditional sections for singular and plural, and check the displayed text with a LibreOffice CSV export.

Frequently asked questions

How do I add "kg" after a number in Excel without making it text? Use a number format with the unit in quotes, such as #,##0.0" kg"; the cell keeps its numeric value and still sums.

How do I show singular and plural units in a number format? Use a condition: [=1]0" item";#,##0" items" shows 1 item and 5 items.

Can a number format convert kilograms to tonnes? It can scale the display by a thousand with a trailing comma, as in #,##0.00," t"; the stored value stays in kilograms.

Does pandas set number formats when writing Excel? Not per column; write the DataFrame, then set number_format on the cells with openpyxl, or use XlsxWriter's column formats.

More on number & date formats

More on number & date formats →