5.4 Format PivotTables - Video Tutorials & Practice Problems

<v Instructor>In this sub lesson,</v> we're gonna talk about a number of things we can do to format our pivot tables. Let's begin. Okay, so what we're going to do first is we are going to go into the values area of the pivot table that we have open and we can see that we have a column here called sum of order quantity., and what we wanna do here is we are going to click the box next to it here like so, and we're gonna go into the value Field Settings so that we end up with the value settings dialogues box like so. What we're going to do now is we wanna actually first go and give the field a good custom name, something better than it was given by default. So what we're gonna do here is we're gonna say total order quantity, like so and we also wanna take a look at the source name is, so we can see the source name out of the pivot fields list is order quantity, so we know where that is actually coming from. So that is the name that we're going to give as a custom field, so we're going to then go and take a look at the different ways that we can summarize our values by. So in this case the default is sum which is what we wanted to choose and we can notice here just by accidentally clicking this that I went and lost the custom name that I gave to my particular report so I'm just gonna go back over and take the value here and put that back into the field here like it was a moment ago. So I can go through and gonna leave those summarize fields alone momentarily, but what I wanna do here is I'm gonna go in and now give my number a format. So, my total order quantity, if I click on number format here I can go and choose a number of different formats. So, I'm gonna go from general, I'm gonna change this to number, I'm gonna knock my decimal points down to two, I'm gonna ask it to use the thousand separator and I want my negative numbers to show up with a negative front and as a red number. So, once I've got all that I can click okay and that is gonna format my number before we go ahead and display it. And what we can also do here is take a look at a couple other ways to show the values based on other values in calculations we've chosen the table, we're gonna do that next year. So we're gonna come back to this tab in a moment, we're going to right now, save this total order quantity custom name and the number formating we did like so. So we're gon' do that, we're gon' notice the label up here changed to total order quantity as did it down in the values area. Next thing we're gonna do is we going to go and re bring the order quantity into the actual buyers pane. So I'm gonna go and drag order quantity in here again, like so, soon it's gonna go like this and put it down in the values ares, like so and now I can see once again by default I've got, but we wanna do here is we wanna go and do some relative calculations instead, so I'm going to click on the sum of our quantity that just got dropped into the values field and say value field setting, and this time what I'm gonna do is I'm gonna give this a custom name of percent of grand total, like so, and what I'm then gonna do is I'm going to go over to the show values as tab and I'm gonna choose a calculation here and say percent of grand total. All right, so I've got my field chosen properly there now and we can go ahead and click OK and we can now see that we have a total order quantity and the percentage of the grand total. And if we now just scroll through each one of these grand totals, we can see that this number right here, this 3.83% is a certain percentage of this grand total down below, so the grand total for all. And we can go ahead and take a look at the couple of different levels, we can look at the product category level or we can look at it at the sales territory level as well. So we can then work our way back up to the top of the pivot table here and see now we've got our total order quantity which we did first and then now we have our percentage of grand total. Okay, so next let's take a look at row and column Field Settings. So we took a look at some fields settings we could do at the values area, but now what about the rows and columns? So I'm gonna get my mouse back into my pivot table and now let's go ahead and take a look at the rows over here in the pivot table area itself. So what I'm gonna do here is go down to the sales territory level, I'm gonna click on this right here and I'm gonna once again say Field Settings. And a couple of things that we can do here first is let's go ahead and rename the custom name to sales space territory so it looks a little nicer on our reporting, and then what we're going to do is we are going to take a look at some of the other configuration settings that we can do here as well. We can see that in the subtotals and filters section that we can allow for automatic subtotals, we can say we want none, so I'm just gonna choose none and click OK, so we can see the subtotals now disappear. Let's go ahead and put those back, so we'll come back over here, go to the Field Settings and say automatic, or what we're gonna do next year is we could then go in and say custom. So I'm gonna go ahead and choose custom and what I'm gonna do here is actually show you that have the ability to actually add in two different subtotals. So I'm gonna go in here and choose some, and with the Control key down, I'm gonna click on average. So I'm gonna pick two here now and then go ahead and click OK. So we can see now that we have the, some of the order quantity here and then right down below, we have the average on each one of the actual orders itself. So we had to actually put two summarizations on there which is quite handy. So just some other configurations of interest here in the sales territory. So I'm just gonna go ahead and open this one back up here, click on Field Settings and over in the Layout and Print, I can change the layout of the actual pivot table itself, and recall we did that back in one of the previous sub lessons, we're gonna leave this in tabular form, we can ask to repeat the item labels if we want, so let's go ahead and show what that looks like and if we had any data in here or categories that had no data we could say, let's show the items with no data. And we're gonna do a demo of that a little later on here in this particular section, just in a moment or two here. So I'm gonna click leave the repeat item labels on like so, we're gonna see the item labels repeat and let's just go ahead and turn that back off here. So we can back in the Field Settings, go to Layout and Print and click on the repeat item label so we don't get those repeating again. Let's go ahead and go like that and now we have our pivot table back to where we had it momentarily a moment ago. So lastly, what we're gonna do here in this formatting of pivot tables, we're gonna go to the product category and down the rows session and I click on this and say Field Settings again and let's go ahead and put a space between product and category just to make this look a little nicer from a reporting perspective, and we're gonna do a couple of things to the data in here to show you some of the other configuration features that we can do as well. So I'm just gonna click OK, so now we have product space category here on the pivot table itself but what I'm gonna do now is I'm gonna go over my sales data here, and let's just go down the bottom that fifth table first actually, we can see that we have all of the entries in here for the sales territories and we stopped at United Kingdom. So what I'm gonna do here is I'm gonna go to the sales data and I'm gonna go in here and I'm gonna change this very first row and just change it to a different country now that doesn't exist. So I'm gonna call this Bulgaria, like so, go ahead and enter that in and I'm gonna go back over my pivot table now and what I'm gonna do here is I'm gonna go to the Data tab and say refresh. I'm gonna go ahead and refresh this pivot table and now we can see that we get a entry in here for Bulgaria bikes, and it looks like it is pulling in a sum of one and an average of one year. So if I go in and take a look at the sales data for that, we'd see that that actually validates to what we had on the screen there, so let's go back over to my pivot table here really quick. And what we can see here is we only have bikes showing up and we can see these other sales territories have multiple different product categories. So we have accessories, bikes, clothing, components, what if we wanted to actually show all of those here? So we can go ahead and do that. So, I'm gonna go over to the product category and go to Field Settings and go over to Layout and Print and now what I can do is say, show items with no data. So I click on that, go OK, and now we're gonna notice that we have accessories, clothing, and components showing up with no entries in here. So we've successfully now got all the categories showing u[ down in Bulgaria, but now what about these cells that have no entries in here? What we can do is go to the pivot table level, so what I can do is, with the mouse inside of the pivot table itself, I get the pivot table analyze tab up top here, I can go over to options and click on options here and what we're gonna do here is in the Layout and Format tab is there's a selection box here a check boxes says for empty rows show and it says nothing. So if I want, I can actually put a zero in there or whatever value I decide to put, and I can go like that, and now I get those values showing up by default. So those are just a couple of different things that we can do to this data here to allow for some additional formatting here, but I'm gonna do is go back where my sales data here and I'm gonna change this back to Germany, like so and what I'm gonna do is go back over my pivot table and I'm gonna go to data and say refresh again, and now we'll notice that Bulgaria and all of its entries disappear from the pivot table itself. Okay, so that is a few of the different things you can do to format your pivot table within the values area and the rows area and you can do similar things in the columns areas as well, but essentially just a repetition of what we did back down in the rows area. So with that, that brings the end to this particular sub lesson.