Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi,

    I am using Excel 2010. The format changes to millions for negative numbers.

    Regards,
    Suresh

    • Hi,

      The solution I posted was for versions of Windows prior to Windows 7. From Windows 7 onwards, if you set the Regional settings (Control Panel > Regional Settings) to India, the number format should show up in the Indian number format.

  • Hi,

    I am working outside India. So, I dont want to change the regional settings to India.
    But I want to use the Indian currency format for India branch reports. Possible?

    I am currently using Windows 7 and Excel 2010.

    Regards,
    Suresh

    • Hi,

      Try this

      1. Select any numeric cell (say cell C5) in your file and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
      2. In the formula bar there, enter =C5<0 and click on Format
      3. On the Number tab, go to Custom > Type
      4. Paste this string there [<=-10000000]##\,##\,##\,##0.00;[<=-100000]##\,##\,##0.00;##,##0.00 5. Click on OK/Apply 6. Copy this cell and Paste Special Formats to all numeric cells The process above is for applying India currency format to negative numbers in your range. For positive numbers, follow all steps above. In step 4, change the string to [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

      Hope this helps.

      • Hi,

        For positive and negative numbers no change in the display. Both cases displayed as positive in Indian currency format.

        Regards,
        Suresh

  • this format is for the numbers till 1 crore. if i want to add more commas, say for 100 crore, what is the formula for that?

  • Hi Ashish, I wanted the numbers to display as 1,00,00,000, 38,00,000, 48,000, 6,000. I have used the customer number format as mentioned in your website as, [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

    Now 1 crore is correctly displayed as wanted i.e. 1,00,00,000 however
    38,00,000 is showing as ,38,00,000
    48,000 is showing as ,,48,000
    6,000 is showing as ,,6,000

    I am using Excel 365 and have already done changes in Regional settings (Control Panel > Regional Settings) to India to get the number format in the Indian number.

    Can you please help on this.

    • Hi,

      If you are using any Windows 10 or 11 and your Regional settings are set to India, you should not feel the need to customise the number format in MS Excel at all. This solution is for older versions of Windows and MS Excel. I use Microsoft 365 Office Apps for Enterprise and without any customised formatting, when I type 3800000 in a cell and format it as Number, I see 38,00,000.