Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

Note: This feature is available in Excel for Windows if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

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 or get support in Communities.

Related Topics

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!