An Excel custom format has up to four sections separated by semicolons, in the order positive;negative;zero;text, and you can colour the negative section with [Red]. For lakh grouping to work in all four sections, the easiest route is to set Windows digit grouping to the Indian style first; a format that uses lakh conditions cannot also have its own negative section.
How do the four sections work?
| Section | Applies to | Example code |
|---|---|---|
| 1st | Positive numbers | "₹"#,##0.00 |
| 2nd | Negative numbers | [Red]-"₹"#,##0.00 |
| 3rd | Zero | "₹"0.00 |
| 4th | Text typed in the cell | @ |
Join them with semicolons and you have the full format: "₹"#,##0.00;[Red]-"₹"#,##0.00;"₹"0.00;@. Select the cells, press Ctrl+1, choose Custom and paste it into the Type box. Excel's named colours include Black, Blue, Cyan, Green, Magenta, Red, White and Yellow, and they go inside square brackets at the start of a section.
What does it look like with Indian grouping?
When Windows digit grouping is Indian, the #,##0 in each section follows it. The same format then shows the following for these hypothetical cell values.
| Cell value | Displayed | Colour |
|---|---|---|
| 1234567 | ₹12,34,567.00 | Black |
| -1234567 | -₹12,34,567.00 | Red |
| -45000 | -₹45,000.00 | Red |
| 0 | ₹0.00 | Black |
How do I show negatives in brackets instead of a minus sign?
Accountants often prefer brackets. Use this pattern: "₹"#,##0.00_);[Red]("₹"#,##0.00);"-"_);@_). The underscore followed by a closing bracket leaves a blank the width of a bracket on positive numbers, so the digits line up in the column. A zero then shows as a dash, which makes a long list of amounts quicker to scan.
What if I cannot change the Windows setting?
On a shared or office computer you may not be able to change regional settings. In that case split the job in two:
- Apply a conditional lakh grouping format to the amount column, for example [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0.
- Add a conditional formatting rule: Home, Conditional Formatting, Highlight Cells Rules, Less Than, enter 0, and pick red text.
- Accept that negatives of a lakh or more will show international grouping, or keep expenses positive in a separate column so the issue never comes up.
The reason is the limit of two conditions per format, and a conditional lakh format uses both. Conditional formatting handles colour only; it does not change the digits.
Can I use the same idea for budget versus actual?
Yes, and a variance column is where red and green earn their place. Use a plus sign format for the difference: [Green]+"₹"#,##0;[Red]-"₹"#,##0;"On budget". If you calculate budget minus actual, a positive number means money left. With a hypothetical grocery budget of ₹20,000 and actual spend of ₹22,500, the cell shows -₹2,500 in red. With actual spend of ₹18,200 it shows +₹1,800 in green, and when both match it shows the words On budget. The third section is for zero, which makes it a handy place for a short word instead of 0.
Will the same format work in Google Sheets?
Google Sheets reads the same section layout and colour names, so the four-section format should work there. Set the spreadsheet locale to India first, then check one positive, one negative and one zero value before relying on it.
Checklist
- Decide between a minus sign and brackets, then use one style in the whole workbook.
- Test the four cases: positive, negative, zero and a text note.
- If amounts reach a crore, check one value that large.
- Save the format on a cell style named Rupee amount, so new sheets pick it up in one click.
- Colour should never be the only signal. Keep the minus or brackets as well, for anyone who prints in black and white.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


