5.8 Create PivotCharts - Video Tutorials & Practice Problems

<v Instructor>PivotCharts provide interactive,</v> graphical representations of the data and their source PivotTables. When you create a PivotChart, the PivotChart filter paint appears. You can use this field to paint and sort and filter the PivotCharts underlying data. Changes that you make to the layout and data in the associate PivotTable, are immediately reflected in the associate PivotChart and vice versa. PivotCharts behave much like standard charts do. You can change the chart type, titles, legend placement, data labels, chart location, and so on. However, there are some differences. Unlike a standard chart, you cannot switch the row or column orientation of a PivotChart by using the select data source dialog box. Instead you can pivot the row and column labels of the associated PivotTable, to achieve the same effect. From a source data perspective, standard charts are linked directly to worksheet cells. While PivotCharts are based on the associated PivotTables data source. Unlike a standard chart, you cannot change the chart data range in a PivotCharts select source dialog box. Most formatting, including chart elements that you add and style are preserved when you refresh your PivotChart. However, trend lines, data labels, error bars, and other changes at data sets are not preserved. Standard charts do not lose this from any ones they're applied. And finally, some chart types do not work with PivotTables. The following chart groupings cannot use the PivotTable directly as a source. There is a scatter chart, map, stock, funnel, tree map, sunburst, histogram, box and whisker and waterfall. So let's go ahead and create ourselves a PivotChart based on this PivotTable we have over here in our Excel spreadsheet. So what we're going to do, is we're gonna click on any cell within the PivotTable, so I'm just gonna go over here to the PivotTable and click on a cell. We see that our PivotTable's field pin reappears. And what we're gonna do now is we're going to go up to the PivotTable, analyze menu. We're going to go here. We're going to find the tools group, and we're gonna click on PivotChart. So what we're going to do here in the insert chart dialogue box, is we're gonna select the column chart group on left hand side, which we can see is already chosen by default. And we're going to choose the cluster column chart. So we can if we want to run our most overtop of a sample of what each one of these would look like, if we wanted to see what the data we have in that PivotTable would look like using these chart types, so you can just roll over each one of these things, and it'll go back to the cluster column chart, like I have right here, and that is what we want, so I could hover a top of it to see a larger preview of what that data looks like. And if I'm satisfied if that's what I want, I'm just gonna go ahead and click okay. Alright, so now I get the PivotChart appearing in my workbook. Okay, so what we're gonna do next here is we're just gonna reposition the chart here, so I'm just gonna grab it and move it over here Like so, and make it a little bit bigger like this, so we're just gonna make it take up a little bit more of the screen, so it's a little bit more visible and easier to see. And what we're first going to do here is go over to the PivotChart, and down at the very bottom of here, we're gonna click on the sales territory right here, like so, and what I'm going to do, is go in and change the sorting options of this particular chart. Because we can see right now, that it is sorted in alphabetical order by the sales territory name like so. But what if we want this to sort a little different? So I'm just gonna click on this, and I'm going to go to more sort options right here. And what I'm gonna do is I'm gonna go over to descending, and I wanna do this in descending order by total required like so, So I go ahead and click okay, and we'll now see that the PivotChart has resorted itself. And we'll also take a look at the PivotTable over here, it has reordered itself as well. Keeping in mind that this PivotTable right here, is associated with this PivotChart over here. So any changes made in one are reflecting the other and vice versa. So further to that, what I'm gonna do here is I'm gonna go over to my PivotChart, I'm gonna click on my sales territory and we're going to take advantage of some of the skills we learned back in some of the previous sub-lessons here. And I'm going to filter my data set. So what I'm gonna do here, is I'm gonna go down to the values filter, I'm gonna go down to top 10. And in this case here, I wanna choose the top five items by my total order quantity. So let's go ahead and do that and click okay, and now we can see that here are our top five. And lastly, what we're gonna do here, is we're going to take this PivotChart now that we put it on this particular worksheet, and showed you the interrelationships between the PivotTable and the PivotChart. We're gonna take those PivotCharts and put it onto its own tab, and specifically, we're gonna move to this tab over here called dashboard. So what I'm gonna do here is I'm gonna click on the PivotChart itself, so that it has focus on my canvas. And what I'm gonna do is I'm going to go up to the very top here, I'm gonna go over to the PivotChart analyze, and what I can to do is go to the actions area here and click on move chart. And what I going do is I'm gonna choose in the Move chart dialogue box here, I'm just going to click on this and say dashboard, 'cause I already have that tab already set up, go ahead and go like that. And now I have this PivotChart moved over here. So I'm just gonna and move it roughly like so something like that, maybe do a little bit of resizing, 'cause I am gonna be putting some more visuals here in the next little bit, so I wanna make sure we've got enough real estate for the dashboard, we're gonna build here over the next few steps. So now that we have that particular PivotChart set, let's go and build ourselves in other visual. So what I'm gonna do this time is I'm gonna go backward on to my PivotTable tab, Like so, and I'm inside of this particular PivotTable right here and if I take a look at the name of it and go from the PivotTable analyze, we're gonna see that it is called the PivotTable PVT total quantity by sales territory if you read through this in the table name. And what I wanna do is I wanna make a copy of this PivotTable so that I can build another PivotChart on top of the newly copied PivotTable, which we're going to do right now. So what I'm gonna do here is with my cell inside of the PivotTable, I am going to go to select over here under the PivotTable analyze actions group, go select, and say entire PivotTable, like so, and then what I'm going to do here is I'm gonna go ahead and copy the PivotTable. So I'm just gonna use Control + C. And what I wanna do here now is I'm just going to go over to cell E2 just move far enough away here, and I'm gonna paste that. So now I have a brand new PivotTable in my worksheet here, and I'm gonna make sure I go ahead and give that a proper name so I'm just gonna go in here and I'm gonna give it the name of PVT total quantity by order date. So I'll highlight back over this cell over here, and we'll see here that it gave it the name PivotTable one by default, I don't like that, as we talked about some of our naming convention a little bit earlier, and just like that, we now have a second PivotTable, which has a copy of the first one, both with different names. And now I'm gonna take this particular new PivotTable and build a line chart of it So, first thing I'm gonna do is I wanna reconfigure this particular PivotTable. So what I'm going to do here is I want to remove my sales territories from the rows, so I'm just gonna go ahead and click this and to say removed field, and the next thing I wanna do here is I'm gonna add my order date over here, down to the rows like, so, and what I wanna do is move the order or do next year we move the order type and order status from the filter area, so I'm just gonna go ahead and say, remove field and remove that one as well. So try to do things a few different ways here, as we go through these particular exercises. And now, next what we're gonna do here is we are going to right click on 2019, so go over here. We'll right click on 2019 in this particular column, and what I'm gonna to do here is go down to expand/collapse. And what I wanna do here is expand this entire field so I'm gonna click on this right here, and expand this out so I see 2019 and then all the quarters like so. And now what I wanna do here is I'm going to right click on Quarter 1 here, in the Quarters column, and what am gonna do is I'm gonna go over and say Expand/Collapse as well, and I'm gonna say, expand entire field once again, to get us down into all the individual months. So we can see that we now have years, quarters, months and the total quantities by those particular months in the PivotTable. So with this data now being set in the PivotTable, we can go ahead and build ourselves a pivotChart on top of it. So once again, I'm just gonna make sure the PivotTable has focus. I'm going to go up into the PivotTable analyze tab, which is already selected. And what I'm gonna do here is go over to my tools group, click on PivotChart. And we can see that we have a cluster column chart presented to us here, but that's not what we want. We are going to go over and choose on the line areas. We'll choose the line chart over in here, And then what we wanna do is let's go over and find the line with markers. So go ahead and take a look at all these here line with markers. And that just puts individual points on each one of the data points right here. that we can see just hover over top of that visual and it shows you a little larger preview of it. And I like what I see and I was gonna click okay. And it puts the PivotChart right here. We already know that this PivotChart is related to the PivotTable we just worked on. So let's go ahead and get this PivotTable move over to our dashboards tab. So I'm just gonna click on the pivotal analyzed actions group move chart. And once again, go over to the dashboard, click okay, and now I have my second chart sitting over here. So, I'm just gonna make this a size roughly... This size of the very first visual we had on there, I'm not gonna get too finicky, about the straightened out ' cause we can spend a lot of time making sure everything's pixel perfect. But let's start with this as our visual and go on from there. Okay, so one thing that we do wanna go about doing is let's go ahead and rename that PivotChart. So I'm just going to go over into the PivotChart itself, so once again, making sure it has focus at some point, there we go, so that's the focus I wanna give it. And what I'm gonna do is go over to my chart name under PivotTable analyze where it says chart 2. I'm gonna go ahead and put that name in there like that. Now, I've got the proper name for that particular PivotChart set up just like that. So, we are done with that PivotChart for now, so what do is go back over to our PivotTables. So I'm just gonna go over to PivotTable here , and what I can do is do is take a copy of this second PivotTable that we just built up. And I'm going to go over to the PivotTable analyze, go select the entire PivotTable, do my copy, so control + C , just move over a little bit in my spreadsheet. Go ahead and drop it in like that. And next, what we're gonna do is make sure this PivotTable has proper names I'm just gonna go and rename it. So make sure that we're focused within the PivotTable itself, under a PivotTable analyze, go and give it the name that we want like so. Alright, so now what we're gonna do is click back inside of this PivotTable, and our fields list seems to have disappeared on us for some reason. And what we gonna do here now is go ahead and get rid of the years, quarters and order date from rows. So I'm just gonna go ahead and remove these, now we're down to our Total Order Quantity. And what we're gonna do is put our product categories so it's gonna go and find product category here in the fields list, and that we're going to put on the rows. And, next thing we're gonna do is go and drop the, what are we going to do next year? We're gonna take these sales, territories and put it into our filters, just like that. So we now have this PivotTable set the way we want, and we're going to click our mouse back in it, go into a PivotTable analyze, go over to the tools group PivotChart, and now what we're gonna do is go over to our insert chart, we're gonna select the pie chart right here. We will go ahead and click okay. That is now sitting in our PivotTable or tab, and I'm just gonna go ahead and get this moved over into the dashboard sheet. So I'm just gonna once again go to PivotChart analyze move chart, dashboard like so, and get it brought over here. And I'm just gonna take this chart and move it down over here like so, so we can see it right on the screen, right about there. So the last thing we're going to do here with this particular PivotChart, is making sure that it has a proper name, so, I'm just gonna go up here to the name property with proper nameing like so, and next what we're gonna do is just show you some of the the downside of the pie chart itself. Now, the way this pie chart is set up, it's really difficult to actually see, which one of these product categories is actually larger, which one's the second largest, third and fourth, because it's not immediately apparent, to the first glance that you make on this chart. In fact, you'd have to really look at this hard to figure out which ones are the biggest, second biggest, third, and so on, and that goes with most pie charts. So, what we're gonna do here is move this particular charts to a different style. So what I'm going to do here is I'm just going to right click on the chart itself, and I'm going to say change chart type. And what I'm gonna do here is I'm gonna actually change this into a bar chart. So I'm just gonna go like so, and go to the bar chart. And I'm gonna choose on the clustered bar chart like that, and we can see now that it actually ranks things a little bit better. So it's a little bit easier to actually see which ones are largest and which ones are smallest. And maybe to actually make this a little bit easier to see on the chart, We are going to go to the product category, click on that. We're going to go to more sort options, and we're gonna say descending, and not by the product category, but by the total order quantity like that. And what we can see now is whoops, made a little change there, and we can actually see now that it is ranked from lowest all the way to the highest. Okay, so that is three PivotCharts that we created in this particular sub-lesson. And we showed you how they're related to their underlying PivotTable, and how to go ahead and create multiple PivotTables to support these charts. And then once that was one thing you could do an awful lot of when you're building PivotCharts, is you're gonna create a bunch of PivotTables to support those. So don't be surprised if you start seeing the PivotTable count in your workbooks start to increase, to support the PivotCharts that are underlying them. So with that, that brings us to the end of this particular sub-lesson.