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

Common Pivot Table Areas - Continuous Rows

Step 2. Column and Data Areas

Column and Data Areas - Continuous Rows

Step 3. Row and Data Areas

Row and Data Areas - Continuous Rows

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

Common Pivot Table Areas - Continuous Columns

Step 2. Row and Data Areas

Row and Data Areas - Continuous Columns

Step 3. Column and Data Areas

Column and Data Areas - Continuous Columns

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 "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. PivotTable design rule
  4. The Add Template dialog box will display some part of your PivotTable. Add a new template to the collection
  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 Apply Template button.
  3. In the Apply Template dialog box select the template you need. Apply a template to your Excel PivotTable
  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 Manage Organize Templates Collection button.
  2. In the Organize Templates dialog box select the template you need. Organize Excel PivotTable Templates Collection
  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 (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.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish this message on the forum
 

Please remember that we work from 8am to 4pm GMT,
this is 4:00am to 12:00pm EST, 1:00am to 9:00am PST, and our public holidays may differ from yours.