Show numbers in lakhs and crores in Excel, Power BI and Python

Decide which of the two formats your reader wants

Indian management reports use two different styles, and mixing them in one pack confuses readers:

Pick one per page. A table with full figures and a chart title in crores is fine; one column in lakhs next to another in crores is not. Always put the unit in the column header or the label itself, never only in a footnote.

Excel: the custom format and its blind spot

Select the cells, open Format Cells > Number > Custom, and type:

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

The first section applies to one crore and above, the second to one lakh and above, and the third to everything else. The backslash makes each comma a literal character, so Excel places them exactly where you typed them rather than every three digits. Values above 100 crore still display correctly, because Excel puts any extra digits into the leftmost placeholder.

The blind spot: a custom format allows only two conditions, and negatives go to the third section. So -12,34,567 shows as -1,234,567. If your sheet has negatives, you have two options:

1. 1Set the Windows region's digit grouping to the Indian style (Control Panel > Region > Additional settings > Numbers, "Digit grouping: 12,34,56,789"). Standard number formats then group every figure, positive or negative, the Indian way. This setting belongs to the computer, so a colleague with a different setting opens the same file and sees Western grouping.
2. 2Keep negatives in a separate column, or show them with the custom format on their absolute value and a "Dr/Cr" or "(-)" marker beside them.

For scaled values, a trailing comma in a format divides by 1,000, and there is no way to divide by 1,00,000. Use a helper column instead:

Value in lakh   =A2/100000          format: 0.00" L"
Label           =IF(ABS(A2)>=10000000, TEXT(A2/10000000,"0.00")&" Cr", TEXT(A2/100000,"0.00")&" L")

The first keeps a real number that charts and sums can use. The second is text, good for a title or data label, useless for arithmetic.

Power BI: separate measures, not format tricks

Power BI format strings have the same limitation: they cannot scale by a lakh. The robust pattern is a second measure:

Sales = SUM ( Sales[Amount] )

Sales (₹ L) = DIVIDE ( [Sales], 100000 )

Sales Label =
VAR v = [Sales]
RETURN
    IF (
        ABS ( v ) >= 10000000,
        FORMAT ( v / 10000000, "0.00" ) & " Cr",
        FORMAT ( v / 100000, "0.00" ) & " L"
    )

Give Sales (₹ L) the format 0.00 and put "₹ lakh" in the visual title. Use it on axes and in tables. Use Sales Label only in cards and tooltips, because it returns text and cannot be plotted.

For full figures with Indian grouping, the grouping Power BI shows depends on locale settings in Desktop and on the viewer's browser language in the service. DAX's FORMAT also accepts a locale as a third argument, for example FORMAT ( [Sales], "#,##0", "en-IN" ), which returns text grouped the Indian way regardless of the viewer. Again, text is for display; check it in the published report as your readers will see it, not only in Desktop.

Python: a function you can test

Python's f"{n:,}" groups in thousands only. The function below uses Decimal so rounding is exact for money, handles negatives, and is checked against known answers.

from decimal import Decimal, ROUND_HALF_UP

def indian_grouping(value, decimals=2):
    """12345678.9 -> '1,23,45,678.90' (last three digits, then pairs)."""
    q = Decimal(str(value)).quantize(Decimal(1).scaleb(-decimals), rounding=ROUND_HALF_UP)
    sign = "-" if q < 0 else ""
    whole, _, frac = f"{abs(q):f}".partition(".")
    head, tail = whole[:-3], whole[-3:]
    pairs = []
    while len(head) > 2:
        pairs.insert(0, head[-2:])
        head = head[:-2]
    if head:
        pairs.insert(0, head)
    grouped = ",".join(pairs + [tail])
    return sign + grouped + ("." + frac if frac else "")

def short_inr(value):
    """Scaled label for charts and summaries: 2.35 Cr, 18.40 L, 9,500."""
    v = Decimal(str(value))
    sign, v = ("-" if v < 0 else ""), abs(v)
    if v >= Decimal("1e7"):
        return f"{sign}₹{indian_grouping(v / Decimal('1e7'))} Cr"
    if v >= Decimal("1e5"):
        return f"{sign}₹{indian_grouping(v / Decimal('1e5'))} L"
    return f"{sign}₹{indian_grouping(v, 0)}"

tests = {1234: "1,234.00", 123456: "1,23,456.00", 12345678.9: "1,23,45,678.90",
         -1234567: "-12,34,567.00", 1234567890: "1,23,45,67,890.00", 0.5: "0.50"}
for n, expected in tests.items():
    assert indian_grouping(n) == expected, (n, indian_grouping(n))
for n in (9500, 1840000, 23456789, -675000):
    print(n, "->", short_inr(n))
print("all grouping tests passed")

Format at the very end, when writing a label, an email body or an export for people. Keep numbers as numbers in every intermediate DataFrame; a column of strings like "1,23,456.00" cannot be summed and will sort wrongly.

A rounding note for scaled reports

When a table shows branches in lakhs to two decimals, the rounded branch figures may not add up to the rounded total. That is expected, not an error, but managers will ask. Either add a line such as "figures rounded; totals computed on unrounded values", or show one extra decimal on the total row. Do not force the total to equal the sum of rounded figures, because then the total is wrong.

Presenting numbers the way Indian business readers expect is a small skill that makes every other report more credible; we practise it alongside the model-building work in our Data Analyst programme. The same function is used for the text messages in Generate a branch-wise daily sales summary for WhatsApp.