Format Numbers in Lakhs and Crores in Excel

In India, large numbers are grouped differently from the international style: the first group has three digits and every group after that has two, so one crore is written 1,00,00,000 rather than 10,000,000, and 12,34,56,789 is twelve crore, thirty-four lakh and so on. Readers in India expect reports in that form, and a finance report showing 123,456,789 makes them stop and count digits. Excel can display Indian grouping, but the usual #,##0 format takes its grouping from the regional settings of whichever computer opens the file — Indian on a machine set to English (India), international everywhere else. A report generated by Python and opened on many machines needs grouping built into the format itself. This page builds that format, handles negative numbers (which the obvious version gets silently wrong), adds the rupee symbol and decimals, checks every case against a Python formatter, and covers reporting in lakhs or crores as units. It belongs to Applying Number and Date Formats in Excel.

Prerequisites

pip install openpyxl

LibreOffice (soffice) is used to export each cell as displayed, so the formats can be checked without Excel.

Step 1 — A grouping that does not depend on the computer

A number format can have two conditions in square brackets, followed by a default section. Use them to pick a different layout of separators for numbers of a crore or more, a lakh or more, and smaller numbers:

LAKH_CRORE = r'[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00'
print(LAKH_CRORE)

In a number format an unescaped comma between digit placeholders means "use the thousands separator", which would put back international grouping. The backslash makes each comma a literal character instead, placed exactly where it is written: ##\,##\,##0 puts commas after the third and fifth digits from the right, ##\,##\,##\,##0 after the third, fifth and seventh. Numbers below one lakh use ordinary ##,##0, which is the same in both systems — 99,999 is written identically. Testing for the crore case first matters, because conditions are checked in order and every crore value is also at least a lakh. Because the separators are literal, the result looks the same on every computer, whatever its regional settings; the flip side is that it always shows commas and a dot, even in locales that use other characters. The general syntax of conditions and sections is covered in Write Custom Number Format Codes in Excel with Python.

Step 2 — Negative numbers need their own format

Both conditions in that format test for large positive numbers, so every negative value falls into the default section. Check what that does with a Python formatter to compare against:

def indian(x, decimals=2):
    sign, x = ("-" if x < 0 else ""), abs(x)
    whole, frac = f"{x:.{decimals}f}".split(".")
    head, tail = whole[:-3], whole[-3:]
    groups = []
    while len(head) > 2:
        groups.insert(0, head[-2:])
        head = head[:-2]
    if head:
        groups.insert(0, head)
    return sign + ",".join(groups + [tail]) + "." + frac

def negative_format(v):
    """A single-section format sized for this value; Excel adds the minus sign itself."""
    if abs(v) >= 10_000_000:
        return r"##\,##\,##\,##0.00"
    if abs(v) >= 100_000:
        return r"##\,##\,##0.00"
    return "##,##0.00"

print(indian(123456789), indian(-1234567), indian(999))

The default section of a conditional format does not add a minus sign of its own, and in LibreOffice the number is shown as its absolute value — -1234567 appears as 1,234,567.00, positive and with international grouping, which is about the worst possible outcome for a finance report. Excel allows only two conditions per format, so one format cannot cover large positives, large negatives and everything in between. Adding negative conditions with a literal minus sign is no cure either: in LibreOffice the first conditional section then shows the sign twice (--12,34,56,789.00) while the others show it once. The dependable answer is the oldest rule in number formats: a format with a single section shows negative numbers with a minus sign automatically. Since Python knows each value as it writes it, negative_format() picks a single-section format with the right number of literal commas for that value's size. The indian() function is the reference everything is checked against below.

Step 3 — Apply the formats and check every case

import csv, subprocess
from openpyxl import Workbook

values = [0, 999, 1000, 99999.5, 100000, 1234567.891, 9999999, 10000000, 123456789, 999999999, 1234567890, -50, -1234567, -123456789]
wb = Workbook()
ws = wb.active
ws.append(["Value", "Indian"])
for v in values:
    ws.append([v, v])
    ws.cell(ws.max_row, 2).number_format = negative_format(v) if v < 0 else LAKH_CRORE
wb.save("lakhs.xlsx")

AS_SHOWN = "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true"
subprocess.run(["soffice", "--headless", "--convert-to", AS_SHOWN, "--outdir", "calc", "lakhs.xlsx"], check=True, capture_output=True)
with open("calc/lakhs.csv", encoding="utf-8") as f:
    rows = list(csv.reader(f))[1:]
