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 🙂
[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0
but Zero Value is Dess ? Code Pl
LikeLike
Truly you will be blessed by the Gods of Computation!
LikeLike
It’s a pity you don’t have a donate button! I’d certainly donate to this excellent blog!
I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account.
I look forward to fresh updates and will share
this blog with my Facebook group. Chat soon!
LikeLike
Thank you! It worked like a magic 🙂
LikeLike
enter 100000 in any other blank column and copy it . then select all the cells we need the digits in lakhs, right click, go to “paste special” click the “divide” option and enter
LikeLike
Thanks a Ton…
LikeLike
Hi, Would you know how to modify this formula to include larger numbers such as 1892,23,13,258.26 and negative numbers such as -1,241,657,500.00 – all in one.
LikeLike
how can write 36,83,861.40 to 36.8386140 en excel sheet
LikeLike
thx a lot
LikeLike
It is very useful,before i do gama separation digit by digit. This method helps 99% time saving
LikeLike
GOOD,
LikeLike
Hi, I want to scale the results of excel formula by crores. For eg: if my excel formula returns 10,000,000 i want to see it as 1.00 only. Its very urgent. Please help.
LikeLike
how to do this in excel
LikeLike
thnksssssssssssssssssssssssssssssssssssssss
LikeLike
good. thanks
LikeLike
nice
LikeLike
i am using Window XP
I could not get the problem solved
LikeLike
was quite helpful, Thanks and cheers
LikeLike
I want to convert the figure of Rs. 143,250.00 into Rs 1.43 Lacs.
For example :-
Rs. 1,43,250.00 = Rs 1.43 Lacs
Rs. 28,55,785.00 = Rs 28.55 Lacs
Rs. 1,25,38,746.00 = Rs. 125.38 Lacs
Please help me to do this.
LikeLike
write 100000 in a remote cell; click copy; select data you wish to round off to lakhs; right click to ‘paste special’ and then choose divide. it should do the trick
LikeLike
boss i want to show numbers in lacks
like 120000=1.2
LikeLike
dear sir,
I write 500000 but show 5 lakhs in excel sheet by using ‘Format Cell’ option.
LikeLike
I was breaking my head. Many Many Thanks
LikeLike
Thanks a million.
LikeLike
dear sir,
I write 25627658 but show 25.63 in excel sheet by using ‘Format Cell’ option.
Please replay
LikeLike
It’s not worked in download excel sheets documents.
LikeLike