Excel Custom Number Format- Change Currency To Indian Format

Convert Number Format or Currency Format

This is something I learned today. One of my friends called me for a help. He has Excel sheet with numbers like this.

33,32,100 and 31,10,100 – Typical Indian Currency format

If we select 2 or more cells with numbers in it, Excel displays the Count and Sum of those numbers.

But in the above case, it did not show up. You can quickly open up an Excel workbook & try this.

How to Convert Numbers to Indian Currency Format?

Problem with above case is that, Excel detects these numbers as just text or string. It will not show the sum even if you use the ‘=Value’ formula.

Here is what you can do in this case.

1. Go to Windows Control Panel.
2. Choose “Region & Language”
3. Select ‘Additional Settings’
4. In Digit Grouping –  choose “12,34,567”
5. Do this in the Currency Tab as well.
6. Click Ok & Close.

Now, the numbers entered in the Excel (Example: 32,20,120) will be identified as numbers.

Note: In case if you would like to reverse this, there is option called “Reset” in the same Additional settings of Region & Language options.

Using Currency Format Option in Excel

The above explained option will affect all the Excel workbooks in the system. In case if you would like to change the format in a particular cell alone, then you can use the Format option of the cell.

1. Right click on the cell.
2. Choose Format cells.
3. Choose Custom
4. Enter: [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
5. Click ok.

This will change the format for the selected cell or range only. This way, you can have multiple Custom number formats in same Excel workbook.