1 00:00:00,210 --> 00:00:06,300 One of the advantages of pivot charts over standard Excel charts is that you can use pivot slicers and 2 00:00:06,300 --> 00:00:11,030 timelines on them without you having to write a single formula. 3 00:00:11,310 --> 00:00:17,100 One of the disadvantages of pivot charts is that you can't use some of Excel standard chart types. 4 00:00:17,100 --> 00:00:23,300 For example, you can't use a histogram or a treemap or a scatter plot as a pivot chart. 5 00:00:23,460 --> 00:00:28,640 But if you're looking to create a simple chart, pivot charts are great for that. 6 00:00:28,640 --> 00:00:34,470 Now remember when you insert a chart, you need data. When you insert a pivot chart, 7 00:00:34,470 --> 00:00:42,780 you need pivot table data. A pivot chart can't exist without a pivot table. Let's go and see what happens 8 00:00:43,140 --> 00:00:50,600 if we insert a pivot chart directly from insert, pivot chart, and let's just go with pivot chart. 9 00:00:50,610 --> 00:00:52,100 This looks familiar, right? 10 00:00:52,230 --> 00:00:57,490 Except it says create pivot chart instead of pivot table. We have to select our table. 11 00:00:57,510 --> 00:01:03,340 So let's just add the table name that we set up at the beginning of this section. 12 00:01:03,540 --> 00:01:06,380 I'm going to add it to this worksheet and click on ok. 13 00:01:06,440 --> 00:01:09,460 Notice what happened. 14 00:01:09,460 --> 00:01:14,000 I get a placeholder for the pivot table, a placeholder for my pivot chart. 15 00:01:14,380 --> 00:01:18,760 And here I get my list of pivot chart fields. 16 00:01:18,790 --> 00:01:26,200 Instead of it saying pivot table, it says pivot chart fields. And instead of this saying rows, it says axis. 17 00:01:26,320 --> 00:01:32,110 Let's create a simple pivot chart for our companies and their sales. 18 00:01:32,140 --> 00:01:35,080 I'm going to drag company name to the axis. 19 00:01:35,150 --> 00:01:37,660 Notice my pivot table is getting updated. 20 00:01:37,660 --> 00:01:39,620 So is my chart. 21 00:01:39,670 --> 00:01:45,460 Now I'm going to drag the sales USD to values and I get a standard column chart here. 22 00:01:45,610 --> 00:01:49,430 But I also get a pivot table here. 23 00:01:49,440 --> 00:01:53,030 Now I get the ability to update my pivot chart. 24 00:01:53,190 --> 00:01:59,070 You can see the options you have for updating the pivot chart are in this new tab called Pivot chart 25 00:01:59,210 --> 00:02:00,190 analyze. 26 00:02:00,240 --> 00:02:02,820 You can also get to them by right mouse clicking. 27 00:02:02,860 --> 00:02:09,030 If you want to change the chart type to something else, you can do it right here. The button here gives 28 00:02:09,030 --> 00:02:10,590 us the ability to filter. 29 00:02:10,979 --> 00:02:17,760 If we had a lot of data and we just want to filter for a few, we can select them and our chart is 30 00:02:17,760 --> 00:02:19,870 filtered to this view. 31 00:02:19,870 --> 00:02:25,830 Now you can also remove these buttons if you don't want them in your report by clicking on the field 32 00:02:25,860 --> 00:02:28,660 buttons here and they're gone. 33 00:02:28,660 --> 00:02:30,270 Now this is something I don't need. 34 00:02:30,270 --> 00:02:31,650 I'm going to delete. 35 00:02:31,830 --> 00:02:40,140 Let's delete, delete, and add data labels to these. Now the formatting of the numbers is driven from the 36 00:02:40,140 --> 00:02:41,580 pivot table here. 37 00:02:41,670 --> 00:02:50,310 To update them, let's update the numbers in the pivot table. Use a thousand separator, 0 decimal places. 38 00:02:50,310 --> 00:02:52,840 That gets reflected here. 39 00:02:52,920 --> 00:03:00,320 Now I can add a title to this and that's my pivot chart. Every time my data updates in the original 40 00:03:00,320 --> 00:03:06,490 table, I have to refresh the pivot table to get the data reflected in the chart. 41 00:03:06,500 --> 00:03:09,050 Now here's one thing you need to be careful of. 42 00:03:09,620 --> 00:03:16,160 If you want to have a pivot table that has more information than your chart, you can't. 43 00:03:16,340 --> 00:03:19,960 The way around this is to create two pivot tables. 44 00:03:20,180 --> 00:03:27,950 So let's say you wanted to have one table that shows the sales in USD but also shows the quantity. 45 00:03:27,950 --> 00:03:29,720 So if I drag quantity here. 46 00:03:29,720 --> 00:03:30,740 Notice what happens. 47 00:03:30,740 --> 00:03:37,100 My charts automatically gets updated by including quantity in there. To remove this from my chart 48 00:03:37,100 --> 00:03:43,430 I actually have to remove quantity from my pivot table. If I wanted a separate report that shows this 49 00:03:43,550 --> 00:03:50,000 and a pivot table that only shows the sales in USD, I have to create two pivot tables. One Pivot Table 50 00:03:50,060 --> 00:03:56,450 is purely for the purpose of creating my chart and the other one is to show the additional information. 51 00:03:56,450 --> 00:04:02,260 Now let's take a look at how we can work with timelines and slicers with our pivot chart. 52 00:04:02,780 --> 00:04:09,350 Well we've already created a pivot table with slicer and timeline in the previous lecture. 53 00:04:09,530 --> 00:04:16,610 Let's actually use that by just adding a pivot chart to this. Click somewhere inside the pivot table 54 00:04:17,000 --> 00:04:20,560 to activate the pivot table options. 55 00:04:20,610 --> 00:04:27,170 Now you can also add a chart by going to the pivot table analyze tab and adding a pivot chart from 56 00:04:27,170 --> 00:04:27,600 here. 57 00:04:27,680 --> 00:04:32,000 You can do the same thing by going to insert and adding a pivot chart. 58 00:04:32,000 --> 00:04:34,280 This time I'm going to do it from this tab. 59 00:04:34,280 --> 00:04:36,560 So let's click on Pivot chart. 60 00:04:36,560 --> 00:04:37,700 We can select one. 61 00:04:37,760 --> 00:04:39,880 Let's go with this and click on ok. 62 00:04:40,100 --> 00:04:47,510 I'm gonna hide these buttons here and make some slight adjustments to my pivot chart. 63 00:04:47,650 --> 00:04:49,470 Let's hide the field buttons. 64 00:04:49,660 --> 00:04:57,820 Let's remove these grid lines and let's update the title. Now because this pivot chart is connected to 65 00:04:57,820 --> 00:05:04,060 this pivot table which is also connected to the slicers, the pivot chart is automatically connected 66 00:05:04,060 --> 00:05:05,350 to the slicers. 67 00:05:05,350 --> 00:05:09,080 If I change the quarter here, it reflects in my chart. 68 00:05:09,100 --> 00:05:12,820 If I change region, it reflects in my chart as well. 69 00:05:13,870 --> 00:05:18,730 If you just want a chart that's connected to these and you don't want to show your pivot table 70 00:05:19,030 --> 00:05:25,090 you just have to move it out of view. So you can cut and paste it or you can officially do it by going 71 00:05:25,090 --> 00:05:31,810 to the analyze tab and clicking on move pivot table. And then selecting the sheet or the location you want 72 00:05:31,810 --> 00:05:32,840 this to be in. 73 00:05:32,860 --> 00:05:37,180 So I'm just going to shift it to column M then click on ok. 74 00:05:37,180 --> 00:05:43,780 Move it out of view there and organize my report in this way. 75 00:05:43,780 --> 00:05:47,760 So it looks like these slicers are directly controlling my chart. 76 00:05:48,950 --> 00:05:52,290 If your chart starts to get too crowded. 77 00:05:52,460 --> 00:05:58,850 If I'm selecting more than one quarter here and it starts looking like this and you don't have enough 78 00:05:58,850 --> 00:06:06,410 space on your report for your chart, what you can do is to change the format of this to a bar chart. 79 00:06:06,450 --> 00:06:12,030 I'm also going to remove the labels here and add the data labels 80 00:06:12,040 --> 00:06:18,250 to the bars here. And let's change the chart type. I just have to right mouse click, change chart type and 81 00:06:18,250 --> 00:06:21,110 select the bar chart instead 82 00:06:21,340 --> 00:06:23,660 and then go with the first one and click on 83 00:06:23,680 --> 00:06:24,680 Ok. 84 00:06:24,830 --> 00:06:33,130 Now remember originally I sorted my pivot table data here which means that the chart data is automatically 85 00:06:33,130 --> 00:06:40,510 sorted. But I'd rather have this the other way around. I can make that setting directly under chart 86 00:06:40,570 --> 00:06:47,820 options for the axis. So click on Control+1 or double click to bring up the format axis options. 87 00:06:47,860 --> 00:06:54,220 Go to axis options right here and place a check mark for categories in reverse order. 88 00:06:54,220 --> 00:06:56,690 Now since we are also in the options here. 89 00:06:56,710 --> 00:07:02,390 Let's adjust the gap width by reducing it a little bit. 90 00:07:02,510 --> 00:07:04,830 And we just give it a bit more space 91 00:07:04,960 --> 00:07:07,890 so the numbers here are visible now. 92 00:07:08,020 --> 00:07:15,440 I can select the quarter, I can select both regions and my charts automatically updates. 93 00:07:15,470 --> 00:07:21,610 Now one last thing to check is what happens when our original data gets updated. 94 00:07:21,610 --> 00:07:24,110 How does that impact our chart? 95 00:07:24,130 --> 00:07:29,480 Let's go and test this by adding a new product to our raw data table. 96 00:07:29,620 --> 00:07:38,520 Let's just bring this information down and change the product here to another product. 97 00:07:38,560 --> 00:07:40,110 Let's change the amount to this. 98 00:07:43,210 --> 00:07:45,220 Let's go back to our report. 99 00:07:45,310 --> 00:07:46,630 We don't see it here yet. 100 00:07:46,630 --> 00:07:51,590 We need to refresh our data, right mouse click and refresh. 101 00:07:51,660 --> 00:08:00,110 Now we see it pop up in here. Now notice the more crowded it gets, the labels become compressed. 102 00:08:00,110 --> 00:08:06,440 So just make sure you have enough space on your report to show everything. 103 00:08:06,480 --> 00:08:09,280 So that's how you can work with pivot charts 104 00:08:09,480 --> 00:08:15,230 and also how you can connect them to slicers and timelines to make them super dynamic.