1 00:00:00,880 --> 00:00:05,210 Let's take a look at creating pareto and histogram charts in Excel. 2 00:00:05,280 --> 00:00:12,750 A Pareto chart is a statistical graph that is actually a combo chart which consists of a column and 3 00:00:12,750 --> 00:00:14,130 a line series. 4 00:00:14,130 --> 00:00:19,860 The special thing about it is that the columns which represent the category that you're analyzing or 5 00:00:19,860 --> 00:00:27,210 the KPI that you're analyzing are organized in descending order. You can quickly see the highest or 6 00:00:27,210 --> 00:00:28,670 the biggest category. 7 00:00:28,830 --> 00:00:35,320 The line series represents the cumulative percentage of the value you're analyzing. 8 00:00:35,400 --> 00:00:38,800 If you take a look at this example, we have sales by region. 9 00:00:38,880 --> 00:00:43,260 We can quickly see Europe accounts for the highest sales. 10 00:00:43,260 --> 00:00:50,190 Not only that but the first point in this line is somewhere around the 50 percent mark. Maybe it's just 11 00:00:50,190 --> 00:00:57,240 below the 50 percent mark. Which means that not only does Europe account for the highest sales but it 12 00:00:57,300 --> 00:01:01,440 also accounts for nearly 50 percent of all sales. 13 00:01:01,850 --> 00:01:08,010 Now if you take a look at the second point on this line chart, we can see it falls somewhere close to 14 00:01:08,040 --> 00:01:16,740 80 percent. Which means that Europe and North America both account for nearly 80 percent of all the sales. 15 00:01:17,220 --> 00:01:24,020 Which means South America Australia and Asia, only account for nearly 20 percent of sales. 16 00:01:24,030 --> 00:01:28,480 This is something that you can quickly tell by looking at a Pareto chart. 17 00:01:28,530 --> 00:01:32,190 The great thing is it's super easy to create in Excel. 18 00:01:32,190 --> 00:01:35,070 Let's make it from scratch. 19 00:01:35,070 --> 00:01:38,250 We're going to create a pareto chart based on this dataset. 20 00:01:38,310 --> 00:01:44,040 We have sales 2019 for the apps that belong to productivity division. 21 00:01:44,040 --> 00:01:49,860 Now all we have to do is highlight it, go to insert and select the pareto chart from here. 22 00:01:49,860 --> 00:01:53,130 It's sitting right beside the histogram. 23 00:01:53,130 --> 00:01:59,860 It's a statistical chart and just click the chart sitting beside the histogram which is the pareto chart. 24 00:01:59,860 --> 00:02:08,009 This is one of the newer charts that got introduced since Excel 2016. Which means that it uses 25 00:02:08,039 --> 00:02:14,940 a different type of interface than the standard charts. Which also means that we don't have all the general 26 00:02:14,940 --> 00:02:18,120 chart options that we're used to. 27 00:02:18,120 --> 00:02:22,390 Tor example, if I just right mouse click on the series. 28 00:02:22,520 --> 00:02:29,850 Notice that I can't change the chart type for this. And if I click on this series here, the line when 29 00:02:29,850 --> 00:02:34,840 I right mouse click all I can do is to format the pareto line. 30 00:02:34,860 --> 00:02:42,780 I can't do any other options here so I don't have those series options. So I could change the line colour, 31 00:02:42,980 --> 00:02:48,870 the dash type, some basic formatting but that's about it. For these ones here, 32 00:02:48,870 --> 00:02:57,300 I can add the data labels so I just right mouse click, add data labels. And I can also change the gap width. 33 00:02:57,330 --> 00:02:57,890 Notice 34 00:02:57,930 --> 00:03:00,450 I do have series options here. 35 00:03:00,450 --> 00:03:06,490 So when I click it, the only option I have is to increase the gap width because the default sticks 36 00:03:06,540 --> 00:03:07,740 all of them together. 37 00:03:07,880 --> 00:03:16,120 So it's basically zero. But I have the option to change that if I want. What can we deduce from this pareto chart? 38 00:03:16,120 --> 00:03:21,550 The sales distribution isn't as extreme as in the previous example. 39 00:03:21,550 --> 00:03:28,270 So we can see it's not just two or three apps that account for 80 percent of the sales. But instead, let's see 40 00:03:28,270 --> 00:03:30,330 the 80 percent mark is somewhere here. 41 00:03:30,340 --> 00:03:38,530 Instead we have around eight apps that represent 80 percent of all the sales. And these seven apps 42 00:03:38,830 --> 00:03:41,800 represent around 20 percent of sales. 43 00:03:41,800 --> 00:03:46,370 Now you might be thinking, this descending order was automatically done by Excel, 44 00:03:46,390 --> 00:03:46,910 right? 45 00:03:47,050 --> 00:03:52,090 Because the data set here is not in descending order. And that's correct. 46 00:03:52,090 --> 00:03:54,090 Excel does all that for you. 47 00:03:54,160 --> 00:03:56,750 You don't even need to sort your data set. 48 00:03:57,340 --> 00:04:00,400 So Voltage here is sitting in the second place. 49 00:04:00,430 --> 00:04:06,250 If I change this to 10,000. Notice what happens. 50 00:04:06,250 --> 00:04:08,760 Everything got sorted automatically. 51 00:04:08,830 --> 00:04:17,779 I didn't have to resort to dataset. Histogram charts are another statistical charts that are great for 52 00:04:17,870 --> 00:04:21,190 visualizing the distribution of data. 53 00:04:21,230 --> 00:04:24,960 In this case we're taking a look at salary distribution. 54 00:04:25,010 --> 00:04:34,520 We have specified these groupings here and we can quickly tell that 15 people fall in the 30,000 to 70,000 55 00:04:34,760 --> 00:04:36,180 salary range. 56 00:04:36,200 --> 00:04:42,530 We have two people that earn less than or equal to 30,000 and one person earning more than 57 00:04:42,530 --> 00:04:44,450 200,000. 58 00:04:44,450 --> 00:04:46,330 Let's make this from scratch. 59 00:04:46,610 --> 00:04:47,770 Here's our data set. 60 00:04:47,780 --> 00:04:52,100 We have the employee names, their entry date, and the yearly salary. 61 00:04:52,160 --> 00:04:55,730 So based on this, I want to create a histogram chart. 62 00:04:55,850 --> 00:05:03,950 All I'm going to do is highlight the data, go to insert and insert the histogram. Now you might be fine with 63 00:05:03,950 --> 00:05:09,770 the standard histogram chart that you get but you probably will want to adjust the bins here. Because 64 00:05:09,770 --> 00:05:17,150 what Excel does is it tries to automatically guess the best fitting range based on your dataset. 65 00:05:17,150 --> 00:05:20,510 But you might want to analyze the data in different ranges. 66 00:05:20,510 --> 00:05:26,810 You can specify that in the options. Just activate the axis by just clicking on it and bring up the 67 00:05:26,810 --> 00:05:31,430 axis option so you can use the shortcut key Control+1. 68 00:05:31,430 --> 00:05:37,730 Right here, we can see it's set to automatic but we can decide on the bin width. Which means the width of the range 69 00:05:37,730 --> 00:05:38,520 here. 70 00:05:38,540 --> 00:05:45,560 Instead of 53,000 I'm going to change that to 40,000 and press enter. Immediately I can 71 00:05:45,560 --> 00:05:49,550 see that the number of bins changed to 6. 72 00:05:49,550 --> 00:05:54,500 It would also be nice to have the ranges rounded to thousands. 73 00:05:54,500 --> 00:06:01,970 I can control that so I can define the over flow and the under flow bin. For over flow bin which means 74 00:06:01,970 --> 00:06:06,770 the highest point that I want to have here is something I'm going to manually set. 75 00:06:06,770 --> 00:06:13,530 I'll change that to 200,000 and press enter. For the under flow bin, 76 00:06:13,550 --> 00:06:15,130 I'm going to change that as well. 77 00:06:15,150 --> 00:06:21,890 Instead of -62,000 default that it has. I'll change it to 30,000 and press 78 00:06:21,890 --> 00:06:22,760 enter. 79 00:06:22,760 --> 00:06:25,950 Now my ranges look a lot neater. 80 00:06:26,000 --> 00:06:33,260 All I have to do is to activate the data labels. Select the series, right mouse click, add data labels. 81 00:06:33,590 --> 00:06:38,220 Let's make them a bit bigger so they stand out more and make them bold. 82 00:06:38,240 --> 00:06:45,910 Now I can remove the axis, remove the grid lines, and I'm done. And all of this is automatic. 83 00:06:46,100 --> 00:06:54,080 If I take Paul Hill out of this range here. Let's say he gets a demotion and he goes down to 20,000 84 00:06:54,080 --> 00:07:02,390 and I press enter. The first range automatically increases and the second range decreases. 85 00:07:02,390 --> 00:07:02,700 That's it. 86 00:07:02,720 --> 00:07:07,610 That's how easy it is to create Pareto and Histogram charts in Excel.