Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Pivot Table Amount in Lacs

0

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.

Answer
Discuss

Answers

0
Selected Answer

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:

  1. Select the PivotTable.
  2. Look tot he Values box and click the value that you want to format and click Value Field Settings...
  3. Then Click Number Format and, in the window that opens, click Custom from the category.

The Lacs / Lakhs Number Format

You must follow these instructions to enter this custom number format (it is weird).

  1. Type 0.00
  2. Input 3 commas
  3. Input 3 spaces
  4. Hit Ctrl J on the keyboard.
  5. Type %%
  6. You're done, so hit the OK button.

I know this is weird and it does leave you with two trailing percentage signs, but it seems to work.

Discuss
0

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.

  1. Select the range of numbers you want to convert.
  2. Hit Ctl+Shift+L

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.

Discuss

Discussion

Thank you so much for your reply
suddushetty (rep: 2) Jun 30, '18 at 3:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login