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 1:
This works in Google Sheets for sure. Should work in Excel too.
With the appropriate cells selected, navigate to:
Google Sheets : Format -> Number -> More Formats -> Custom Number Format
Excel : Format -> Cells -> Custom -> [Custom text box]
Enter one of the following and Apply:
A] For Lakhs and Crores with the Rupee symbol and decimals
[$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00
B] For Lakhs and Crores with the Rupee symbol and without decimals
[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0
C] For Lakhs and Crores without the Rupee symbol and with decimals
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
D] For Lakhs and Crores without both the Rupee symbol and decimals
[>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##0
Edit: I've tested these on both Google Sheets and Excel. Negative numbers work as well.
Solution 2:
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]
Below screenshot demonstrates above custom format in use:
Reference: https://www.raghunayak.com/2020/07/how-to-show-inrrs-in-lakh-crore-format.html