If your nation's currency system is in Lakhs, and not in Millions and also Billions, this article is what you are looking for.
This is a useful Excel tip for users in India. You might have faced the problem of Excel constantly showing amounts in millions and not in lakhs, e.g. 1,000,000 instead of 10,00,000. Certainly, here's a solution to convert cell values to Lakh Decimal and also obtain them to display appropriately. Please follow the steps below to achieve this.
1. Right-click on the cells which you want to show as lakhs;
2. Click on Format Cells, then go to the Custom-made category and input the following 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. However, this 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.
Then, you may display numbers in Lakhs.
1. Select all the cells where you want the numbers to. Then display in Lakhs.
2. Right-click and go to 'Format cells'.
3. Select 'Personalized'. Then in the' Kind' box, input this. [>= 10000000] ## \, ## \, ## \, ## 0; [> = 100000] ## \, ## \, ## 0; ##, ## 0.
4. Click Ok button.
5. Succeed. You can write 1 Crore (1 with 7 absolutely no's) to test the output.