PivotTables & Slicers Made Easy! 4 Amazing Examples for WAAT Accounting Seminar August 26, 2016

welcome to the Washington association of accounting and tax preparers august 26 2016 accounting seminar this is our second video for this seminar in this video we'll talk all about pivot tables we have four amazing examples of pivot tables that we're going to create and in the process we will learn everything from beginning to end about how to create pivot tables now if you'd like to download the files and follow along you got a click on the link below this video and go to the very bottom section the other section and look for the W AAT section there are five files there are actually two videos in this conference one is about lookup formulas the other ones about pivot tables so there's three excel files and two PDF notes now before we even start I want to ask the question what is a pivot table we can see one of the N results right here it looks like we have some products and some regions and then there is the sum of units in between now I want to go look at the actual data set because before you can even create a pivot table like this you have to have a proper data set I'm going to go over to the sheet pivot tables and here's our data set field names at the top or column headers records in rows empty cells all the way around or you can have the actual row headers or column headers but you can't have data next to your data set now notice our data set these are transactions so we have date sales rep the region the products sold the color of the product number of units and revenue let's go back over to this topic sheets because the question still is what is a pivot table if someone asks you what is a pivot table it's very simple that number right there that is a calculation with conditions or criteria now 102 units total can you name how many conditions are criteria went into calculating that a hundred and two well let's do it together it had to go through the data set and find only the product qua that is the row header right then it had to go through and find only the region south so far we only have two conditions or criteria but look at this up here this is called a filter this filters the entire report every one of these numbers is for a product that had the color blue not only that but this is called a slicer and look at this we selected one of the sales teams calling drew Kyle and Shari are all on a sales team so guess what that a hundred and two total units was for seven conditions or criteria one two three four five six seven not only that but some of the conditions are from and logical tests and some of them are from or illogical tests if we look over here the actual calculation for that hundred and two is they add up all of the products that were quad and all the regions that were self and all the colors that were blue and the sales reps that were either calling or drew or Kyle or Shari now the fact that we're allowed to make some calculations with such complex criteria with such an easy tool called a pivot table is why pivot tables are so amazing if we try to do these same calculations with formulas it would be unbelievably complicated all right let's build our first pivot table we're going to create a cross tabulated report and all cross tabulated means there are some column header criteria and some row header criteria so it's as if boom for that hundred and two there's a cross there is the number cross tabulated report sum of units with seven conditions or criteria let's go over to pivot tables now we have a proper data set and we can click in any one cell in the data set if your cursor starts out here it will not know where the dad that is I click in one single cell I go up to insert and in tables I click on pivot tables now since pivot tables are so common we need to learn the keyboard and it's a Succession keyboard normally we do ctrl C to copy and we hold them down at the same time but alt keyboards are success and you hit the Alt key and then you can notice there's an N so I let go of the alter now I hit N and notice there is a V so that I hit a V and just like that it opens up the create pivot table dialog box now I'm going to do that in fast speed alt and V and there it is now a guess is right because we have empty cells or column headers and row headers surrounding our data set the default is on a new worksheet which is what we're going to do if we wanted it on this worksheet we would click existing worksheet and pick the location I want new worksheets and now I click OK or hit enter so really the keyboard is alt + V Enter now I'm going to come down and double click and name this sheet product report and enter now here is the field list it's called pivot table fields because these are the column headers or in database and we refer to these as fields now we can simply drag and drop these fields down to where the criteria goes columns filter or rows and then we drag fields we want to make calculations upon down to values now remember in our example in the rows we wanted products so this is where we drop products we simply come up here and click and drag and instantly anytime you drag a field down to any of the criteria areas you will see a unique list of items that means that actually went through that entire data setting gives us exactly one of each there are our products now we want region down in the columns and just like that we have our cross tabulated form now I'm going to drag color down to filters now when we drag something down to filters it doesn't give us a you list but it does give us an option to select this criteria will filter the entire pivot table now we come down to units and I drag units down to values now the fact that I'm dragging a number field down to values means the pivot table will default to sum we can see it says sum of units later we'll see how to change the calculation now why is it called a pivot table here's why because once we have our form if you decide to pivot it you can click and drag and I'm going to drag region below product and now we don't have a cross tabulated report we have a vertical report I'm in a drag this back or pivot it now there's a couple things we want to immediately do want to create pivot tables row labels and column labels are not helpful labels in a report if you're going to print this out and give it to someone it'd be better if they said region and this said product we can easily change that by going up to design in the layout group we come to report layout we can either show an outline or tabular outline we'll leave no gray lines inside the pivot table it has a slightly different structure tabular will leave the gray lines and there we have it region and product I also want to change the number formatting now these are units so I definitely don't want dollar signs but I definitely want commas now a big mistake that people will make with pivot tables is they will highlight all the cell's and right click format cells if you open up this I want you to notice how many tabs there are six tabs this is the normal format cells dialog box we do not want that the reason why is it will actually format the cells remember pivot tables can pivot and so these cells might contain number formatting but when we pivot it that number formatting will not carry forward so here's what you do you click in any one cell in the values area and right click number formatting that number formatting option does not occur when you're out in a No PivotTable area it only shows up in a pivot table and when I click that look at that a single number tab that is an indication that this is for a pivot table notice they call it format cells what they should have called it is number formatting for pivot tables or something like that now I'm going to select number use a comma and these are units so I want zero decimals and click OK that's already looking great now think about it we dragged and dropped criteria we dragged a field down to values where we made a calculation we made sure to change the report layout so it shows the field names not column headers and we added number for many that really is the basic of how to build a pivot table now one other thing we can do is we can add style if we come up to design there are some built in styles and you know I don't really like any of them so I'm going to show you how to build your own style which will remain in your workbook and you can use it multiple times I'm going to click on new pivot table style now over here we have a list of all the elements we might find in a pivot table and then we have a format button we also have a preview now I'm going to start off with whole report for this whole report I want black borders on the inside so I'm going to come down to format and on the border I'm going to select outline and inside I can see I have the inside borders and then the outside now I'm going to click OK we can see our preview starting to emerge over here now I'm going to come down to header row this will be this up here format and I would like to have a dark blue fill at the top if I'm going to have a dark fill then I definitely want a light colored font you always want the value difference between font and fill to be as large as possible now I'm also going to come over to border because up here I do not want the actual inside lines so I'm going to say none that removes it all and then outline that out line means that will only be around the outside edges now I click okay I'd like to do something to the very bottom row so the grand total row that will be this right here I'm going to click format how about we add some borders I'm going to add a medium line for the top so that'll be that line right there and then a double line for the bottom now I'm going to come over to font and I want to make this bold now I can click OK now one thing nice about this is we can come back in and edit later notice there's also set as default pivot table styles for this document I'm going to click OK hey wait a second it didn't happen well now I can come up and go design click the more button in there it is so now I've applied my style now if I look up here I see that I forgot the filter area so I'm going to come back up to design and come up to this pivot table style and right click modify first off I'd like to give it a good name I'm going to call this blue pivot and now I want report filter labels click format I want the same font color white the same fill dark blue and the same borders none and then outline now I can click OK so I've edited two things report filter labels and the name click OK and there I have my format now before we add the slicer to add conditions off to the side I actually want to show you a trick and I'm going to go back over to the topic sheet because actually this is an example number two and it's how to create many pivots from a single click let's go back over to product report sheet now notice we added this color criteria to the filter area and if I click the drop down we can see there's 1 2 3 4 5 so there's 5 different colors if I would like one pivot table for each one of these conditions or criteria I can click a single button and instantly down here it will add five new sheets with five new pivots keeping the original one that says all now in order for this to work you have to have all selected then you come up to analyze over to pivot table and here's the options drop-down and there it is show report filter pages now when I click this it's going to ask me which one of the conditions we only have one so it's already selected when I click OK instantly down here there'll be five new pivot tables when I click OK just like that blue I can see it right there clear I can see it right there purple rainbow and red that is pretty amazing now let's come back over to our product report sheet I'm going to change the criteria up here to blue now notice as soon as I click blue here every single number in this table has the condition blue and that's different than the quad in the South that 246 is only product equals quad and region equals South when I click right here its quad and Midwest but both of these have the criteria blue applied so all the numbers have blue now we need to add our slicer that gives us the ability to select multiple sales rep now actually you can and later in our later pivot table we can actually instead of having the filter and having to mess around with this drop-down we can add color as a slicer but in order to get that trick to work here we needed to have that in the filter area now one last thing before we add the slicer I would like to name the pivot table right click and pivot table options anytime you want something Universal to apply you might look through this dialog box things like auto fit column widths on updates sometimes if you're pivoting it a lot the column widths change a lot so that's a good one to change but here we want to come up here and name in this pivot table I'm going to call this product pivot oh one now normally if you just have a single pivot tip you don't really need to name them but later when we start having multiple pivot tables or multiple slicers it's really helpful to have a pivot table name I'm going to click OK now how do we add a slicer now this is a filter that filters the whole report if we go up to analyze the filter group has our slicer because a slicer is just a fancy visual filter for our entire report so I'm going to click insert slicer here is our option it's all the fields all I want is sales rep when I click OK just like that I'm given a slicer now notice there's these context-sensitive ribbons wherever we're clicking we have context-sensitive ribbons so I'm going to select this slicer and then go up to options I would like to add two columns now I can come over come over to the corner and widen it a bit drag it down and you can size it however you'd like and I'm going to try to add some color to this slicer options more button and I'm going to select this color and now I'm going to click Colleen and instantly the entire report is filtered for just that one sales rep right now there is one two three and four conditions for each one of the cells at our pivot table if I want to also select drew I can hold the ctrl key and now it's calling or drew now we're using an or logical test because these two conditions are coming from the same column here I want to also add Kyle so I'm holding ctrl and Shari and just like that there is that 102 that is the sum of units with seven conditions quad and South and blue and calling or Drew or Kyle or Shari now there is our finished first product you know I should have created this as the same blue to be consistent so I'm going to click here go up to right click modify down to header row format fill and I'm going to select this blue right here click OK report filter labels format and select that same blue click OK click OK and that looks a little bit better alright let's go back to our topic sheet because we want to look at our pivot table number three that we're going to create now this one's going to be amazing we're going to have two different pivot tables with two slicers and we'll connect both of the slicers to both of the tables and in this products report we're going to have total revenue then we're going to count then we're going to calculate the average so we'll have three different calculations for each one of our row header conditions or criteria then we're going to see how to group by month we'll take individual daily transactions and roll them up into total revenue for each one of the months and a percentage of total calculation for each one of our months all right let's go back over to pivot tables I'm going to make sure I have a single cell selected and we want our pivot table on a new sheet so I'm going to use the keyboard alt + V enter and just like that there is a new sheet with a pivot table I'm going to double click and call this two pivots report and enter now our first pivot table will be based on the product so I'm going to drag the product field down to the rows instantly we get a unique list now remember we need it to sum the revenue count the transactions and calculate the average on revenue so I'm going to drag revenue down to values now because that's a number field notice it defaulted to sum if I dragged product which is a text field watch what happens I'm dragging us down to the values it will default to count now it actually doesn't matter which one of these fields that we drag down here if we want to count because what it's really counting is how many Belen's were sold so even though there's lots of columns counting will always count whatever the criteria is in the row column slicer and filter area now I have a third column and I want that to be a calculation on revenue so I'm going to drag it down to values notice we have three fields down here sum of revenue count and sum of revenue two now we're going to actually have two for each one of these fields in each one of these calculations either change number formatting whatever the name is at the top and the calculation so we'll start with sum of revenue now this one's already fine it's the sum of revenue it has the right calculation so I'm simply going to right click and change the number formatting now I'm actually going to not include even though that's a dollar amount I'm going to indicate the dollar amount with the name at the top so I'm going to use number comma and I don't think I really want to see the pennies for this particular column so I'll put decimals zero click OK now I'd like to change the title and indicate that these are in dollars so I'm going to simply type and in parentheses I will indicate the dollar so that is the unit now I'm going to come over to the second column right click number formatting will do something similar yes I'm using numbers but this is not dollar amount so at the top I'm simply going to type count transactions and enter I won't indicate that there's a dollar unit finally this column now we have to change the title at the top the actual calculation and the number formatting so when you have all three sometimes it's easier to right click and go down to the one-stop-shopping value field setting so when I click that here's the one-stop shopping I can change the calculation there's 11 different aggregate calculations here I'm going to change it to average I'm going to come up here and type and I'm going to indicate the dollar sign here up at the top this will appear at the top and now look at this there's our number formatting so now I can come over and say number use a separator and this is going to be an average so I definitely want to see two decimal places click OK click okay and then all three things have taken effect now I want to get rid of that row labels and add gray lines so go up to design layout report layout show in tabular click off to the side and that's our first pivot table I want a second pivot table right here so I'm going to go back over to the sheet with a single cell I'm going to use the keyboard alt + V and now I'm going to say existing worksheet and then select the location there's my collapse button so I can put it anywhere I'm going to click on this two pivots report I can see the sheet name right there and then I'm going to click in f3 click OK now we're using Excel 2013 here so watch what happens when we drag date down two rows instantly we get a unique list now if I drag revenue down to values that would be great that would be the daily sales but I want monthly sales so there's a great feature called the grouping feature now if you're using Excel 2016 or later actually when you drag a date down to rows it automatically groups into months and years but in earlier versions you have to right-click somewhere in the date field in the pivot table and point to group now it'll ask us how we want a group we have dates so I want months and I'm definitely using years we can see we have multiple years here sometimes if you have times like from internet sales maybe you want to group by hour or minutes now I click okay and just like that when I drag revenue down to values instantly I get the total amounts for each month in each year now if we look down here it says years and date so the years field is also up here but notice that the date actually represents the field for our months now I'm going to drag the year off and when you remove a field you can actually drag it anywhere because we're going to add a slicer for the year now let's fix that row labels design port layout and I'm going to say in tabular right-click number formatting and we're going to do something similar number come on no decimals click OK and I'm going to call this and indicate the dollar sign so there is the total for each month ah but those are both years that's not what we want we'll get to the slicer in just a moment we have another calculation we want to make here so I'm going to drag revenue down to values now whereas in this column here and these three columns we used aggregate functions to make some calculation we actually want to make a calculation that's the percentage of each one of these of the column total so I'm going to come up and right click now if we summarize values by we get our aggregate functions but now if we do show values as there's a bunch of amazing calculations percent of grand total percent of column total now in our case these would both be the same because we don't have a cross tabulated table percent of row difference that's the change from period to period percent difference running total all sorts of amazing calculations I'm going to say percent of grand total and just like that it calculated the percent of grand total I'll change the title at the top percent a revenue total and enter now we need to add a slicer so I come up to analyze filter group in there is insert slicer now I want two slicers I'm going to select color and years click OK now I want to add some rows above the pivot table so I'm going to highlight one two three four rows and right click insert because I've highlighted the rows not the cells when I right click insert it will know to insert those extra rows now I'm going to bring the slicer up here and try and resize it first let's change columns to one two three four five and now I can resize it bring years up here options now notice I changed it to two columns here and I see some ghosts that I don't really want so here's a great trick right click slicer settings and we can see hide items with no data click ok now I can resize this now if we want to make both of these slicers the same height and size we can come over here and I'm going to notice the height is 0.68 so I'm going to come over to the other slice and height I'm going to say 6 8 and enter so now they're both the same height now using the ctrl key I'm going to select both of them come up to options align top now I'm going to add with them both selected the same color so up to options and I'm going to select that color so there we go now let's we have a couple things we want to do to the pivot table I want to select one of the pivot tables go up to design and there's the more button and there's our custom format we made in our first pivot table click in the second pivot table design the more button and there is our custom style now let's just see what happens if I slice these if I select blue watches when I select blue its governing the monthly report here but not the product report if I come over here and select 2015 yes it's governing this pivot but not this one now before I connect these both to both pivot tables let's name each pivot table right click pivot table options and I'm going to call this product pivot o - click OK right click pivot table options and I'm going to name this monthly pivot and enter now I can come up to the slicer right click and there it is report connections and you can see there are a bunch of pivot tables here luckily we named them I'm going to say product pivot - that's the one we want selected and click okay right-click report connections same thing all connected to both of our pivots click OK and now when I select read all the numbers change when I select 2016 just like that blue 2000 2015 that is pretty amazing so we created this pivot for products with one two three aggregate calculations over here we grouped by month got the total for each month and then did a show values as calculation percent of total and then we connected both pivots to both slicers alright we have one last pivot table report we want to create and it's actually going to do a couple other show values as calculation let's go over to topics and here's our fourth report we're going to group by month then we want to see the total the running total the cumulative percent or percent running total the amount of the change and the percentage change and then connect it to two slicers so let's go back over to pivot tables I have a single cell select I'm going to use the keyboard alt + V and enter here is our field list date down two rows and look what happens every time we build a pivot it's going off the same cached source of data so it remembers that we grouped by month now all of the calculations are going to be on the revenue field so I'm going to actually have to drag the revenue field one two three four and five times we're not going to need this field list anymore so I close it I'm going to get rid of the row labels design report layout show in tabular one at a time we will change each one of the calculations this one already has our sum so I'm simply going to go to number formatting number comma 0 desc oles click ok we'll call this total revenue in dollar I'm actually going to do the amount of the change it's more appropriate that's right here next to the amount so we can see the actual amounts and the amount of change then the percentage change then they're running totals right click show values ass and I want difference from it's going to ask us two questions what's the base field well it's date and sometimes we like to see the amount of difference from a based period like January but that's not what I want I want to say from previous when I click OK just like that we can see the difference between January and February with minus down 4870 now I'm going to add some number formatting right click number formatting number comma zero decimals click OK and I think I'll change the title up here change and indicate the unit right click show values as and we come down to percent difference from date and I want previous click OK look at that it already has the number formatting I'll come up here percent change and enter that's pretty amazing that we can do those two calculations so quickly in a pivot table now let's do running total right click show values as and there is running total in and it's going to ask us what well we only have one criteria off to the side so date is fine click OK right click number formatting comma zero decimals click OK and I'll indicate the label running total and enter so we can see as we're accumulating the very last period is the same as our grand total come over to the last column right click show values as percent running total in date is fine click OK so as we're moving forward we can look all the way through November accumulative we had sixty three point three nine percent of the revenues of course the last category is 100 cent change the label percent running total sometimes this is the percent cumulative total and enter come up to design more button and there's our custom style right-click pivot table options and I'm going to call this monthly pivot o 2 and enter now I want to add two slicers analyze insert slicer we're going to do color and year click OK bring the years right over here we'll say two columns right click slicer settings and I'm going to hide items with no data resize this I'm down here select both of them at the same time options more button and I'm going to select that color now I can select 2016 blue and there it is the total for each month the amount of the change percentage change running total and percent running total that is the power of pivot tables Wow we saw a lot about how to create pivot tables and how easy it is to create calculations with conditions in criteria we saw how to do the pivot table and the slicers how to do multiple calculations both aggregate and these awesome show values as calculations back here on two pivots we did two different pivots with a series of calculations including multiple slicers and connected both pivot tables to both slicers and then over here on product report our first pivot table which illustrates the true meaning of what a pivot table is for each calculation in the pivot table it's a calculations with one or more conditions in this case we had 1 2 3 4 5 6 & 7 calculations alright that was a lot of fun at the waa t-- august 26 2016 counting seminar this video we did pivottables the last video we did look up formulas alright we'll see a next seminar

Channel: ExcelIsFun

Views: 276,591

Rating: 4.9312134 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Washington Association of Accounting & Tax Preparation Excel Seminar, WAAT Bellevue August 26, 2016, PivotTables, How to Build PivotTables, Pivot Tables, Excel Pivot Tables, PivotTables are Easy, Excel Slicers, PivotTables Slicers, Pivot Tables Slicers, WAAT Accounting Seminar, Cross Tabulated Report, What is a PivotTable?, Group by Month PivotTable, Show Values As

Id: p0U9oeRggFk

Length: 35min 37sec (2137 seconds)

Published: Wed Aug 24 2016