Format a Pivot Table in Excel 2003 Classic Style

Last week, someone asked me how to make an Excel 2010 pivot table have the same look as the default settings in Excel 2003.

You might have forgotten what those pivot tables looked like, or maybe you never used that version of Excel. In the screen shot below, you can see the pivot table that I created, with Region and City in the Row area, Order Yr in the Column area, and Quantity in the Data area.

Using the same data and fields, here is the default pivot table in Excel 2013.

Spot the Differences

Just like those puzzles, we can play Spot the Differences with these two pivot tables. How many differences can you see?

Here’s what I found:

• The 2003 version has dark blue outlines around the sections, and you can drag and drop the field buttons
• The 2013 version has both Row fields in the same column, instead of separate columns
• The 2013 version has blue heading cells and grand total row, while the 2003 version has no fill colour

Did you see anything else?

Format the Excel 2013 Version

With a few steps, you can make the 2013 version look almost like its older relative.

Turn On Classic Layout Setting

To be able to drag and drop on the worksheet, follow these steps to turn on the Classic Layout

• Right-click a cell in the pivot table, and click PivotTable Options.
• On the Display tab, add a check mark to Classic PivotTable Layout.
• Click OK, to close the PivotTable Options dialog box.

Change to Tabular Layout

When you turn on the Classic Layout, the pivot table automatically changes to Tabular Layout, with each Row field in a separate column.

If you haven’t applied Classic PivotTable layout, you can still change to Tabular layout, with these steps

• Right-click a cell in the pivot table, and click PivotTable Options.
• On the Display tab, add a check mark to Classic PivotTable Layout.

NOTE: You can use Tabular form, or Outline form, with the Classic PivotTable layout. If you change to Compact form, the blue outlines will be visible, but you won’t be able to drag and drop the fields.

Change the Style

Those old pivot tables didn’t have much style. You could apply one of the AutoFormats, but they weren’t automatically set on a new pivot table.

In Excel 2013, you can apply a wide variety of pivot table styles, and one is automatically used on new pivot tables. To get close to the Classic look, follow these steps:

• Select a cell in the Pivot Table.
• On the Ribbon’s Design tab click the More button on the PivotTable Styles list
• At the top of the list, click None as the PivotTable Style

After the Changes

After you have changed the Excel 2013 settings and style, it looks pretty close to the old version.

The lines are grey, instead of black, and the field headings don’t look like buttons. But, you can drag and drop, and there is no fancy formatting.

Default Settings in PivotPower Premium

If you’ve bought a copy of my PivotPower Premium add-in, you can store this “Classic” look in your default settings.

• Open the Default Settings window, and go to the Pivot Table tab
• For Report Layout, select Tabular
• For Style, select None
• Add a check mark to “Classic PivotTable Layout (drag/drop)
• Click OK, to save the settings.

Then, you can use the Apply Defaults command to apply these settings to any pivot table.

_____________________