How to convert an Excel PIVOT table to Excel 97-2003 format

There are times when the new PIVOT table layout, introduced in Excel 2007 onwards, is not suitable for your needs, learn how to convert the PIVOT table into the older style Excel 97-2003 version…




Excel 2007 introduced a feature of compacting PIVOT Tables, with the aim of making them more modern looking and user-friendly.  While whether this is the case is argued among some of the Excel fanboys and girls out there the thing you need to know is that if for some reason the format of the Excel 2007 PIVOT table is unacceptable you can easily revert it back to the more classic Excel 97-2003 layout.

What is the difference?

The key difference that causes the most problems is the compacting of columns, for example if you would like to PIVOT a staff members sales by month the latest version would look like this:

Whereas the 97-2003 version would look like this:

As you can see the classic PIVOT table has the month in column B, whereas in the new style the month is merged with the staff name in column A.

How to change the Excel PIVOT table to an Excel 97-2003 PIVOT table

Fortunately Microsoft recognised that this might cause a few issues so they provided the ability to convert your PIVOT table to the Excel 97-2003 format quickly and simply, the only caveat is that you will have to do this on each PIVOT Table you create, it is not a setting that you can change permanently in an Excel 2010 file.

To convert your PIVOT Table:

1)      Create the PIVOT table in your version of Excel (2007 onwards), then right click on the PIVOT Table and select “PivotTable Options” from the menu box:

2)      In the PIVOT table options menu select the “Display” tab from the top, then check the box marked “Classic PivotTable layout (Enables dragging of fields in the grid)” like below:

3)      Click OK and you have converted your PIVOT table, I told you it was quick and simple!

As you can see the PIVOT Table will revert to the older format and you can then tidy your PIVOT Table up as needed, for example you might want to remove sub-totals (do so by right clicking on one of the sub-totals and then unchecking the “SubTotal” item in the menu box).

Summary

This does have practical uses, especially in the business environment where as an Excel analyst you will come across historic files made in earlier versions of Excel.  Sometimes complicated processes can be created that use PIVOT tables and a change to the new style can cause the process to fall over so it is always worth keeping in mind when converting any older Excel files to newer versions.

And yes, you will also find plenty of Excel users out there still working on Excel 97-2003 versions despite it being 2013 now!  With that in mind conversion tricks to older versions like this is always something every proficient Excel analyst should have up their sleeve.

Keep Excelling,

1 Comment

  1. Pingback: excel pivot table 2003 vs 2010 | PDF Finder

Leave a Comment