I have One Excel file with partywise data and i have summarised that data in pivot table but i want amount in Lacs only using number format.
I have One Excel file with partywise data and i have summarised that data in pivot table but i want amount in Lacs only using number format.
Wow, I have to say I thought this would be A LOT simpler than it apparently is, haha.
So, first get to the number formatting screen for the PivotTable Values column that you want to format:
You must follow these instructions to enter this custom number format (it is weird).
I know this is weird and it does leave you with two trailing percentage signs, but it seems to work.
The long and the short of it is that it can't be done satisfactorily just with number formatting. Therefore, unless you can accept the "%%" following each number, it must be done using a division by 10^5 and the question is how to do that efficiently. In the attached workbook (a copy of the one you posted) it is done in two steps.
Try it.
With 1 Ana's worth of programming you can reduce this to one step - the second one above. In the code that does the job you will see the lines reproduced below.
Set Rng = Selection
' Set Rng = NewSalesReport.Range("J4:J9")
Set and remove one apostroph so that you have this code instead.
' Set Rng = Selection
Set Rng = NewSalesReport.Range("J4:J9")
This will select the range to convert automatically as J4:J9. You can change the range in the code, and now you only have to hit Ctl+Shift+L.
Since, by now, you have become an expert programmer you will also be able to change the number format which the code applies to the calculated cells, or amend the line .Value = CellValue / 10 ^ 5 to .Value = Round(CellValue / 10 ^ 5, 2). This would have the effect of truncating the undisplayed decimal digits and such ensuring that a calculated grand total is equal to the sum of the displayed line totals.
To install this system in your own workbook open both your own and the attached workbook. Press Alt+F11 to open the VB Editor. In the Project Explorer window on the left of the screen, find Module1 in my workbook and drag it into your own. This will install the Sub ConvertToLacs in your workbook. Save it in XLSM format.
Now you will need the Developer tag in your ribbon. If you don't have it, click File -> Options -> Customize Ribbon. Check the Developer tab in the right panel.
On the Developer tab click Macros -> select ConvertToLacs -> Options. Confirm that the indicated Macro Name = ConvertToLacs. Enter L for the Shortcut key, next to Ctr+Shift+. Copy the description of what the macro does from the same location in the attached workbook or write your own.
Voila! You are set to press Ctl+Shift+L in your own workbook now and change millions to lacs to your heart's content.