☑️ Top 25 Advanced Pivot Table Tips & Tricks For Microsoft Excel

in this video we're going to take a look at the top 25 advanced pivot table tips and tricks for excel the first tip we're going to take a look at is using a table as the source data in your pivot tables and this is going to make it a lot easier to update your pivot tables when you had new data so if I go to the insert tab and create a pivot table with this data you can see that excel references that data with a hard-coded range value so I've got data in a 1/2 H 1471 and if I create this pivot table and then later add data passed row 1471 I'm going to need to update that range reference for the data to flow through to my pivot table so instead what we're gonna do is go up to the insert tab and first use the table command with our data and our data has column headers in the first row so we're going to leave this option checked here and press ok and Excel creates a table object and our data is contained inside that table and the first thing we're going to do is go up to the table design tab and give this a new name so let's call it HR data and press Enter and now if we go to the insert tab and create a pivot table and you can see that Excel is referencing that table name instead now and as long as you pad data into the table then that's going to flow through to your pivot tables so let's create our pivot table and let's just create a simple pivot table here with gender and monthly salary and let's go back to our data and add some new data so let's scroll down to the bottom and let's just copy the last row and paste it there and you can see that this is inside our table still and let's just update the value here to something really large so we can see it in our data and let's go back to our pivot table and right click and refresh and there we go we get our new data in our pivot table so the best practice is to always use a table as your source data for pivot tables it makes it a lot easier to add data into your pivot tables if you want to see the detailed data behind any value inside a pivot table you can do that with the show details option so here we've got total monthly salaries of 41 thousand seven hundred and twenty four if I right-click on that and go to show details then Excel is going to create a new sheet for me with only the data pertaining to that value and you can see if I add up the monthly salary column here we have forty one thousand seven hundred and twenty four now let's go back to our pivot table you can do the same thing by double clicking on any cell so let's double click on that cell and here's a new sheet with the data behind that value so that's the show details option which allows you to see the detail data behind any value in a pivot table in this tip we're going to take a look at deleting the source data in your workbook so once you have a pivot table set up you can actually delete the source data and you'll still be able to use the pivot table like normal so here's my source data and this HR data tab and I can right click and delete that sheet and delete and I'll still be able to use my pivot table as normal so here I can move around fields or add in new fields if I want to etc so this is going to help with reducing the file size of your workbook as well as preventing accidental editing of your source data now if you want your source data back you can always use the show details trick to get your data back so you can show details on the grand total right click and show details and here's our source data back again by default pivot tables are going to display blank cells whenever there's no data for that combination of items so for example in our data we don't have any females in the human resources job role with a doctor's level education if you want to display some value here and stead of a blank cell you can do that if you right-click in the pivot table and open up the pivot table options and go to layout and format tab there's an option here for empty cells and just make sure that's checked and you put a value here so maybe zero and press ok and then instead of blank cells we get a zero displayed here now you don't have to display a number you could display some text so let's open up the pivot table options again and maybe instead of zero we want to display no value so that's how you can display a custom value instead of a blank cell and your pivot tables and this tip we're gonna take a look at how we can show items in our pivot table that don't have any data so in this pivot table I've got job role and education level and gender in the rows area and then if I look for healthcare representatives with a doctor level education then I don't actually have any data for any females and as a result I only see males here now if I want to show females and just have a blank cell for that value and I can do that by selecting a cell in that field and right clicking and opening up the field settings so here's our field settings for the gender field and if we go to the Eagle layout in print tab there's an option here to show items with no data we can check that off and press ok and then in our pivot table we get that value displayed here and we get a blank cell because there's no data for that value so that's how we can show items with no data in our pivot tables in this tip we're gonna take a look at grouping together items in a field so here we've got our education level field and then we're showing the total monthly salary for those education levels and let's say we want to further analyze this by those who went to university and those who didn't then we could use the grouping feature to do that so we can select the items in our field that we want to group together so let's select our doctors bachelors and if we hold control you can select masters as well and if we right-click we can group those together now that's gonna create a group with those three items in it so here it's called Group one we can rename that and we can group the other two items together as well right-click and group those and rename that as well and notice when you group items in a field we get this new field here and our pivot table fields list so it's called education level 2 and we can click on that and open the field settings and rename it to something more descriptive and press ENTER and once we have that new field we don't need to have the field that's based on in our pivot table so we can remove our education level and we still have that field there in our pivot table so that's how you can group together items in a field in this tip we're going to take a look at grouping numeric fields so if we add a field like total working years into the rows area then what happens is we get a list of all the unique values in that field but what we actually might want is to have this grouped so we can group numeric fields by right-clicking on the field and going to group and that's gonna bring up this numeric grouping menu and we can enter a starting value and an ending value and a value to increment the group's by so let's create groups of 5 years and press ok and what we get is our experience levels in grouped buckets so that's how we can group numeric fields and a pivot table in this tip we're gonna take a look at grouping date fields so when you've had a date field into a pivot table Excel is going to automatically group that field for you so if I add the start date into the rows area you can see that Excel has dripped those by years and it's also added a quarters field so we have it years in quarters field here and we also have our original start date here now if you don't want this automatic grouping you can press ctrl Z and that's gonna ungroup those fields or if you want to change the grouping you can right-click on the date field and go to group and that's gonna open up this grouping menu for dates so you can enter a starting and ending date and then Excel is going to group anything before or after those dates into one single bucket and we also have options down here on how to group our dates together so we have years quarters days hours minutes and seconds as well so let's just say we wanted to show months we can select our months field and press ok and now our data is grouped into months so that's how you can group a date field Excel will automatically do the grouping and you can edit that grouping by right clicking and going to the group option if you always want to be sure that you're working with the latest data in your pivot table you can have Excel automatically refresh your pivot table when you open the file to enable this setting you can right click on your pivot table and go to pivot table options and go to the data tab and there's an option here to where you fresh the data when opening the file so we can check that off and press ok and the next time you open this file Excel is going to refresh your pivot tables to get the latest data in this tip we're going to take a look at using the show report filter pages option so this is going to allow you to quickly create a pivot table for each item in a field and to use this option we need to have at least one field in our filters area so here we've got education level and we can see that filter up here on our pivot table and education level has five items in it and we can use this option to create a pivot table that's filtered on each of those items so to do that we can select our pivot table and go up to the pivot table analyze tab and there's options here we can click on that and in there there's the show report filter pages option we can select that and that's going to open up this menu where we can select which field in our filters area so if we have multiple fields in there we can select which one we want in our case we've only got this one so let's select that and press ok and in this case Excel has created five new sheets and each sheet contains our pivot table and that pivot table is filtered on a single item so this is the masters sheet and it's been filtered on a masters item in education level so that's how we can quickly create a pivot table for each item in a field and this tip we're going to take a look at how we can apply a label filter and a value filter at the same time so if we go up to the sort and filter toggles we have these options for label filters and value filters but you can actually only apply one at a time so if we try to add a label filter and let's maybe try contains and let's look at things that contain the word sales and if we look at that we can see this check mark indicating that we've got a label filter and now if you try and add a value filter so let's look at things less than a million and press ok then you can see that that label filter check mark has been removed and we now have our check mark next to you our value filter so we're only filtering on things less than a million now if we want to apply both those filters at the same time we need to enable that option so we can right click on the pivot table can go to pivot table options and if we go to you totals and filters tab there's an option here to allow multiple filters per field we can check that and press ok and now if we go back to our label filter and contains and let's try contain sales again and press enter now we've got both those filters applied so you can see that they're both checked there and we're only showing things that contain the word sales and our under a million total salary so that's how you can add multiple filters per field we need to enable the option first in this tip we're gonna take a look at how we can sort our pivot tables based on a value field so here we've got job role and then the corresponding total monthly salary for that job role and we can sort this pivot table based on our total monthly salary field so let's click on the sort and filter toggle and we can go to more sort options and that's going to open up this sort menu for our job role field and here we can choose either ascending or descending order and then instead of job role we can pick our monthly salary and press ok and there we go that's going to sort our pivot table based on the monthly salary column and here we've sorted that in descending order and this tip we're going to take a look at creating a custom sort order in your pivot tables so when you add a field into a pivot table Excel is going to sort that field in alphabetical order by default but if you want to display your results in another order other than alphabetical you can do that with a custom list so here I've got a field education level and it's currently in alphabetical order by default but maybe we want to have it displayed in a different order so maybe we want to have bachelor master and doctor appear first and then below college and college level we can do that with a custom list so first we need to create a custom list you can go to the file tab and go to options and go to Advanced Options and if we scroll down to the bottom there is a general section and here we have the option to edit custom lists you know we can actually import our custom list from Excel so we can select that and select that range and click on import and we see our custom list here now so we can press ok and press ok again and now we've got our custom list set up if we sort in alphabetical order we lose that sort order so what we're going to need to do is enable an option to use our custom list order instead of our alphabetical order so we can right click on our pivot table and go to pivot table options and in the totals and filters tab there's an option to use custom lists when sorting so we can enable that and press ok and we get our sort order based on that custom list now in this tip we're going to take a look at how we can change the layout of a pivot table so by default we get a compact form layout and that just means that when we have multiple fields in our rows area we get them displayed in a single column in our pivot table we can change this layout so if we select our pivot table and go up to the design tab and in report layouts we have a couple options here so we're currently in compact form let's check out outline form so in outline form we get our fields in separate columns but fields are still nested underneath each other let's check out the other option so report layouts and tabular form this time we get our fields in separate columns again but there's no nested values here so those are the different options available for the layout of your pivot table reports in this tip we're going to take a look at how we can repeat item labels in a pivot table so when you're in outline form or tabular form labels in your pivot don't repeat by default so for example this pivot table here is in tabular form and in my education level of field the first item here Bachelor underneath it I have blank cells so that label isn't repeating we can change that so that the labels repeat we can select our pivot table and go to the design tab and in report layouts we can repeat all item labels and that's going to fill the label down those blank cells for us so that's how we can repeat item labels and a pivot table and this tip we're going to take a look at how we can turn off the automatic get pivot data formulas when you're trying to reference a value inside a pivot table so when trying to reference value inside a pivot table Excel will automatically create a get pivot data formula for you but you can easily turn this feature off so let's press escape and if we select our pivot table and go up to the pivot table analyze tab under the options we can't turn off generate get pivot data and now when we try and reference value inside our pivot table we get the standard cell references so that's how you can turn off the automatic get pivot data formulas in this tip we're going to take a look at how we can prevent Excel from changing the column widths when you update or refresh a pivot table so by default when you refresh a pivot table Excel is going to autofit the column widths for you so if I right click and refresh my pivot table you can see that the column widths change if I don't want that to happen I can change the options so if I right click on my pivot table and go to pivot table options and in the layout and format tab there's an option here to auto fit column widths on update if we uncheck that and press ok then column widths shouldn't change when we refresh our pivot tables so if we change our column widths and then refresh our pivot table those column widths won't change and this tip will take a look at how we can add new calculations into our pivot tables with calculated fields so let's say that instead of monthly salary we wanted to see annual salary we could create a new calculation that multiplies that by 12 so let's select our pivot table and go up to you the pivot table analyze tab and in fields items and sets we can add a calculated field and we can give our calculation name so let's call it annual salary and our formula is gonna be 12 times and we're gonna multiply the monthly salary by 12 so I can double click on that and add it into my formula then I can add this calculated field into my pivot table and press ok and there we go we've got a new field in our pivot table called annual salary and it's 12 times e monthly salary so that's how we can add calculated fields into our pivot tables you can easily visualize your pivot labels with pivot charts and when you create a pivot chart it will be dynamically linked to your pivot table so when you update your pivot table the pivot chart will also update so to create a pivot chart you can select your pivot table and go to the pivot table analyze tab and select pivot chart and that's going to open up this insert chart menu and you can select from various different chart types here and when you select a chart type there's different options for that chart type along the top here so let's go with a column chart and let's go with the basic column chart and press ok and that creates our pivot chart and now when we update our pivot table our pivot chart is also going to update so let's say we add in gender into this pivot table and you can see that our pivot chart updates with that new field as well so that's how you can create charts that are dynamically linked to your pivot tables in this tip we're going to take a look at how we can add a slicer to our pivot table so a slicer is just a visual filter that's always displayed in your workbook so to add a slicer you can select your pivot table and go up to the pivot table analyze tab and choose the insert slicer command and that's going to give you the option to insert a slicer for one or more of the fields in your data so let's add in a slicer for gender and notice that my pivot table doesn't have the gender field in it so you can add slicers to filter data based on fields that aren't in your pivot table so let's press ok and here's our new slicer object and it's got the items in our gender field and then you can click on either those and filter your pivot table to just that data in this tip we're going to take a look at how to insert a timeline for your pivot tables so a timeline is just like a slicer it's a visual filter but it's for date fields so we can select our pivot table and go to the pivottable analyze tab and insert a timeline and this time we're only going to be able to select date fields in our data so we can select our start date and press ok and then we get this filter here it's a timeline filter and it's going to allow us to filter on different dates so here I'm filtering on August September October and November and if we want to we can change this to be able to filter on different years here I'm filtered on 2014 2015 and 2016 etc so we also have months and days available so that's how we can add a timeline filter to our pivot tables and this tip we're going to take a look at how we can connect multiple pivot tables to the same slicer so I've got two pivot tables here and a slicer and you can see that this slicer only affects the first pivot table so I can connect the other pivot table to this slicer by right clicking on the slicer and going to report connections and that's gonna give me the option to add other pivot tables so I can check off that other pivot table and press ok and now this slicer is going to work with both those pivot tables so that's how you can add multiple pivot tables to a slicer in this tip we're gonna take a look at how we can filter our pivot table to the top n or bottom end results so we can add a top and filter by clicking on the sort and filter toggle and in the value filters section we have an option for top ten and that's going to open up the top 10 filter menu for our job roll fields and here we can choose a couple options so we can choose to show the top or bottom results and we can change that 10 to something else so we can show the top 3 items and if we had more than one field in our values area we could choose which fields to show the results by and we can press ok and now we're filtered on the top 3 results here so that's how we can filter on the top or bottom results in our pivot table in this tip we're gonna take a look at how we can add multiple subtotals to a pivot table so in this pivot table I've got two fields in the rows area I've got gender and education level in there and because I've got multiple fields in my rows area you can see that I get some subtotals here so at the gender level I have subtotals and these subtotals are just the sum of the values below so we can add different types of subtotals into our pivot table if I go to the gender field and click on that and open the field settings then you can see that subtotals is set to automatic so that just means the subtotal type is going to be the same as what we've got in our values area here so we're taking a subtotal that's the sum but we can choose to add custom subtotals so we can take a sum or a count or an average and you can in fact select multiple of these so let's select sum count average and Max and min and press ok and you can see that we get those subtotals here so even though our pivot table is displaying the sum of the monthly salaries we can get different information such as the average monthly salary for females or the max or min value etc so that's how you can add multiple subtotals to your pivot tables in this tip we're going to take a look at these show values as options so with these show values as options this is going to allow you to display the results of your pivot table as different types of calculations so for example we could display the values here as a percentage of the grand total so to do that we can right click on the value and select show values as and here we have a lot of options for these show values as calculations in this example we're going to choose percent of grand total and now our values are displayed as a percentage of the grand total so here we can see that the bachelor level of edge Asian makes up 39 percent of the total monthly salaries so that's how you can use these show values as calculations in your pivot tables want more awesome excel tips then sign up for my excel newsletter so the link for that is in the description below this video when you sign up for the newsletter I'll send you a free copy of my excel tips I booked and also send you a free copy of my mega book of Excel keyboard shortcuts and my ultimate guide to vlookup if you enjoyed this video you can help me out by hitting the thumbs up button and subscribing to this channel if you haven't already done so that's it for this video see you in the next one

Channel: How To Excel

Views: 614,494

Rating: 4.9480848 out of 5

Keywords: Microsoft Excel, Excel, Microsoft, Tutorials, Pivot Tables, Tips & Tricks

Id: RmTxvkhzFPo

Length: 28min 29sec (1709 seconds)

Published: Mon Jul 22 2019