Excel Pareto Chart

in Excel

Pareto chart in Excel

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:

Excel Pareto Chart

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:

Excel Pareto Chart

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.

Excel Pareto Chart% 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.

Excel Pareto Chart

After the dreadfull task of selecting that data series we will go on and plot that on the secondary axis.

Excel Pareto Chart

Ok, this is our chart so far.

Excel Pareto Chart

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:

Excel Pareto Chart

We are making progress:

Excel Pareto Chart 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.

Excel Pareto ChartDownload

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.

Write a Comment