Displaying Lakhs and Crores in Excel April 23, 2006
Posted by exertia in India, Personal Computing.trackback
Here's a useful Excel tip for users in India and other places that use number formats like 100,00,00,000 instead of 1,000,000,000:
You may have faced the problem of Excel always displaying amounts in millions and not in lakhs e.g. 1,000,000 instead of 10,00,000. Here's a trick to get them to display correctly:
Right-click on the cells which you want to display as lakhs, and click on Format Cells, then go to the Custom category and enter the below formats:
For Lakhs (+ve and -ve):
With 2 decimals:
[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00
Without decimals:
[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0
For Lakhs and crores (+ve)
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
For Lakhs and crores (-ve)
[<-9999999](##\,##\,##\,##0.00);[<-99999](##\,##\,##0.00);##,##0.00
There is an easier way to change the global settings from Settings > Control Panel > Regional Settings > Numbers, but that will affect all your Excel sheets and the change will be visible only on your system and not on your files which you send to others.
Happy XL-ing 
Thank you.. been breaking my brains trying to do this
Hi Dude!
Thanks for it. I needed it badly.
Thanks
This really helped with our spreadsheets!
Ayyappa
Indian Property Show
Susanna
He knows best what good is that has endured evil…
Drugo
Time is the great healer… Drugo
Great help
Thanks, this information has really helped
Really worked a Big help…
Microsoft Customer Service also didnt have any clue..
pls post other Calcuations & Formula relevant to Indian Context
Tejash
Thanks a lot. I, and many of my colleagues, have been at our wits’ (well, what little there is of it) end to find a solution. I turned to google and a link to your page appeared like manna from the heavens! Seriously, your piece is very useful from the Indian perspective where we are accustomed to lacs and crores rather than millions and billions.
Thanks again.
Thank you very much.
Cheers
Thank you very much; this is a great help.
thanks a ton…this is like i getting the loan itself…
But how to have Crores and lakhs with and without decimal ? You have lakhs with and without decimals – not not with crores -
Congratulation.. I was unable to found myself the solution. I’ts not only good is there an elegant and powerfull solution.
Thanks a lot friend . . .
Keep going
Great Job. Is there any of chance of same format for both +ve and -ve numbers of lakh and crore figure
Thats cool!. Thanks a lot.
Thats gr8.. Apprediate you
Just wanted to say thanks. I use this frequently.
thanks a lot
Thank you. This format adds the Rs. symbol
[>9999999]” Rs.”##\,##\,##\,##0.00;[>99999]“Rs.”##\,##\,##0.00;##,##0.00
can u tell me how to reverse this. As i want to show the format in millions series, but by default its showing in the lakhs format.
thank u
Hi..
Would like to convert the number 1,00,00,000 to apear like 1,00.00 (i.e. in lacs).
When we want to appear a number in million we can do it by changing the format of the cell to 0.00,,
Same way instead of million i want to conver it in Lacs.
Please provide the solution if you know.
Thanx
Parth
Dear Sir,
Presently in excel if you want to display figure in lakhs you have to divide by figure by 1 Lakh, in case of thousand or million you have to simply go to
Format Cell —-> Number ——> Custom, there in Type option you have to type 0.00, or 0,(single comma for thousand) or 0.00,, or 0,, (double comma for million) and so on.
There is absolutely no need to divide the figure by million or thousand the figure remain in full but only thing is that it displayed or appear in million or thousand.
Now is that similar type of formating possible for displaying figure in Lakhs through visual basic editor.
Thank You
Regards,
Amit Malpani
This allows to format a cell either +ve or -ve figures seperately.
Is there a way to have single formula for both.
Thnx a million and also a billion for this….i’m gonna impress my boss with this…thnx a crore and lakh again…
thanks man a lot…!!! burnt my brain b4 this frm many days ….. Yipee…!!
thanks a lot.. and give a complete formulae for the positive and negative also in the same formulae.
The way to do this is to have Conditional Formatting where you can add 2 rules, one for >0 and another for <0, and apply that to the range you want. It works
Is there is any way to call the complete figures in Lakhs in another sheet. I have tried through Custom but it is limited to thousand please help me out
My question is similer to Amit Malpani’s Question!
You are my man….
Dude, This rocks. Thanks a crore.
Rgds
Ani
Perfect! Thanks for this useful tip.
-Abhi
It really helps, thanks a lot…
thanks a lot. it was really helpful
Fantastic! This is brilliant. Why can’t the Microsoft guys put this in as a standardised feature?
Because this is not easily available, there is a movement to get Indians to start talking millions-billions instead of lakhs-crores.
this is available by default in windows 7 indian locale
very helpful small hints goes long way
Very nice, many thanks and lot of love
thanks a lot……..it was so helpful
this really works…..
Thanks a lot! We are not accustomed to millions & billions.
Thanks a lot Sir. It helped.
Helo Everyone,
I need a quick assistance from you.
My question is: In my excel sheet we have a column called “entry date” and “closed date”
Who ever started working on an issue they should enter the “entry day” the day they started working on it eg:- 06/04/2010(mm/dd/yyyy) and as soon as they resolved the issue they have to capture the date in “closed date” field
eg: I have closed on 06/09/2010.
When I do TAT calculation I will do “closed date” – “entry date”
eg: 06/09/2010 – 06/04/2010 I will get a result as “5″
My problem sarts here in the “5″ days included ‘saturday’ & ‘Sunday’
I wan to eliminate weekend and calculate only the working days.
Kindly assist me how to exclude week end days from my TAT report.
Regards,
Vinod.
Dear Sir,
I am facing some problem for lakh and crore number. It is in million & Billion
I tried in regional setting and did it but while I put coma it is working in million & billion system.
I got lot problem to write number in lakh & Crore. Like I want to write 52,75,25,000 But it is not working. I tried in regional setting but it appears in system but while I click coma on the number it appears in million and billions.
Please advise me to solve the problem
Hii!! I want some help Regarding formatting cells for displaying Rs. 10000000 (1 Crore) to Rs. 1.00 (1 Crore).
Can anybody help me with the solution please.
Atul Agarwal
Just follow this simple soln.
[>=10000000] ##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00
oH THIS TIP HAS OPNE THE GATES TO HEAVEN!! THANKS DUDE!
thanks dude it helped me a lot
amt u r great this helped me a lot
Thanks for this very useful tip. Was wondering how I can have this custom format permanently in Custom format list within Excel. Otherwise, for every spreadsheet I have to add this format.
Is there anyway I can make this custom format global within Excel, so that it is available for any spreadsheet that I open?
This is great…just love it when the number appears in in lakhs with the comma’s in the right place
Thanks!
can some one help reverse this…
i mean by default my system now puts nos in lakhs…
i need it back in millions…
it will b a big help
thanks
i haven’t changed any of the settings on my mac, yet excel now has everthing in lakhs and crores and i don’t want it that way. i need it in millions. can anyone help? i can’t seem to find a solution and it’s really frustrating.
thanks.
Thanks really helped
It is indeed worth thanking…
thanks a lot..
The best reply
thanks
its great Help
Thanks very much .I have really need it.I have got it.
Great! Hitherto I was wondering as to how to put commas without getting difficulties while calculating the sum etc., Thanks a lot. BTW, can u please suggest how to round the amount in crores to the nearest thousands by using this method. I tried by typing in the custom format as “#,##0,”. But this rounds only to the nearest thousands.
Thanks
Great job, solved my concern
Thanx a lot!
Hi,
This solution is not working in MAC. any Idea ?
For Follow up
Thanks a lot man!
Thanks dude.. really helped me out..
thanks boss,
it is very usefull in my work as i am working in a government office.
Thank you very much
Great, thank you
sanjiv
pass aj ke jindage may bhut khuch pasay ke bina ko kiSE KO NAHI JANTA HA JAI MATA DI
[...] • 6:01am Anon User The other way to do this is with a custom format, as described http… (more) Sign up for free to read the full text. Login if you already have an account.Comment [...]
Very helpful. Thanks a lot! Your blogpost came up at the very top in Google.
thanx a lot
Thanks a lakh plus a crore:-)
Thanks a lot for this format . looking for this link long. Tried multiple things but were not working. Thanks a lot.
I was wondering if you ever thought of changing
the structure of your site? Its very well written;
I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better.
Youve got an awful lot of text for only having 1 or two pictures.
Maybe you could space it out better?
But when we applying the above formula figure not come correct for CRORES??? it shows like this : 123,45,678.00 it should display 1,23,45,678.00 what to do?
You are a rockstar.
Thanks for sharing your thoughts about finance.
Regards
Thanks a lot. God bless you. Indeed I am obliged. Please let me know, if possible how to write these figures in words in excel. The formula =spell(cell number) doesn’t work.
best regards
It was a great help! Thanks.
Wow, this piece of writing is pleasant, my younger sister is analyzing such things,
therefore I am going to inform her.
Thank you very much, it solve my problem.
All I wanna say is…. Thanx!!! U ROCK!!!
thanks a lot
Going back to email distribution, make sure that your potential customers
have the opportunity to sign up for your email newsletter.
With 49 percent of users turning to their phones and tablets
to do business searches, there is a growing need for reliable information.
You can also do an in-house customer survey, or ask other business leaders in your
field for their feedback.
nice format very helpful
Thanks a lot !!
Sir I need more help from u.
i want the numbers shown like in below condition
for 120000 – 1.2 lakhs.
please help me
Hi, Neat post. There is an issue along with your web site in web explorer, may
test this? IE nonetheless is the marketplace leader and a huge part of people will miss your magnificent
writing because of this problem.
thank’s a lot
Quality articles or reviews is the main to invite the visitors to pay a quick visit the web
page, that’s what this web site is providing.