Set PivotTable default layout options

If you have an existing PivotTable laid out the way you like, you can import those settings, otherwise you can edit them individually. Changing the default PivotTable settings will affect new PivotTables in any workbook. Existing PivotTables aren’t impacted by changes to the default layout.

Watch Doug from Office give a quick introduction to PivotTable default layout options:

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

To get started, go to File > Options > Data > Click the Edit Default Layout button.

Edit the Default PivotTable Layout from File > Options > Data

Edit Default Layout options:

PivotTable Default options
  • Layout Import - Select a cell in an existing PivotTable and click the Import button. That PivotTable's settings will be automatically imported and used in the future. You can reset, import new settings, or change individual settings at any time.

    Import existing PivotTable settings by selecting any PivotTable and clicking Import

  • Subtotals - Show subtotals at the top or bottom of each PivotTable group, or don't display subtotals at all.

  • Grand Totals - Turn Grand Totals on or off for both Rows and Columns

  • Report Layout - Pick from Compact, Outline or Tabular report layout.

  • Blank Rows - The PivotTable will automatically insert a blank row after each item.

  • PivotTable Options - This will launch the standard PivotTable options dialog.

    PivotTable Options dialog

  • Reset to Excel default - This will restore Excel's default PivotTable settings.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

LinkedIn Learning Create PivotTables course

Create a PivotTable to analyze data

Create a PivotChart

Use slicers to filter PivotTable data

Create a PivotTable timeline to filter dates

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×