In the last blog post, you saw how to turn off buttons and drop downs in an Excel 2007 pivot table. In the comments, Jon Peltier suggested a simple Copy and Paste Values instead, but that doesn’t paste the pivot table style formatting.
Then, John Walkenbach pointed us to one of his articles, which describes how to paste the pivot table style formatting from the Office Clipboard. Thanks! So, if you want to send someone a pivot table that’s not really a pivot table, you can use that technique.
Pivot It Old School (Old Skool?)
While we’re on the topic of pivot table formatting, someone asked me how to make an Excel 2007 pivot table look like an old-fashioned Excel 2003 pivot table. He didn’t explain why he wanted the old formatting, but maybe it’s for consistency in a published document. It’s easier to make a couple of new pivot tables look like the old ones, than to reformat all the old ones. Or maybe he just likes the old style better.
Anyway, in case you’ve forgotten, here’s what a plain old pivot table looked like, in Excel 2003.
Here’s the same data in a default Excel 2007 pivot table.
Change the Pivot Table Report Layout
The first step is to change the Report Layout, so all the Row Labels aren’t in the same column.
- On the Ribbon, under PivotTable Tools, click the Design tab.
- In the Layout group, click Report Layout, then click Show in Tabular Form.
That moves each Row field into a separate column, just like it was in older versions of Excel.
Change the PivotTable Style
Next, instead of using the default PivotTable Style, click None, in the Light group.
That clears out all the fancy formatting, and leaves you with a bare bones pivot table.
Finish the Formatting
If you want it to look even more like the old pivot tables, you can add some grey fill to the cells that contain field headings. I also turned off the grid lines, because parts of the pivot table are opaque, and other parts aren’t (Excel 2007 and Excel 2003).
And there you have it! An old style pivot table in Excel 2007.