• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+
The Office Tech Pro

Pivoting Multiple Ranges in Excel® 2007: Yes, you can!

Get PDF file

by on
in The Office Tech Pro

I’m not sure whether it was intentional, but there seems to be something missing from the Create Pivot Table dialog box. In Excel 2007 and 2010. In the previous version of Excel (2003), when you clicked on Data Pivot Table and Pivot Chart Report, you saw a selection that allowed you to pivot multiple ranges (it was called, multiple consolidation ranges). In 2007-10, the title of the dialog box seems to have changed from Pivot Table and Pivot Chart to the Create Pivot Table dialog box. Beyond the name change, the option to pivot multiple ranges is also missing.

In any case, there are two ways to regain that functionality.
1. The shortcut Alt+D+P
2. Adding the Pivot Table and Pivot Chart Wizard button to the Quick Access Toolbar (QAT)

For those who are still learning about how the new ribbon interface works, there are two things to know. First, if you use to use the Alt hotkey shortcuts to access the drop down menus in previous versions, most key sequences still work! For example, we used to click on the Data drop down menu by pressing Alt+D and the Pivot Table and Pivot Chart selection by then pressing P, so Alt+D+P. This hotkey sequence like many others, (I use Alt+E+S to see the Paste Special dialog box) will do what they used to do. If you weren’t a hotkey user, this doesn’t do a thing for you. Mouse clickers, read on.

For those that prefer to click a button, you can add most buttons from previous versions to the QAT. To do that:
1. Click the down arrow next to the QAT.
2. Click the More Commands selection.
3. Change the Choose commands from field to All commands
4. Use the single key type ahead feature in the list below to locate the Pivot Table and Pivot Chart Wizard button.
5. Double-click or click the Add button to put it on your QAT.

Let me take this opportunity to thank you for your patronage to my blog and wish you a very, very Happy Thanksgiving!

Leave a Comment