Pivot Table Macro and Excel Giveaway

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.

_________________