The Pivot Table Wizard isn’t available on the Ribbon in Excel 2007. To open the Pivot Table Wizard, you can use the keyboard shortcut — Alt + D, P — as described in the article on creating a pivot table from multiple sheets.
Another option is to add the Pivot Table Wizard button to your Quick Access Toolbar (QAT), by following the steps below.
Customize the QAT
To add the Pivot Table Wizard to your QAT, follow these steps:
- Click on the Customize Quick Access Toolbar button
- Click More Commands
- From the ‘Choose commands from’ drop down list, select ‘Commands Not in the Ribbon’
- In the list of commands, click PivotTable and PivotChart Wizard
- Click the Add button, then click OK
Open the Pivot Table Wizard
Now that the Pivot Table Wizard button has been added to the QAT, you can click it to open the Pivot Table Wizard.
___________
When I add the pivot table wizard I am still unable to go to Layout which is what i need to see.
To be able to use Pivot Table – Layout feature in Excel 2007
In the pivot table, right click. Select the third option from the bottom of the menu
which should be “Pivot table options”.
A window will pop up. Select the third tab “Display”
Look down the list to “Show Classic Pivot Table Layout”
Press OK
On “PivotTable and PivotChart Wizard – Step 3 of 3,” there is an action button called “Layout” that opens the “PivotTable and PivotChart Wizard – Layout” screen, which was heavily used in Microsoft Excel 2003. This is what cleveland is asking for help in accessing. I am scouring the web looking for the same information and keep finding dumb responses like this. It’s not the classic view of the pivotTable he needs, it’s the the layout design dialogue.
You’re right — the Layout screen is gone, even if you manage to open the PivotTable Wizard in Excel 2007.
Were there some actions that you could do there, that you can’t replicate in the PivotTable Field List?
As “pretty” as the “PivotTable Field List” is, it’s not as user friendly as the Layout screen. Support for it reminds me of how everyone is saying how nice Google+ will be to go to from Facebook because of less privacy concerns and easier to understand TOS. I’ve used Google +. It’s not as great as hoped for in actual use. In Excel 2003, the layout screen has a preliminary view of how your information will be laid out. In addition, and of lesser priority, while the PivotTable Field List brings this about “real-time” (and not in the classic view the initial responder wrongly ascribed to in responding to cleveland’s request), creating P&P for slides is oppressive with it. Also, you can edit and format all the values fields you needed to right away in the layout screen, enabling less mouse “travel.” The more people use computers, the more the user-friendly applications will succeed. iWork is improving. Microsoft need be aware, especially with the growing market share of OS X.
Thanks for describing the features that you prefer in the Layout screen. I’ve been using Excel 2007 for a while, and it’s hard to remember all those little things about Excel 2003.
You can right-click on a field in the drop zones at the bottom of the PivotTable field list. Then, change the field settings, similar to what you did by double-clicking on a field in the Layout Screen. And you’re right, it’s more mouse travel, but changing the settings in the field list is probably quicker than doing it in the pivot table.
One advantage to the field list, over the Layout Screen, is in a pivot table with lots of fields — it’s easier to see the list of field names. They can be tough to read on those Layout Screen buttons!
People in my company are still on excel 2003. My group was moved up to Excel 2007 only because we look at hundreds of thousands of rows of data while Excel 2003 only allows less than 66k per worksheet. But there are things missed from Excel 2003 that are now less intuitive in Excel 2007, PivotTable creation being one of them.
I disagree with the last statement. My fields are not even showing up on the field list. The 2003 layout view showed you the table exactly as you would see it. The field list is not in the same order or view so you have to try items to see what happens. It is alot more time consuming than the layout view. The answer to reading the layout buttons is to make the box bigger not take away the view and replace it with something that is harder to use.
You really have to wonder *why* the Pivot Table wizard was removed, or if it was removed, why you can’t get to the data input you would have found via it via the new Pivot Table Data tab.
It sounds like Debra is with MS, so I’m sorry Debra, here’s some user feedback from the coal face. Office 2007 was a complete and unmitigated disaster. I don’t mean it was bad, I mean it was utterly terrible. Not one of my professional colleagues…not a single one…likes it. It’s considered to be just terrible. Office 2010 has tried to pull things back, but to be honest, I’d much rather use Office 2003 any day. The only thing in 2010 I can say I really like is being able to paste plain text into HTML messages directly rather than having to go via Notepad just to dump the formatting. Excepting that, Office 2003 was a pinnacle and it’s all gone downhill from there.
There’s an old adage: Don’t fix it if it ain’t broke. Sadly the attempt to improve the Office ribbon and UI in Office 2007 and 2010 are just a disaster. I can find nothing any more and constantly have to resort to F1. I *deeply* resent the time I waste having to work around dumb things like removing the Pivot Table wizard and there being apparently no place else to update the data source (sheet reference) for the table. My time is precious and you guys, despite your good intentions, burn lots of my time and I do not appreciate it.
So sorry. I’m sure lot of effort and work going into trying to improve things, but I have to tell you that you’re going backwards. You’re not improving, you’re just completely messing with my ability to complete my work with MS software. What’s more, you want me to *pay* to have my software wrecked!
There’s comment here about MS needing to be aware of the competition. Listen to it. Don’t start telling us how much effort you put in and how great you feel the changes are, just listen to the comments made here. If we all have to learn new software we might as well move to something new as opposed to struggle with something we *used* to know and use well. At least then we could be frustrated with something we know we don’t know as opposed to something we once knew, but know no longer.
@Iain, I’m not with Microsoft, and can relate to the problems that you’re having with the transition to Excel 2007. I’m supporting customers in Excel 2003, as well as Excel 2007 and 2010, so it’s a constant struggle to remember where the commands are. At least the Ribbon can be easily customized in Excel 2010, so that helps somewhat.
I posted a reply to your comment yesterday, but it seems to have disappeared into the ethernet, or I accidentally deleted it during my daily spam cleanup. Sorry about that.
I also was a great fan of Excel 2003, skipped 2007 and started using 2010 for personal use. I do use 2007 for work and have got to like some of its features also. For someone who needs to use 2007 and wants to get up to speed a book I found valuable was
Excel 2007 Expert Skills with the Smart Method.
I have become accustomed to using the pivot tables in 2010, and on occasion put them in the same view as I would in Step 3 of 3 of the Pivot table wizard in 2003. I have got to like the added features in the options and design tabs for banding columns and rows, adding blank rows between row items, etc. It seems pretty easy to add a pivot table
to a PowerPoint slide and make more formatting changes there if desired.
@JENNIE, thanks for the book suggestion, and for sharing your experience on upgrading to Excel 2010.
I think the reply to @JENNIEE rather makes my point, and without wishing to appear as if I’m whining, it is ‘interesting’ that a post from someone who says they like/are getting used to Office 2007 gets a response, yet a comment from someone saying that having used Office from long before it was Office (I go back to Word 1 PC, Word Mac 3) and find the changes, dropping of things from menus, no compatibility mode etc. is making the software more difficult to use gets, no comment at all…as if willfully ignored.
I think it was Bill who said something along the lines of; “Your greatest critic is your greatest asset” (we used to have it painted on the wall at a place I worked). He also apparently said; “Success is a lousy teacher. It seduces smart people into thinking they can’t lose.”
Sage words Bill, it’s what got you to where MS are now, but from my standpoint, it now seems very much as if people only want to hear the good news. The sad story is I know *no one* who has liked Office 2007. And I work in software and so deal with people with a lot of use and experience, and to a man/women, they have *all* disliked it. And if those making decision at MS continue to ignore this kind of feedback, the problems that behest Vista and to an extend Win7 will continue.
I remember Vista being touted to me as the great breakthrough. We were even due to have Steve B come talk to us about it, but then look at how it turned out. So please Debra, take the feedback as constructive input, simply being told everything is grand will indeed seduce you into thinking you can’t lose.
You’re welcome Debra. Where I work there is also a mixture of 2003, 2007 users. Those that have not used anything else take to it immediately which I guess is only natural. I bought the 2010 version after trying the Beta version and really enjoyed some of the new features.
I can relate to the ‘struggle with remembering where the old 2003 commands are’. The ability to customize the ribbon in Access 2010 was a plus for me, then I started to do the same in Excel. When I needed to use 2007 for work I found a couple of good links on the Microsoft site:
Reference: locations of Excel 2003 commands in Excel 2007
Interactive – Excel 2003 to Excel 2007 command reference guide
Another site: Chandoo.org I have found useful – search for
Master Excel 2007 Ribbon with this Free Learning Guide
@JENNIE, thanks for the links, and there is a search tool for the Ribbon too, from the Microsoft Office labs:
http://blog.contextures.com/archives/2011/06/27/quickly-find-excel-ribbon-commands/
I am so angry because I have wasted hours trying to construct a pivot table in Excel 2007 that I have been routinely contstructed for years in Excel 2003. I used to use that layout window, accessed from the button in the pivot table wizard. Instead Microsoft has apparently disabled that feature; I guess in an attempt to upgrade. The result for me has been utter frustration. No matter how I try to use that new layout feature I can never get it to construct the table the way I have in the past. I have even resorted to re-installing the 2003 version and copying the finished table back to the 2007 version. This is such a waste of time and resources. Why in the h*ll can’t they leave the things alone that work.