New Excel pivot table templates with AutoFormat for PivotTables add-in

AutoFormat for PivotTables for Microsoft® Excel® - Online Help

AutoFormat for Microsoft® Excel® PivotTables

For Microsoft Excel 2010, Excel 2007, Excel 2003, Excel XP (2002), Excel 2000.

How to work with Autoformat for Microsoft Excel PivotTables

• Designing Pivot Tables with Continuous Rows
• Designing Pivot Tables with Continuous Columns
• How to add a new template to the Templates Collection
• How to apply a template to your PivotTable
• How to manage your PivotTable Templates Collection
• Several notes about Autoformat for PivotTables

Back to Autoformat for PivotTables overview

Designing Pivot Tables with Continuous Rows

All Pivot Tables are formatted in order "columns before rows". In this case you get data columns, column subtotals, and column grand totals interrupted by similarly formatted rows (see the screenshot below).

Let's see how to format pivot tables based on continuous rows. The general approach can be described as "first rows, then columns". For example:

Step 1. Common Pivot Table Areas

Step 2. Column and Data Areas

Step 3. Row and Data Areas

Designing Pivot Tables with Continuous Columns

Now let's see how to format pivot tables based on continuous columns. The general approach can be described as "first columns, then rows". For example:

Step 1. Common Pivot Table Areas

Step 2. Row and Data Areas

Step 3. Column and Data Areas

Note: In the outline mode, you can not use AutoFormat for PivotTables with pivot tables based on continuous columns.

How to add a new template to the Templates Collection

1. Select a cell in your PivotTable.
2. On the AutoFormat for PivotTables toolbar, click the "Add Template button. The dialog box will open.
3. In the dialog box, specify if your table is based on continuous rows or on continuous columns. This dialog box doesn't appear if you use the Layout form for PivotTable fields.
4. The Add Template dialog box will display some part of your PivotTable.
5. In the Template Name field, enter a template name.
6. In the Group field, select the name of the existing group or enter a new group name.
7. Click the PivotTable Areas tab and select the areas whose format you want to save.
8. Click the Format Options tab and select the format options you want to save in your template.
9. Enter a comment for this template into the edit box on the Comment tab if you wish to.
10. Click the Add button to save the template to Templates Collection.

How to apply a template to your PivotTable

1. Select a cell in your PivotTable.
2. On the AutoFormat for PivotTables toolbar click the Apply Template button.
3. In the Apply Template dialog box select the template you need.
4. Check / uncheck the Recognize groups checkbox to enable/disable automatic group determination. In the first case, AutoFormat for PivotTables considers the fields with subtotals as the starting points of the groups.
5. Click the Pivot Table Areas tab and select the pivot table areas whose format you want to apply to your pivot table.
6. Click the Format Options tab and select the format options you want to apply to your pivot table.
7. Click the Apply button.

How to manage your PivotTable Templates Collection

1. On the AutoFormat for PivotTables toolbar click the Organize Templates Collection button.
2. In the Organize Templates dialog box select the template you need.
3. To rename the selected template, click the Rename button and enter a new name.
4. To remove the selected template, click the Delete button.
5. To export the selected templates click the Export button and name the AutoFormat for PivotTables transport file.
6. To import templates click the Import button and select the AutoFormat for PivotTables transport file.
7. To move templates between groups, just select the templates you need and drag them to the group.
8. Click the Close button.

Several notes about Autoformat for Excel PivotTables

Why borders are not stored?

Unfortunately, Microsoft guys "added" numerous bugs to pivot tables what made applying borders in pivot tables difficult. We have made a comprehensive research in an attempt to work out a solution that will allow recognizing borders in pivot tables correctly, but we couldn't find such a solution. Now AutoFormat for PivotTables doesn't recognize and doesn't store borders of pivot table cells.

You can find more about pivot table borders here:
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP052035071033&CTT=8&Origin=EC011081751033
(see the "Formatting disappears when I refresh or change the layout" item in the "Formatting and customizing a report" section).

If you have any ideas on how to cope with this problem please let us know. The behavior described relates to all Excel versions from 2000 across 2007.

Undo/Redo

Unfortunately, Microsoft Excel doesn't provide a way to realize Undo / Redo. That is why it is not possible to undo the action after the template was applied.