Tips for Excel, Word, PowerPoint and Other Applications
15-Second Pivot Tables - Building Proficiency
Why It Matters To You
The only way to build proficiency is to practice. Through a combination of keyboard shortcuts in Excel and a basic familiarity with pivot tables you can become quite fast at building pivot tables - I can do this exercise in under 15 seconds, almost without ever picking up my mouse. This changes the fundamental nature of the pivot table from a discrete and unpleasant task to just another condition of the job.
To this end, I've put together a short data set, actually the same one used in the Introduction to Pivot Tables article. You should have already read through both of the articles linked above before you dive into this exercise. When you're ready, I'd like you to save this file to your desktop and see how fast you can generate a pivot table showing Gross Amount by for each PO# and ShipTo location.
How To ...
Download the a sample data set and save it somewhere where you can find it easily. This is a representative invoice for office supplies and we'd really like to better understand which office locations are ordering supplies. Once you have the file saved, open it up and move to the top left hand corner of the data set. This should be cell A3. Then we're going to do the following:
- Hold down CTRL + SHIFT then left arrow and then down arrow to select the entire data set, which should be highlighted now. Make sure your column headers were included in the selection.
- Hold down ALT + D, P to activate the Data: Pivot Table wizard
- Hit Enter to acknowledge you want to analyze Microsoft Excel data and create a pivot table, the default selection
- Hit Enter again to acknowledge your highlighted cells is the data you want to use in the pivot table
- You should now be in Step 3 of 3. Hold down ALT + L to activate Layout wizard
- The more astute of you will realize by now that I still haven't yet touched my mouse. Now you're actually going to pick it up. Grab the PO# and drag it over to the Row box, grab ShipTo and place it in the Column box, finally, grab the GrossAmt and put it into the Data section.
- Hit Enter to close the Layout window
- Hit Enter one last time or ALT + F to finish the pivot table wizard
If you did this right, you should have a new Sheet1 with your pivot table in it. Try it again. I can do this in under 15 seconds and the only time I pick up my mouse is to drag the pivot table elements into place.
Practice it a few times until you can do it naturally and quickly.
Notes
| Last updated | 9/2/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | |
| Related Tips | None |