Spreadsheets

Show amounts in lakhs and crores in Excel (Indian comma format)

Show amounts in lakhs and crores in Excel with a custom number format, the region-setting alternative, and an approach for Google Sheets.

Editorial illustration for Show amounts in lakhs and crores in Excel (Indian comma format)

To show 1250000 as 12,50,000 in Excel, use a custom number format with two conditions. Select the cells, press Ctrl+1, choose Custom on the Number tab and type the format below. It switches to lakh and crore grouping by size without changing the underlying number.

What is the custom number format?

  • [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Read it as three parts separated by semicolons. If the value is at least 1,00,00,000 (one crore), use the first pattern. Otherwise, if it is at least 1,00,000 (one lakh), use the second. Everything else uses the ordinary pattern with a comma every three digits, which is already right for numbers below one lakh. The backslash before each comma tells Excel to print it as a literal comma, not treat it as a thousands scale.

Value in cellDisplayedIn words
8500085,000Eighty-five thousand
125000012,50,000Twelve lakh fifty thousand
999999999,99,999Ninety-nine lakh and change
456789014,56,78,901Four crore and more

What are the limits of this format?

  • Negative numbers fall into the last part, so -1250000 shows as -1,250,000 in western grouping. If your sheet holds negatives, add rules, or use the region approach below.
  • Decimals need the pattern extended, for example ##\,##\,##0.00 in the second part.
  • Values of a hundred crore and above display with extra digits in the leading group, such as 100,00,00,000, which still reads correctly.

Is there a simpler way through the region setting?

Yes. If Windows is set to English (India) as the regional format, Excel can follow the system's digit grouping, and the plain Number format with Use 1000 Separator groups in lakhs. Open Settings, Time and language, Language and region, and look for Regional format. In Excel, File, Options, Advanced has a Use system separators tick that must be on. The behaviour depends on your computer, so test with 1250000 before relying on it. The benefit is that negatives and decimals just work. The cost is that anyone opening your file on a computer with another regional format sees western grouping.

Can I show lakhs as a short number instead?

If you prefer 12.5 L over 12,50,000, divide in a formula and label it: =A2/100000 with the number format 0.00" L". For crores, divide by 10000000 and use 0.00" Cr". The cell then holds the scaled number, not the original, so keep the true rupee value in another column and never total the scaled one by mistake.

Once the format works, save yourself the typing. Select a formatted cell, click Format Painter on the Home tab, then drag across the other cells. Or save it as a style through Home, Cell Styles, New Cell Style, so a single click applies it. Chart axes can use the same code: right-click the axis, choose Format Axis, then Number, then Custom, and paste the format.

What about Google Sheets?

In Google Sheets, open Format, Number, Custom number format and paste the same format. Sheets also reads conditions in square brackets, so it works the same way for positive numbers. You can alternatively set File, Settings, Locale to India, which normally switches default grouping to the Indian style. Check on a known number such as 1250000, since this is easy to verify.

One more habit helps: type plain digits, not commas. Entering 12,50,000 with Indian-style commas works in some regional settings and turns into text in others. Type 1250000 and let the number format do the grouping, which keeps the cell numeric whatever computer opens the file. If a column will not total, select it and look at the status bar: a Count with no Sum means the cells are text.

Checklist

  • Test with one lakh and one crore exactly.
  • Test a negative value.
  • Keep totals on the real numbers.
  • Mention the grouping style in a note if you share the file.
SPENDIXAI JOURNAL

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

Explore your reports