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:
To get started, go to File > Options > Data > Click the Edit Default Layout button.
Edit Default Layout 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.
-
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.
-
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
