Create Pareto chart in different versions of Excel including Excel 2016, Excel 2013, Excel 2010 and Excel 2007 in simple steps with examples. You will be able to create pareto chart using any version of Excel after going through this article.
Pareto chart using Excel 2016 or Excel 2013
Pareto analysis is very useful in assisting management with the selection of the more important and impactful problems or defect areas, this helps us in directing corrective action resources at right places.
Making Pareto chart using MS Excel is very simple, but you need to understand the concept and know few steps. I can assure that you will find it very easy and you will be using it very soon in your daily work life.
So lets start, in step 1 just create the list of causes or defects with their frequency in an excel table. We have taken example of customer complaint calls at a contact center. Sample of 300 calls were audited on 12 parameters as listed in the table, number of defects observed are listed under “CSQ Parameters”.
Please make a note of step 2, you have to sort the data in descending order. Many people miss this step and then their Pareto chart doesn’t look like a Pareto chart.
In step 3, we need to add one column for proportion/percent contribution for each defect type. After this we have to add one column for cumulative proportion. Cumulative proportion shows the combined impact of parameters added.
Calculation cumulative proportion is very easy, just check the below table and explanation. First row will have the same value as “Proportion” column, then apply formula from second row as shown below.
In the current version of Excel 2016, we get the option of Pareto and it has become very easy. Now in step 5, just select the first, 2nd column along with the “cumulative” column, then go to “Recommended Charts” under “Insert” option.
You will see the Pareto Chart option there and just select that as shown below.
Pareto chart using Excel 2010 or Excel 2007
Earlier versions of Excel had different options so we need to perform Step 5 onwards differently.
You can use these steps in the newer version of Excel as well and these are pretty simple, so no need to worry.
In a Pareto chart, the cumulative data needs to be shown in a line chart. So, the idea here is to change cumulative bar into line chart and keeping the other bar as it is.
As you can see below, you have to select “Combo” option which basically means that you can combine Bar and Line charts in a single chart. Here just select “Line Chart” for Cumulative only, also tick on the “Secondary Axis” option for Cumulative line only.
So, basically you have to covert “cumulative graph” into Line chart and add Secondary axis for it.
Your Pareto chart will be ready and you can do some customizations as required.