How to format numbers as lakhs and crores in excel google spreadsheet when the number could be negative too

Question

I have to format numbers in the Indian formatting system with commas showing up for lakhs(1,00,000) and crores (1,00,00,000).

This answer works for positive numbers

Displaying Lakhs and Crores in Google Sheets

But negative numbers show the commas for millions and billions only. How do I make one format work for both positive and negative?

Answer 1

Excel formatting, in my opinion, can only accept a maximum of two requirements, plus a default if those conditions are not met. Conditional formatting may be your only escape from this, in my opinion. I'd format this in a cell using one of the examples from your linked solution.

[$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00

Then in conditional formatting, you would need to have a condition if cell<0 and apply the conditional format of:

[<-9999999][$₹]-##\,##\,##\,##0.00;[<-99999][$₹]-##\,##\,##0.00;[$₹]##,##0.00

If you want to change decimals, symbols, etc you should be able to work it out from the linked solution, and this one.