Spreadsheets

Indian lakh comma format in Excel (12,34,567) with a custom format

Show Excel amounts as 12,34,567 with a conditional custom number format, see where it breaks for negatives and crores, and when to use the regional setting.

Editorial illustration for Indian lakh comma format in Excel (12,34,567) with a custom format

Excel groups digits in threes (1,234,567) unless Windows is set to Indian grouping. To get 12,34,567 in one workbook without touching Windows, select the cells, press Ctrl+1, choose Custom under Number and type this format: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

What each part of the format does

  • [>=10000000]##\,##\,##\,##0 catches values of one crore and above and prints them as 1,23,45,678.
  • [>=100000]##\,##\,##0 catches one lakh up to 99,99,999 and prints 12,34,567.
  • ##,##0 is the "everything else" section. Below one lakh, Indian and international grouping look identical (98,765), so ordinary commas are fine.
  • The backslash before each comma tells Excel to print a plain comma character instead of treating it as a thousands separator, which is what lets you place commas at the 2-digit positions.

Here is what the format shows for some hypothetical amounts. The cell itself still holds the plain number.

Value in the cellShown on screenSection used
9876598,765Third
1000001,00,000Second
123456712,34,567Second
500000050,00,000Second
456789014,56,78,901First

Where does this custom format break?

Excel allows at most two conditions in one format, and the third section catches everything else. That design causes a few limits you should know before you apply it to a whole ledger.

  • Negative amounts fall into the third section, so -1234567 appears as -1,234,567 with international grouping.
  • From 100 crore upwards (ten digits), the leftmost group swallows extra digits and the commas look wrong.
  • Decimals need a small edit: write ##\,##\,##0.00 in the lakh section and ##,##0.00 in the last, so paise show consistently.
  • Amounts stored as text (left-aligned, often with a green corner flag) ignore number formats entirely. Convert them to numbers first.
  • Because only two conditions are allowed, you cannot also add a red colour for negatives in the same format. Use conditional formatting for the colour instead.

Can the format include a rupee sign?

Yes. Put the symbol in double quotes at the start of every section: [>=10000000]"₹"##\,##\,##\,##0;[>=100000]"₹"##\,##\,##0;"₹"##,##0. If a column is all rupees, a header like Amount (₹) with the plain lakh format keeps the cells less cluttered and is easier to read in a long list.

Is the Windows regional setting a better fix?

Often, yes. If Windows digit grouping is set to the Indian style, Excel's normal comma format (#,##0) already produces 12,34,567, with no conditions, no limit on size and correct handling of negatives. The trade-off is that it changes the display in every workbook on that computer, and a colleague who opens your file on a PC with the default setting will see international grouping.

QuestionCustom formatWindows setting
Works for negativesNo, third section onlyYes
Works above 100 croreNoYes
Travels with the file to another PCYesNo
EffortPer column or sheetOne-time change

How do I keep negatives readable?

The simplest route is to avoid negative numbers in the amount column. Record spending as positive amounts and keep income in its own column or a Type column, then subtract in the summary. If you do need negatives, add a conditional formatting rule under Home, Conditional Formatting, Highlight Cells Rules, Less Than, with 0 as the limit and red text. The lakh format still governs the commas, and the rule only changes the colour. Remember that a negative below one lakh, such as -45,000, looks right anyway, because it uses the ordinary third section.

A short checklist before you format a sheet

  1. Make sure the amounts are real numbers, not text.
  2. Apply the three-section format to the amount column only, not to dates or IDs.
  3. Test one negative value and one value above a crore to see how they display.
  4. Decide whether the file will be shared; if it will, keep the custom format so grouping travels with it.
  5. Add the format to a cell style or copy it with Format Painter so every new month uses the same look.
SPENDIXAI JOURNAL

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

Explore your reports