Pivot Table Macro and Excel Giveaway

learnexcel 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 it below.

Bob is also having an Excel Giveaway on his Simply Learning Excel blog this week, and the details of that are also listed below. It’s a great giveaway, even if you already know the Excel essentials. The top prize a personalized 1-hour online Excel session with Bob, and it’s transferable! So, if your co-worker (or relative) is driving you crazy with Excel questions, you can send them to Bob for help. Sweet!

And now, here’s Bob…

Macro to Establish Classic PivotTable Settings, Plus…

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.

A “Simple” Giveaway

The Giveaway Prizes

  • The top prize, with a value of approximately $145, is a copy of the book Simply Learning Excel 2007 and a live, personal, one-hour Excel session with Bob via WebEx. Bob will cover and/or answer any questions about any topic from the book, e.g. PivotTables, Filters, VLOOKUP, etc. Bob will contact the winner to arrange a mutually convenient time, and also find out what the winner would like to cover. This prize is transferrable, so the winner can give it to a family member or co-worker, who needs some extra help, or they could even share it with a few co-workers, in a meeting room.
  • There will be four runners-up, and each one will win a free book, with a value of approximately $20 each. They, along with any reader of the book, can get their questions answered at no extra cost via a live WebEx session by submitting their questions to Ask the Author… LIVE!™ at www.SimplyLearningExcel.com.

How to Enter

Simply submit your answer to this question at http://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/:

  • In all the years (or days) that you have known Excel, what is the most valuable thing – feature, formula, etc – you have learned about Excel,

and/or

  • What is the one thing about Excel that you have shared with others that they have valued most? Was it PivotTables? Keyboard shortcuts? Spreadsheet design? Macros? Please let us know.

The Giveaway Rules

  • All responses must be submitted to Bob’s blog at http://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/.
  • The entry deadline is 12:00 noon (Eastern time zone) on Monday, August 23rd, 2010.
  • One entry per person – any additional entries will be deleted from the draw, with one exception. If you answer both parts of the question, you will be entered twice.
  • A random draw will select the winner from all valid entries.
  • The winner will be notified by email, so please provide a valid email address. This will not be publicly visible.
  • The winner will be announced on Bob’s blog on Tuesday, August 24th. Good Luck!

Watch the Video

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. For more information, please visit Bob’s website at http://SimplyLearningExcel.com.

_________________

You may also like...

7 Responses

  1. Yard says:

    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. Tammara says:

    I did not see any place on the http://www.simplylearningexcel.com/blog/ site to enter my response. His site tells you to submit your answer to SimplyLearningExcel.com but that just brings you to his home page with there is a link to the contest … but round and round you go.

  3. Tammara, sorry you had trouble finding the place to submit your comment. I changed the blog links so they take you right to the page with the blog post and comments.

  4. @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.

  5. Vishal Bhilare says:

    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.

  6. Jeff Weir says:

    Vishal…can you describe in a little more detail what you are trying to do?

  7. 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 Vishal Bhilare Cancel reply

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