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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.