Search code examples
google-sheets

Displaying Lakhs and Crores in Google Sheets

I would like to use Google Sheets for its convenient global access, but I really need to display numbers in Lakh and Crore (Indian style) format to avoid confusion among managers.

150,000 should display as 1,50,000 and 12,000,000 should display as 1,20,00,000.

I tried setting the format to ##,##,##,000 but my number still displays as 12,000,000. I couldn't find anything in the docs.

Does anyone know of a way to accomplish this?

Solution

  • Indian currency format displays numbers like below

    1 - One Rupee
10 - Ten Rupee
100 - One Hundred Rupee
1,000 - One Thousand Rupee
10,000 - Ten Thousand Rupee
1,00,000 - One Lac Rupee
10,00,000 - Ten Lac Rupee
1,00,00,000 - One Crore Rupee

    So below formats seems to be correct.

    To show positive and negative INR/Rs. up to Lakh

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

    To add support for positive lakhs and crores

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

    Add support for negative lakhs and crores

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

    For Microsoft excel, add the above format at Format -> Cells -> Custom -> [Custom text box] enter image description here Below screenshot demonstrates above custom format in use:

    Reference: https://www.raghunayak.com/2020/07/how-to-show-inrrs-in-lakh-crore-format.html