Displaying Lakhs and Crores in Google Sheets

100
votes

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?

2
google-sheets
This question isn't about programming related to Google Apps, so it is off-topic for Stack Overflow. It belongs on WebApps. – Mogsdad

2 Answers

6
votes

As of Dec 2020, Google Sheets has added Indian Rupee formatting as well. Just go to File -> Spreadsheet settings -> Locale and select India.

Let it refresh, you will find Rs. symbol in the toolbar.

enter image description here

328
votes

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.