Spreadsheets

Excel shows 1,234,567 instead of 12,34,567: change digit grouping

Excel shows 1,234,567 instead of 12,34,567 because it follows Windows digit grouping. See where to change it, what it affects and what to do on a Mac.

Editorial illustration for Excel shows 1,234,567 instead of 12,34,567: change digit grouping

Excel takes its digit grouping from your computer's regional settings, so 1,234,567 appears whenever Windows is set to the international style. Change the digit grouping to 12,34,56,789 in the Windows region settings and every normal comma-formatted number in Excel will use lakh grouping, in every workbook, without a custom format.

Where is the digit grouping setting in Windows?

Menu names shift between Windows versions, so use the route below as a guide and look for the words digit grouping.

  1. Open Control Panel and choose Region (searching for Region in the Start menu usually finds it).
  2. On the Formats tab, click Additional settings.
  3. Open the Numbers tab and find the Digit grouping box.
  4. Choose 12,34,56,789 from the list and click OK, then OK again.
  5. Close and reopen Excel so it reads the new setting.

Alternatively, setting the regional format itself to English (India) usually applies the Indian grouping together with the other local defaults. After you do either, check the Numbers tab again to confirm the grouping stuck.

Why does this affect every workbook and not just one file?

The workbook stores the actual number (1234567) and a format code such as #,##0. It does not store the commas. Each time Excel opens the file it draws the commas using your Windows setting. So changing Windows changes how all your files look, and it also changes other programs that read the same setting.

One more switch is worth checking. In File, Options, Advanced, look for Use system separators. If it is ticked, Excel follows Windows. If someone unticked it, Excel uses its own separators and the Windows change will not show. Tick it back on, or leave it off and rely on a custom format for that workbook.

What will I notice after the change?

  • The Comma Style button on the Home tab shows 12,34,567.
  • Formats such as #,##0 or #,##0.00 follow the Indian grouping.
  • Pivot tables, charts and totals use the same display.
  • Files you email still contain plain numbers, so the reader sees their own computer's grouping.
Where you lookWhat you see
Your PC, after the change12,34,567
A colleague's PC with default settings1,234,567
The cell's formula bar1234567 (no commas ever)
A CSV file opened in a text editorUsually plain digits without grouping

What if my numbers still show in threes?

  • The cells may hold text. A leading apostrophe, a left-aligned value or a green triangle in the corner points to this. Convert to numbers.
  • The cell may carry a custom format with a fixed pattern. Reset it to General or Number with the thousands separator ticked.
  • Excel was not restarted after the Windows change.
  • The workbook was opened in a protected or web version that uses the device region. Check your account or browser language settings.

Why do some bank downloads refuse to add up?

If a downloaded statement carries amounts with commas already typed in, such as 1,23,456.00, Excel may read them as text, left-aligned and ignored by SUM. Grouping set in Windows cannot fix that, because the cell holds characters, not a number. Select the column, use Find and Replace to replace the comma with nothing, then apply a number format. Test with a total at the bottom: if it matches the statement, the column is now numeric. Check a couple of rows by hand before deleting the original file.

Which fix suits you?

If you are the only person who opens your finance files, change Windows once and forget about it. If you pass workbooks to family, a landlord or an accountant, a custom number format inside the file keeps the style with it. Some people use both: Windows for daily work and a custom format on the one sheet they send out.

A final check

  1. Type 1234567 in any blank cell and apply the Comma Style.
  2. Confirm it shows 12,34,567.
  3. Delete the test cell.
  4. Open an older workbook and see that its totals look the same.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports