• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Set Excel PivotTable Default Layout

You are here: Home / Excel PivotTables / Set Excel PivotTable Default Layout
June 13, 2017 by Mynda Treacy

New in Excel 2019 onward and Microsoft 365 is the ability to set a PivotTable default layout, which can save a load of time.

If you’re like many PivotTable users the first thing you do after inserting a PivotTable is waste a minute or so fixing the layout so it’s just how you want.  Waste no more, because now you can set the default for any new PivotTables in any workbook.

Setting a default PivotTable layout doesn’t change or update existing PivotTables though.

Watch the Video

Subscribe YouTube

How to Set Excel PivotTable Default Layout

You’ll find the settings for the default PivotTable layout in the Options: File tab > Options > Data > Edit Default Layout:

set Excel pivottable default layout

You can import a layout from an existing PivotTable; just select a cell in the PivotTable and click ‘Import’:

import from existing pivottable

Or you can specify the layout for the Subtotals:

specify layout for subtotals

The Grand Totals:

specify layout for grand totals

The Report Layout:

report layout

Even the PivotTable Options:

pivottable options

And if you get tired of your default layout, you can restore the Excel default:

restore Excel default layout

Note: the default layout only applies to the current workbook. If you want to have these defaults in every new workbook then you can customize your default workbook.

PivotTable Default Styles

Unfortunately, the styles aren't part of the default PivotTable layout settings, however they're easy to set. Simply right-click the style in the gallery that you want as your default and select 'Set as default':

default pivottable styles

PivotTable Default Number Formats

With regular PivotTables there's no way to set the default number format for a field. However, with Power Pivot we can. Simply add your data to the Data Model when creating the PivotTable:

add to data model dialog

Then open the Power Pivot window, select the column you want to format and on the Home tab set the number format:

default pivottable number formats

Note: there are some knock on effects when adding data to the data model:

First, the GETPIVOTDATA function references are slightly different.

When you reference a value cell in a Power Pivot PivotTable the references contain the word ‘Measures’ and the table and column names are included, as you can see below:

=GETPIVOTDATA("[Measures].[Sum of Sales]",$A$3,"[Table1].[Category]","[Table1].[Category].&[Beverages]","[Table1].[Product]","[Table1].[Product].&[Chai]")

Whereas when you reference a value field in a regular PivotTable, Measures doesn’t appear, and the references are simply the field names and items like this:

=GETPIVOTDATA("Sales",$E$21,"Category","Beverages","Product","Chai")

These differences aren’t really an issue, but if you want to create dynamic GETPIVOTDATA formulas then you need to allow for this.

  • GETPIVOTDATA function for regular PivotTables
  • GETPIVOTDATA function for data model/Power Pivot PivotTables

The other main difference is that you no longer have access to the calculated fields and columns functionality we have with regular PivotTables. You’ll notice they're greyed out in the menu:

calculated fields and items

Instead, with Power Pivot we must add calculated columns in the Power Pivot window using the DAX formula language. And calculated items are measures, which are also written with DAX. The good thing is the DAX formula language is very similar to the Excel functions we know and love. If you’d like to learn DAX, please check out my Power Pivot course which covers DAX.

Bonus for OLAP PivotTables

Tip: a recent performance improvement to Excel 2019 onward means that disabling Subtotals and Grand Totals can make OLAP PivotTables faster.

Non Microsoft 365 Users

If you don’t have the latest version of Excel then there are still some one-click shortcuts to making some of the more common changes to PivotTable layouts. Namely, removing all subtotals and reinstating the ‘classic PivotTable’ layout, which is tabular, as described in this post.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTables
Previous Post:Script Lab LogoScript Lab – Explore the Office JavaScript API & Learn to Build Add-Ins
Next Post:List Files in a Folder Using FILES XL Macro (XLM)List Files in Folder Using FILES XLM

Reader Interactions

Comments

  1. Sherryl Heath

    June 7, 2018 at 9:20 am

    Thanks for this – I have been waiting forever for this change as I work with a specific pivot table format all the time. In the past I have had to import a pivot table with the format, apply the design to the new pivot and then delete the imported sheet. A pain!

    Reply
    • Mynda Treacy

      June 7, 2018 at 9:30 am

      Glad you’ll find it useful, Sherryl 🙂

      Reply
  2. Roni

    July 6, 2017 at 6:57 pm

    Do you know when this option will be made available in the non-365 versions of Office?

    Reply
    • Mynda Treacy

      July 6, 2017 at 7:02 pm

      Hi Roni,

      It won’t ever be available. If you want new features you have to buy a new version of Excel 2016 or purchase the Office 365 subscription licence which gets the updates.

      Mynda

      Reply
  3. Jesse

    June 15, 2017 at 9:40 am

    This is great! Thank you for sharing. I’m so glad that we no longer have to go through the tedious process of setting up a new PivotTable. The time savings from this will certainly add up.

    On a similar note, is there any way to set the default number format on the “Value Field Settings” menu? I would like to set the default number format to “Accounting”.

    Reply
    • Philip Treacy

      June 17, 2017 at 2:43 am

      Hi Jesse,

      No, sorry. There’s no way to set the default number format for PivotTables.

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.