How to show Indian Currency Format in Excel?

Posted on July 26th, 2010 in Excel Howtos - 58 comments

This is a guest post by our excel ninja – Hui.

Indian Currency Formatting, Number Formatting in ExcelToday 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

  1. Goto the Control panel and select the “Region and Language” button
  2. Select “Additional Settings”
  3. Set digit groupings to the Indian grouping like this:
    Indian Currency Formatting, Number Formatting - Settings in Control Panel
  4. 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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

58 Responses to “How to show Indian Currency Format in Excel?”

  1. Cyril Z. says:

    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.

  2. jpc says:

    I don’t have additional setting option in region and language button. Is this facility available only in vista and window 7?

  3. Dominik Petri says:

    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.

  4. Leonel Q. says:

    Interesting, but could you explain what the backslashes are for? I’ve never used a backslash in a custom format.
    Regards.

  5. Chandoo says:

    @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?

  6. Rick Rothstein (MVP - Excel) says:

    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.

  7. Ninad Pradhan says:

    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.

  8. Naga says:

    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!

  9. bill says:

    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

  10. Rick Rothstein (MVP - Excel) says:

    @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.

  11. Chandoo says:

    @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.

  12. bill says:

    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

  13. Rick Rothstein (MVP - Excel) says:

    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

  14. Rick Rothstein (MVP - Excel) says:

    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.

  15. Amit Joshi says:

    Thank you for the perfect solutions for excel. I resolved my problem through this ..

  16. Oh! thank God for this!

    I am going to try it right away!

  17. bill says:

    @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

  18. Rick Rothstein (MVP - Excel) says:

    @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).

  19. R.Ravi says:

    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

  20. Rick Rothstein (MVP - Excel) says:

    @R.Ravi,

    Can you make use of the information at this link?

    http://en.wikipedia.org/wiki/Indian_numbering_system

  21. Rahul. says:

    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.

  22. Native says:

    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!

  23. shamisha says:

    Thanks Ninja-HUI,
    Great! Nice formatting tips. It helps.

  24. […] To use Indian formatting for numbers (i.e. Lakh, Crore instead of Millions) see this article: “Indian Currency format in Excel” […]

  25. Rakesh Kumar Mishra says:

    How can we chang the US doller into indian currency in balance sheet (Excle file).

  26. Hui... says:

    @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

    • Martin says:

      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

      • Martin says:

        @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

  27. AMIT KUMAR says:

    Hi, Thanks Hui for this nice solution… u made my work easier… thanks a lot

  28. Chood says:

    Thanks Hui.

    -Chood

  29. Raghu says:

    Thanks friend,
    It saved lot of time for me.
    Regards,

  30. murali says:

    Thanks dear friend

  31. Ken says:

    You deserve a big thanks…. Your a genius

  32. Aviator says:

    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!

  33. […] "Lacs "##0.00;[Red]-"Lacs "##0.00;0 For more on formatting Indian currency see this link How to show Indian Currency Format in Excel? How to show Indian Number Formats in Excel? | Chandoo.o… […]

  34. shyamala says:

    nice ……………..!
    helped me a lot……………

  35. SAN says:

    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.

    • Hui... says:

      @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

  36. Sachin says:

    Thanks Chandoo,
    It was very very useful.
    Regards,
    Sachin

  37. naveen sharma says:

    Thanks Chandoo,
    I.
    Regards
    naveen

  38. mangi says:

    Thanks lot for the solution.

  39. Jyoti says:

    Hi Hui,

    Thank you so much you solved my problem

  40. Abhay G says:

    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.

    • Abhay G says:

      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.

  41. ragav says:

    excel ninja-hi this works well for positive numbers. ofr negative numbers it is not.
    any ideas?

  42. Vinod James says:

    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

  43. Kailas Gaikwad says:

    Thanks a lot, i am unaware of it, it helps my account department.

  44. Ajeet says:

    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 !

  45. Nikunj says:

    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.

  46. Dheeraj says:

    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 ??

  47. Rajesh says:

    Thankyou very much Sir,
    its very useful.

  48. Wang says:

    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

  49. Santhi says:

    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

  50. Iedc says:

    Cool ! Thanks

  51. Ketan says:

    Thanks man
    it is really helpful

  52. Bhuvaneshwer says:

    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.

  53. Rijo says:

    Thanks a lot for your Instruction.

Leave a Reply