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.
96 Responses
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,
the facility is available only with Windows Vista / Windows 7 and above.
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.
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 for the info on formatting spreadsheets for INR. This approach works well for me except that I need it to display negative numbers in RED, and I can’t figure out how to add that to your custom cell format:
[>=10000000]”RS “##\,##\,##\,##0;[>=100000]”RS ” ##\,##\,##0;”RS “##,##0
According to the tutorials, everything before the first ‘;’ is for positive numbers, and before the second ‘;’ is for negative numbers, but it looks like you’re using both areas for positive numbers.
Can you help me please?
Thanks
Martin
@Hui
Thanks for the custom formats for INR. Much appreciated.
One issue I’ve run into with what you’ve proposed is that the formatting doesn’t always work for negative numbers.
To see what I mean, please put these two numbers into adjacent cells and then apply the custom format for INR on it:
-1897400
1897400
When I do this I get the correct formatting for the positive number, but not the negative number. I think that this may be because (according to the tutorials) everything before the first ‘;’ is for positive numbers, and before the second ‘;’ is for negative numbers, but it looks like you’re using both areas for positive numbers.
Thanks
Martin
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
You deserve a big thanks…. Your a genius
This is a simple and great solution to a problem that had been bothering me for long. Thank you very much, Hui. You really are a genius!
nice ……………..!
helped me a lot……………
My excel appear 10,00,000.00 like that, anyone please tell me how to change to 1,000,000.00. I was try with several method but not possible.
@San
Most people in India want it to display like yours and can’t
It can be one of two things
1. The local currency or number format in Windows is set like that
Its the opposite of the instructions above labelled”
A permanent solution to Indian Number Formats:”
2. Change the number format of the cells
Select some cells
Ctrl 1
Custom
In the Type: Box type: 0,000.00
apply
Thanks Chandoo,
It was very very useful.
Regards,
Sachin
Thanks Chandoo,
I.
Regards
naveen
Thanks lot for the solution.
Hi Hui,
Thank you so much you solved my problem
Hi Chandoo,
Above custom number formating works fine with positive numbers. However, if I want to display negative numbers in [Red] and I do not want to change windows level setting. How it is to be done.
Two heads up:
1. No VBA code please
2. Only Custome Number formats to be used.
I thought of workaround by using conditional formatting. But, I want to save this Indian format in Cell Styles so that I can easily apply whenever I need it.
excel ninja-hi this works well for positive numbers. ofr negative numbers it is not.
any ideas?
Thanks Chandu This is a great help…!!!!
I have try to seek this option from last 5 years
but the option you told here fully valid and permanent.
Thanks again
Thanks a lot, i am unaware of it, it helps my account department.
Thanks a Ton Chandoo for the great tip !
The Regional setting idea never occurred to me. Have been trying to tinker with the Custom Format in Excel and wasting time !
I used a code similar to one published by someone in comments above
.NumberFormat = Trim(Replace(Format(String(Len(Int(.Value)) – 1, “#”), _
” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0?), ” \,”, “”)) & “.00?
Is there a way to format negative numbers with brackets
i.e. -5,00,000 should be displayed as (5,00,000)
0 should be displayed as –
Also, using this code 3-digit numbers such as 5,000 show up as ,,5,000 and numbers 3-digit numbers such as 56 show up as ,,,56
Is there a way tp sort this?
The full code i am using is
Sub IndianNumberFormat()
For Each C In Selection
C.NumberFormat = Trim(Replace(Format(String(Len(Int(C.Value)) – 1, “#”), _
” @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0″), ” \,”, “”)) & ” ”
Next
End Sub
P.S: I’m an excel noob and have picked up this code from a forum i came across.
Above format style doesn’t work with negative values.
e.g. – When value is positive it shows 5,00,000 but when value goes to negative it shows -500,000. Is there any way to tweak that ??
You can try this [>=10000000]_ [$?-4009] * ##\,##\,##\,##0.00;[>=100000]_ [$?-4009] * ##\,##\,##0.00;_ [$?-4009] * ##,##0.00
Thankyou very much Sir,
its very useful.
Thank you for the great resource. How would you do it for Chinese yuan?
1 Wan = 10000 = Ten thousand = 10,000
1,000,000,000 = 10 Yi = 10,0000,0000
Hi,
Thank you so much.
I have try to seek this option, but the option you told here fully valid and permanent.
again Thank You so much
Cool ! Thanks
Thanks man
it is really helpful
Hi Chandoo,
I have done all the setting in system (using Win8 Machine) to make it Indian format but in excel it is still showing upto lacs correctly but in crores it is again not taking (sample ? 134,55,000).
I used custom setting for each cell that worked.
Is there any permanent Solution because every-time i have to format cell.
Thank you in advance.
Thanks a lot for your Instruction.
I actually used this to change from lacs format to thousand as this is pretty much a standard. Unfortunately my laptop came with a setting which only allowed lacs format. Your tips defenitely helped. Thanks a ton.
Regards,
ViN
How do u change the $ or the Rs. Symbol to the new indian rupee symbol in the Format Cells columns in excel.
Please help been trying for quite sometimes now.
Regards
Raymond.
Thanks, it worked for me!
Really Its very wonderful for me
A minor change to what has been suggested:
[>=10000000][$?-4009] * ##\,##\,##\,##0;[>=100000][$?-4009] * ##\,##\,##0;[$?-4009] * ##,##0
This will introduce the INR symbol and format the cell as per accounting view (rather than a currency view). That is, left aligned rupee symbol and right aligned numeric value.
Hope this helps!
Regards,
MT
Oops, guess the font of this discussion page does not have the INR rupee symbol !!
Hence, whosoever wishes to use the above suggested custom format, please replace “?” in the custom format with the rupee symbol.
Thank You, Thank You, Thank You for the helpful post.
tnx a ton
Thanks a lot. I just tweaked a little with Indian currency symbol and with decimal(paise) values.
[>=10000000]”? “##\,##\,##\,##0.00;[>=100000]”? “##\,##\,##0.00;”? “##,##0.00
i changed in region settings… but its not working in the excel sheet… can u help me to solve this,,????
Dear Chandoo,
Thank you so much for the solution 🙂
Keep posting stuff like this….
Hello Purna,
We have made an easy method to convert amount into Indian currency in words.
You can check it out here https://www.teachoo.com/860/279/Convert-amount-in-words-(Indian-currency:-Lakhs,-Crores)/category/Macros/
Can you please link to us as well?
Regards
Ritika
Thank You!!!
thanks you ….
Hi Chandoo,
i want 10 K instead of 10000, 10 Lac instead of 1000000, 11 Cr instead of 110000000
Can you help me for this….
I tried the custom format but it doesn’t show the the digits (Paise).
Still no format to get negative numbers in Lakhs & Crores………?????
Anyone????
It worked & thanks a lot
Custom format solution for both positive & negative numbers:
For positive numbers, use the custom (up to 99.999999 crore)
[>=10000000][$?]##\,##\,##\,##0.00; [>=100000][$?]##\,##\,##0.00; [$?]##\,##0.00
‘?1,55,00,880.05
?1,55,880.05
?55,880.05
?,58.00
For negative numbers, use the custom format: (up to -99.999999 crore)
[<=-10000000][$?]-##\,##\,##\,##0.00; [<=-100000][$?]-##\,##\,##0.00; [$?]##\,##0.00
'?-1,55,00,880.05
?-1,55,880.05
– ?55,880.05
– ?,580.00
I am not able to get rid of frontal , in small numbers.
Also, can I have one formula that will work with both positive & negative numbers?
-Vinay Bhatt, Austin, Texas
Thanks for asking.
I am also looking for universal formula for positive and negative numbers as profit or loss will keep changing for various scenario.
Control Panel option is not smart one, for, many a times you want to show the USD amounts in one column in US Format and equivalent INR amounts in Indian digit grouping format in adjacent column.
I am grown up man, I mean, have been using Lotus in MS DOS times (early 90s). Is not Microsoft Excel designed by idiots whose minds did not cross to add simple few lines of codes to allow for Indian digit grouping in Excel (not through universal Control Panel) as a cell format option, in all these decades ???
? in my comment above should be interpreted as RS symbol.
Thanks a Lot…
thanx a lot bro my boss is an asshole. he make me angry for this tiny problem. now i fix it and chill thanx again
Thank you
Thank you, Chandoo for your hard work and contribution.
The solution given for indian system applies only for positive numbers any solutions that includes zero and negative numbers
Formet Cell ME JAKAR Accounting+English (India) NHI AA RHA HAI KAISE LAYE.
OR COMMA THUNDRED,THOUSAND,LACS,CORRE KAISE SET KARE
Superb! thank you so much! Works flawlessly!
Thank you! works perfectly!
If anyone would like to use it with the Currency Symbol, Please use the below one:
[>=10000000]”? “##\,##\,##\,##0;[>=100000]”? ” ##\,##\,##0;”? “##,##0
Is it possible to recreate the format in TEXT command?
Thanks, brilliant.
Searched everywhere, but found on your website.
However, it does not work for negative numbers when I tried by reversing the More Than sign with Less Than.
Please help.
M. Ahmed
I want to format rupees in Lakhs with Comma’s, I tried the below noted formatting, However the results were not coming with comma’s. Can you help in this regard
_(#0\.00,;_(* (#0\.00,);_(* “-“??_);_(@_)
Hi,
The below formula is applied to two cell in excel A1 & B1.
[>=10000000]” “##\,##\,##\,##0;[>=100000]” ” ##\,##\,##0;” “##,##0
The resulting number format will be lie this X,XX,XX,XXX
Below Cases
————–
A1=10,00,000 & B1=8,00,000
Case 1 : When I subtract B1 from A1 (ie., 10,00,000 – 8,00,000), the result obtained is 2,00,000 (positive number). it is satisfying the above number format
Case 2 : When I subtract A1 from B1 (ie., 8,00,000 – 10,00,000), the result obtained is -200,000 (negative number), here it is failing the above number format.
Pl. help in resolving to get the result to be shown as -2,00,000
Thanks
KSK
First of all, thanks for the great website. I keep following it for many years.
For this specific topic, I see there is a solution for +ve numbers. There is a separate solution for -ve numbers. Do you have a single solution to handle both -ve and +ve numbers? I want to do accounting in my worksheets.
I think with the new rupee symbol and the decimal places. It should be as below:
[>=10000000]”? “##\,##\,##\,##0.00;[>=100000]”? ” ##\,##\,##0.00;”? “##,##0.00
i want to divide collection of cash amount in different accounts , therefore i need to divide those currency notes in to their denomination as per the amount decided to deposit in particular account
for example i have 1,00,000/- (500*100 , 200*100, 100*300)
i decide to deposit as per below
A 25,000/-
B 20,000/-
C 15,000/-
D 10,000/-
E 5,000/-
F 5,000/-
G 10,000/-
H 10,000/-
for each of these accounts i want denomination auto divided from the main collection of 1,00,000/- denomination by any formula
if anyone has solution for this pls help