Pivot Table Formatting Old Style

abacusicon 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.

PivotFormatOld01

Here’s the same data in a default Excel 2007 pivot table.

PivotFormatOld02

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.

PivotFormatOld03

That moves each Row field into a separate column, just like it was in older versions of Excel.

PivotFormatOld04

Change the PivotTable Style

Next, instead of using the default PivotTable Style, click None, in the Light group.

PivotFormatOld05

That clears out all the fancy formatting, and leaves you with a bare bones pivot table.

PivotFormatOld06

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).

PivotFormatOld07

And there you have it! An old style pivot table in Excel 2007.

_________

You may also like...

13 Responses

  1. Jon Peltier says:

    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.

  2. CurlyQ says:

    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.

  3. sea says:

    or you can right click on the 2007 style table, click pivot table options, display, classic pivot table.

    done.

  4. Manjini says:

    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.

  5. Excelerocious says:

    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

  6. Saumen says:

    Thnx for the valuable information

  7. Suzanne says:

    Thank you so much! I HATE the new style pivot table!

  8. Tim says:

    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.

  9. Jessie says:

    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. ☺

  10. John says:

    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.

  11. Frank says:

    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….

Leave a Reply to Manjini Cancel reply

Your email address will not be published. Required fields are marked *