Pivot Table Data Crunching for Microsoft Office Excel 2007

Take a look at the pivot table shown in Figure 11.1. You know that you can refresh this pivot table by right-clicking inside the pivot table and selecting Refresh Data. Now if you were to record your actions with a macro while you refreshed your pivot table, you, or anyone else, could replicate your actions and refresh this pivot table by running the macro.

NOTE

If you are using Excel 2000 or a later version, your macro security settings may be set to High, effectively disabling all macros. In order to utilize macros, you will have to set your security settings to Medium or Low. Go up to the application menu and select Tools, Macro, Security. It's generally a good rule to set the macro security level to Medium. This will allow macros to run, only after you explicitly enable them when you open Excel.

The first step in recording a macro is to initiate the Record Macro dialog box. Go up to the application menu and select Tools, Macro, Record New Macro.

When the Record Macro dialog box activates, you will fill in a few key pieces of information about the macro:

• Macro name Enter a name for your macro. You should generally enter a name that describes the action being performed.

• Shortcut key You can enter any letter into this input box. That letter becomes part of a set of keys on your keyboard that can be pressed to play back the macro. This is optional.

• Store macro in Specify where you want the macro to be stored. If you are distributing your pivot table report, you will want to select the option This Workbook in order for the macro to be available to your clients.

• Description You can enter a few words that give more detail about the macro.

Because this macro will refresh your pivot table when it is played, you will name your macro RefreshData. You will also assign a shortcut key of R. You will notice that the dialog box, shown in Figure 11.2, gives you a shortcut key of Ctrl+Shift+R. Keep in mind that you will use the shortcut key to play your macro once it is created. Be sure to store the macro in This Workbook. Click OK to continue.

When you click OK from the Record Macro dialog box, you initiate the recording process. At this point, any action you perform is recorded by Excel. That being the case, you want to record the process of refreshing your pivot table.

Right-click anywhere inside the pivot table and select Refresh Data. After you have refreshed your pivot table, you can stop the recording process by going up to the application menu and selecting Tools, Macro, Stop Recording.

Congratulations! You have just recorded your first macro. You can now play your macro by pressing Ctrl, Shift, and R on your keyboard at the same time.