jump to navigation

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 :-)

About these ads

Comments»

1. Sunil - September 25, 2006

Thank you.. been breaking my brains trying to do this :-)

2. Darshan - September 26, 2006

Hi Dude!

Thanks for it. I needed it badly.

Anonymous - June 26, 2014

SURAJ

3. Ayyappa - October 11, 2006

Thanks

This really helped with our spreadsheets!

Ayyappa
Indian Property Show

4. Helegor - October 30, 2006

Susanna

He knows best what good is that has endured evil…

5. Drugo - November 30, 2006

Drugo

Time is the great healer… Drugo

6. Venu - January 30, 2007

Great help

7. Gawde - March 2, 2007

Thanks, this information has really helped

8. Tejash DOshi - April 25, 2008

Really worked a Big help…
Microsoft Customer Service also didnt have any clue..
pls post other Calcuations & Formula relevant to Indian Context
Tejash

9. D S Mahanty - June 11, 2008

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.

10. Amit - June 13, 2008

Thank you very much.

Cheers

11. Balaji - June 15, 2008

Thank you very much; this is a great help.

12. Lakshmeesh - July 17, 2008

thanks a ton…this is like i getting the loan itself…

13. Kamal Ratra - August 29, 2008

But how to have Crores and lakhs with and without decimal ? You have lakhs with and without decimals – not not with crores -

14. Jean-Jacques Maillioux - September 12, 2008

Congratulation.. I was unable to found myself the solution. I’ts not only good is there an elegant and powerfull solution.

15. L. Balakrishnan - November 7, 2008

Thanks a lot friend . . .
Keep going

16. murthy - November 20, 2008

Great Job. Is there any of chance of same format for both +ve and -ve numbers of lakh and crore figure

17. Madhavan - December 5, 2008

Thats cool!. Thanks a lot.

18. Venkatesh - January 29, 2009

Thats gr8.. Apprediate you

19. Coty - April 27, 2009

Just wanted to say thanks. I use this frequently.

20. Anonymous - April 27, 2009

thanks a lot

21. Arun - May 17, 2009

Thank you. This format adds the Rs. symbol
[>9999999]” Rs.”##\,##\,##\,##0.00;[>99999]“Rs.”##\,##\,##0.00;##,##0.00

22. Shyam - June 7, 2009

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

23. Parth Modi - June 9, 2009

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

24. Amit - June 16, 2009

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

Vivek Rungta - January 27, 2014

Dear Amit,

THanks for the above suggestion, it really helped me a lot.

Request you to provide me formula for displaying the figures in Crores. I am not ale to get the same from the above, The numbers are displayed either in millions or billions.

Best Regards,
Vivek Rungta

25. Belliappa - June 21, 2009

This allows to format a cell either +ve or -ve figures seperately.
Is there a way to have single formula for both.

26. Rasik - July 26, 2009

Thnx a million and also a billion for this….i’m gonna impress my boss with this…thnx a crore and lakh again…

27. Raj - September 21, 2009

thanks man a lot…!!! burnt my brain b4 this frm many days ….. Yipee…!!

28. Kannan - October 1, 2009

thanks a lot.. and give a complete formulae for the positive and negative also in the same formulae.

ash - December 7, 2010

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

29. CA Aashish - October 8, 2009

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

30. CA Aashish - October 8, 2009

My question is similer to Amit Malpani’s Question!

31. jmd - October 13, 2009

You are my man….

32. AA - November 21, 2009

Dude, This rocks. Thanks a crore.

Rgds

Ani

33. Abhishek - December 2, 2009

Perfect! Thanks for this useful tip.

-Abhi

34. Fermi - January 25, 2010

It really helps, thanks a lot…

35. hari. - February 3, 2010

thanks a lot. it was really helpful

36. Vikram Murarka - February 8, 2010

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.

jason - June 10, 2011

this is available by default in windows 7 indian locale

37. m - February 13, 2010

very helpful small hints goes long way

38. Shaikh Atiq - February 20, 2010

Very nice, many thanks and lot of love

39. praveen - February 23, 2010

thanks a lot……..it was so helpful

40. aakanksha - April 19, 2010

this really works…..

