Excel Quick Tips: Set a default PivotTable Layout

insightsoftware -
August 10, 2017

insightsoftware is the global provider of enterprise software solutions for the Office of the CFO to connect to & make sense of data in real time, driving financial intelligence across […]

Excel Quick Tips

After a short moratorium, we are bringing back the beloved, Excel Quick Tips videos! This week we’re covering something near and dear to our hearts: PivotTables.

Like many companies, we have a preferred way of looking at PivotTable reports, and like many companies, building a new PivotTable used to mean spending unnecessary time reformatting the PivotTable to match that preference. But we’re about working smarter, so we discovered a way to reclaim that precious time and reduce the tedious process of formatting all at once.

With this Quick Tip, you will learn how to save Subtotal, Grand Total and Report Layout options within minutes, as well as any other PivotTable options you need. Now you can use all the time you save watching cat videos instead!

Learn how to set a default PivotTable layout with this weeks’, Excel Quick Tip:

How to Set Excel PivotTable Default Layout

Locate the settings for the default PivotTable layout in the Options: File tab > Options > Data > Edit Default Layout:

Edit Default Layout

If you already have a PivotTable formatted, you may import that layout directly; just select a cell in the PivotTable and click “Import”:

Import Pivot Table


Alternatively, you can also specify the layout for the Subtotals, Grand Totals, and the Report Layout individually. You can even get into the PivotTable Options:

Pivot Table Options

If you no longer need the new default layout created, restore to the Excel default by selecting, “Reset to Excel Default”:

Reset To Default Excel Layout