Solved Display Numbers in Indian Format in Google Sheets

mstoic

Administrator
Staff member
#2
Hi.

To show numbers in lakhs and crores, you need to apply a custom number format.

Please note that the below method doesn't work with negative numbers. There is an alternative method at the end, which works with negative numbers as well, but it will show spaces instead of commas.
Live Google Sheet: https://docs.google.com/spreadsheets/d/11p9a0B-jU-2hXD8sl5lL6-5pwoAUchnLtLFsnWMevjs/edit?usp=sharing

1. Select the cells/columns/rows with numbers to which you want to apply the Indian number formatting to.

2. In the top menu, select Format -> Number -> More Formats - > Custom number format...
Custom-Number-Format-Google-Sheets.png

3. Now you have to paste any one of the custom formula given below in the box and click Apply.
  • Show formatting in lakhs and crores with decimals and the rupee symbol.
    Code:
    [$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00
  • Show formatting in lakhs and crores with decimals (without the rupee symbol).
    Code:
    [>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
  • Show formatting in lakhs and crores without decimals and with the rupee symbol.
    Code:
    [$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0
  • Show formatting in lakhs and crores without the decimals (without the rupee symbol).
    Code:
    [>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##0
Apply-Custom-Number-Format.png

Source: https://stackoverflow.com/questions/32359814/displaying-lakhs-and-crores-in-google-sheets


The Alternative Method With Negative Number Support:

You can use the below custom number format to show spaces instead of commas. This also supports negative numbers.

Code:
## ## ## ## ##0.00;- ## ## ## ## ##0.00; 0.00

To make it clearer, you can even add more spacing.

Code:
##  ##  ##  ##  ##0.00;- ##  ##  ##  ##  ##0.00; 0.00
 
Last edited:
Top