Pivot Table Quick Formatting Macro

My friend and client, Bob Ryan, from Simply Learning Excel, has just published a hands-on, no fluff, Excel book — Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less.

To celebrate the book launch, I asked Bob to share one of his favourite Excel tips with you, and you can read Bob’s answer below.

Bob’s Pivot Table Macro

Bob Ryan’s top Excel tip:

Some time ago, I was getting ready to train a group of people about PivotTables. As I was documenting the steps, I started feeling more and more annoyed at the number of steps it took to create the kind of PivotTable I typically use.

So, I wrote a macro to automate the steps. (I also submitted a suggestion to Microsoft to allow users to create a customized standard/default PivotTable, but I don’t see it in Excel 2010.)

I wanted to share this macro, but since my website is generally geared to folks who don’t know about or need macros (yet), I asked Debra if I could be a guest writer, and she kindly agreed.

A final note: While I appreciate Debra’s willingness to share this information on her site, the content really belongs to her because most of this information came from her books, website, and/or her personally. I hope you find this useful.

What the Macro Does

Once you insert a PivotTable and enter a field(s) into the Values area, the code does the following to PivotTable(1) on the active sheet:

  1. Applies the Classic PivotTable display, with gridlines and no colors (I like this so I can Copy the PivotTable and Paste Special Values and Formatting.);
  2. Ensures that only data that still exists in the data that drives the PivotTable will appear in the PivotTable dropdown lists.
  3. Sets all fields to ascending order with no subtotals, including fields that are not in the Row Labels or Column Labels areas, and;
  4. For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and, if the field in the Values area is named “Amount” or “Total Amount” it shortens the label in the PivotTable to “Sum Amt” or “Sum TtlAmt” respectively.

Download the Sample File

You can view the pivot table formatting macro code, and download Bob’s Format Pivot Table Macro sample file.

The file is zipped, and in Excel 2007 format. Because it contains a macro, you’ll have to enable macros when you open the file.

Watch the Video

Thanks, Bob, for sharing your pivot table macro!

To see how much time you can save by using a macro to format a pivot table, watch this video.

It took me a couple of minutes to manually format the Excel pivot table, and change some of the pivot table options, and just a couple of seconds to do all the same steps with Bob’s macro.

Note: If you record your own pivot table formatting macro, follow Bob’s example to add variables, so the macro works on any pivot table, no matter what the field names are, or where it’s located.

About the Author

Robert Ryan, MBA, CPA is a long-time passionate user of Excel, the author of “Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less,” a unique step-by step book designed for basic to intermediate users, and the host of “Ask the Author… LIVE!™” where Bob answers questions from readers of his book in live WebEx sessions at no extra cost.

_________________

7 thoughts on “Pivot Table Quick Formatting Macro”

  1. Hi,

    This will be a popular one I’m sure!

    I’d be tempted to replace

    Set pt = ActiveSheet.PivotTables(1)

    with this

    On Error Resume Next
    Set PT = ActiveCell.PivotCell.PivotTable
    On Error GoTo 0

    If PT Is Nothing Then
    MsgBox “No PivotTable selected”, vbInformation, “Oops…”
    Exit Sub
    End If

    to take account of multiple pivot tables on the same worksheet.

  2. @Yard, thanks for the suggestion for making the code run on the selected pivot table. Next week, I’ll post about the different options for selecting a pivot table, or running the code on all pivot tables. I’ll include your example as one of the options.

  3. hi
    I am looking for a VB Macro to change filter of Row Labels/Column Labels of pivot table.
    Have looked for various macros on this website, but was unable to find any code related to my i requirement.
    Please mail me the code if it possible to change the filter of row labels using vba code.

  4. Some useful tips on Excel Pivot Tables:
    You can apply any formatting to the pivot tables. MS Excel has some very good pivot table formats (and they are better in Excel 2007 and 2010).
    You can easily change the pivot table summary formulas. Right click on pivot table and select “summarize data by” option.
    You can also apply conditional formatting on pivot tables although you may want to be a bit careful as pivot tables scale in size depending on the data.
    Whenever the original data from which pivot tables are constructed, just right click on the pivot table and select “Refresh Data” option.
    If you want to drill down on a particular summary value, just double click on it. Excel will create a new sheet with the data corresponding to that pivot report value. (This is extremely useful)
    Making a pivot chart from a pivot table is very simple. Just click on the pivot chart icon from tool bar or Options ribbon area and follow the wizard.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.