Microsoft Office Tutorials and References
In Depth Information
Excel Macro Examples
Here are the high-level steps needed to create this type of setup:
1. Create your pivot table and a pivot chart.
2. Start recording a macro.
3. While recording, move a pivot field from one area of the pivot table to the other. When
you’re done, stop recording the macro.
4. Record another macro to move the data field back to its original position.
5. After both macros are set up, assign each one to a separate button.
You can fire your new macros in turn to see your pivot field dynamically move back and forth.
Offering one-touch reporting options
The last two examples demonstrate that you can record any action that you find of value. That is, if
you think users would appreciate a certain feature being automated for them, why not record a
macro to do so?
In Figure 13-14, notice that you can filter the pivot table for the top or bottom 20 customers. Because
the steps to filter a pivot table for the top and bottom 20 have been recorded, anyone can get the
benefit of this functionality without knowing how to do it themselves. Also, recording specific
actions allows you to manage risk a bit. That is, you’ll know that your users will interact with your
reports in a method that has been developed and tested by you.
This not only saves them time and effort but it also allows users who don’t know how to take these
actions to benefit from them.
Figure 13-14: Offering prerecorded views saves time and effort and allows users who don’t know how to use
advanced features to benefit from them.
Feel free to visit Chapter 14 for a refresher on how to create the top and bottom reports
you see in Figure 13-14.