Formatting Indian Lakh and Crore numbers into Microsoft Excel
So, you are working on a excel sheet and calculating the amounts for a client. But the problem is everything you are asked you have to check the commas to convert the millions into crores.
It's a big hassle and a problem if you make a human error, luckily you can convert it into native number formats and this will not only make calculations easy but also look good.
Let's see how
Let's start with a basic number
Right click of the cell and select "Format Cells" or check Ctrl + 1 after moving to that cell
In the format cell window, you will see various options
If you select Number you can format it has a number and select the decimals and commas
But that will give you the numbers in millions and billions
to convert to Indian system do the following:
in format cells select 'Custom'
In the type cell delete whatever is written and paste the code given below
[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0
and press enter
Now you will see that the number have changed to Indian format
If you also want to add decimals then use this code in custom type
[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00
If you want to add the rupee symbol '₹' as well
use the below formula
[>=10000000]₹##\,##\,##\,##0;[>=100000]₹##\,##\,##0;₹##,##0
for both rupee symbol and decimals use the following code
[>=10000000]₹##\,##\,##\,##0.00;[>=100000]₹##\,##\,##0.00;₹##,##0.00
Now you are done with formatting you number system
Finally, In case your client doesn't want to see such big numbers in and wants you to round them to Cr value so that the numbers are only given as their crore value and decimal.
You can use the below code and paste it in the custom tab in format cell and it will give only the cr value
[>=10000000]#\.0,, "Cr";[<10000000]0\.#0,, "Cr"
And it will give you the lakh and crore values in rounded figures with decimal in place
Through this method you can easily convert all the figures into Indian number system format as per your needs.
