In this video, learn how to seamlessly use Excel PivotCharts to illustrate results and draw insights from your data.
- [Instructor] With Power Pivot, you can quickly illustrate the results of your data model using Excel's in-built Pivot Charts. If you'd like to follow along, have 03_03 Results open and make sure that you're on the Pivot worksheet. What we want to do now is create a new worksheet called Charts, and this is easy to do. Simply click on the Plus at the very bottom of the screen, and then double click over Sheet1, and type in Charts, and hit Enter on the keyboard. And the next thing we want to do is insert a Pivot Chart, so click on Pivot Chart here in our menu option, and you'll see that the dialog box has appeared. And there are a few options that we can select, but we do want to use the workbook's existing data model, that way we're bringing all the data that we need to access to create our chart. We can create a new worksheet, but I've already created an existing worksheet called Charts to have the chart appear on, so let's use that one. Click on OK. Now this next step may take a couple of seconds, but in a few moments, you'll notice that a box has appeared, allowing us to prepare our pivot chart. And all we need to do now, is prepare our chart as we normally would, using Excel's Pivot Chart features. Over on the right, I'm going to use Work Group as my Filter. I'm going to use Permit Type as my Axis. And the last thing I'm going to do is use Total Revenue as my Values. And just like that, Excel has created a column chart for us. I actually find this information a little bit difficult to view in a column chart, so I'm going to change it from a column chart to a pie chart, and that easy to do using the Design tab at the very top. All you do is select Change Chart Type, select Pie, and then OK on the keyboard. And let's go ahead and change the chart title from Total to Total Revenue. Filtering by the Work Group is possible using the dropdown box here in our Pivot Chart, but did you know that there's a much easier way and more flexible way that we can do this, using something called Slices. So let's click on Cancel, drag the pie chart across to the left, and make it a little bit larger, and then under Analyze, select Insert Slicer, and the field that we want to use is Work Group. So let's select that, and click on OK. Let's drag the Slicer to the left, now you may need to move the Pivot Chart just so that we can fully see the Slicer to the left. And then have a look at what happens if we select one of the options here under Work Group. The Pivot Chart automatically changes the data set that it uses. And there you have it, you've just learned how to illustrate the results of your Power Pivot data model using Pivot Charts.