SubscribeHow to show Indian Currency Format in Excel?
This is a guest post by our excel ninja – Hui.
Today we will learn how to format numbers and amounts in Indian currency format. Indian numbers are grouped differently than standard English numbers.
English Grouping 123,456,789.01
Indian Grouping 12,34,56,789.01
Quick and easy fix to show numbers in Indian format:
A custom number format of:
[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
or
[>=10000000]"RS "##\,##\,##\,##0;[>=100000]"RS " ##\,##\,##0;"RS "##,##0
Will solve the problem.
A permanent solution to Indian Number Formats:
However there is better and more permanent solution using the Systems Regional Settings
- Goto the Control panel and select the “Region and Language” button
- Select “Additional Settings”
- Set digit groupings to the Indian grouping like this:
- Do the same on the Currency Tab and Apply
Now in Excel the Default “,” and “$” Style will show the way you wanted.
Thanks Hui:
Thank you so much for sharing this hack with all of our readers. 
More Quick tips & Number Format tricks.
| ||
|
Spread some love,
Posts & Navigation
Tags: custom cell formatting, Excel Howtos, guest posts, hui, indian formats, Learn Excel, number formatting, quick tip |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
30 Responses to “How to show Indian Currency Format in Excel?”
Wow ! I wasn’t aware of that kind of grouping. I’m so stuck in “occidental”-thousand grouping way of thinking…
Can you explain the historical/cultural reason for that grouping ?
Cyril.
I don’t have additional setting option in region and language button. Is this facility available only in vista and window 7?
Hi Chandoo,
didn’t know about different grouping in India! Very interesting…
I don’t have time now to work out the “indian style”, but if you are curious about what can be done in regards of international number formats, check out these webpages:
http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx
http://www.science.co.il/language/Locale-Codes.asp
Regards,
Dominik.
Interesting, but could you explain what the backslashes are for? I’ve never used a backslash in a custom format.
Regards.
@Leonel .. the backslash forces excel to treat , as a comma instead of thousand’s separator.
@Cyril.. not sure why, but I think we are used to counting in hundreds and we have established notations for hundred thousand – lakh and hundred lakhs – crore. May be they would have just adopted the convention of separating hundreds when they started writing numbers in script (and eventually on to computers).
@Dominik.. good links, thanks for sharing.
@JPC what version of windows are you on?
Just want to point out that suggested Custom Format will only handle non-floating point numbers and apparently will only correctly format a maximum of nine digits. If you want to be able to handle up to 15 significant digits, including a forced display of two decimal places, then here is some worksheet event code that will automatically format the cell “on the fly” (just set the range being assigned to the R variable to the range of cells you want to have an Indian Number Format)…
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range(“C:C”)
If Not Intersect(Target, R) Is Nothing Then
With Target
.NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) – 1, “#”), _
” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0″), ” \,”, “”)) & “.00″
End With
End If
End Sub
For those who have not worked with event code before, you would install this code by right-clicking the name tab at the bottom of the worksheet you want to have this functionality, click “View Code” on the popup menu that appears and then copy/paste the above code into the code window that appeared. That’s it! Now go back to the worksheet and type in a number with up to 15 significant digits into Column C (if you left my code alone) or into whatever range you changed my example “C:C” cell range to in the assignment to variable R.
You can also download the new font from http://blog.foradian.com/ (it’s not official – I think) and is also mapped to the ’tilde’ key (above Tab).
Ninad.
For more information on the Indian numbering system you can see http://en.wikipedia.org/wiki/South_Asian_numbering_system but to be frank we never heard of any number above 10 power 7… surprised to see that other terms are infact captured here!
hank you very much for this thought provoking article. A few rather random thoughts:
The notation needed for India currency was also news to me… very interesting a learning experience. I can’t believe how provincial I feel.
Anyway, I looked up the balance sheet for the State Bank of India. http://money.rediff.com/companies/state-bank-of-india/14030001/balance-sheet... I got so wound up trying to reparse the numbers (from hundreds, thousands, lakhas and crores TO hundreds, thousands, millions, billions) that I immediately lost track of the importance of the information content… and then there was the (Rs crore) notation on the bank information which told me that the parsing process was going to be even more complicated… and that does not even factor in currency exchange rates to get to a point of relative reference (in my case back to dollars).
Chandoo tells us that: Large amounts of Rupees are expressed in lakh rupees or crore rupees. A Lakh Rupee is one hundred thousand rupees and a crore rupee is ten million rupees. And a little more digging gives these additional two digit group names: arab (one billion rupees), kharab (one hundred billion), neel (ten trillion) and padma (one quadrillion).
So one question:
Is this two digit comma notation used only for India Rupee curency value or is it used also used in India for volume measures (number of shares, tonnes of rice or bales of tea, etc)?
As to the formatting formula there seems to be no way to add another filter term to this formatting line “[>=10000000]“Rs. “##\,##\,##\,##0;[>=100000]“Rs. ” ##\,##\,##0;”Rs. “##,##0″ and if you just add more “##\,” to the format and the number is less than 10000000 then you get a lot of commas at the left side of the number.
As suggested, using the “Kannada (India)” choice at the operating program level (Windows Vista) will immediatly switch everything not formatted in something specific to the two digit comma offset notation.
I could not get the VBA programming (suggested by Rick) to work. When I implement this it gets hung up on the “Set R” code line and the “.Number” code row turns a nasty red color.
Two more questions:
>When I assign the Currency format with the symbol as “Rs. (English) India” to a cell and then look at it under custom formatting I get “[$Rs.-4009] #,##0″ what does the “-4009″ mean? This number changes for different currency symbols… but I cannot figure out the logic.
>What needs to happen to get the new India Rupee symbol available to Excel users worldwide?
-bill
@bill…. I think the problem you had with my code is due to the comment processor Chandoo uses… look at ALL the quote marks in all the lines of code… they are NOT the quote marks you need to use in the VB code editor. If you delete each one and replace it with one typed in from your keyboard, my code should work then.
@Bill.. very interesting points you make. You are right in guessing that Indians often use the hundred / lakh /crore notation for measuring other numeric things, although we do follow metric system.
Also, your questions:
1) I dont know what the -4009 means either. May be someone can tell us what is the mystery behind this.
2) The Rupee symbol has been submitted for unicode approval. Once approved, I assume various font makers (and Microsoft, Apple etc.) would include the unicode and corresponding symbol in their distributions. Only then you can get it in to Excel , word etc with ease. Until that time you have to use a font like http://blog.foradian.com/ as Ninad points out above.
Rick, your suggestion worked. I retyped (in the VBA editor) all of the code that was not alphabetic giving particular attention to the quote marks and the minus character… and it worked. Very interesting VBA, this opens up all sorts of possibilities. Thank you.
Quick note: I have noticed that there is even a small sweet spot for formulas (placed within the R range) that yield numbers between one hundred thousand (a lakah) and nine hundred ninty nine thousand nine hundred and ninety nine (one short of a crore). Why does this this VBA parsing work at all on a formula?
Bill
Yes, it looks like that line has to be retyped in its entirety… I just copied it into my VB editor and the quote marks seemed not to be the only characters that are causing problems (I suspect some of those “space characters” are not true spaces… retying the entire line cleared all syntax errors. Anyway, to your question about the code working for values displayed by formulas… actually, it doesn’t work for formula values. What I think you are seeing is a left over Custom Format (what my code does) in a cell you changed from a constant to a formula. I think if you change the formula to display number differing greatly in magnitude, one or both will display incorrectly. HOWEVER, your statement caused me to go back and re-work the code so it responds correctly whether the cell contains a constant or a formula. As with my previous posted code, if you get a syntax error from the copy/paste, just retype the offending line of code manually and it should clear the syntax error. Here is the now more robust code…
-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, Cell As Range
Set R = Range(“C:C”)
If Not Intersect(Target, R) Is Nothing Then
With Target
If WorksheetFunction.IsNumber(.Value) Then
.NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) – _
1, “#”), ” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0″), _
” \,”, “”)) & “.00″
Else
.NumberFormat = “General”
End If
End With
ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
For Each Cell In Target.Dependents
If Not Intersect(Cell, R) Is Nothing Then
With Cell
If .Value “” Then
If WorksheetFunction.IsNumber(.Value) Then
.NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) – _
1, “#”), ” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0″), _
” \,”, “”)) & “.00″
Else
.NumberFormat = “General”
End If
End If
End With
End If
Next
End If
End Sub
A follow up to my latest posted code… besides needing to retype some of those lines of code because of non-standard character conversions by Chandoo’s comment processor, I see one of the lines had its “not equal to” symbols… a less than sign followed by a greater than sign… removed by the comment processor as well. So, this line of code…
-
If .Value “” Then
-
should read this way instead….
-
If .Value .NE. “” Then
-
where you would replace the .NE. with a less than sign followed by a greater than sign.
Thank you for the perfect solutions for excel. I resolved my problem through this ..
Oh! thank God for this!
I am going to try it right away!
@rick – your revised code works perfectly. Thank you.
By the way, I found that if you add “[$Rs. ] & ” after the equal sign and before the “Trim” function in the NumberFormat line you get the current rupee sign before the correctly formatted numeric string.
In experimenting with this I have found that the only issues are with negative numbers. The negative sign pops out to the left of the Rs symbol and a zero value does not format properly (with the Rs addition). I am thinking that by adding conditional statements (LT 0, = 0, and GT 0) are set up at the IF loop where the number is being evaluated than this might lead to handling these outlier cases. I have not yet had a chance to try it.
bill
@bill,
It looks like I omit the error checking routine to protect against no dependents for the target cell in my last posted event code. To correct for this, add this line of code…
On Error GoTo NoDependents
immediately after the first Set statement at the top of the code and add this statement…
NoDependents:
as the last line of code (immediately above the End Sub statement).
Sir, Now recently our Indian Government declared a notation for our currency. How can we include in our doc, since the notation is not available on the web, I think. Would you please guide me for this. With regards
@R.Ravi,
Can you make use of the information at this link?
http://en.wikipedia.org/wiki/Indian_numbering_system
Hello Chandu Bhai.
I have tried the regional option’s and it does not group.
As soon as I apply the changes, all other changes, Rs. and decimal places, happen instantly. Not the grouping. Just not happening!
Help!
Ra.
Thanks a ton for Regional setting idea! For a year I haven’t been able to figure that out and had waster lot of my time. Thanks for the tip!
Thanks Ninja-HUI,
Great! Nice formatting tips. It helps.
[...] To use Indian formatting for numbers (i.e. Lakh, Crore instead of Millions) see this article: “Indian Currency format in Excel” [...]
How can we chang the US doller into indian currency in balance sheet (Excle file).
@Rakesh
You can do it a number of ways
1. Add extra Rows which are in Indian currency
2. Add a multiplier to all the cells
=Current Formula * ExchRate
where ExchRate is a named formula for the exchange rate
3. Copy the exchange rate from a cell and Paste Special; Multiply, over the whole area
Hi, Thanks Hui for this nice solution… u made my work easier… thanks a lot
Thanks Hui.
-Chood
Thanks friend,
It saved lot of time for me.
Regards,
Thanks dear friend