41. fetchie - April 29, 2010

Thanks a lot! We are not accustomed to millions & billions.

42. wibas - April 30, 2010

Thanks a lot Sir. It helped.

43. Vinod - June 2, 2010

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.

Anonymous - July 16, 2014

Use Networkdays formula

44. Sukumar Chakrabortty - June 5, 2010

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.

45. Sukumar Chakrabortty - June 5, 2010

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

46. Atul Agarwal - June 7, 2010

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

47. SPT - July 7, 2010

Just follow this simple soln.

[>=10000000] ##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

48. IRFAN SAIT - July 16, 2010

oH THIS TIP HAS OPNE THE GATES TO HEAVEN!! THANKS DUDE!

49. sasi - August 4, 2010

thanks dude it helped me a lot

50. VASANT - August 11, 2010

amt u r great this helped me a lot

51. Ujjwal - October 1, 2010

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?

52. Shariq - November 13, 2010

This is great…just love it when the number appears in in lakhs with the comma’s in the right place

53. Sanjay - November 28, 2010

Thanks!

54. amit - December 1, 2010

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

55. naz - February 11, 2011

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.

56. Sneha Pednekar - March 1, 2011

Thanks really helped

57. Dinesh Shanbhag - April 5, 2011

It is indeed worth thanking…
thanks a lot..

58. Anonymous - July 6, 2011

The best reply

59. Lokesh - July 7, 2011

thanks

its great Help

60. Sachin - August 11, 2011

Thanks very much .I have really need it.I have got it.

61. Sreedharan - October 3, 2011

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

62. Anonymous - November 30, 2011

Great job, solved my concern

63. Jain Thomas Pala - December 10, 2011

Thanx a lot!

64. Senthazal Ravi - January 10, 2012

Hi,

This solution is not working in MAC. any Idea ?

65. Senthazal Ravi - January 10, 2012

For Follow up

66. Suresh - February 22, 2012

Thanks a lot man!

67. Anonymous - March 12, 2012

Thanks dude.. really helped me out..

68. santosh - March 28, 2012

thanks boss,
it is very usefull in my work as i am working in a government office.
Thank you very much

69. Abu Thahir - April 4, 2012

Great, thank you

70. sanjiv - May 1, 2012

sanjiv

71. sanjiv - May 1, 2012

pass aj ke jindage may bhut khuch pasay ke bina ko kiSE KO NAHI JANTA HA JAI MATA DI

72. Microsoft Excel: How can I get Microsoft Excel for Mac to show a U.S. number with Indian-style "lakh" and "core" formatting, for example "100000" as "1,00,000"? - Quora - May 9, 2012

[...] • 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 [...]

73. Prakash - July 17, 2012

Very helpful. Thanks a lot! Your blogpost came up at the very top in Google.

74. Surendra Suren - July 18, 2012

thanx a lot

75. Anonymous - July 18, 2012

Thanks a lakh plus a crore:-)

76. Anonymous - July 19, 2012

Thanks a lot for this format . looking for this link long. Tried multiple things but were not working. Thanks a lot.

77. hcg canada import - August 15, 2012

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?

78. Kumar - September 3, 2012

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?

79. Sooj - September 25, 2012

You are a rockstar.

80. Best binary options platform - October 21, 2012

Thanks for sharing your thoughts about finance.
Regards

81. Fouzia Bilal - December 3, 2012

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

82. gbk - December 12, 2012

It was a great help! Thanks.

83. advertise my home based business - January 10, 2013

Wow, this piece of writing is pleasant, my younger sister is analyzing such things,
therefore I am going to inform her.

84. Kajal Roy Choudhury - January 24, 2013

Thank you very much, it solve my problem.

85. shreekanth mekala - February 13, 2013

All I wanna say is…. Thanx!!! U ROCK!!!

86. feroz - February 20, 2013

thanks a lot

87. ravalramarc.thoughts.com - February 22, 2013

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.

88. Anonymous - April 1, 2013

nice format very helpful

89. Anonymous - April 3, 2013

Thanks a lot !!

90. sudharshan - April 4, 2013

Sir I need more help from u.
i want the numbers shown like in below condition
for 120000 – 1.2 lakhs.

