Excel 2010: Is there a way to set default pivot table layout to 'Classic'?

[brianh]

Apologies if this has been previously asked/answered. I could not find reference to it.

I've recently upgraded from Excel 2003 to 2010. Now the default pivot table layout is... different. I can't get comfortable with it and always revert to the 'classic pivotable layout' by manually changing the display options in the PivotTable Options window. This is a pain everytime I create a pivot table.

Is there a way to save my 'classic layout' preference so that future sessions of Excel and pivot tables default to the 'classic layout'? If Microsoft didnt build in an easy setting, perhaps there is VBA code that I can save and use for future Excel sessions - if that's the case then can you share the VBA code and process for implementing it on my PC?

Thanks for your consideration. BRIANH

 

[JoePublic]

It would be something like this (just select a cell in the pivot table first):

Sub SetPTClassicLayout()
    With Selection.PivotTable
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
End Sub

 

[p45cal]

Once you have a pivot table in the style you want it, create a new style by:Pivot Table Tools|Design|Pivot Table Styles dropdown: New Pivot Table Style… give it a name if you want. Ignore the set default PivotTable quickstyle for this document check box. Go once again into the Pivot Table tools drop down and you should see your new style in the Custom section at the top. Right-click it and choose Set as Default.

 

[brianh]

Thanks p45cal. I followed your instructions - and as far as I can tell that only affects the formatting of pivot table elements (eg. font, border, fill) of the current workbook file. I want to affect changes to Excel defaults and more than the formatting - such as reverting to the 'Classic' (2003 and prior versions) pivot table display which enables the user to drag fields directly in the grid versus the new way of indirectly manipulating the pivot in the PivotTable Field List pane.

 

[brianh]

Thanks JoePublic. Being a programming neophyte, I know how to access the VBA window but have no idea where to insert your code. Then, I have same question as with p45cal's solution, will the code then only apply to the current workbook file or will it change the Excel default, which is what I desire. In other words, I want to open a data worksheet with a fresh version of Excel 2010 and create a new PivotTable which defaults to the 'Classic' layout.

 

[JoePublic]

It isn't possible to change the default settings. This code has to be run after creating the pivot table to set it to classic layout.

 

[p45cal]

Thanks p45cal. I followed your instructions - and as far as I can tell that only affects the formatting of pivot table elements (eg. font, border, fill) of the current workbook file. I want to affect changes to Excel defaults and more than the formatting - such as reverting to the 'Classic' (2003 and prior versions) pivot table display which enables the user to drag fields directly in the grid versus the new way of indirectly manipulating the pivot in the PivotTable Field List pane.

Right, further searching reveals that this is probably not possible, so you should go with JoePublic.
Where to put the code? Record yourself a macro, but when the first dialogue box comes up (Record Macro), call the macro PTClassicView (or some such), give it a shortcut key combo if you want, then in the Store macro in: field choose Personal Macro workbook, click OK, then record something very mundane such as selecting a cell, then stop the recording. Now press the keybard combo Alt + F8, highlight the newly recorded macro (make sure you're displaying macros from all open workbooks or from PERSONAL.XLSB first), click the Step into button (the Edit button won't let you edit a macro in a hidden workbook which is what Personl.xlsb is), then in the VBE which comes up, click the Reset button (or Run|Reset from the dropdown menus)to stop the code from trying to run, and replace all the code between the first Sub line and the End Sub line with:

On Error GoTo finish
With Selection.PivotTable
  .InGridDropZones = True
  .RowAxisLayout xlTabularRow
End With
finish:

I added a line to prevent a crash if you should run this macro without the selected cell being on a Pivot Table.
Now to put a shortcut to the QAT.
Click on the Customize Quick Access Toolbar dropdown at the right hand end of that toolbar and choose More commands…, click on the Choose Commands from field dropdown and select Macros (it's the 4th one on my machine) then scroll down the list below looking for your PERSONAL.XLSB!PTClassicView (or whaever you've called it), highlight it and click the Add button in the middle of the dialogue box, now click on OK. You should have a new button on the QAT.
First click on a cell in the (new) Pivot table then click the new QAT button.

When you exit Excel, you may be asked if you want to save changes to the Personal workbook, you should make sure you do.