Displaying Lakhs and Crores in Excel

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 🙂

Advertisements

182 thoughts on “Displaying Lakhs and Crores in Excel

  1. 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!

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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