Adding Indian Style Commas to numbers in MS Excel - Using Cutom Number Format

     Well, the better way to use Indian Style commas while working in MS Excel is to change the 'Region and Language' Setting of your Windows system. However, there is another work around in MS Excel to automatically add commas to number in Indian Style i.e. first comma at three digits and then the subsequent commas are at interval of 2 digits. For instance, 1,000,000 will appear as 10,00,000. This is particularly useful when you are working with currency in INR. 

 Steps to follow:

    1. Select the range which needs to be formatted. Let's take the following sample data which uses the default 1000 comma separator :

 

    2. Press Ctrl + 1. This will open the number format dialog box and then select Custom category shown below.

   

    3.    The add the this custom number format string [>1000000]"Rs" #\,##\,##\,##0"/-";[>=100000] "Rs "##\,##\,##0"/-";"Rs "##,##0"/-" into the 'Type Text Box' as shown below.

 

    4.  Then click OK button and the following result may appear:

 

 The above process also prefixes the text "Rs " and suffixes the "/-" which is commonly used in India for writing the amount of money. You can do any mathematical operation on it as the Custom Number Formatting doesn't alter the underlying value, it only changes the way it appears for easy readability. I have seen many people who type their commas and Rs in excel but doing so is totally unproductive because it changes the format from Number to Text and then you can't do any analysis or operation on it which is the purpose of using excel in the first place. 

It is pertinent to mention here that the above custom format string has a limitation of formatting  numbers smaller than 1 Billion (100 Crore). No commas will be added to number bigger than 1 Billion.

If you don't want to add "Rs " or "/-" symbol in your formatted numbers then simply remove those parts from the custom format string (including the quotes) and it will only add commas to your numbers. 

 





Comments

Post a Comment

Popular posts from this blog

Do the Data Entry in your Excel WorkSheet in a right way!

Image
Introduction Let's admit that entering data in your worksheet manually is not fun. Mostly, you get your data from other database in CSV and TXT format which you then import in your sheet or you do the copy from one location and then paste in your worksheet. But sometimes, it becomes necessary to feed data manually to your worksheet before you can unleash the power of Excel. It may be noted that data entry is a slow process and, if done in haste, it becomes prone to errors especially if you have delegated the data entry work to your subordinates (in many organizations there are data entry operators for this type of work).  The Data Entry work requires constantly looking at the source material and then typing it in the designated Cell of your worksheet. In most cases, you type the contents of one cell and then press Enter or Tab to move Down or Right of your cell. In most cases, you want to move down in your Excel Worksheet in row by row manner (especially if your source material is
Read more

Take the print out of your spread sheet the way you want.

Image
  Introduction It is a common workplace issue that people face when they take the print out of their neatly created Excel sheet only to find out that one or more columns of their Excel sheet have exceeded the margin of the A4 sheet. It is frustrating and also leads to the wastage of paper.   Excel automatically divides your sheet into separate pages for printing based on margin settings, scaling option, paper size etc. without any regard to the number of columns or rows you may be having. As the number of columns of your sheet increases, it becomes more likely that your sheet will not print the way you want i.e. all columns in one page or all rows in one page. People try many ways to work around this by changing the Font Size, manual resizing of Cell width, applying Landscape orientation to accommodate more columns in one page etc. But sometimes, even after these adjustments the sheet just doesn’t fit in one page. Also, it doesn’t increase your productivity if you are doing such
Read more