How To Change Default Layout Of PivotTables in Excel?

Posted on Updated on

Imagine that we have an existing PivotTable laid out exactly the way we want it to be, where we have spent a lot of time on it. What happens to those settings though? Do we have to repeat them again and again for future PivotTables that we create? Well, it is possible to import these settings, otherwise will have to edit them one by one, individually. If we change the Default Settings of a PivotTable, all these settings will take place to all the new PivotTables in all future Workbooks. PivotTables that already exist, will not be affected at all by the changes that are made to the Default Layout. If you want to find out, how to modify and change the Default Layout of PivotTables just keep on reading the post below.

How To Change Default Layout Of PivotTables in Excel

We must select the File tab so we can move into Backstage View.

How To Change Default Layout Of PivotTables in Excel

Once in Backstage View, from the left of the drop-down menu we select the category Options as shown in the image below.

How To Change Default Layout Of PivotTables in Excel

Once we select the command Options, the Excel Options dialog box appears as shown below, where from the left we select the category Data. Once the category Data is selected, we locate the area Data Options in the middle of the Excel Options dialog box. Then at the right of where it mentions Make Changes To The Default Layout Of PivotTables, we select the Edit Default Layout button as shown below.

How To Change Default Layout Of PivotTables in Excel

The Edit Default Layout dialog box appears where we have the available options available so we can edit the Default Layout of the Pivot Tables:

  • Layout Import: We must select a Cell in an existing PivotTable and then click the Import button located at the right. The PivotTable’s settings will be imported automatically, where it will be used from now on. We can change the settings, reset or import new settings any time we want to.
  • Subtotals: We can either show Subtotals at the top or bottom of each PivotTable group, or we can even not display the Subtotals at all.
  • Grand Totals: We can either turn on or off the Grand Totals for both Rows and Columns.
  • Report Layout: Once selected, from the drop-down menu we can either select to be shown in Compact, Outline or Tabular Report Layout.
  • Blank Rows: If we activate the command, the PivotTable will automatically insert a Blank Row after each item.

How To Change Default Layout Of PivotTables in Excel

PivotTable Options: By selecting the PivotTable Options button, the PivotTable Options dialog box will appear as shown below. This dialog box is separated in the following tabs where we can use and are: Layout & Format, Totals & Filters, Display, Printing and Data.

How To Change Default Layout Of PivotTables in Excel

At the bottom of the Edit Default Layout dialog box is located the command Reset To Excel Defaults. By using this command, it will restore Excel’s default PivotTable Settings.

Below you can check out the video describing How To Change The Default Layout of PivotTables in Excel.

Don’t Forget To Subscribe To My YouTube Channel.

YouTube Channel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s