please help me

sekar - December 18, 2013

Sir I need more help from u.
i want the numbers shown like in below condition
for 120000 – 1.2 lakhs.

please help me
Reply to my mail id
ncs_sekar67@yahoo.co.in

91. Bennie - April 8, 2013

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.

92. saddam - April 26, 2013

thank’s a lot

93. Levela Skin Care - May 16, 2013

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.

94. mj financial Partners solutions - May 27, 2013

I know this web site provides quality dependent posts and other stuff, is there any
other web site which presents these stuff in quality?

95. Anonymous - May 29, 2013

Thanks a lot, I was looking for this format since long time. Search ended here. Thanks again!

96. pure raspberry ketone - June 16, 2013

Thank you for sharing your info. I truly appreciate your efforts
and I am waiting for your next post thank you once again.

97. raspberry ketones - June 16, 2013

Superb, what a website it is! This web site gives valuable information
to us, keep it up.

98. raspberry ketone reviews weight loss - June 17, 2013

Yes! Finally something about red raspberry.

99. http://dermalmedsblog.com - June 18, 2013

Pretty! This has been an extremely wonderful post. Many thanks for supplying this information.

http://seekorea.pe.kr/zbxe/?mid=email

100. Buy mito slim - June 25, 2013

I loved as much as you will receive carried out right here.
The sketch is attractive, your authored material stylish.
nonetheless, you command get got an edginess over that you wish
be delivering the following. unwell unquestionably come more formerly again as exactly the same nearly very often inside case you shield this hike.

http://pendientedemigracion.ucm.es/BUCM/wiki/index.
php/Usuario_Discusi%C3%B3n:BeckyCarr

101. Buy Nutra Burn - June 28, 2013

Its like you read my mind! You seem to know a lot about this, like
you wrote the book in it or something. I think that
you can do with some pics to drive the message home a little bit, but instead of that, this is magnificent blog.
An excellent read. I will definitely be back.

102. ATUL AGGARWAL - July 2, 2013

THANKS A LOT

103. us fast cash customer service number - July 9, 2013

Hi there, I enjoy reading through your post.
I like to write a little comment to support you.

104. Anonymous - July 22, 2013

Thank U… and will need continue support

105. Carmen Elias - July 29, 2013

Now I am ready to do my breakfast, once having my breakfast coming yet again to read more news.

106. Kerty - July 30, 2013

Thanks a Ton for this one!!! very useful…

107. meg - August 5, 2013

thanks buddyyyyyyyyyyyyyyyyyy

108. nik - August 11, 2013

how can i enter figures in this format 54,501,074.00

109. nik - August 11, 2013

how can i enter figures in this format 54,501,074.00 or 72,818,402.98 etc?????

110. dooraniapl.beeplog.com - September 16, 2013

I like the valuable information you supply to your articles.

I’ll bookmark your blog and test again right here regularly.

I am fairly certain I will learn a lot of new stuff
proper right here! Good luck for the next!

111. Anonymous - September 17, 2013

thanxxxxxxxxx man…

112. Danish - September 17, 2013

thanks dude…..

113. health needs - September 24, 2013

Its such as you read my thoughts! You seem to grasp a
lot approximately this, like you wrote the book in
it or something. I believe that you simply could do with
some % to drive the message home a little bit, however other than that, this is excellent blog.
An excellent read. I’ll certainly be back.

114. government jobs - September 24, 2013

My relatives all thee time say that I am wasting my time here at net, but I know I am getting knowledge daily by reading thes
nice articles.

115. new mustang 2015 - December 10, 2013

I got this website from my friend who shared with me regarding this web page and at
the moment this time I am visiting this site and
reading very informative posts at this time.

116. best video baby monitor - December 13, 2013

Right now it appears like BlogEngine is the best blogging platform available right now.
(from what I’ve read) Is that what you’re using on your blog?

117. Thomas Verghese - January 9, 2014

Thanks a lot for showing how we could enter numbers as per the Indian System of Lakhs and Cores.
You are doing a great service by giving such pieces of information, which are most useful.
Wonder how I could return my gratitude.

118. my opinion - January 14, 2014

