How to do Pareto Analysis in Excel?
In excel, Pareto Analysis is conducted by using “Pareto Chart”. This chart is a combination of the “Column & Line” chart, the column bar chart shows the actual values and the line graph shows the cumulative percentage of those values. Below given are the steps to Pareto analysis in excel
- Step 1: Sort the data highest to lowest.
- Step 2: To create a Pareto chart we need to create a cumulative total column. Apply below formula to create a cumulative column.
Here we got a Cumulative Total of all Expenses.
- Step 3: Next we need to find the cumulative percentage value. Apply below formula to find the cumulative percentage value.
Below are the results.
- Step 4: Select the entire data now and insert “Clustered Column Chart”.
Our Pareto analysis chart looks like as shown below.
- Step 5: Select the chart and under the “Design” tab click on “Change Chart Type”.
- Step 6: We can see the “Change Chart Type” window now. In this window click on the “Combo” option.
- Step 7: From the combo, chart type change the “Cumulative %” series to “Line Chart” and make this as “Secondary Axis” then click on “Ok” to close the above window.
It will take us back to the worksheet and now we have a Pareto analysis chart like the below one.
- Step 8: In this chart “Cumulative” column is unwanted, so select the “Orange” colored column bars and delete it.
As soon as we delete the above series column we will have a new chart look like this.
From the above we have two series values now, one is “Values” and the other one is “Cumulative %”. Column bar indicates the “Values” and the line chart indicates the “Cumulative %” series values.
Interpretation is the beauty of the Pareto Chart, from the above Pareto Chart we can clearly see that the cumulative % line touches the second column bar which is at 80%, so “Salary Expenses & Office Rent” are the major contributors for the total expenses.
About 80% of the cost is occurred by 20% of the expenses.
Home Work for Readers
Now we know how to create Pareto Chart & Analysis in excel. By using the below data try to create Pareto Chart on your own.
Things to Remember
- Pareto Analysis is also called as “80/20 Rule”.
- You need to sort the data in descending order to Create a Pareto Chart in excel.
- Always exclude cumulative values from the chart and retain only actual values and cumulative%.
This has been a guide to Pareto Analysis in Excel. Here we discuss how to do Pareto analysis in excel along with practical example and downloadable excel template. You may learn more about excel from the following articles –