5.5 Use PivotTable commands - Video Tutorials & Practice Problems

<v Instructor>In this sub-lesson,</v> we're simply gonna review some PivotTable commands that may be of interest to you. Right, so let's begin by going into the PivotTable cell, so making sure the PivotTable cursor is focused inside the PivotTable. We can see the PivotTable Analyze tab, so I've already got that highlighted here, and let's go over some of the different groups of functions that we have available to us here. So, let's start first in Actions group. If we want, we can go in and take a look first at the Select one, so we'll go in the middle here, and we can go in and enable ourselves to go in and select the entire PivotTable, if we want. So we can go ahead and click on the entire PivotTable. We're gonna notice now that the whole thing has been highlighted, and this is pretty handy if you have a very large PivotTable, and you wanna go ahead and move it. So, now that we have got the PivotTable selected here, so let's just go back over here and choose the Select again, so it choose the entire PivotTable. If I wanted to, I could click on this Move here to take this PivotTable and move it to, you know, perhaps a new sheet or a new location. Let's just leave it on the exact same sheet, but maybe we just wanna move it over to a different location, maybe we're just gonna stick this over here in B, let's just put it over here in B5, something like that, click that, and now we're gonna notice, the PivotTable has moved. So, we've shown you two of the functions there in the actual Actions group, so I'll click back there, showed you how to go ahead and select the entire PivotTable itself and then move the table. Over here in the Clear section, we have a couple of different things we can do, as we've got a Clear All and the Clear filters. So, there's a couple of different reasons why we have these different selections here is that Clear All resets your PivotTable in any field you brought into the filters, rows, columns or values, and they get completely removed. It doesn't delete your data or the link to the data, just simply return this to a blank canvas. And if we have a PivotChart associated with this PivotTable, Clear All also removes the related PivotChart fields. So, if I want, I could go ahead and click Clear All, and we'll notice that I get myself back down to a blank canvas, like so. What we can also show you here is, this has been done through a couple of sub-lessons, but we're showing it again here, now that we have a live example is, the PivotTable, I have the mouse inside the PivotTable here, but the fields list isn't shown. So you can go back up to the Show group here and click on Fields List, and that will bring the fields list back into focus as well if I wanted to go ahead and play around with it. So, what I'm gonna do here is I'm just gonna reset the PivotTable back by clicking the Undo button here, and let's go and actually set a couple of filters on this PivotTable. So I'm just gonna go and just randomly uncheck Sales Person, like so, and in the rows label, I'm just gonna choose Australia and Canada, something along those lines there. So now, my PivotTable looks like that. And if I wanted to wholesale and clear the filters on this PivotTable, right now, it won't be that hard to go through each one of these drop-down boxes here, but what if we had a lot of different filters or maybe even these two seem like a bit of a pain to go and get cleared out. So, what I can do is, with the PivotTable focused again, I can go to Clear and say Clear Filters, and that will get rid of the filters on my actually PivotTable. All right, so moving over to the left, so we've covered everything in the Actions group, what about the Data group here? So, we can go into the PivotTable itself, and we can change the data source for the PivotTable, so if I click on Change Data Source, we're gonna see what the source is. We can see, right now, it's just using Table1, which is over on my Sales Data tab, and you'll leave it alone for now 'cause I don't have another data source to change it to, but if I wanted to give it a different source, I could go ahead and change it right here. So, go ahead and click Cancel there, go back up to my PivotTable Analyze, and if I want to refresh this PivotTable, either the single one that I'm highlighted on right now, I can click Refresh, or if I had multiple PivotTables, I could click Refresh All on all those as well. So, to show you what that'll do here, and we've done this back in some previous sub-lessons, but let's just, once again, show you the example. I'm gonna go down to the bottom of the table. I'm gonna go over to my sales data here, and I'm just gonna change Germany here to Bulgaria, get it spelled right, perfect, so go like that. And now, I've changed the data at source to a different value. So I'm gonna go back to my PivotTable here, and if I go to my PivotTable Analyze and say Refresh, and click, choose Refresher, now we're gonna see the entry down comes from that data table in the form of Bulgaria, which we just modified before. So I'm gonna go back for my Sales tab here and just change this back to Germany, like so, and then, go back for my PivotTable and refresh on my data and click Refresh. And now, we'll notice Bulgaria disappears from my PivotTable. Right, so, going a little more to the left here, let's take a look at the PivotTable group right here. So, couple of things we can do in here is, we can change the name of the PivotTable, so I'm not really a big fan of this one here, maybe I wanna give this PivotTable a proper name, and I'm gonna say Pivot, let's see, what is this OrderQuantity, OrderQuantityBySalesTerritory or something like that, let's just go ahead and make it nice and simple. Big thing is making sure we give it a name that's meaningful, and we gonna come back to things later on and reference it, especially when we start getting into using slicers and timelines, so I wanna give it a good name. We can also, under PivotTables group, click on this Options tab here and take a look at a couple other things we can do with the PivotTable itself. Now, as we go through this Options dialog box here, there's a lot of things that we've already seen in other locations when configuring the PivotTable. So, you know, going through and taking a look at the totals and filters, so I'm gonna turn off the Show the grand total for the rows, Show the grand totals for the columns, this one's already on, if I wanted to turn it off, I could do it here, and keep in mind, we could have done this, a number of these things out on the Design tab under the format of the actual report itself. So, when we have the ability to play around with this, we can allow for multiple filters per field under Layout and Formatting, which we're just on. We could go ahead and, if there's any empty cells in our PivotTable, so we can see, it looks like the components for France right here has empty value here, so I could go in and put a zero here at the PivotTable, I will click OK, and now, we'll see that zeros are getting placed wherever there was no value, so that's a handy one as well, so we can go back up over here. So we've talked about that when we can go ahead in Printing, so there's some things we can do for allowing us to expand and collapse the buttons when this is displayed under PivotTable for Printing. Under the Data tab here, we can ask that the data's refreshed when the file's opened. That's usually one of the biggest ones there. So we have some All Text, we could put on the PivotTable here, which is gonna help with accessibility later on once we move into some subsequent sub-lessons. And finally, under the Display tab, so let's click over here. We can allow us to show the Expand/Collapse button, so I can click on this, to allow these plus or minus boxes to disappear, so let's just go ahead and do that, click there, go down here, we can notice that they disappear. We could also change it up here in the Show group under PivotTable Analyze. So once again, there's a lot of things that we can do in multiple different places. We can turn the contextual tool tips off, if we wanna show the values row, and over here in the fields list, what we can do is we can actually have it sort in the data source order or sort in alphabetical order, which once again, we could have actually changed over here in the PivotTable's list as well. So let's just go ahead and change that one, it's the last thing we're gonna do here in this particular sub-lesson. So let's go ahead and say, in our fields list, we want that to sort from A to Z, click like that, and now, we'll notice the fields list has modified itself to make it a little bit easier to find the fields 'cause now, they're sorted in alphabetical order, not what they were back at the source. Okay, so with that, that brings us to the end of this particular sub-lesson.