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