Let's take a look at creating pareto and histogram charts in Excel.
A Pareto chart is a statistical graph that is actually a combo chart which consists of a column and
a line series.
The special thing about it is that the columns which represent the category that you're analyzing or
the KPI that you're analyzing are organized in descending order. You can quickly see the highest or
the biggest category.
The line series represents the cumulative percentage of the value you're analyzing.
If you take a look at this example, we have sales by region.
We can quickly see Europe accounts for the highest sales.
Not only that but the first point in this line is somewhere around the 50 percent mark. Maybe it's just
below the 50 percent mark. Which means that not only does Europe account for the highest sales but it
also accounts for nearly 50 percent of all sales.
Now if you take a look at the second point on this line chart, we can see it falls somewhere close to
80 percent. Which means that Europe and North America both account for nearly 80 percent of all the sales.
Which means South America Australia and Asia, only account for nearly 20 percent of sales.
This is something that you can quickly tell by looking at a Pareto chart.
The great thing is it's super easy to create in Excel.
Let's make it from scratch.
We're going to create a pareto chart based on this dataset.
We have sales 2019 for the apps that belong to productivity division.
Now all we have to do is highlight it, go to insert and select the pareto chart from here.
It's sitting right beside the histogram.
It's a statistical chart and just click the chart sitting beside the histogram which is the pareto chart.
This is one of the newer charts that got introduced since Excel 2016. Which means that it uses
a different type of interface than the standard charts. Which also means that we don't have all the general
chart options that we're used to.
Tor example, if I just right mouse click on the series.
Notice that I can't change the chart type for this. And if I click on this series here, the line when
I right mouse click all I can do is to format the pareto line.
I can't do any other options here so I don't have those series options. So I could change the line colour,
the dash type, some basic formatting but that's about it. For these ones here,
I can add the data labels so I just right mouse click, add data labels. And I can also change the gap width.
Notice
I do have series options here.
So when I click it, the only option I have is to increase the gap width because the default sticks
all of them together.
So it's basically zero. But I have the option to change that if I want. What can we deduce from this pareto chart?
The sales distribution isn't as extreme as in the previous example.
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
the 80 percent mark is somewhere here.
Instead we have around eight apps that represent 80 percent of all the sales. And these seven apps
represent around 20 percent of sales.
Now you might be thinking, this descending order was automatically done by Excel,
right?
Because the data set here is not in descending order. And that's correct.
Excel does all that for you.
You don't even need to sort your data set.
So Voltage here is sitting in the second place.
If I change this to 10,000. Notice what happens.
Everything got sorted automatically.
I didn't have to resort to dataset. Histogram charts are another statistical charts that are great for
visualizing the distribution of data.
In this case we're taking a look at salary distribution.
We have specified these groupings here and we can quickly tell that 15 people fall in the 30,000 to 70,000
salary range.
We have two people that earn less than or equal to 30,000 and one person earning more than
200,000.
Let's make this from scratch.
Here's our data set.
We have the employee names, their entry date, and the yearly salary.
So based on this, I want to create a histogram chart.
All I'm going to do is highlight the data, go to insert and insert the histogram. Now you might be fine with
the standard histogram chart that you get but you probably will want to adjust the bins here. Because
what Excel does is it tries to automatically guess the best fitting range based on your dataset.
But you might want to analyze the data in different ranges.
You can specify that in the options. Just activate the axis by just clicking on it and bring up the
axis option so you can use the shortcut key Control+1.
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
here.
Instead of 53,000 I'm going to change that to 40,000 and press enter. Immediately I can
see that the number of bins changed to 6.
It would also be nice to have the ranges rounded to thousands.
I can control that so I can define the over flow and the under flow bin. For over flow bin which means
the highest point that I want to have here is something I'm going to manually set.
I'll change that to 200,000 and press enter. For the under flow bin,
I'm going to change that as well.
Instead of -62,000 default that it has. I'll change it to 30,000 and press
enter.
Now my ranges look a lot neater.
All I have to do is to activate the data labels. Select the series, right mouse click, add data labels.
Let's make them a bit bigger so they stand out more and make them bold.
Now I can remove the axis, remove the grid lines, and I'm done. And all of this is automatic.
If I take Paul Hill out of this range here. Let's say he gets a demotion and he goes down to 20,000
and I press enter. The first range automatically increases and the second range decreases.
That's it.
That's how easy it is to create Pareto and Histogram charts in Excel.