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.

Objective

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!

Resources

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

13 Comments

  1. Rudy B Quall on February 5, 2016 at 6:53 am

    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?

    • jefflenning on February 5, 2016 at 6:56 am

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

  2. Amanda Blake on February 5, 2016 at 7:59 am

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

    • jefflenning on February 5, 2016 at 8:03 am

      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!
      Thanks,
      Jeff

  3. Amanda Blake on February 5, 2016 at 8:05 am

    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!

    • jefflenning on February 5, 2016 at 8:24 am

      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 🙂
      Thanks
      Jeff

      • Amanda Blake on February 5, 2016 at 8:26 am

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

  4. Jovita on March 10, 2016 at 7:52 am

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

    • jefflenning on March 10, 2016 at 7:54 am

      Yes…to do that update the format code to include [Red] for negatives, something like this:
      #,###;-#,###[red]
      Thanks
      Jeff

  5. Jovita on March 10, 2016 at 8:01 am

    Thank you, it worked!

    • jefflenning on March 10, 2016 at 8:02 am

      🙂

  6. CYNTHIA JOHNSON on November 5, 2018 at 8:50 am

    I created the Macro and used it for months. Every time I opened Excel, the Personal.XLSB workbook would open. One morning last week, it didn’t open. I can’t figure out how to get it back. I could find no file when clicking on the Unhide button. Can it be recovered?

    Thanks,
    Cynthia

  7. CYNTHIA JOHNSON on November 5, 2018 at 9:08 am

    I found the workbook! Yay! I just don’t know why it’s not opening when excel is opened. Thank yo

    Cynthia

Leave a Comment