for (raw, shown), v in zip(rows, values):
    expected = indian(v)
    print(f"{v:>16,.3f}  {shown:>18}  {'ok' if shown == expected else 'differs: expected ' + expected}")

Every value matches the reference except one: 1,234,567,890 — 123 crore — displays as 123,45,67,890.00, where Indian grouping would be 1,23,45,67,890.00. The crore format has a fixed number of literal commas, and with only two conditions available there is no way to add a third tier for a hundred crore. For most reports — invoices, departmental budgets, sales by region — values stay well below 100 crore and the format is exact. For company-level totals that can exceed it, report in crores instead, as in Step 4. Negative values show their sign and Indian grouping, -12,34,567.00 and -12,34,56,789.00. Python picking the format per cell has one limitation worth stating: the negative formats are sized for the value written, and if someone later types a different number into the cell in Excel, the cell keeps the old format, so apply this to report output, not to input sheets.

Step 4 — The rupee symbol, and reporting in lakhs or crores

The rupee symbol goes in as a literal in each section, and for large totals it is usually clearer to show amounts in lakhs or crores rather than with all their digits:

from openpyxl.styles import Font

RUPEE = r'[>=10000000]"₹"##\,##\,##\,##0;[>=100000]"₹"##\,##\,##0;"₹"##,##0'
IN_CRORE = r'"₹"#,##0.00" Cr";"-₹"#,##0.00" Cr"'

totals = wb.create_sheet("Totals")
totals.append(["Region", "Revenue", "Revenue (₹ crore)"])
for name, amount in [("North", 412_385_000), ("South", 1_865_240_500), ("West", 96_500_250)]:
    r = totals.max_row + 1
    totals.append([name, amount, f"=B{r}/10^7"])
    totals[f"B{r}"].number_format = RUPEE
    totals[f"C{r}"].number_format = IN_CRORE
for c in totals[1]:
    c.font = Font(bold=True)
wb.save("lakhs.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true,false,false,-1",
                "--outdir", "calc", "lakhs.xlsx"], check=True, capture_output=True)
from pathlib import Path
print(Path("calc/lakhs-Totals.csv").read_text(encoding="utf-8"))

Number formats can scale by thousands — each trailing comma divides by 1,000 — but not by a lakh (100,000) or a crore (10,000,000), so the division is done with a formula, =B2/10^7, and the format adds the "Cr" label. The South total of ₹186.5 crore shows the full-digit format's limit from Step 3 — its 1,865,240,500 is over 100 crore, so the full figure has an extra digit in its first group — while the crore column reads cleanly as ₹186.52 Cr. A formula column also keeps the full-precision value available for anyone who needs it. For other localisation needs — month names, decimal commas, currency placement — see Localise an Excel Report for Other Languages and Locales.

Common pitfalls

• Relying on #,##0. Its grouping comes from the reader's regional settings.
• Unescaped commas. Write \, so commas are literal, not thousands separators.
• Negatives in a conditional format. They lose their sign or gain two; give them a single-section format.
• Values of 100 crore or more. The grouping breaks; show them in crores.
• Scaling with commas. Commas divide by 1,000 only; divide by 10^5 or 10^7 with a formula.

Conclusion

To show Indian digit grouping in a generated workbook, use a conditional number format with literal commas — one layout for a crore or more, one for a lakh or more, and ##,##0.00 for the rest — and, for negative values, a single-section format with the right number of literal commas, chosen per cell in Python so the minus sign is added automatically. Check the display against a Python formatter with a LibreOffice CSV export, and show totals of 100 crore or more in crores with a formula and a "Cr" label.

Frequently asked questions

How do I format numbers in lakhs and crores in Excel? Use a custom number format with two conditions — one for a crore or more, one for a lakh or more — and literal commas written as backslash-comma, with ordinary ##,##0 as the third section; the exact code is shown in Step 1.

Why does #,##0 show Indian grouping on some computers but not others? Excel takes the digit grouping for the thousands separator from the operating system's regional settings, so it depends on the computer that opens the file.

Why do negative numbers lose their minus sign with the lakh format? Negative values fall into the default section of a conditional format, which does not add a minus sign; give negative cells a single-section format such as ##,##,##0.00, which shows the minus sign automatically.

How do I show values in crores in Excel? Divide by 10^7 in a formula and apply a format such as "₹"#,##0.00" Cr", because number formats can only scale by thousands.

Related

• Up one level: Applying Number and Date Formats in Excel — number formats from Python.
• Format Numbers in Thousands and Millions in Excel — scaling with commas.
• Format Excel Cells as Currency with Python — currency symbols.
• Show Zeros as Dashes or Hide Them in Excel — the zero section.