A Classic Pivot Table

Old School Pivot Table

Pivot Tables changed in Excel 2007 and 2010. The default setting doesn’t let you drag and drop the field names on the Pivot Table itself. You could do that in Excel 2003 and you can still do it in the newer versions. All you need to do is change a setting.

Just like a Classic iPod you can now have a Classic Pivot Table!

Right click the Pivot Table and select Pivot Table options. Click the Display tab. Tick the Classic Pivot Table layout option and click OK. See image below.

 

If you already have a number of Pivot Tables and you want to convert them all to the Classic layout the macro below will convert all the Pivot Tables in a file. (As with most macros – save your file before trying it and check the results before saving it again – if it doesn’t give you what you want, don’t save the file – just close it without saving.)

Sub PT_Classic()
 
Dim ws As Worksheet
 
Dim Pvt As PivotTable
 
    For Each ws In Worksheets
 
        For Each Pvt In ws.PivotTables
 
            With Pvt
 
                .InGridDropZones = True
 
                .RowAxisLayout xlTabularRow
 
            End With
 
       Next Pvt 
 
    Next ws
 
End Sub

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.