Copy Pivot Table Source Number Formatting

When you add numeric fields to a pivot table, Excel applies General format, in most cases. It doesn’t matter if you’ve spent hours applying fancy custom formatting to the numbers in the source data – all of that is ignored.

After the numeric data has been added to the pivot table, you can manually change the formatting of each field, in the Field Setting window. It’s not too painful in a small pivot table, but can take a while if there are several Values fields.

If you’ve bought a copy of my PivotPower Premium Add-in, it has commands that quickly format all the Values fields, or just the fields that you have selected. That’s better than manually changing the fields, but still isn’t perfect.

Macro Applies Source Formatting

To make things easier for you, I’ve created a new macro that:

• looks at each Value field in the pivot table,
• finds that field in the source data, and
• applies the same number formatting that it uses.

This should save you some time – it’s much quicker than formatting each field individually.

NOTE: I’ll be adding this macro to the Number Formatting options in my PivotPower Premium Add-in too, in the next version.

Test the Sample Code

If you’d like to try this macro in your own workbooks, go to my Contextures website, where you can copy the formatting code, and put it into a workbook of your own.

You can also download the sample file that I created, and test the code there. In the sample file there is a small table of data, and a pivot table based on that data.

There are two buttons on the pivot table sheet:

• General Format – click this button to apply General number format to all the Values fields. I use this to clear out any formatting, before testing the other macro.
• Source Format – click this button to copy the number formatting from the Source Data.

NOTE: The macro works for pivot tables that are based on a formatted table, or a named range.

Download the Sample File

To download the sample file, go to the Copy Source Formatting page on my Contextures website. The zipped file is in xlsm format, and contains macros.

__________________