Pivot Tables-Format Multiple Data Fields

[beadedbytes]

Is there a way to format multiple data fields at the same time?

Data field 1--Number w comma & wo digits
Data field 2--Number w comma & wo digits
Data field 3--Percentage
Data field 4--Currency w $ symbol & cents
Data field 5--Currency w $ symbol & NO cents
Data field 6--Currency w $ symbol & NO cents
Data field 7--Currency w $ symbol & NO cents

In the example above, is there a way to format data fields 1-2 at the same time? Likewise, can data fields 5-7 be formatted at the same time?

 

[Gavona]

I think you would need a macro to do this (and you may want to use it to re-set the column widths as well). I might consider making the macro applicable to any pivot table by storing the info about number format, width above the pivot table in some (hidden) rows. rather than hard coding it.

If you want to pursue a macro approach then post in forum707.

HOWEVER, two other thoughts:

It sounds as if you are recreating the same pivot table, presumably with different source data. You could just copy the pivot table. Go to the Pivot Wizard, hit back a couple of times and change the source data. The number format would be remembered.

If you apply a number format to a pivot field (doesn't have to be a data field) in one of the correct ways then it does get remembered. For column fields you have to use this approach: Hover above the field name in the pivot table and you get a thick arrow. Clicking alternates the selection between the field name and the data. With the data selected apply your format (format, cells.... works ok).

Gavin