Lessons

Lesson Info

9:45 am - PivotTables, PivotCharts & Subtotals

So we're going to move away from tables now, and we're going to go into the tables, all right? So let's, go to pivot tables and have fun with that, so we're going to jump back over to the pc side, and we're going to go into pivot table and sub total was going open that up, and we're going to be working with the same broad data to be able to do our pivot table and our sub total. So basically, we have all this all this data here, and then we're also going to be working with a separate sheet that's going to appear, once we create our own, our pivot table, and then we come back, we're going to do our sub totals on this, all right? So let's, talk about what a pivot table is, in fact, why you'd want to use a pivot table, the pivot tables are essentially designed for you to be able to do complex type of statistical analysis on it without actually being a statistical math wizard. So basically, you're wanted looking for themes, right? You're looking for things that are going, you wantto kind of...

combine your data to kind of look at it in different ways, you sort of hold it up to the sun, you're going kind of look at a three dimensional, ten dimensional different ways people to see if combinations of different data appear as a result of you moving things around number one but number two as a result of you maybe kind of filtering things also you're going to say okay, well, you know what? Let's see how the central region did with quarter one two and three with just one certain product, that kind of thing so then we'll be able to see okay, well let's see how the quarter too did with on lee this particular product all right at this quarter and then you gotta flip it around a little bit, but it also gives you the option to be able to combined different things not necessarily just as a column in a row but also everything in one row and you can see how things can kind of stacking hierarchy all right, you're going to see how it's really really cool how you could do all these things and it does it with just a couple of clicks and drags and it's amazing how you can really get your questions answered that you didn't even know you had questions too because all these themes start to emerge and you'll see like, wow, I can actually, you know I can predict certain things where I can see certain trends and themes and things like that all right, so we're gonna pivot tables is a very complex thing but a very easy thing to implement, so we're going to see how excel really just does all the work for us. All right? So we're just gonna go ahead and click anywhere inside of here doesn't really matter where I'm going to go back into our insert menu and remember how we have table over here, right next to it is pivot table. It gives us a little bit of explanation what it is summarized data using a private table pivot tables, make it easy to arrange and summarize complicated data and drill down on details all right, so that's one way describing it, but I like to think that, like we can seek kind of a different sort of themes emerged based on you, moving your data around in a way that we can't ordinarily see, because if you look at our data right now, it's very two dimensional it's very flat, and we're going to make it a little less two dimensional let's go in and click on that and you see here just like with our table, this thing pops up and it's going to say, choose the data that you want to analyze, selected table or range is gonna have that right there now if you're trying to do this on the mack, it may actually just do it for you automatically actually, it'll just the the pivot table part we're about we're about to get to automatically just happens this part does not happen on the mac from what I remember all right and then where do you want to put it? And we're going to put it in a new worksheet because we're not going to really mess with this because our pivot table is goingto appear on another sheet altogether very good so now let's, go ahead and click okay, now this appears and if those of you who were trying it on the mac, you should probably already be here at this point. So now we're in pivot table land. We're in a place where we can now start to create our pivot table. Now a rule of thumb when you're working with pivot tables is toe always identify what we call your values first all right that's always going to be the thing that's going to help you like, well, what do I do? I don't work with the tables always identify what your numbers are when I say values I'm talking about numbers when excel says values it's talking about numbers so of all of these things you could probably guess of the four of these things which is going to be my values most like that's going to be sales it's not going to my product names like my quarters are going to be the region it's gonna be sales you'll notice here I have all four of my column headings here down below. I have four different quadrants and I could be working with my column labels, my role labels and my values, and I also have a way to be able to do a filter over the entire report. So pretty much this is like kind of getting your name right on a test and you get points for it. We know right away what we need to do first essentially just take sales and move it into here so I could just simply click and drag this right into sales. And then I got the beginnings of my pivot table, nothing too impressive at this point because all they've done is just added up every single number on there, which is kind of impressive. He asked me, but it's not really doing anything for us at this point, it's not really showing us any trends, it's not really looking at the data at the data in a very different way, we just have a big sum of everything. All right, which isn't really anything that's gonna help us at this point so we'd like to do is basically move some of these things down here and kind of experiment alright, we're basically experimenting to see, well, what's gonna happen? If so, I like to kind of you know, ask questions ahead of time to say like, well, what am I looking for? What kind of trends in my looking for I had a time but more than anything I like to see what things emerge without really knowing what I'm asking you just sort of like on a little safari and you're going to see what excel can do for you as you kind of spice to taste I like to think about it like that you see what happens I put a little oregano in here a little bit of salt a little bit of free raja something like that okay, I like that I like that you put that in there and then you know, you balance it out with whatever you want and you just keep experimenting so this is kind of an experimentation that you really have very little consequence it's war so what I'm going to do now is I'm gonna move product into my column label and you'll see automatically disappears just like that I'm gonna move quarter down into my role labels, okay? And you'll see automatically disappears ok, ok it's starting to look somewhat interesting I could see I actually have some empty data here and then maybe this is the way that I want to look at it however it is okay so guess what I can do I can actually switch these around because you know what? This might not be how I want to see everything because notice how I have all this data here but you know it's a little bit too wide because guess what right? My my column labels are all my products, so I don't I prefer not to have it so wide so I'd like to do is actually to switch these around and it's a simple as a dragon drop so what I'm gonna do is I'm going to drag this over here and then dragged this back over here I'm going just drag this over here is gonna look a little bit funky for a second I dragged this back over here and now I can actually start to see things that I couldn't see before I can see okay, great. So here's all my oh my products and everything and then here's each individual thing and it looks a little bit different than what we started from all right? I can now isolate things in a very different way so I go back to my raw data you'll see here how it looks a little bit different here, okay? And it doesn't really communicate to me as much as something like this does for each individual quarter I have this mount of information I have no information for this one and it's basically kind of turning it on its head a little bit now let's, go ahead and add on region to this all right let's go and add on region to my role abel's I'm gonna go ahead and drag this down and let's see what happens now things are about to get a little bit more complex we're adding on another value into here assured me another another variable in here so you just go ahead and click and drag and write that down in the bottom of product and you'll see now what's going on here now it's allowing me to look at it sort of a hierarchy see that I have each individual product with each individual region that corresponds to it, and now I can actually see what's going on for each thing here. All right? So I'm actually looking at a nice little higher q things now just I'm going to stop here for a second because right now we're looking at this and like, it may not mean anything to you because this is all data that has nothing to do with work that you're working on. But if you're working with data that's relevant to you, this will just scream out answers it'll be like, oh my god, like you'll just have these ahhh ah moments of trends and things that you didn't understand that was even existing and that you were able to isolate different things that are going on okay, so this might look a little bit weird my my job here today just be able to show you howto work with pivot tables you understanding this data that's probably not gonna happen because it's all just sort of for the exercises so I just want to put that out there I want you to understand how to work with the pivot tables and understanding kind of what we're what we're looking at here, okay, so again we now have because we have our product in our region down like this our product is sort of the parents essentially so we have adobe alan arkin all this stuff here and our region is essentially right underneath it, right? So we have these different levels here now let's just say that's not really how I want to look at it. I want to actually look at it more bye region than I do for each individual product and that's a simple as just dragging this up above this. Okay? Because I understand this is like the parent child relationship now region will become the parent. All they do is simply click and drag this up. You say I'm getting this kind of faint blue line a drag I let go and now notice how I have each individual region stacked together so this is like if I want to isolate and identify okay what's going on for central right now okay and then how each of my sense sales people doing how's it each individual product doing within that region I can isolate it now by how they're doing by quarter really amazing just a dragon a drop and it's doing so much powerful machination of number crunching I mean it's really fantastic and you see all this stuff here show me all kinds of information and it breaks down as it goes from each individual region and it goes up there and then did it all that kind of stuff here all right? And you'll also notice that it automatically gives me the sum for each one as well you can see that they're for each one okay now if you don't want to actually see all these things you can actually click on the minus sign for each of these things as well see that's why I can actually make that so they're collapsed or expanded you see that now I'm actually just looking at the totals so this is a nice way to basically save space save time people don't want to see all the minutiae of everything so it's really really nice to be able to do all that kind of stuff it just makes it just you know nice for people who were viewing it it's nice to be able to just kind of isolate different answers things like that okay so let's just jump over here to our tab now I don't notice here how I have another contextual tab. All right? We've talked about contextual tabs. We talked about working with tables contemptible. Taft. We talked about charts, contextual tabs. Next segment's coming up, we're gonna be talking about images and contextual taps. We talked about headers, contractual type. So always keep your eyes open for contextual tabs to able to get different options for you to work with. Okay, so there's, lots and lots of stuff that we can do here. All right, so you'll notice here I have a few different options as I go in here and I can actually do expand entire field, right? You see that how we did that for me or I can collapse entire field. All right, I made you do each individual plus and minus sign kind of put you through boot camp, so you can appreciate how awesome this is, ok? And you can go ahead, expand or you can collapse to see that and that's really, really nice. The fact that you couldn't do that saves a lot of time. And you just get right to the point when you want to. Ok. So again, that's under options under my pivot tables, contextual tab, and then I have expand, and I have collapse right there. Okay, so if I go ahead and expand it, you'll also notice that I'll have the ability we're not gonna do this right now. These numbers might not dictate it, but you can see how I can sort these different things as well. These actually are sorted to a certain degree already alphabetically, but I could start these the opposite way as well. So you have a lot of the same features as you do in normal data, as you do in a pivot table, it gives you all the options to be able to do that, right? I'm starting right here doing all these things, all right? In a minute, we're going to get to pivot charts. All right? So I just want to actually just introduce you number one just to this segment here inside the contextual tabs, right? Let's, play around a little bit more with the contractual tap let's see whatmore fun we can do, and just in terms of complexity, all right, so let's, now go over back to this part here where we have our column labels and let's take a look at some things that maybe we want to get a little more complex in terms of the questions that we're asking, so what I'm going to do now is I'm going to drag quarter over here. So we have actually all three of them in one row label let's just see what that's gonna look like. I click and drag and bring this down, and you'll notice. Now I have another kind of hierarchy here, which may not answer your questions for something like this, but it will possibly in the future, and I want you to just kind of start asking these more complex questions like what if? What if what if member of this kind of experimentation or spicing to taste and see what happens? I want you to know that you can do that, and they also know that I can actually move this around for each one, so I just dread quarter up to the top, you'll see now I get a very different looking pivot table. You see that just from clicking and dragon quarter upon top, I now have a very, very a different way of looking at my data that maybe mohr of what I'm looking for that's really the kind of thing where you want to experiment as much as possible, because you have very little consequences to be able to do so, and you might just find a lot of a lot of answers as a result of doing just a little bit of clicking and dragging, all right, so you can see we have all this stuff here for a little look at we can isolate this weekend identity and all these different things here ok what's that giving us all right now let's jump back over here to our pivot table tools and I want you to see this designed tab and this is great we already know about design don't we? We worked with our table designs already we work with styles before because guess what? This looks very boring okay, we want to make it look a little bit more fun a little bit easier to read so I can just go ahead and click on that and then let's choose another one she's another one okay and also notice I have banded row options I have all these other options here okay? You see that now it's looking a little bit more interesting little more colorful but possibly also a little bit easier to interact with. Okay let's try this one okay, there we go I like that one probably best of all the ones I've chosen and you see that now it's a lot easier to read because we have headings and are headings have color associated with them the whole thing has color to it so we have now taken a pivot table that was pretty extraordinary in terms of working with analyzing numbers moving them around but now they're a lot more accessible they're a lot more easy to read isolate the information we're looking for and we have everything right here bam bam bam and noticed how we have quarter one which is sort of like if we look at a parent child this is like the grand parents and notice how it's a different color grand parent and then parent and all the children then we get to the other grand parent which is the quarter all right that's going to be the color that quarter one wass okay, so what there's there's a method to the madness to be able to isolate what's what? Okay, you'll also notice that my pivot table options have disappeared all right understand that might happen to why's that happening because if you click away from the pivot table there's nothing there you clicked back on top of it all your options reappear again I've seen that just scare the lights out of people and where to go what did I do? What did I break my out of it already like no, you just clicked away from it see that now it's gone you click on it again and it comes right back okay so just understand that okay? And one other thing I want to show you is working with filtering okay? So filtering is very similar to filtering when you're filtering with justin you're in your normal data but we're now going to turn our pivot to ah mohr deeper level of pivoting where we're actually gonna pivot it even further in terms of what we want to see as far as what we currently have somewhat, we might want to only see quarter one quarter to when I want to see quarter one a quarter two and on lee these particular products or for this particular region. So right now we're looking at everything, but we've kind of shown a different way of how we're viewing it, but now we're going to see how what is going to look like, but only a few of these options because we're asking different questions now what's gonna happen if we on ly have certain products or certain regions, things like that? All right, so how do we do that? Very simple. So we come right on top of here and we'll see that we have each of these different options and you will notice that we have all these different had this top right up here we have this little filter drop down just right there, and you'll notice here it says select field when I click on this it's a select field and I have product quarter and region that's how I'm going to isolate these things here, so if I'm looking for certain regions, I'm gonna click on region, and then I could just simply choose which ones I do not want all right, so I'm just gonna I'm gonna unclip each of these individual wants and unclip central and east so then on ly north, south and west are going to show click okay? And now all I have a quarter one, two and three and four but I on ly have them for these three regions now I'll do that once again I was going to drill down just only the north, so we can kind of see it a little bit better so I'm gonna go down to region and I'm going to go out and on lee shoes north so we can see how powerful this is he now on ly north is showing see that really, really cool how you can just kind of look at your data and just turn it on its head and see it how you want to see it and what you want to see what you don't want to see and also show it in a nice presentable way but understand about filters, okay? You can filter on pretty much any one of these categories. All right? So just understand that you can filter on any one of these things is essentially the same process you can go from here, she's whatever field you want and then do everything you want here and just also notice that when you're in your filter you can do all the same type of filtering that we could do yesterday if you remember the filtering, we have our label filters, we have our value filters, all right? You can sort a dizzy you can do all these things here. So it's, essentially what we learned yesterday, you can apply even to pivot tables so it's really valuable information that we're just sort of building towards this as our sort of grand moment, but everything kind of feeds into everything else, right? When you learn one thing it's not just isolated to that one topic we can now use filtering and sorting and some of these other criteria on other things as complex as pippa tables. Okay, all right, so we'll leave it at that and we'll come back on the maxine and just a little bit, and we maybe do some some different examples now let's talk about I'm gonna go to my options now and we're going to talk about the pivot charts. All right? And this is the one thing that is does not exist on the max side of yet. I don't know why maybe we'll get to it in the next part, but they have not incorporate that onto the max side, alright, so I want to make this into a chart I want to make it so it's going to be shown blah blah blah you know how I want to show it and it's kind of take a look at it in a more visually communicative way right that's going to be our goal right remember we talked about charts yesterday it's all about visually communicating so we only just don't have just numbers we have the ability to preciously present our stuff in a more visually appealing way with colors and all that kind of stuff here all right? And you gonna see that charge going to be very similar to how we learned yesterday I'm gonna click on pivot sharpe and then all I do now is choose the chart type I want just like yesterday I click on that I say ok, you'll see here that comes in all right that's a little bit crazy because it's a little bit small so I'm going to do now just say mood chart just like I did yesterday so that some review you can watch the the videos from yesterday so all they do now is to go to move chart I'm going to say go to a new sheet just like that I'm gonna keep it a chart one and now I have this just right here okay? So like magic now I've taken my pivot table and now I'm looking at it as it's sharp so you can see here I have let me just kind of zoom in a little bit you can see I have all my difference products and everything right here. And you can see how it segmented out it's on ly for north and each individual quarter. Exactly how I presented it in my data. Okay, do you see that? Because that's how I had it, I had it as my grand parent, parent and child all right, but I also filtered it by each individual region. Can you see that there? And I have all my numbers here and everything. So when you're working with his data and you have your own data here, this is just gonna be me magic for you to be so powerful to be able to look at this and say, wow, I didn't know that this kind of theme could emerge now is so great about working with these charts and besides the fact that just being really, really awesome and the fact that, like you khun b visually communicate your stuff really cool, you have all the same options as you do on a normal chart. I have now pivot sharp tools, just like I had yesterday is chart tools. I don't have habit chart tools and you see here I have design, layout and format. All right, I'm not going to get into the because we did a lot of this stuff yesterday but you'll see how the options or exactly exactly the same with the exception of this analyze and you can kind of drill down a little bit deeper on these so I was going to go back to here and what we're going to do is come back to our chart you'll notice that this chart has a couple of different options that chart from yesterday did not and it's these little guys right here what's happening here is we actually have the ability to filter right here on the fly on the chart itself so we can actually change the chart that having to go back to the original data okay that's pretty amazing so if I decide I want to bring back another region guess what? I just simply I'm going to do that in slow motion and I'm gonna click on this see this region right there I click on that and you'll see now it's the same options but I'm in my chart this time really really cool so I was gonna bring up south sea what happens that look okay? Okay and now I'm looking at this information so I have north and south just like that I can actually compare them next to each other and I'm doing it right here on the fly right in the chart just phenomenal phenomenal stuff now it's you know it's getting a little too busy not really care too much about quarter three and four I can man just right here on the fly just click on quarter and I'm just gonna go ahead and just uncheck three and four click okay and now I'm looking at quarter one into and north and south within quarter one into has really really fantastic stuff all right, so and then also they make it very, very easy for you to do as well and of course I could go ahead and change my colors and do all that stuff if I want to okay, so that's the charts in a nutshell and that's pivot tables in a nutshell very tasty very wonderful nutshell so let's just go jump over the max side and we're gonna go into pivot tables and sub totals make that bigger all right? So I'm just gonna go ahead and and go to tables and, um where's my pivot tables um some data it's under data thank you in a little while. Ok, thank you. All right, good. So we're gonna go here and then I'm gonna go ahead and choose a table and then automatically does it as I told you okay? So when you're working with on the max side you don't it doesn't do the selection and then the confirmation it automatically just creates it for you and then spits it out and it actually puts in some things there for you okay, so again I know the little bit of fast so just select anywhere inside there and then choose data and then pivot table okay, so once again you can just click anywhere inside your table, go to data and then choose pivot table okay and then it's going to spit out this thing right here for you and then it tells me here what's actually happening so we got to get a little bit of an introduction we're working with here, so pivot tables summarized data and reveal patterns and trends ok, I think that's actually little nicer way to say it than we saw in the mac side, okay? It reveals patterns and trends that's what we're looking at here all right? So it tells you hear about the pivot table builder, which is the sky right there I'm gonna go ahead and close this and you'll see actually put some stuff in here automatically for me just kind of maybe taking a guess of what you want to do. So this isn't really anything that we've seen yet it's kind of mork come two dimensional stuff, but all we're going to do is simply drag and drop everything into here, right? We're going to come essentially do the same thing, so I'm gonna go ahead and move quarter over here, just like I did on the pc sound was going to drag this over here and then I have something that looks like this and notice how product has sort of left out of the mix. I'm just going to drag that into here. I'm going to move that down below. Okay? And now we've got something that looks like that. So I have my region as the grand parent I have. My quarter is the parent and all the children are the products themselves. Okay? All right. You see that there? And I can also move those things around. We're gonna have moved quarter up on top. Just drag it and then now see me region quarter is up on top. Come on, now. You gonna work with me here, let's? See that? Okay. Um okay, there. Are you not going it let's try it again. A little bit stubborn. I want to do it. So maybe you just goals, maybe just bring them down. Maybe that's the best thing to do, right? Rather bringing it up it's not doesn't really again. Different interfaces on each individual one can each individual platform. Okay, so I'm gonna have here is quarter region and product okay that's a nice way to look at it here and you'll see it automatically puts in the different style as well okay, so your style is going to be under this you now have a new contextual tab on the mac side which is pivot table all right, so the contextual taps appear but they appear in a little bit of a different way on the max I'd what we have you know your date or whatever and then as soon as you if I click outside of it notice how it disappears notice how everything disappears because I'm no longer inside of the pivot table when I clicked back on on top of it not only does this appear my pivot table builder but also this new contextual tap called have a table yes still tub up there called table and that's where I wass right, yeah, exactly it's a little bit confusing I mean, yeah, my memory was that it was still in table but it's not in table having under data and it spits out from outside of data. All right, so then you can see all your options right there and then now I've just changed everything and then within it okay, you can go ahead and make whatever changes you want and you can also expand and collapsed it's the same way, okay? So I'm gonna go ahead and collapse thes and so you could do all that exact same stuff here as you can on the pc no one interrupt the work but we're getting some compliments online and they're saying how impressed they are how well you're following along to david and being able to pay attention and do everything all the same time huh yeah good I wanted to thank you as well so thank you for that okay great okay alright excellent so and right and here we are right? Which is more styles and everything and just bend that's kind of very similar to what we did before and so we have all of our options right there. So very cool all right and you can see here it's a little bit hidden it's not a yes or no, madam necessary no menu with their check boxes you can see here we have our rohan column section here that's where are banded rows and columns options they're going to be on the mac side. All right, it's not like we had it on the tables it's a little bit different when we worked with tables we did see those check boxes won't work with pivot tables is going to be a drop down menu to be able to do this. Okay it's really just a question of space that's why they created the drop down menu here so I can actually choose banded rose and you can see how much my options are going to change now that I have banded roses my options here okay, so I could do something like that and it's going to do some craziness can do that okay? So we'll see how that works all right now yes, so there is no chart option within here, but we do have the option to work with our filters just the same way to remember our filters going make a little drop down here, so when I click on this, we're going to get very similar options where we have our select field and weaken ben choose okay, you know what? I only want to filter out by certain quarters so I click on the quarter from the drop down and I just say I only want quarter one into and I automatically updates it for me you see that there I can come down here and I can see what it's shown me just quarter one and quarter to just bam just like that. So what I would recommend so you don't feel like you're kind of left out if you're on the mac side is you could copy and paste this and you can extend this as a normal table and then just make a chart out of it. The only difference is it's not necessarily going tohave all those filters right on top of it, but you can still do it and the data will still be live so you can still do that so just because it's not there is an option to automatically convert it doesn't mean that you can't do it so there's a little bit of a work around for you basically treat the data like you normally would you can actually just create a chart right from this and then it should be able to just do it right from there so we're just not it's not necessarily a pivot table chart, but it's still going to be a chart nonetheless okay, so just began just because it's not there doesn't mean you can't you can't do it excel still gives you a bunch of work arounds to do that okay, just not right off the shelf inside the pivot table section. Okay, um all right, very good and you can see everything right there and you can move all the stuff around exactly how we do it on the pc side of things now in terms of our values and such there, maybe sometimes that you don't necessarily want the sum as an option. Okay? Because notice here we have some of sales if I now click on this little eye over here on the right hand side inside of my pivot tabled older, you're going to see that all these other functions appeared instead of doing some I can actually do account I could do an average I could do a maximum minimum right all these things here fine I'll just say all right I want to know account of how much is going on for each of these things here I could go ahead and say okay now how notice how everything changes then instead so I have twenty five inside quarter one and then here's the breakdown of each individual one for that particular quarter you see that and all I could do to change that back to something else let's look at an average click on that and then bam just like that everything changes so this is where it gets a little more complex in terms of mathematical formulas or you can actually do mathematical formulas built right into it and then have it immediately go into their based on what your values are and what type of formal you want to do and you could do all these other ones right here if you want okay, so I'm just going to the same thing on the pc side but a slight slight different way where you have this here and then we have a drop down and then we actually have to go to value field setting so doesn't actually automatically pop up if you go to value field settings and then we go to here and then we could go out to make those changes as we see fit so you just go in and say average for this one all right, go back to my she and we can see it all right there okay there's my average we couldn't see it actually in real time because we're still working on that sharks let me just go ahead and do it again what she was counts you can see it all right and everything changes right there okay, so lots and lots of stuff you can do here very quickly and a lot of complexity but hopefully with a lot of these for you okay? So that's pivot tables now you know, tables the audience is really really gonna be funny again there's always they're putting in a question and then they're going so right david ok come in excellent all right so now we're going to finish off with doing sub totals alright now sub totals are also really, really powerful and you're going to see how it works with subtitles so let's actually open right out before even given explanation for it I would actually open up to ourselves. Actually we're gonna work on the same file so we're going to going back to raw data within the same file and we're going to be working with now is a data set that needs to be designed to be working with sub tails first so what if some totals first and what I mean by that we want to make sure that way have certain criteria or I should say certain categories already sorted out and group together so for example, I'm asking a question about region in this case so I want to make it so all of my regions are grouped together so how central is all grouped together? East is grouped together in north group together and we know how to do that right? We could do that with sorting a very simple sort would allow that to happen so if you have all your data and it's all jumbled up just simply do a sort by the thing that you want to do it on so if you're looking at sub total of all your clients and you have a number of different clients but you have a lot of different projects working with them, you've wasted going to sort out all your clients bam right there and you have them all grouped together in a table and when you when you when you sort them out they're going to be together bam bam and all that it's going to group together and then what we're going to do is to renew a sub total so we're going to ask excel were going to say, hey excel, can you please get me a sub total whenever central ends or whatever the region changes to something else whenever east ends right whenever north ends and what's gonna happen like magic is going to be a new sub total roe will appear right here at road twenty six and new subtitle robles here, right here at forty six, etcetera, showing us what's going on here, right, it's going to save us a lot of time, a lot of headaches, potential mistakes and everything. So how do we do that? So very simply, we're just gonna go ahead and click anywhere on top of here inside of our data, and we're going to go to data and on the mac side, it should be the same thing. We're gonna click on data and on the far right hand side we have sub total and it says total several rows of related data together by automatically inserting sub totals for the selected cells. All right, so understand the key word here is related data. The related data in this case is going to be the region. Okay, remember I talked about that. They should all be grouped together and sorted independently from each other, so let's not go and click on sub total and you'll see here. It gives me a very nice kind of conversational way of doing some totals where it says, okay, let's, do a sub total at each change in blah, use this function and add the sub total to what, okay, so at each change in my region. Which is correct because that's what I'm concerned with is what's group together use what function let's go ahead and summit right now so I can go ahead and some all of these okay let's go in and some this together and then that's great because that's what we're looking for and then add the sub total toe what? And it already knows that the most logical thing is going to be a safe sales because that's the only thing you can really sum up from all of these things so not actually automatically put that in there and the rest of these are ok replace the current sub totals there are incurred some total so it's not gonna matter, but we're going to come back to this in a little bit and unchecked that and then you could also do page breaks between each group, which I think is really, really great because you may be one of a different page for each client for each project, whatever you're working on and automatically just blocking out for you run than having all jump teo jumbled up together okay, so all we're going to do now and we're gonna keep this all right all we do now let's click okay very simple at region do sum and then some up sales okay and then bam it could do for each one and then watch the magic happen look okay and then we'll notice that right there as promised in row twenty six it bam puts an essential sub total bam right there and add it all up there for me and then bam there's east another one and it keeps going and going and going and it's just amazing how he could do that very very simply okay let's do another one and this time we want to know the average so now we're going to do another type of sub total but a different type of function so again we're going to click anywhere inside here go to sub total and this time I'm going to keep it at exchanging region same thing I'm going to say instead of some I'm going to say average and now I'm going to say do not replace current sub totals that's essentially just a check box then I click okay very simply and guess what now I have to sub totals one for my summing and one for my average really like magic really really helpful very quick very slick all right so you'll also see how I have all these like crazy lines and minus signs and all this stuff here and also noticed how you have this one two three four this is going to allow us actually expand and collapse it's this sort of excels way of saying expanding collapse for these particular things here so I start off with number four you see, nothing happens because exactly where we are right now if I do three you'll see it expands it out by both the total on the average right there extreme it collapses it right there so I'm not actually seeing all the minutia beneath it and I say to it only does the totals have I do one it does just the grand total on the grand average all right, so you have lots and lots of options here is what you're looking for there's still here you can see that twenty seven, forty nine, seventy one right it's doing all that stuff however it's all kind of hidden in there so I go back before it all just expands all over again. Okay, so that's it I want to quickly jump over to the max side of things and essentially do the same thing so we come back over here, I'm gonna go back to my ron data, okay? And we're gonna go back to data here and then okay? So sub total is actually not in this section. We're going to find some total under data under this part here so some total does not exist in the same place some total on the mac side is under the data menu and then you'll actually see some total right here those you're on the mac just understand it's a little bit different and we see here we can still get to it. Finished a little bit difference. Let's cook on sub total. But here is where it's exactly the same as promised. You that's gonna be the main thing here is what we're working with the mac in the pc. We could do a lot of the same stuff how we get to it that's always going to be a little bit different now we can say same exact thing here at each change in region. Do us some and then add the sub total sales same exact thing here in the dialogue box exactly the same. And I just simply click okay, and then bam just like that it's right here. Okay. And it's right there, right, exactly exactly the same thing we've been cr expand and collapse options right there. Let's do one more let's go out and save data. We're going to say some totals and then this time we're going to keep it out region we're going to do and set a summing. We're gonna do average and I'm gonna uncheck this just like I did with on the pc side. I'm going unchecked that so it's not replacing it just gonna add to it click okay, and then there it is just like that and, of course, I can work with all these. One, two, three, four everything right there. Now, at some point, you might want to say, you know what? I'm done with the sub total, and you don't want it anymore. Just understand. You could always go back to that same dialog box and just say, remove all and that's going to come back to where you started from again so that sub totals and that's going to be something that could be super super power for for you and just save you a ton of time. When you're working with a lot of the same things you want to basically know what's going on for that particular one, that particular one, but still keep them in the same data set. So that's, that's, really nice.