• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting multiple value fields in pivot table at once!

Ravi Dawar

Ravi Dawar

New Member
Hi,

I have a table that has 14 columns.
Column 1 contains the project names
Column 2 has sub projects (1 project can have multiple sub projects)
Column 3-14 contain monthly costs from Jan to Dec for each of those projects. The costs are up to 2 decimal points.

I created a Pivot which has projects and sub projects in Rows section and all monthly costs in Values section. In the pivot, I want all costs to appear without any decimal point.

Right now, I have to go to each individual field and change the format.

Is there a way to change the format of all 12 fields at once? (I know I can change the format in the source table but I do not want to do that)

Appreciate an answer!

Ravi Dawar.
 
Hi Ravi. One of my most frequently used macros is my InstantPivot routine, that does different things depending on the context:
  • If you point it at an existing PivotTable, it adopts the formatting of the source data
  • If you point it at a range, it creates a PivotTable out of it, and turns the source range into an Excel Table, so that any time you add more data to the source range, the PivotTable knows to include it automatically on refresh.
That routine will solve your problem if your source data is already formatted the way you want it.

Otherwise, what you're after is pretty simple: I'll whip up a dedicated macro and post it back here when I get some time, unless someone else beats me to it.
 
jeffreyweir said:
Hi Ravi. One of my most frequently used macros is my InstantPivot routine, that does different things depending on the context:
  • If you point it at an existing PivotTable, it adopts the formatting of the source data
  • If you point it at a range, it creates a PivotTable out of it, and turns the source range into an Excel Table, so that any time you add more data to the source range, the PivotTable knows to include it automatically on refresh.
That routine will solve your problem if your source data is already formatted the way you want it.

Otherwise, what you're after is pretty simple: I'll whip up a dedicated macro and post it back here when I get some time, unless someone else beats me to it.
Click to expand...
Thanks a lot Jeffrey, how can I get hold of instant pivot? I really appreciate your help with the macro, that would save me a ton of time.
 
You must log in or register to reply here.
Back
Top