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 ðŸ™‚

### Like this:

Like Loading...

*Related*

## Published by nikunjsanghvi

exerting thoughts to overcome inertia.
View all posts by nikunjsanghvi

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