Excel can only scale numbers by thousands in a custom format (a trailing comma), so there is no format code that turns 7,85,000 into 7.85 L on its own. To show lakhs or crores you divide the value in a helper column and add a suffix such as L or Cr in the format, or use a TEXT formula when you want a ready-made label.
What can a custom format do, and what can it not?
A custom format changes appearance and never changes the underlying number. It can add text such as "L", set decimals and group digits. It cannot divide by 100000, because the only scaling Excel offers is one comma per thousand. That is why a format like 0.00,, gives millions, which is not an Indian unit.
Method 1: a helper column with a suffix format
This is the cleanest approach for reports because the numbers stay numeric and can be summed or charted.
- Keep your original amount in column C, say ₹7,85,000.
- In D2 type =C2/100000 and fill down. The result is 7.85.
- Select column D, press Ctrl+1, choose Custom and enter 0.00"L".
- For crores, use =C2/10000000 with the format 0.00"Cr".
With a hypothetical ₹2,45,00,000 sale, =C2/10000000 returns 2.45 and the format shows 2.45Cr. Put a space inside the quotes (" Cr") if you want 2.45 Cr.
Method 2: one formula that picks L or Cr automatically
When a list mixes small and large amounts, a single formula can choose the unit. This returns text, so it is for display only, not for further sums.
- =IF(ABS(C2)>=10000000, TEXT(C2/10000000,"0.00")&" Cr", IF(ABS(C2)>=100000, TEXT(C2/100000,"0.00")&" L", TEXT(C2,"#,##0")))
- ABS() makes the rule work for negative amounts as well.
- Keep the original number column next to it and use that for totals.
Which method should I choose?
| Need | Better choice | Why |
|---|---|---|
| Totals, pivots, charts in lakhs | Helper column with suffix format | The value stays a number |
| A mixed list that needs L or Cr labels | TEXT formula | Unit changes row by row, but result is text |
| Only Indian comma grouping | Lakh grouping format | No division needed |
| Presenting a summary to a family or partners | Helper column, rounded to one decimal | Short and quick to read |
Does the unit show on a chart axis?
A number format applied to the axis can add L or Cr as text, but it still cannot divide the values. If the chart plots raw rupees, the axis labels read 0 to 1500000 whatever the format says. Plot the helper column instead, so the axis already runs in lakhs, and add a title such as Spend in ₹ lakh to make the unit clear.
A worked example with three amounts
| Original amount | Helper formula | Displayed |
|---|---|---|
| ₹7,85,000 | =C2/100000 | 7.85 L |
| ₹45,00,000 | =C3/100000 | 45.00 L |
| ₹2,45,00,000 | =C4/10000000 | 2.45 Cr |
The second row shows why units matter: 45 lakh and 0.45 crore are the same money, and a reader can mistake one for the other if the label is missing. Pick one unit for a whole column, and use crores only when most of the amounts in that column are above a crore.
Checklist before you share the sheet
- Label the column header with the unit, for example Amount (₹ lakh).
- Keep the original rupee column, even if you hide it.
- Use the same unit down the whole column. Mixing L and Cr in one chart confuses readers.
- Test one negative value and one blank cell to see that the formula does not return an error.
If you find yourself rebuilding these helper columns every month for statement data, a tool that totals categories for you can save the effort. SpendixAI has ready-made reports and a CSV download for that, but the helper column method above works fine in any workbook.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


