PivotTables - Set Default Layouts & Formatting And SAVE TIME!

if you're like many pivot table users the first thing you do after inserting a pivot table is waste a minute or two fixing the layout so it's just how you want well waste no more because now you can set the default for any new pivot tables in any workbook including how to set the default number format which in my opinion must be one of the most annoying time wasters there is let's take a look now before we get started i should mention that the functionality in this video is only available in excel 2019 onward or if you have a microsoft 365 subscription all right in this file i've got some data let's quickly insert a pivot table and i'll pop it on a new worksheet we're going to put the category and product in the row labels and the sales in the values area and on the design tab i'm going to choose a different layout so instead of compact form which it's currently in i want it in outline form that's going to allow me to repeat all the item labels so in column a we'll see beverages condiments and so on repeated and let's put the subtotals at the top of the group in here i can also choose to change the settings for the grand totals i'm going to leave them as is let's also choose a different color we'll go with this turquoise style and lastly the number format needs some work let's add a comma separator and no decimal places okay so i've spent about i don't know 30 seconds or so performing those steps and it might not seem like a lot but it's a pain having to repeat them again and again for every pivot table so let's set this as my default style in the file tab under options under data i can edit the default pivot table layout now because i had a cell in a pivot table selected it's populated that reference there and i can simply click import here to bring in those settings you can see my subtotals grand totals layout and the repeating of item labels is all done for me you can of course make changes here if you prefer i can also go into the pivot table options here this is the right click options menu that we normally see in pivot tables i can change the layout and format totals and filters display printing and data i'm going to leave them as is for this pivot table in this dialog box you can also reset it to the default so i'm happy with that i'll click ok and okay again now in order for these settings to stick you need to close excel and then open it again so i'll pause while i do that okay so let's go and insert another pivot table this time i'm going to put it on the existing worksheet we'll just put it beside the original one and then we'll build exactly the same pivot table so you can see it's picked up some of my settings it's got the layout correct but it hasn't retained the style and my number formats don't have the comma separators now the style is easy to deal with let's go into the design tab choose the style and then i'm just going to right click and set it as the default that's going to set it as the default for this workbook not all future workbooks now unfortunately there's no way to set the default number format for regular pivot tables which is what we have here however if you add your data to the data model that is powerpivot then we can set the number formats there so let's go ahead and insert another pivot table and this time i'm going to check the add this to the data model button i'll put it on a new worksheet and click ok so now my data's in the data model what i can do is go to the power pivot tab manage that opens the power pivot window and in here i select the sales column let's add a comma separator and we're going to decrease the number of decimals now this doesn't change the underlying value you can see in the formula bar that the decimal place is still there it's just for display purposes and anytime i use this sales field in any pivot tables it's going to retain the formatting that i've set in the power pivot window so let's close it down and now let's build the pivot table so there we go i have my pivot table formatted in the layout that i want i've got the color style that i want and my numbers have the comma separators now there are some knock-on effects when you add your data to the data model the first is the getpivotdata function and references are slightly different so let's reference a cell in this pivot table get pivot data is automatically populated and you can see the word measures in the formula when you see measures you know that this is a pivot table generated from the power pivot model let's go and have a look at that same cell in a regular pivot table so here we'll reference beverages and apple juice you can see the formula is much shorter it still get pivot data but the structure of it's slightly different let's just take a look at those side by side so i'm just going to add a new window and then we're going to arrange them horizontally and i'll use the formula text function to just display that formula let's copy it onto sheet one which is the power pivot pivot table so now you can see the two functions beside one another whilst they're both using the get pivot data function the syntax in the formula that references the power pivot pivot table is a little bit more complicated now it doesn't really matter unless you want these formulas to be dynamic and then you need to allow for this i've included a link in the video description that will take you to the tutorials on the two different get pivot data formulas now the other difference is that with power pivot pivot tables you no longer have access to be able to add calculated fields and items you can see they're grayed out instead in powerpivot we need to add any calculated columns in the power pivot window so i can click on add column and in the formula bar i can enter my formula and we do that with the dax formula language and if we want a calculated item then these are similar to measures that we have in powerpivot and they're also written with dax and the good thing is the dax formula language is very similar to the excel functions that we know and love if you'd like to learn dax then there's a link in the video description to my powerpivot course which covers dax now some things to note you'll find that the formatting that is applied in the powerpivot window will feed through to any pivot tables that use this field so it will retrospectively update them however any styles that you've set as your default style including the styles will not be applied retrospectively to pivot tables you've built prior to setting those preferences the other thing to keep in mind is the style preference is only specific to this workbook whereas the preferences we set via the file tab and then options they'll be available in any new workbooks you create and of course the number formatting set in powerpivot has to be done in each powerpivot model i hope you found these techniques useful if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching you

Channel: MyOnlineTrainingHub

Views: 18,210

Rating: 4.9633985 out of 5

Keywords: pivottable default, default pivottable

Id: 2tGCYuR3Bco

Length: 8min 1sec (481 seconds)

Published: Wed Jun 02 2021