CA Rishabh Pugalia (ExcelNext)
I have a large record set of the amount. I want to add prefix i.e. Rs. in my records so that I can apply sum, average, min, max function. I can easily do it by typing it manually but then I will not be able to apply any function.
If I enter any amount in this column I will need to add Rs. prefix manually. If you know this trick then you do not have to do this manually.
Now from the above picture, it is clear what my raw data is and what I actually want.
1. Select your range and then press Ctrl +1.
2. Now I am going to custom format in excel and then type "Rs." 0.0. This ensures that numbers are there with one decimal.
As soon as you click on ok you will find that numbers are formatted with Rs. as a prefix. And it will look like below.
Notice these are numbers with one decimal but I don’t find any comma separated values, the way you have in million and lakhs.
So what will be my approach? To apply this you need to go to home tab then apply the comma separator, reduce one decimal and then press ctrl +1 and press custom. You will find a long list of custom codes which signifies comma separator should be with the million and lakhs.
Now before the starting of the custom code put "Rs.". You will notice in the preview section Rs. has been added. And then click on OK.
As you press ok you get the benefit of both prefix Rs. and the numbers with a comma separator.
Recommended course to learn MS Office tools like Advanced Excel, Charts, PowerPoint Ninja, VBA Macros and Word for Finance Professionals.
Click here: /coaching/584-ms-office-bundle.asp
CAclubindia
