5.1 Create and understand basic PivotTables - Video Tutorials & Practice Problems

<v Instructor>As mentioned in the lesson overview,</v> the first step in a lot of analytic endeavors is to first get your data organized into an Excel table, and take advantage of some of the features and functions available in that. As we can see in this example here, we have a very basic Excel table that is listing off some orders that we have for our particular business. We can see over on the far right hand side, we're taking advantage of some conditional formatting to show the line totals. We can see at the very bottom of the table, we have a total's row set up to show us the total for all of the line totals that are available. We can also then go through and sort our data sets, so if I wanted to go and sort by the largest to smallest line totals like so, I could go ahead and do that. If I wanted to go ahead and sort my data set or not, sort of to filter to only show maybe the sales that are in Australia, I can go ahead and do that. And everything goes ahead and reacts to what we had just chosen. So this is really nice for doing some basic analytics, but what happens now if we actually wanna start looking at much more deeper levels of aggregates? So, that is where the pivot table is gonna come in, to help us aggregate this information a little bit better. So on this particular sheet, I have an Excel table that is very large, it has a number of rows of data in it. If I wanna go down, I can take a look at, in particular, how many rows of data are in the actual file itself. So I can see that I have 46,647 rows in the actual file itself, so an awful lot of data. I wouldn't necessarily wanna be using the same techniques I showed you previously here to go ahead and make some sense of this data here. This is where pivot tables are going to help out considerably in terms of digesting this information and aggregating things better, and allowing us to maybe drill into lower levels of detail. So scenario number one is your manager comes to you and they want a summary of sales by product category. So what we would wanna do here first is let's go ahead and actually apply some of the naming conventions that we talked about a little bit earlier in the class here. And this sheet here is called sheet number one, let's just double click this and we'll call this Data. So we can see that our data is on this particular tab. If I went my most highlighted in the actual table itself, I can see my table design tab pops up. And this table is called table one, let's actually give that a better name, let's call this tblSalesData like so. Go ahead and hit enter. And now what we wanna do is go ahead and create pivot table out of this. Now, there are a lot of ways to go about creating this pivot table, so we'll just show you this one as a first method. So what we wanna do here is go ahead and click on the insert tab. And under the tables group, we can see here that we have this pivot table. There is the ability to recommend different pivot tables, but myself, I always find it easier just to go ahead and choose the pivot table like so, and then build it how I see fit. So I'm gonna go ahead and just click pivot table. And we can see that it has picked up on the fact that my mouse was inside of this table of data, and it asked me, do you want the table or range called tblSalesData? And this is one of the reasons why we really like to name our objects properly in this side of Excel so that we can more easily reference them and understand what we're picking up on when we're doing some subsequent work here. So what we'll do now is we're just going to click OK. After we've actually made a selection here, we're going to put this into a brand new worksheet, and this being the pivot table, so click OK. And now we end up with this brand new sheet of data. So what we're gonna call this is we're going to rename sheet one, we're just gonna call this shtPivotData like so. Give that a proper name. And the second thing we're gonna do is we're actually gonna go and name this pivot table as well. So what we'll see here is if I put my cursor inside of this actual pivot table canvas right here, and go to the pivot table analyze tab, I'll see that this pivot table is actually called PivotTable2. Let's actually go ahead and name this to pvt, short of pivot, and call this SalesData. So let's actually give this a proper name. So I go ahead and do that, hit enter. We're gonna see now that right here, right in here in the screen, that has now been renamed to pvtSalesData as well. Now, I'm gonna go ahead and turn my gridlines off as well 'cause I'm not a big fan of working with my gridlines, like so. And now, let's start building that pivot table that our manager had asked us for. So what we're gonna do first is we are going to go over to our pivot table fields and scroll down to very bottom here, and find the line total. And we're just gonna click on line total and drag that into the values bucket right here. And what we're gonna see right now is we end up with the sum of line total. Next, what we're gonna do here is we're gonna grab our product category from the fields list, so product category here. And we're gonna drag it into the rows like so. Now alternately, I could if I wanted to click the checkbox right next to product category like so, and it will also put the data in the rows like so. Okay, so this meets the requirements that our manager has brought to us. So you take it to the manager and say, "That's fantastic. I really liked this, but I wanna see that in a little different form." And that form happens to be, "Now, I wanna see this data over time." So what we're gonna do here now is we are going to take the new requirement from our manager to make this data set show up over time, and go ahead into our fields list here. And we're gonna do now is we're gonna grab our order dates. So I'm gonna grab order date and drag it into the columns bucket here like so. And now we're gonna see that we have our product categories by the different years with the line totals in here. So now, we're further aggregating our data down to different levels so that we can traverse through the different levels to see what are all the different months within 2019 and quarters, and same thing in 2020, and actually drill down to all these different areas, and better slice and dice our data than we could have in that Excel table had in excess of 46,000 rows. Now, you may notice that this pivot table isn't particularly visually appealing. We could use to format it a little bit nicer, but we're going to talk about that in subsequent lessons. But before we do that, let's actually just do a quick tour of the different regions of our pivot table. So what we can do is if our cursor is inside of the actual pivot table itself, we're gonna, once again, see we have this pivot table analyze tab, and over on the far right, under this show group, we have a number of different buttons that we can go ahead and click. So these different buttons here are this, if I click the field is we'll notice down below here that this pivot table fields panes gonna disappear, so they click this and give it a sec here, it disappears. And if I click it again, we'll notice it reappears. If I click this plus, minus buttons, you'll notice over here in the pivot table, these plus and minus symbols will disappear, so now the data is not drillable. Click on that. And then if I click on this field headers, you're gonna notice that these two headers here will actually disappear, so the column labels and row labels would disappear as well like so. And there we go. So there's a few different things we can do to our pivot table from a visual perspective to at least show some different things. But, let's actually walk through the different parts of the actual pivot table itself. So over here is the actual pivot table. So right in here, if we go ahead and choose that, that is our pivot table. If we want, we can go ahead and continue to add to our pivot table, and we do that by going into the fields list, which is right here, so we can see all the different fields that are part of this particular pivot table. And the ones that are currently in use are in bold here, and have a check mark next to them. If I had a very large list of fields in my table, I could actually go ahead and use this search bar to go ahead and find the columns I want. So if I type in du, I would end up with a column here called due date, and that allow us that we, to go ahead and find things a lot quicker. Down below, as we've already started interacting with, we have our columns area. So here's where we can put all the different fields that we want on the columns, all the different fields we want on our rows, the fields we wanna put on the filters that are particular to this particular pivot table, or I can actually go ahead and put things down here in the values area. And these are typical things you wanna sum, average of min, max or counts, so these are generally our numeric values. You also, down below here, have the ability to defer the layout of the pivot table, which is really nice if you have a very large data set and you wanna move things into the rows and columns, and values, maybe even filters, but you don't want the pivot table to continuously refresh. It can tend to slow things down a little bit if you have a really have large data set. So you can click on that defer layout and then just click this button over here called update, which actually allows you to update only when you see fit. I'd say for the most part I have that box unchecked 'cause the data sets I'm working with on the machines I typically work with aren't usually that big of deal to let just refresh as we're actually going ahead and building things up. And the last thing we wanted to point out. So in addition to when you have the pivot table highlighted, having this pivot table analyze tab, which we've already started interacting with some of the functions in there, we also have the design tab. So we are going to spend some subsequent some lessons going through each one of these different tabs and their various functions and features to show you different things you can do with your pivot table.