In case you didn’t hear about Pareto, let me shed a little light. He is the person that gave us the Pareto principle, also known as the 80/20 rule or the uneven distribution principle.
It’s not my point to tell you the story of his life, you with a short Googl’ing session you can find out more about him. What I want to show you is how to create a Pareto Chart in Excel, one that looks something like this:
People who work in quality departments in large companies are more familiar with these types of charts. However, this article is also for the rest of us that want to understand how we can create a combined chart on 2 axis.
Our example
Mary, the quality manager of the company of our fictional company just made a short list on what are the main causes because of which our products do not work as they should. This is the list:
She wants to create a chart that will justify why she needed to buy MS Office 2013. This chart is a crucial one, most important it has to be blue and orange. Let’s give her a hand.
Simple column chart
We will start by creating a simple column chart that contains only the first 2 columns from Jane’s list. Of course, we will make sure that it will be blue, it should look like that.
% Cumulative on the secondary axis
Moving on we will do something a bit funny, copy the %Cumulative and paste it onto the chart. You will see a second data series showing up.
After this you will need to select this second data series. Because the lines are pretty small I will give you a tip on how to do that, see the image bellow.
After the dreadfull task of selecting that data series we will go on and plot that on the secondary axis.
Ok, this is our chart so far.
Line, not column
You probably realized that the %Cumulative column is plotted with orange as a column chart. Guess what, we want it plotted as a line. No problem, right-click on orange and you will see this:
We are making progress:
Cutoff line at 80%
Because we absolutely have to have a line at 80% we will copy the Cutoff column and paste it onto the chart. We will see a line showing up. I made it thinner and dashed and we are finished.
Download
You can download the document that I’ve been using here.
This is it, hope it was helpful, if you have any remarks or question let’s talk more in the comments section.