Howdy! This is my first visit to your blog! We are a collection of volunteers and starting a new project in a community in the same niche.
Your blog provided us useful information to work on.
You have done a outstanding job!

119. gagner des gemmes Sur Clash of clans - January 15, 2014

The i – Phone a few and cheaper iphone rumors have been awfully hot lately, still it will
usually the 5S that the majority of leads the way
for Apple path of its next version. Big Boss will let you touch a contact and
immediately the speed dial function activates. Battery life: The i – Phone 5,
5s and 5c batteries support up to 10 hours internet use and up to 220 hours on standby.

120. Premium Tempered Glass - January 16, 2014

The Problems The greatest problem with the Targus 360 degree case is that the stand does not work well.
I personally wouldn’t bother with a screen protector
for two reasons: one, the display is so lush
I don’t want anything getting in the way of it; two, it’s really not that necessary as the Gorilla glass screen is pretty darn tough and resists scratches well.

You will not be doing anything embarrassing online or on your desktop, but knowing that somebody can watch what you’re doing is
slightly disturbing.

121. www.instructables.com - February 9, 2014

What Facebook visitors do is scroll through their
newsfeed until an image gets their attention ‘ it attracts their eyes towards the photo vs.
will host its famous “Live From the Red Carpet” show featuring Ryan Seacrest
and Giuliana Rancic.

122. hot tub repair hull - February 19, 2014

Superb, this article has everything in detail.

123. Http://Www.Clashofclansgemhack.Com - February 20, 2014

Make sure your video connection is the highest grade available.
All the people enjoy extra While I have left everything behind.
The husband’s group transfers property to that of the wife
in trade for procreation and sexuality on the wife’s
part.

124. Thomas Varghese - February 22, 2014

Have no words to thank you for this Innovation.
In appreciation would like to contribute something ; let me know how.
Regards,
Thomas

125. Http://Www.Youtube.Com/Watch?V=Vs7M3JI8Nks - March 1, 2014

You can’t say that it was for seeking gain Or to have punishments to deter crime.

“The only way to stop this is to say something nice about Hinata. Mario and Luigi was then task to join Bowser and get her voice back.

126. Christy - March 18, 2014

I have learn several just right stuff here.
Certainly worth bookmarking for revisiting. I surprise how a lot
attempt you set to make one of these great informative site.

127. http://www.youtube.com/watch?v=ejB3vIM1kQg - March 19, 2014

Pretty nice post. I just stumbled upon your weblog and wished to say that I’ve really enjoyed surfing around your blog
posts. After all I will be subscribing to your feed and I hope you write again very soon!

128. http://tech-gadgets.hpage.com/ - March 23, 2014

Hello there! This is my first comment here so I just wanted to give a quick
shout out and say I genuinely enjoy reading your blog posts.
Can you suggest any other blogs/websites/forums that
deal with the same subjects? Many thanks!

129. high lignan flaxseed oil - March 26, 2014

Hey There. I found your blog using msn. This is a very well written article.
I’ll make sure to bookmark it and return to read more of your useful information.

Thanks for the post. I will certainly comeback.

130. Plombier Avon - March 28, 2014

It’s remarkable to pay a quick visit this web page and reading the views of all mates regarding this paragraph, while I am also keen
of getting experience.

131. http://techgadgets.webgarden.com/ - April 2, 2014

This is very interesting, You are a very skilled blogger.
I’ve joined your rss feed and look forward to seeking more of your excellent post.
Also, I’ve shared your web site in my social networks!

132. fitness program - April 14, 2014

It’s an awesome paragraph for all the online viewers; they will obtain advantage from it I am sure.

133. Anonymous - May 14, 2014

Thanks a lot for your help.

134. internet - June 6, 2014

There are many SEO India firms operating in the country but it is important to select
the right organization to get the best result. Click here to
see information about social bookmarking service or visit our website : submitcube.

include organic SEO, Reciprocal and one way link building, web based content writing for online submissions, Blogging and the like.

135. Viswanathan - June 8, 2014

Thanks. Worked like a charm in Excel 2003

136. skater skirt - July 15, 2014

Ahaa, its fastidious discussion regarding this post at
this place at this website, I have read all that, so now me also commenting here.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: