Macro to Quickly Format PivotTable Values


PivotTable users frequently spend time assigning the same number format to PivotTable values. To my knowledge, there isn’t a built-in setting that allows us to define a default value field format. But, it is pretty easy to set up a macro that instantly assigns a desired format. This post walks through the steps of creating such a macro.


Generally, PivotTable value fields are automatically assigned the General format, as shown below.

Default Excel PivotTable format by Jeff Lenning

To clean up the report, we manually change the format of the value field, to something such as a number, no decimals, with a comma, as shown below.

PivotTable format by Jeff Lenning

Rather than perform this task manually for each value field in each PivotTable we create, we can set up a macro to apply a specific format.

Detailed Steps

Overall, we’ll use the macro recorder to have Excel prepare a basic starter macro and save it in the Personal Macro Workbook. Then, we’ll modify the macro so it can be used on new PivotTables going forward.

Our steps will be:

  • Record a macro that formats a cell
  • Edit the macro
  • Set up a QAT button
  • Clean up

Let’s get started.

Record a macro

We will ask Excel to watch us apply the desired format to any random cell and save the recording in the Personal Macro Workbook. To do this, we simply start the macro recorder by clicking the following ribbon command:

  • View > Macros > Record Macro

In the resulting Record Macro dialog box, we assign a name, avoiding spaces and funky characters, and opt to store the macro in the Personal Macro Workbook, as shown below.

Record Excel Macro by Jeff Lenning

Now…I mean right now…Excel is watching everything you do. So, simply apply the desired number format to the cell that is currently active. You can use any of the standard methods to apply your desired format.

Here, I opened the Format Cells dialog with Ctrl+1, and then assigned a number format, no decimals, with a comma.

Then, STOP the recorder. You can do this with the following ribbon command:

  • View > Macros > Stop Recording

Step one is now complete. Since our macro simply formats the active cell, we need to make it a little bit smarter.

Edit the macro

We need to update the macro and tell it to apply this format to all PivotTables on the active worksheet. Since the macro is stored in the Personal Macro Workbook, we’ll need to first unhide this workbook in order to edit the macro.

To unhide the Personal Macro Workbook, simply click the following ribbon icon:

  • View > Unhide

In the resulting Unhide dialog box, select PERSONAL.XLSB as shown below.

Unhide Excel workbook by Jeff Lenning

With the Personal Macro Workbook visible, we can now edit any macros it contains. To do so, we’ll open the Macro dialog by clicking the following ribbon icon.

  • View > Macros > View Macros

In the resulting Macro dialog box, as shown below, we simply pick our new macro and click the Edit button.

Macro dialog by Jeff Lenning

Doing so opens the Visual Basic Editor and places the cursor inside our new macro, as shown below.

Visual Basic Editor by Jeff Lenning

The macro simply applies the specified number format to the active cell. We want our macro to apply this formatting to all value fields in all of the PivotTables on the active worksheet.

Thus, we’ll make a few changes, as follows.

Sub PTValueFormat()
  For Each pt In ActiveSheet.PivotTables
    For Each df In pt.DataFields
      df.NumberFormat = "#,##0"
    Next df
  Next pt
End Sub

Note: you can copy the code above and paste it into your visual basic editor instead of typing it. 

Or, we could add an additional loop so that the macro assigns the format to all value fields, in all PivotTables, on all sheets in the active workbook. Here is the code for that version.

Sub PTValueFormatAllSheets()
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      For Each df In pt.DataFields
        df.NumberFormat = "#,##0"
      Next df
    Next pt
  Next ws
End Sub

Now, anytime we run this macro, Excel will apply the desired format to the PivotTable value fields. Let’s make the macro easy to run by setting up a QAT icon.

QAT button

To set up a QAT (Quick Access Toolbar) icon, we simply open the Excel Options dialog with the following:

  • File > Options

Click the Quick Access Toolbar category on the left side of the dialog, and then you want to Choose commands from Macros, as shown below.

Add QAT by Jeff Lenning

Select the new macro in the left list box and then click the Add button. The macro will appear in the right list box, and then you click OK to close the dialog.

Now, anytime you want to assign the desired format to your PivotTable value fields, just click the new QAT icon.

Clean up

Since we previously used the Unhide command to show the Personal Macro Workbook, we’ll probably want to hide it again. To do so, make it the active workbook and then click the following ribbon icon:

  • View > Hide

Also, since we want to save our new macro, we’ll want to be sure to click Save to the following dialog when existing Excel.

Save Changes to Excel Personal by Jeff Lenning

And that is how to set up a macro to apply a desired format to PivotTable value fields. If you have any other fun PivotTable formatting tricks or formatting macros, please share by posting a comment below…thanks!


This article was written by jefflenning


  1. Rudy B Quall

    This is great teaching tool. Perfectly communicated, give this guy a raise. How do you create the screen shots and dialogue for a presentation like this?

    1. jefflenning Post author

      Thanks Rudy! I just use the snipping tool built-in to Windows…and I appreciate the kind comment!

  2. Amanda Blake

    Is there a way to modify this so that negative numbers are formatted with parentheses, instead of the “-” symbol?

    1. jefflenning Post author

      Hi Amanda!
      Yes…absolutely! You can simply update the format code as desired. For example, changing the code to “#,##0;(#,##0)” would format negative numbers with parentheses. You can edit the macro by hand and insert any of the standard Excel format codes…which are actually the same ones used in the Format Cells dialog, Custom format. You can also define the desired format code when you initially record the macro. Either way should work just fine.
      Hope it helps!

  3. Amanda Blake

    Actually, figured that out – use df.NumberFormat = “#,##0_);(#,##0)”

    Can you also create a macro to change ALL numbers in a worksheet to a format, not just those in a pivot table? Thanks!

    1. jefflenning Post author

      Great…glad you got it!
      Regarding an additional macro to change all number formats…I’ll add that topic to my “things to blog about” list :-)

      1. Amanda Blake

        Thanks, that would be great. I found this to be very helpful! I will keep an eye out for that one!

  4. Jovita

    Great blog, thank you! Is there a way to set negative numbers to be displayed in red?

    1. jefflenning Post author

      Yes…to do that update the format code to include [Red] for negatives, something like this:

  5. Jovita

    Thank you, it worked!

    1. jefflenning Post author


  6. Faith

    Post writing is also a fun, if you be acquainted with
    after that you can write if not it is difficult to write.

  7. kaajas

    Good replies in return of this issue with firm arguments and telling the whole
    thing regarding that.

  8. Ирина

    Watch the Format Pivot Table in Classic Style macro video to see the manual formatting time, compared to running the macro.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.