Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples

welcome to Highline Excel 2013 class video number 35 hey if you want to download this workbook for a week 6 and this is the 2nd workbook click on the link below the video or go to our class website wow this video is all about Excel 2013 pivot tables and it's going to be an epic video we're going to go from the basics to advanced 55 examples of pivot tables now there's going to be a long video with a lot of topics so you want to click on the show more button below the video and there'll be this whole table of contents with time hyperlinks and you can click on any one of the hyperlinks and go to the particular section of the video you want now this is supposed to be a one-stop shopping for pivot table so if you want to learn you're trying to get up to speed watch this from beginning to end all right now we're going to start off by going to the first sheet data 1 now at the top of each one of these sheets data on data 2 all the way to 5 there'll be all of the topics that we're going to cover in this particular section of the video alright here's our data set here's our 1 2 3 reports we're going to create using pivot tables now these reports are created using formulas and these formulas are fine and dandy and we'll compare and contrast pivot tables and formulas after we build a few pivot tables now to start off we just want to know what is a pivot table if you ever get asked this in an interview here's what you're going to say calculations with criteria that's it that's what the pivot table does so amazingly now really it creates reports that contain calculations with conditions or criteria and this is what that means right here this is going to be a pivot table result with all of our sales rep as one of the row headers and each one of the reasons as the column headers the intersecting cell well we're adding here that 3,200 is adding with two conditions or criteria it's adding all of the sales from the sales column for fam in the Midwest you can count do the max do the min in each one of these columns we're doing one of these calculations with one condition over here each one of these cells is making the some calculation with two criteria Qin sales in the East so the one line answer what is a pivot table do calculations with criteria now number one before you can do a pivot table what do you need a proper data set field names column headers must be in the first row records in rows and empty cells all the way around the data set or row headers or column headers now the trick to pivot tables especially when you're learning is to visualize the end result first now this will be our first pivot table that we create here no problem your boss says I want each one of the sales rep by region in a cross tabulated table so you quickly you know sketch this out you see oh wait a second the sales rep names are going to be in the row headers right so there's a bunch of calculations in this row and at the head of the row is the criteria sales rep GALT sales rep fam so this is our row so when we go to the pivot table well which field did these names come from it came from the sales rep field so we're going to drag this field name into the row area of the pivot table not only that but each one of these columns has a criteria at the head of the column so since the region names come from the region column will drop the region field into the column area then we have some values we're making calculations upon now they happen to be numbers here we'll see you let you can do calculations on text items like counting later but they call this part of the pivot table values all right let's create our pivot table what do we do with all the data analysis features we've seen sort and other features you click in a single cell then you can go up to insert pivot table now we're going to be creating a lot of pivot tables and if in your job you create a lot of pivot tables forget clicking insert pivot table you want to teach yourself the keyboard so you click in a single cell and hit the Alt key you notice that it's n you tap the N key to enact this ribbon and you see that V is for pivot table now the beautiful thing in 2013 is that they've shortened this keyboard shortcut it used to be alt n VT all right you ready it's one key shorter one cell selected alt n V now we also want to notice that if we're going to create pivot tables on new sheets this button is light blue that means if you hit enter it will click the ok for you now why are we going to do that because we know that the selected table range because we have a proper data set it's always going to get that right if you want it on a new worksheet that's the default and we'll talk about this amazing new option in 2013 later all right so I'm going to escape and do it one more time all 10 V enter instantly a new sheet is created this is where our pivot table will appear and over on the right is our user interface now we want to click up in the title bar and click and drag and move this closer it's kind of hard you have to pull it always before it moves and look at this here's our field list all of our fields from our table there's a sales rep there's a region there's the sales those are the fields we're making calculations or pulling our criteria from down in the bottom part of the user interface that's the row area that's where our row criteria goes this is where our column criteria goes and that's what we drop the field want to make the calculation on so you're ready if you look over here it's suggesting that you check these boxes up here now that'll work sometimes but sometimes it won't by checking sometimes it sends the feel to a crazy place and so we want to learn a better method simply point to the field click and drag if the field is up in the field list it says no no no no but as soon as we get down to the bottom part you could see that green line means yes you can drop it here so I'm going to drop it into row area instantly look at that a unique list of sales rep names now let's just admire let's go back to our data set that means we pulled the sales rep filled with many duplicates in the pivot table went through the whole column and shows us just one of each that's called a unique list now we can do the same thing for region click and drag down to column when I see that green bar let go just like that East Midwest Southwest check that out a unique list of regions I did not even have to know what the list was these are four categories four regions of sales I didn't have to look through the whole list and figure out how many different regions we have I just drag and drop and it tells me there are exactly four now I drag sales down to values area when I see that green bar drop can you believe it look at that there's our calculation right there adding with two conditions fam sales in the Midwest right here Galt sales in the West that's amazing with a simple few clicks we have our pivot table now what do we do Alt + V enter drag drag drag now this is great if you're just quickly trying to figure out what the numbers are you're doing some quick data analysis but if you're printing it out we still have some work to do for example column labels row labels that's the default for pivot tables we don't to say column labels I wanted to say region I want the actual field name here I wanted to say sales reps every pivot table comes out this way so if you want to change it you have to go up to pivot table tools context-sensitive menu design over to the layout group report layout show in tabular form we'll have to do this a lot in this video OOP and instantly region sales rep that's formatting tip number one formatting tip number two is these numbers need number formatting these are dollar amounts so I'd like to show some sort of currency or accounting instead of formatting the cells like we normally do right click format cells and notice when we use this dialog box we get all of the tabs for formatting cells so instead of this dialog box there's a few ways we can actually number format the field so it sticks with the field when we pivot the pivot table right click number format when we open this from a pivot table it has just the one tab and we can apply this number formatting now there's a few other places we'll see this single number tab format cells dialog box where we can format the actual field and it will stick with any pivot we make I'm going to select currency and showtunes no decimals you can pick whichever one you want accounting show decimals I'm going to do that click OK all right number formatting the third formatting tip is if we'd like to highlight and add some different colors if we go up to home and start using these formatting commands in the home ribbon it won't stick with the pivot table when we pivot it'll do just what we said a second ago with format cells the formatting will go in the cells and we want it to pivot with the pivot table so instead we click in the pivot table and go up to pivot table tools design now there's some built-in ones we can click this more button you can use these if you want I like to be minimal and use exactly the kind of formatting I want so we're going to see how to create a new pivot table style click and it's pretty easy first we're going to give it a name I'm going to call this blue then there's different elements to a pivot table and you can go through and try the different ones this is like most dialog box you click click click in format and watch the preview I'm going to be minimalist I'm going to scroll down just a little bit and I just want header row that's this up here and now I'm going to with my element selected click format so font color how about white bold fill dark blue click OK so I can see my little preview starting to emerge now I'm going to come all the way down I'm going to do grand total on the row that's this down at the bottom of the pivot table formatting I'm going to select a different lighter blue font white I might add some border to click okay so I'm slowly building my formatting for my own pivot table style now that's my minimal pivot table style I'm going to click OK oh but check this out before you click OK after you've created your style you can set this as a default that means any time you create a new pivot table I'll have this blue style I'm going to click OK now we do have to apply this one because it's already been created so I'm going to click my more and it's right at the top under custom there we go later when we create lots of pivot tables will always come out like this now let's do a little experiment if this formatting really sticks let's pivot the pivot table and see if the formatting will with it both the style formatting and the number formatting hey not only that we'll get to learn why they call a pivot table a pivot table now you're in your budget meeting you made this beautiful report sales rep by region and the person in the budget meeting says hey we really wanted all of the products for each sales rep in each region hey that means we'd have three criteria so you're like no problem you come over you find the product field and you click let's drag this up here and you click and drag it down two rows now I'm going to wait till I see the green bar below sales rep and instantly look at that sales rep each one of the products in each one of the region it looks like all of this number formatting has totally followed the pivot and so has our style formatting that is amazing hey guess what formulas are not going to do that that would be one monster insane formula I don't even have any idea how to do that but a pivot table simply click and drag totally updates every one of these calculations 3criteria chin Carlota in the Midwest that's the number 460 now to remove a field simply uncheck or click and drag anywhere and instantly we've pivoted back to our original pivot table now what about source data if we were to look at fam West 9000 790 what would happen if we go back to our data set and let's see fam in the West there's a number what if we changed it not only that over here we have a formula right I think we already know the essence of formulas is if I were to change anything in this column it updates I wonder what the pivot table does well let's try it I'm going to click in cell G 46 and type 5,000 now before I hit int or watch over here ready enter well of course the formula updates instantly let's go back to our pivot oh it doesn't update here's the deal when you create a pivot table from a data set it actually stores the data in a cache so you have to refresh a pivot table if source data changes now you can do this a few ways right click refresh analyse refresh notice refresh has a keyboard alt f5 if you have many pivot tables ctrl alt f5 all right so I'm going to click in one single cell alt f5 and instantly it updates after we refreshed it now let's go back and change that number again data 1 instead of 5,000 it's 8 10 and 71 pennies enter the formula instantly updates pivot tables right click refresh instantly it updates and you can click in any cell and then use your keyboard or right-click or analyze now let's have a little fun with this pivot table and learn some more about why the pivot table is so powerful I'm going to highlight the whole pivot table there's actually a great trick if you point to the upper corner and you see that horizontal black arrow clicking it highlights the whole pivot table ctrl C to copy and watch this I'm going to do something dangerous I'm going to paste it right below ctrl V why is that dangerous because if I pivot this by adding product I'll get a warning that says you're going to replace all the data below but no problems sometimes you want to see your reports one on top of the other now we're going to change it just a little bit we can do all sorts of things to a pivot table we saw how we could pivot it but what about changing the function right now it's some there's 11 functions one way to change the function is right click in the value area and it says summarize values by and on the short list is just a few of the functions hey I want to change the to average instantly all the calculations are averaged so here I'm making the average for Galt sales in the south not only that but it changed the label at the top also now there's a few other things you can do in a pivot table here's a drop-down and guess what there is sorting I can sort Z to a and instantly Smith is on top I can go back a to z chin is back on top I can filter it uncheck I'm going to show Galt and Smith click OK I can unfilter clear the filter so you can pivot it change the function filter and sort now I'm going to copy this pivot table I'm going to use my little trick Boop ctrl-c ctrl-v and now I want to count transactions for each sales rep in each region and I want to look at the one-stop shopping dialog box for calculations in the value area I'm going to right click anywhere in the value area right click and point to value field settings value because the value areas where we make calculations field because we dropped a field in here and settings because this is where all the settings are one-stop shopping and I'm going to open this dialog box we can change the name that appears in the top corner we can change the function oh we can do our number formatting we can even do some other calculations from the show values as tab or look at this later all right so you ready there's 11 functions available in a pivot table just like the same 11 we saw in subtotals now as I look through here I see a count and there's account numbers yeah count numbers does just what it says it counts numbers this is parallel to the count function we saw in the Excel spreadsheet count is parallel to count ah that we saw on the spreadsheet that not empty cells I have numbers so I'm going to click count numbers and oh there's some number formatting with counting I don't want dollar sign so I'm going to click the number formatting again this is the one-stop shopping you can access everything you need from the value field settings dialog box I've selected general click ok so number formatting function and I'm going to come up here and backspace and change the name also just count click OK now for this example we counted how many sales Frank's had in the Midwest and we use count numbers because we were counting on this sales column but now I want to do the same pivot on one I want to make it from scratch and look at the default calculations for fields that are numbers like sales units and cost of goods sold and what the default calculation is for text fields like product region and sales rep so I'm actually going to go back to the data set click in a single cell alt NV existing location click on pivot one and very carefully click in a36 click ok so I'm going to create a new pivot table sales rep in the rows region in the columns now when I drag sales down to values this is number so automatically it defaults to sum if I right click value field settings I can see it's sum now it has no number formatting because this is a new pivot table let's drag this back up here or wherever and now watch what happens when I drag any text field drag-and-drop if the default is count and if we right click value field settings we can see it chose count which is really count ah that's the function accounts cells that are not empty now let's notice something interesting the this is counting and getting the same number as this pivot table up here so count of region we're actually County and region up here we were counting sales but we changed the the name up here notice that what it's really counting is how many transactions had the row header criteria and the column header criteria so this number really means how many transactions where there are records for Galt in the Midwest alright and watch this I counted Region four counting you can drag any field I'm going to drag product same numbers if I drag sales rep same numbers if I drag cost of goods sold or whatever and right click right click value field settings count numbers click ok it's always going to get the same numbers because it's actually counting transactions where the criteria from the row header and column header are met now there's a list over here of all the functions st over here here's the functions for pivot table the same as for the subtotal we saw the only difference is later we'll see there's something called data model in 2013 only and there's a unique count function but we'll talk about that later now I just click back on pivot one sheet and we just saw how if we take a number field and drag it down to values it's the sum function if we drag a text field it's the count function but we got to talk about what will happen if there's mixed data now I'm going to create a little data set up at the top here so we're going to have some two numbers an empty cell and text now let's create a pivot table from this mixed data and see what happens alt NV existing worksheet location I'm going to click in K 3 right next to our data set click OK drag region down two rows now sales down to values because it's mixed data it defaults to count I'm going to change this to a number and see see what happens right click refresh not going to change because there's a count there which is only counting not empty cells let's see if I drag this back up here if I drag it down again will it show me some no the reason is that empty cell in a number column will always make the pivot table default to count now this is important sometimes you get a column with sales numbers and there's one empty cell or even worse dates and there's one empty cell later when we see how to group dates in two months we'll see that empty cells can cause big trouble so if you can avoid empty cells by all means do if you drag a number into your values area and it comes up count it probably means it's a data mismatch if that's the case and you can't rectify the empty cells you can certainly change it to some right click summarize values by and let's do some all right so mix data text data number data let's go back and create our next report I'm going to go to data one now right here is the report we created in our subtotal video and we want to create this report with a pivot table and see it's a lot easier than that subtotal feature now we also want to review five easy steps for creating a pivot table click in one cell in a proper data set Alt + V enter field list click and drag region to the row sales rep to the row sales to the values with our pivot table selected design report layout show in tabular right click number not format cells and some number formatting currency with zero decimal showing and there you go plus notice that default I love it we set it as a default and now every pivot table gets that style five easy steps that sure is easy now we're going to go back actually we always name our seats I'm going to call this pivot to enter go back to our data set we want to create this report right here one two three calculations all in one pivot table based on region criteria click in a single cell l NV enter for three calculations we drag region to the row and then sales one two three immediately come up to design report layout tabular the number formatting and the functions we actually have to do three different times now the first one is count so I'm going to do my one-stop shopping value field settings change it to count numbers use my number formatting oh I don't need any number formatting general is perfect and I'm going to type count transactions click OK right click value field settings change the function to max number formatting some sort of dollar sign and then change just to max of sales click OK right click value field settings min number formatting change the name one two three click okay so pretty quickly we've created our report I'm going to come down here and call this pivot three three calculations in one pivot table the trick is just drag the same field or whichever fields you want to make calculations upon and then it's a multiple step you have to format change the function or calculation on each field now let's go to the sheet 55 topics and just to compare and you can read this if you want here's the kind of comparison between pivot tables and formulas pivot tables are easy for multiple criteria calculations usually formulas with criteria are harder to create if we have source data changing formulas don't update automatically formulas do if you're pivoting and changing the criteria pivot tables easy to do formulas basically impossible to pivot whole categories of criteria functions easy to change in a pivot table functions are more difficult to change when you have formulas hey there's 11 functions in a pivot table there's 450 functions when it comes to formulas formatting is limited in a pivot table formatting is less limited when it comes to formulas you know we can go and get whatever formatting we want from the ribbons now I want to go back over to the sheet pivot - and we have a couple amazing pivot table tricks we got a look at here now this is our subtotal report and remember we could see with a subtotal the entire transaction well one thing about pivot tables is they do have these collapse buttons similar to what we saw with subtotal but remember when we uncollapse for subtotal we saw the whole record but watch this there's a double click trick for pivot tables now what's going to happen if I double click any calculation it will extract all of the records that went into that calculation and dump it on a new sheet so watch this this is Gault in the East I want to see all the transactions from the data set double click and instantly on a new sheet it extracted from that big data set all of the records there's East Gault amazing I'm going to call this D extract let's go back to pivot - now here I want to right-click this and go to expand collapse and I want to say expand entire field an amazing thing with pivot tables this is pivotable we can change the functions and everything guess what we can make a pivot chart now you can with one single cell in the pivot table go up to insert and pick your chart you could also go up to analyze and there's pivot chart you can also use your keyboards now the keyboard for the default chart on this computer the default chart is column so I'm gonna use alt f1 and you got to be kidding me look at this I'm going to close this field list and look at that now this pivot chart is totally connected watch this if I collapse this and then collapse this that is amazing now I may we maybe want to expand this but it's these two pivot table pivot charts these two things are completely connected not only that but I could do it down here also I just want to see south and west click OK and instantly I have pivoted my chart pretty amazing alright now we want to go over to data two and we want to talk about grouping so I'm going to come over to data two now this is a different data set than we've been working with but here we have a huge data set if I ctrl down arrow so there's about a thousand records control up arrow we have dates here and if I scroll down and there's all sorts of months and there's all sorts of years too so down here there's 11 there's 10 control home I'm going to click in a single cell and my boss just asked me to calculate the total line revenue by month now if I was going to do this with formulas sure we could do it but it would be a lot more complicated than what we're about to see the grouping feature inside a pivot table so single cell in the data set Alt + V enter I'm immediately going to double click this sheet 10 and call it pivot 4 enter watch this date down to row come over right click group now there's all sorts of things we could group by seconds minutes hours days months quarters years let's do months oh but be careful I always click years also even if I know it's only from one year just because if there's a rogue date in here I don't want all the January's to be grouped together when I click OK that is simply amazing and here it is I simply drag total line revenue down to values and instantly I have summarized by month design tabular right click number something like currency click OK now if you come over here you'll see that a new field has been added if you didn't have years it would look like this so that's all the January's for three years so if I click back on years instantly oh look at that that's why I don't like to click and send things to my report it sent it below no problem I can drag it above date and there's the year and then the month that's grouping by date so here's our date field right here now I'm going to go back to our data set data - we want to group by time you're kidding me now this spans many months and years but I just want to count for this whole period and see what is the hour that most transactions occur alt NV enter time down two rows right click group uncheck months I'm going to say hours click OK instantly this is for all of the years all of the periods and I want to count I can drag any one of these as I mentioned earlier I'm going to choose to drag text because I want to default to count and instantly there we have so it looks like there's maybe a pattern how about alt f1 for my default chart so instantly from a pivot table I have summarized by our the count when did most of the transactions occur from this sample wow so most of them are right in between eleven and five immediately I'm going to come to this sheet 11 and call this pivot five enter now I want to go back to data two and let's say I want now a daily sales report so alt NV enter let's call this pivot 6 getting a lot of pivots here scroll over here remember our my goal here is to do a daily sales report so I go to rows oh they're grouped if I come down here and I drag years above oh they're grouped now here's the deal what's happening is there's one cache of data from this data set and the reason in earlier versions they used to have multiple caches every time you did a new pivot table but that that would increase the file size so what they've done is that is coming from my earlier cache so if you want to have one grouping on one sheet by month and another grouping on another sheet from the date field with a different grouping or no grouping we have to create a new cache I'm going to come down here and delete this right click delete I probably should have named it in the first place it says do you want to delete it yes we do so here it is here's the big trick we have to invoke the 2003 or earlier dialog box and you got to do it with you can actually add an item to the kua if you want to but here's the keyboard alt d P alt D for the old data menu P for pivot table here it is the old three step wizard we're going to say Excel list we want a pivot table click OK it's guessing that's guessed it right click OK and here's the message it's saying basically hey you're going to increase your file size if you do this if you click yes you will save memory if you click no the two reports will be separate glad I read dialog boxes No now the third step is where do you want to put it I'm going to put it on a new worksheet finish and now when I click date down to row there we have dates not grouped so now I could scroll down long total line revenue down to values and there I have my daily sales report double-click this and I'll call this pivot six enter now we want to go over to data three and talk about two other grouping issues now I have a data set right here the dates we can obviously see there's a problem there's an empty cell and there's a date stored as text now I'm going to leave that one there for you to practice with we're actually going to use this one and then fix the problem all right so let's try to group with dates this is a really common known problem with dates alt envy and I'm going to place this on this existing sheet right below click ok so now if I try to drag the date down to rows right-click group not going to work this very unhelpful message cannot group selection what why doesn't it say hey data mismatch so I'm going to highlight this and clear it alt eaa that's clear all then fix the ones last one size 11 and then so now we have a proper data set with full dates in the column alt NV existing I'm going to click right below click ok so now when I drag the dates down here right-click group no problem it has it can group now because there are real dates I'm going to say days not months and then say I'm going to group by two and there we have grouped by dates you could do that for a week obviously and have seven days in your week units down to values and there we have grouped by those two day groups so that's a known issue with dates data mismatch doesn't work all right now here's another interesting grouping problem sometimes we have integers sometimes we have decimals and when you group them they totally group differently now why would you ever want to group sales numbers like this because you want to see from zero to $5 how many counting how many transactions that were from 15 to 20 how many total transactions I want to start with the integers alt NV existing location I'm going to put it right next to the data set click OK drag sales down to row right click group and immediately it picks out the min and the max now I'm going to start at 1 and end at 20 just because we have even numbers there anyone want the increment for the group to be 5 click ok and look at the categories we get there very polite 1 2 5 6 to 10 11 to 15 now I could drag this down to values and it counts between 11 and 15 bucks there were 5 transactions now let's try the decimals alt NV existing location I'm going to put it right below it click OK drag the decimals down to rows and right-click group I'm going to start for this one since it's decimals at 0 and go all the way up to 20 and the increment is going to be 5 click OK and look what they did here 0 to 5 five to ten so how do you know where the five dollar transaction would go so these labels are ambiguous however they count correctly they just count in this way the upper limit is included so for the five if there was a transaction there was exactly five bucks it would be counted in this category lower limit is not included so the five is here the ten is here the fifteen is here if you remember back to the frequency function in our array video the frequency function created categories in the same way the upper limit was always included so that's what you want to watch out the labels are ambiguous but as long as you know it's the upper limit that's included it will count correctly all right so grouping those are a bunch of tips about grouping now we want to go over to data for sheet now we have these amazing pivot table topics here and we're going to start with recommended chart and recommended pivot table now here's our data set control down arrow that's 10,000 records control up arrow now charts usually we summarize the data first off to the side so we take the region we'd get the total for each region either with formulas or pivot tables and then go to charts but this new recommended chart is amazing click in a single cell and watch this recommended charts and watch what happens it's his sum of price by product sum of revenue by product it went through the product column and summarized all the revenue that's amazing and that icon right there tells you how it did it it did it with a pivot table if we click down here sum of price by region now it does a number of them this is sum of revenue by region this one's sum of units anytime they're number categories like this it thinks you might want one of those summarized by one of these text columns over here so check this out summer price some of Avenue by region that's exactly what I want so when I click okay pivot table and a pivot chart with a single click now I'm going to double click this and call this pivot ce7 enter now if we go back over here obviously when we go to insert recommended charts it's not going to do all the things we want most of the time but sometimes for simple charts I mean if you always get region and you're always doing revenue just remember recommended charts click the third one ok that's pretty amazing well if you know what recommended chart does you probably can guess what recommended pivot table does single cell and a proper data set let's click recommended pivot table sum of revenue sum of price by region Wow look at this one this one's got region and then sales rep down here we have all the products I'm going to select this third one region sales rep click ok now we have to fix it up a little bit right design tabular right click number add some number formatting but that's pretty amazing recommended pivot table and recommended chart whoops let's rename this double click and I'm going to call this pivot 8 now I'm going to go back over to data 4 and we want to talk about report filter now report filter is just adding a filter or a new criteria to the pivot table report not only that but there's a bunch of cool report filter tricks report filter show report filter slicers using a slicer and report filter to extract data and even timelines now let's click in a single cell and create a pivot table on a new sheet oh wait a second I'm not going to use alt n V I'm going to do insert recommend it because I want to do products only so I'm coming down here and click drag this over and click OK and just like that I have a new sheet I'm going to double click this and call it pivot 9 enter all right so here's filter right here we simply are going to drag a field here's region o at the top there's a drop down now I can simply click and select East and instantly that's a new criteria added to our report so any intersecting cell that some of revenue is done for Sun Bell in the East region alright I'm going to come up to design tabular right click number and there we have our report now this is great you could print out east and come up here and select south and print each one of those out now your boss comes in and says hey this is great you're printing these out but I'd like a workbook with each of these reports on a new sheet so instead of doing it manually we're going to go up to analyze and over here in the pivot table group and I know this is hidden here there's options and the drop down arrow right next to options show report filter pages this is just amazing now it'll ask you sometimes you have multiple filters and allows you which one you want to show report filter pages for I'm going to select region now before I click ok watch down here this is where our pivot is but instantly boom off to the left there'll be a new sheet for each one of the items in our region so for us it'll be for West East South and Midwest ready click OK look look at that here's the original pivot there's West South Midwest East did even name the sheets just that alone not having to double-click a name each one of the sheets is an amazing time-saver that show report filter pages now on pivot nine we want to look at slicers I'm going to close this pivot table field list and we want to look up oh there's a hole filter group timelines and slicer they're just fancy good-looking easy to use filters notice this drop-down sometimes it's very small it's hard to see you have to come over here and click it we need a better way to filter so I'm going to click insert slicer and check region and watch this click OK there is our filter called a slicer I'm going to click and drag this up we can put this over to the side we can change the colors notice slicer tools options change the colors I can change this to different columns meaning if I type a 4 here Boop instantly enter we have our buttons in columns now I'm going to click and drag this and watch this filter Midwest instantly bloop and it shows it up here east now let me show you something here right-click I shouldn't have closed the field list I'm going to point to show felis let's just drag this filter away and no problem does it work you betcha one nice thing about having the region though down here also is that if you ever want to double click to extract records if you don't have a filter up here meaning in the filter area you just have the slicer it's not going to respect that criteria so actually with drag it like this and let's double click this soup that's it Carlota so I'm going to right click delete this but now if we add our filter region filter now because it's Midwest there when I double click this it's going to have two criteria lota and midwest so double-click to extract records carlota midwest all the way down I'm going to double-click this and call this extract D for data - all right back to pivot 9 now let's see something else and I'm going to close this feel this amazing about slicers we're going to come back up to pivot tick click inside the pivot table analyze insert slicer I'm going to say sales rep click OK now there's a lot of sales reps here I'm going to try and drag this over and make this as big as possible I'm going to come up here to columns and say 7 pull this way over and the way the region and sales rep work is there's only some certain sales rep selling in certain region so when I click east instantly I get a list of all of the sales reps in the East all the sales reps in the Midwest the south now we can unfilter by selecting the declare filter button there and now watch this I'm going to right click this blue slicer and go to slicer settings and down here hide items with no data I'm going to check that click OK and now when I click Midwest boom South West and then clear filter that is absolutely amazing so you can combine and show whichever slicers you want absolutely amazing so if I just click Christina fuller oh this one will work also this is just for Christina fuller just charlie wood that is absolutely amazing I don't know what region elbert is in but this will tell me how about Isabel Oh Isabel's in the East Robert in the Midwest that is absolutely amazing we're using slicers to filter our report now I want to go back to data for and create a new pivot table pivot in and look at timeline so I'm going to click in a single cell and I am going to use the keyboard alt Envy enter because I didn't see any date grouping in my recommended pivot table so I'm going to drag the date right click group it found the min and Max dates months and years don't forget that years and click OK so just like that I have my grouping now I'm going to come over and get revenue drag them down to values design tabular right click number some sort of currency alright so we have our basic report but I want a filter I want a beautiful fancy filter off to the side for dates I'm going to close this field list with some cell selected analyze you're not going to believe this insert time-line date click OK look at this I can click just July well there's nothing in July 2015 but I can scroll over to September 2014 I can click and drag look at that is that not amazing so this one is four months look at this months I can click the drop down and say years just show me 13 just show me 14 drag in and show 13 and 14 I can select quarters you've got to be kidding me all the way I'm just want to see Quarter 1 how about Quarter 1 2 3 is this profound that is absolutely amazing and we could show days now days here is not going to really work I mean it will work if I click January 2013 the second it shows me exactly that one day if I didn't group this and it was just days then I could see daily sale but look up here I have only January and it's still that number adjust depending on what number of days I select in January 2013 all right let's go back to them months oh look at that just a little bit of January selected so I could show January to April in 2013 this is a timeline of filter if I want to unfilter boop absolutely amazing now let's name this I'm going to call this pivot 10 enter now we want to go over to data 5 and we want to talk about a bunch of calculating options inside a pivot table some built in calculating options and even how to build a formula inside of a pivot table now we want to start off with talking about the top 3 here which our pivot tables based on percentages percentage of grand total percentage of row total and column total now if we look at our data set we have toys these are Lego and DUPLO toys and what I'd like to do is look at each one of the sales by year so we'll have to group this category here and then here there's the total line revenue all right so we're going to create our base report looking at Toys by years and then we're going to convert them to percentages all right click in a single cell alt + V enter now we have our field list and the first thing we want to do is take date and drag it down to rows right click group months and years now look down here it says years and date date is actually the month so when I drag this up here now I have just date now watch this that's now a category 1 2 3 so I'm going to drag it up to columns there we have 1 2 3 toy name down to rows total line revenue down to values toy company down to filter so now looking at this the years the toys absolutely awesome so we're looking through here a man what's going on these toys are sitting on our shelves not selling any here's our double click trick let's go see how many transactions made up that $9.99 I'm going to double click that Wow only two transactions right click delete so maybe scooter and Stan is not a toy we want on our shelf anymore policeman that looks like the biggest amongst all of these double click that Wow so we have lots of transaction sold for that particular toy right click delete now we'd like to convert these to percentages each one of these numbers compare to the grand total so I'm going to highlight the whole pivot table ctrl C and right below control V now right click value field settings this is the one-stop shopping for everything to do with your value calculations and show values as there's a bunch of amazing options right at the top percent of grand total and it will do the heavy lifting it will actually calculate each one of those numbers as a percentage of that grand total now later when we do row and column totals you can always tell what the base is the denominator because it'll show up as a hundred percent in your pivot table report well clearly scooter our scooter and stand you know less than 1% each policemen 20 17 17 now sometimes you want to see this column each one of the items so if we look up here each one of these items as a percentage of the column total 11 the column total is right there 12 column total so highlight this and create percentages for each column total control C control V and now if you want to get to the show values as quickly right click and write below summarize which is our functions we have show values as oh I love it percentage of column total and instantly that's a lot of formula work that would be hard hard to do with formulas but look each one of the toys for 2010 is expressed as a percentage of the total that's the column total so obviously looks like airplane police officer and motorcycle and police officer are the big winners for that year similarly for each one of the other years now what about looking at each row so if we go up here there's a row total right we can get this as a percentage each one of those numbers as a percentage of the row total so I'm going to copy control-c control-v same trick right click show values as and percent of row total totally awesome now remember there's the hundred percent that tells you what the base is so for family twenty fifty three twenty six so some for some reason we sold a lot more in 2011 up here we saw the pound signs by the way when we create a new pivot table one of the default settings and pivot tables options is to always change column widths to fit this data if you want to turn that off go to pivot table options meaning right click pivot table options there I'm going to manually change these now we want to see something absolutely amazing all of these tables are similar and they all have a report filter if I change this I want to see just DUPLO click OK come up here select just Lego click OK well it'd sure be nice to do that with a slicer and it'd be nice to do it once and have all of these pivot tables change I'm going to close the field list so you're ready analyze insert slicer toy company click ok and now it's got a filter I'm going to unfilter it Lego hey wait a second these didn't change down there no problem watch this I'm going to come down click in the table analyze filter connections and I'm going to connect it to that filter look at that instantly so I'm going to do that for each one of these each one of these filter connections so now when I come to the top and I collect DUPLO instantly each one of our reports is updating that is amazing Lego and instantly they're all updated ok so we saw how to do some percentage calculations with show values as and even how to do that slicer and connect it let's double click and name this pivot 11 enter now we want to go back to data 5 now let's look at a few other amazing show values as pivot table tricks running total that will give us a cumulative running total from period to period difference from that'll tell us hey what's the difference between one period to the next and then percentage difference from let's click in a single cell alt NV enter now we're going to drag date that's our months and then we want years so I'm going to drag years above date line revenue to values this gives us the actual amount for each period for each month notice if we were running cumulative it would be 150 here but then it would be 150 plus 127 here it would be 150 plus 127 plus 45 if we were doing the difference this would be a minus because we went down from the previous period way down and then way up alright right show values as and we want running total in now it's going to ask us what's the bass field it's date click ok there we go there's our cumulative all the way to the end of 2010 and then here's the cumulative for 2011 that is pretty amazing now let's change this F to and I'm going to backspace all the way ends it's a running total and change the column width right click number currency click OK design tabular hey that's pretty amazing there now let's copy this whole thing ctrl asterisks ctrl C and I'm going to paste it right here notice down here I'm going to say hey give me the same column widths that control smart tag and now watch this right click show values as difference from you're going to ask us what the base is that's date of course but base item I always want it from the previous so I'm going to say previous sometimes you want to base it all off of one period only but I want previous click OK and just like that so we have no difference there it's the first period and then minus 23 minus 85 and then way up again so it's showing us the difference for each period I'm going to click here ctrl asterisks ctrl-c ctrl-v come over to the smart tag and say column widths right click show values as percentage difference from previous click OK and now we can see the percentage 15% down 64 percent down and then 500 percent up those are some amazing pivot tables and it's all pivotable we could also do our filter for a year and connect them like we did in our last video all right let's double click this and call this pivot 12 enter and let's click here on type different enter call this percent difference enter now actually I want to do a couple things I want to insert some rows here right click insert ctrl asterisks ctrl C and paste it right here column whit's right click show values as no calculation that way we can see the actual numbers the running total the actual difference and the percentage differences that is a pretty amazing set of reports created with a pivot table now let's go back to data 5 and we want to talk about the last few show value as tricks parent percentage of parent and then there's percentage of row and column total and ranking parent is when you have multiple items in the row area and rank is ranking first 2/10 for example so let's click in a single cell alt + V enter and now for our percent of parent total we need to have multiple items in the row so we're going to go up to toy company that will be the parent because under each parent or toy company we have a bunch of toys now we'll get a total for this row for for Lego and for DUPLO and then express each one of these items as a percentage of that total we need line item revenue Design Show in tabular I'm going to change this column width now we're going to change this revenue enter change the column width alright so we have a DUPLO total and a Lego total now we'll express each one of the Lego items as a percentage of the total but before we do that you know this line right here I'd like to differentiate this total line from the rest of the items with some formatting so I'm actually going to come up and modify the style right click modify I'm going to scroll down here and under header row I want subtotal row one format font bold click OK click OK and just like that I have bold for those subtotals alright looks right click show values as percent of parent total now it's going to ask us what the base field is we're going to say toy company click OK and so Boop there's our percentages if you highlight and look down in the status bar you can see obviously they add up to 100 now right here DUPLO and Lego or listed is a hundred percent which I kind of like for this report here but if you wanted to see those also as a percentage of the grand total you would right click show values as an percent of parent row total the only difference between the first report and this one is now these two items add up to a hundred percent and you can see one hundred percent down here if we had our report flipped meaning these two items were up in the columns then we can use right click show values as in percent of parent column total all right now we're going to go back to data five and create our last show values as it's going to be a rank and I'm going to rank the toys alt + V Enter we're going to click and drag our toy name find the line revenue and drag it twice design tabular for this one I'm going to right-click number click up here in revenue change the column width rank change the column width right click show values as ranked by the way rank and percent of parent came in in Excel 2010 I'm going to rank largest to small us toy name click okay so instantly we have the revenue and the rank right inside of our pivot table now let's I forgot to name me so I'm going to come down to the r63 and call this pivot 13 enter double click 64 or whatever year says pivot 14 enter now how about we sort this pivot table can we do that can we sort on a value field you betcha but we got to use the row field drop down more sort options I'm going to say descending on revenue click OK and instantly Boop that's pretty amazing now let's go back to data five and we have one last calculating example we want to see how to create a formula now notice we have total line revenue and total cost but we don't have a profit column no problem we can create what's called a calculated field and take this field minus this field let's go and click in one cell alt NV enter drag our field list over here and we're actually going to add a field by going up to analyze up in calculations field items and sets we want calculated field we're going to give it a name profit enter now we come down here and we create our formula we can use our fields from down here scroll down total line revenue minus total cost of goods sold now notice this is strange it uses little apostrophes there over in our table formula nomenclature we had square brackets even over an axis we had square brackets but they didn't put square brackets here that's okay I'm going to click OK and it's added down in values and it should be at the bottom of the list here profit now we can pivot on this formula any way we want if we want to do toy company toy name there's the profit for each absolutely pretty cool to be able to create a formula right in our pivot table all right let's go back actually we better name this pivot 15 enter now we want to go over to data 6 now we want to talk about something new in 2013 the data model now the data model is actually based off of the power pivot engine and I have power pivot installed here however for this video I'm going to assume that you don't have that and we can still access data model even if we don't have power pivot now what's so cool about data model is it'll allow us to get an extra function called distinct count now actually it doesn't give us an extra function it replaces the product function so that's one benefit the other benefit is sometimes you want to build pivot tables from more than one table so we'll see how to create a pivot table from two tables now let's first just look at a simple example about getting distinct count now here is a small data set and I have no idea how many days we had sales on well if I could get a unique count of dates you could see here we have some duplicates so on the 18th there's a duplicate I just want a number that tells me how many days we actually had sales on well if we add this small data set to the data model distinct count will allow us to do that now when you're using a data model you want to convert your tables to official Excel table so ctrl T click okay I'm immediately going to come up and call this date sales enter all right so we have this excel table alt in V and then the create pivot table dialog box I'm going to add this to the data model then I'm going to put this right next to the the table click OK and so now a couple amazing things here we have a new tab called all and it will show all tables that are listed in this workbook we're only working with r1 so we're on the active tab and now if I drag dates down to values all I want is a single count right now it's counting all of them but I'm going to change the function right click one-stop-shopping value field settings and there's no longer a product but down at the bottom there's a distinct count that's so cool and instantly we have our distinct count of 8 and what's so particularly nice about distinct count is the formulas for doing distinct count are big array formulas so that's pretty nice and when we pivot the pivot table depending on criteria it'll give me a distinct count based on all that criteria and those are wild hard formulas to create all right now let's scroll over here the other thing we can do with data model is we can use two tables in a pivot table now here's the deal let's think about this transaction table well we've already had this exact table here I want to go over to data 4 and look at this table except for the difference is this one has region but let's go back to data 6 here we don't have that region column we have two separate tables and there's the unique identifier or primary key in this table is Sales Rep there's exactly one name of each for the sales rep and then the second column we have the region they sell in without data model what would we do we'd come over here and create V lookup to look up the sales rep it would go over here get the region and create that extra column if you don't want to do that you can use a pivot table with data model now we have to convert both of these tables to excel table so I'm going to click in one cell ctrl T click okay immediately come up to table name and design and call this transactions enter come over here ctrl T okay enter come up to table tools design and I'm going to name this sales rep region enter all right so here we go this is actually kind of wild we're going to add this to the data model inside our create pivot table dialog box alt + V add this to the data model click OK I'm going to put this on a new sheet I'm going to call this pivot 16 now if we look over on all here's our table starting to emerge now we want to notice something the black label at the top tells us that this has been added to the data model this one has not I'm going to come back over to active actually while we're at it let's right-click and show in active tab but we need to have this one in the data model and this one and we need to create a relationship if we go back over to data 6 if we remember from access we could create relationships between tables so the relationship would be this here's our normally in Excel our vlookup table here's the item we're going to be looking up so we'd have our vlookup function here lookup that it would look to this table which has a primary key sales rep is listed only one time retrieve that and bring it back over here we're going to create a relationship just like access we would tell this field hey create a relationship based on this primary key over here this is called the primary key this would be the foreign key so we're going to go back to our pivot table 16 click inside the pivot table and up in analyze calculations is relationships and I'm going to click new the trick is is primary key that's where the sales rep name lists in the first column exactly one time each so this is our lookup table this is going to be sales rep region then we go over to our primary and select sales rep now we go to transactions table and here the sales rep is going to be called the foreign key now when we click OK we have that relationship between the two tables and notice that's been added to the data model now so get this we can just drag region down to rows revenue down to values and instantly we get two tables making a single pivot table that's pretty amazing and we can add more row criteria click and drag and just like that there are our two row criteria we created our pivot table from two separate tables all right I'm going to come back over here that was one epic video that was 57 topic all right we'll see you next video

Channel: ExcelIsFun

Views: 313,669

Keywords: Excel 2013, Highline Community College, Busn 214, Spreadsheet Construction, Basic To Advanced Excel, Mike excelisfun Girvin, excelisfun, Slaying Excel Dragons, Excel 2013 PivotTables, Excel 2013 Pivot Tables, PivotTables, Pivot Tables, PivotTable, Pivot Table, Value Field Settings, Data Model, Distinct Count Function, Slicer, Timeline, Slicer Connections, Calculated Field, Show Values As, PivotTables Formulas, Relationships, Foreign Primary, sort PivotTable, Filter PivotTable

Id: e-yuYNgsHAk

Length: 81min 49sec (4909 seconds)

Published: Sat Nov 16 2013