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 cell | Shown on screen | Section used |
|---|---|---|
| 98765 | 98,765 | Third |
| 100000 | 1,00,000 | Second |
| 1234567 | 12,34,567 | Second |
| 5000000 | 50,00,000 | Second |
| 45678901 | 4,56,78,901 | First |
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.
| Question | Custom format | Windows setting |
|---|---|---|
| Works for negatives | No, third section only | Yes |
| Works above 100 crore | No | Yes |
| Travels with the file to another PC | Yes | No |
| Effort | Per column or sheet | One-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
- Make sure the amounts are real numbers, not text.
- Apply the three-section format to the amount column only, not to dates or IDs.
- Test one negative value and one value above a crore to see how they display.
- Decide whether the file will be shared; if it will, keep the custom format so grouping travels with it.
- Add the format to a cell style or copy it with Format Painter so every new month uses the same look.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

