DEV Community

HODADAK
HODADAK

Posted on Fully Autonomous

Excel custom number format: add units without breaking SUM

#excel #productivity #tutorial #beginners

Expense sheets and price lists often need a unit after the number: a currency name, "pcs", "kg". The quick way, typing it straight into the cell, has a cost that's easy to miss. The moment a cell contains 25,000원 (25,000 won, Korean currency) as typed text, Excel stops treating it as a number, and SUM silently skips it.

In our test sheet, the correct total was 194,500. With the unit typed into the cells by hand, =SUM returned 41,500. No error, just a wrong number.

TL;DR: keep the cell a number and let a custom format such as #,##0"원" draw the unit.

Steps

  1. Select the amount cells and press Ctrl + 1 to open the number format options.
  2. Pick the Custom category and type a format in the format box, for example #,##0"원". Apply it. (In Excel for the web, Ctrl + 1 opened a full formatting window and we applied the format there.)
  3. The values themselves don't change. Only the display does: 25000 now shows as 25,000원.

After that, the total is right again (194,500원), and any new number you type into those cells, say 40000, shows up as 40,000원 without extra work.

We checked this in Excel for the web, using the Korean interface.

Reading the format code

  • #,##0 means "whole number with a thousands separator".
  • Anything in double quotes is printed as-is. Swap "원" for whatever unit you need: #,##0" pcs", #,##0" kg", #,##0.00" USD". Put a space inside the quotes if you want a gap.

Common mistakes

  • Fixing the format but not the data. If cells already contain typed text like 25,000원, the format won't convert them. Retype the numbers, or strip the unit with Find and Replace first.
  • Forgetting the quotes. Some letters have meaning in format codes (for example d, m, y for dates). Quoting the unit avoids surprises.
  • Exporting to CSV. The unit lives in the format, not the value, so a CSV export will contain plain numbers. Usually that's what you want, but it catches people out.

Setting it from code

The same format string works anywhere Excel number formats are accepted. In openpyxl:

for row in ws["C2:C10"]:
    for cell in row:
        cell.number_format = '#,##0" pcs"'
Enter fullscreen mode Exit fullscreen mode

And in VBA, Range("C2:C10").NumberFormat = "#,##0"" pcs""" (quotes inside a VBA string are doubled). Both leave the values numeric, so formulas that read those cells keep working.

Drafted with AI help; the numbers above come from a test sheet in Excel for the web.

Top comments (0)