Pareto Chart in Microsoft Excel

 

What is Pareto Chart?

Wilfredo Pareto is the father of the Pareto chart. Pareto chart is used for analyzing the impact of poor quality process, so that we can focus step wise on how to improve the quality. It is an advanced type of chart in Excelwhich is the combination of two charts i.e. Line Chart and Column Chart.

Before preparing the Pareto Chart we should understand about the Line chart and Column Chart individually, so that we would be able to understand the role of every chart.

Line Chart: – Use this chart type to show the trends over time (years, month, and days) or categories.

Column Chart: – Use this chart type to visually compare values across a few categories.

Where can we use Pareto chart?

You can use a Pareto chart to analyze every problem.

1.You can use Pareto chart for transport business.
2.Use to find out the problem in working area.
3.You can use in business analyses.

How to make a Pareto chart?

Let’s take an example and understand, how can we prepare a Pareto chart

I have School Complaint’s data in the range A3:B13. In which column A contains the type of complaints, and column B contains the no. Of complaints.

 

img1

 

To create the Pareto chart we have to do Pareto analysis,  follow below mentioned steps:-

  • Sort the data in descending order (Largest to Smallest).
  • To sort the data, select the range of no. Of complaints D4:D13, go to the Data tab in the Sort & Filter group, click on the sort option of Largest to Smallest.

 

img2

  • Calculate the cumulative count in column C.
  • Enter the formula shown below C5 and drag the formula down.

img3

  • Calculate the cumulative percentage in column D.
  • Enter the formula shown below D4 and drag the formula down.

img4

  • Select the data in column A, B, and D.
  • Go to “Insert” tab, click on Column chart and select Clustered column Chart from the Chart group.

img5

  • Right click on the Cumulative %age bar.
  • Click on “Change Series Type”.
  • Change Chart Type dialog box will appear.
  • In the Choose the chart type and axis of your data series group, Select the chart type Line with Marker for Cumulative %age and tick on secondary option.

img6

  • You will get the result like below shown picture:-

img7
Conclusion:- In the snapshot you can see how the Pareto chart performs to analyze the problems. The chart is showing that 80% complaints come from the Broken Benches, Fans not working, and Bad Atmosphere, high donation, school buses not clean, and unfriendly staff.

 

image 29

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us @ info@exceltip.com



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>