Solved Display Numbers in Indian Format in Google Sheets

Discussion in 'Google Sheets' started by Dipak Manjhi, Nov 7, 2017.

  1. Dipak Manjhi

    Dipak Manjhi New Member

    How can I display numbers in the Indian money format in Google Sheets?
     
  2. mstoic

    mstoic Administrator Staff Member

    Hi.

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

    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: Nov 8, 2017
  3. Richard Kessler

    Richard Kessler New Member

    This is really great one
     

Share This Page