Today, we’ll look at pivot table formatting, old style, but first, here’s an update. In the last blog post, you saw how to turn off buttons and drop downs in an Excel 2007 pivot table, and there are a couple of updates on that topic.
First, in the comments for that post, 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
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.
Default Pivot Table in Excel 2007
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.
Fields in Separate Columns
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.
_________
Maybe it’s just me showing my age, but whenever I see a table or pivot table formatted a la 2007, I can’t help but think some kid got loose with watercolors again. The most minimalist built-in styles are still overdone.
Another advantage to the old skool pivot tables is the ability to drag field buttons around right on the worksheet. All the new pivot features hardly make up for the loss of this capability.
I think it also works to right click on the pivot, go to pivot table options, select the display tab and check the option called classic pivot table layout.
or you can right click on the 2007 style table, click pivot table options, display, classic pivot table.
done.
HI Experts,
Here in the above examples datafields are appearing in column wise. But when i write in vba the datafields are appearing row wise.
I am using the vba coding as.
With pt.PivotFields(“Job Size”)
.Name = “Pages Produced”
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
Please correct me.
Most of my college friends and I adore the 07 improvements and 10 even more so. Rote statistical analysis via pivot has never been easier. Give me watercolors anyday.
PS. Silly, you can still drag field buttons, you just have to do it within the Pivot Table Field List.
Cheers
Thnx for the valuable information
Thank you so much! I HATE the new style pivot table!
I like the old pivot table style for the borders when the layout is tabular. Is there any way to create a style based on this ‘none’ style so that the borders are in the same position, but with custom formatting? I’ve tried again and again by duplicating and modifying one of the other styles, but with no success.
THANK YOU SOOOOOO MUCH!!!!! I already had a pivot table report in my file that had it’s gridlines inside of the report(greatly minimizes errors, confusion, and eye strain for me)and when I made a new pivot table in the same file I could not get my precious gridlines no matter what I tried…until I glanced on the snipet of your website in the search results talking about using the old style of pivot table report and as soon as I saw it I knew that this was the answer and viola! problem solved. I cannot fathom why the people at microsoft would want to eliminate such a useful option in their new style, let alone tease us with the option to show or hide gridlines, only to have it applied to every other cell except the actual pivot table report. Are they sadistic or just daft? anywhoo, you saved me a great deal of aggravation and I sincerely thank you again. ☺
You’re welcome! Glad to help.
Classic pivot table the borders grouped the data with solid lines. When I change to classic view I can still get the lines but they are much lighter. Is there a way to get the solid line or make changes to those lines – color or thickness? I would appreciate any help, I have been unable to solve this one.
Thank you also for this article, i HATE the way pivots are built in 2007/2010/2015/2016 Office….
Now I am back to being a boss of my own pivot again in stead of Excel making me feel stupid with all that indentation etc….