I want a custom formula for Google Sheets to format both crores and lakhs in Indian Number System. I have the custom formula for positive, but couldn't figure out how to add negative number system in that.

Custom Formula for positive numbers: [>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##0

Lakhs: 1,000,000 -> 10,00,000

Crores: 100,000,000 -> 10,00,00,000

1 Answer 1


Conditional number formats are limited to two conditions plus a default when neither condition is satisfied. The conditions are limited to a comparison operator and a value.

To use an Indian number format, you need two conditions to handle lakhs and crores, so there is no condition left to use to test for negative/positive.

The easiest workaround is to use a text() formula like as shown by player0 in Displaying Lakhs and Crores in Google Sheets. Note that this workaround gives text strings that look like currency values rather than real numeric currency values.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.