Pareto Chart in Excel
Pareto diagram is an advanced type of chart in Excel. Consists of both a column chart and line chart. It presents a graphical incidence of certain factors (eg, causes complaints or defects). It is used mainly for quality control, to show what the focus mainly to get the best results (eg, elimination of one defect may reduce the number of complaints by half.) Pareto Diagram was the originator of the Italian economist Vilfredo Pareto.During his research he concluded that 80% of the country’s wealth is held by only 20% of the people. In this way, the so-called. The Pareto Principle, which is based on the proportion of 20 / 80. According to the Pareto Principle:
- 20% of clients includes 80% of complaints
- 20% of the effort results in 80% of the effects
- 20% of customers generate 80% of the company’s revenue
- 20% of the causes is making 80% of complaints
There is a lot of examples of Pareto Principle. With its simple Pareto Principle is used, among others. in quality management.
It is in this area is very useful Pareto diagram (sometimes also called a Pareto- Lorenz Diagram ). See how to create a Pareto Diagram in Excel.
First you will need a data table. Focus on quality management. A simple example for Pareto Diagram is a table where we can find the most common car damages. In this example they are: light bulb burned out, dead battery, blown fuse, worn brake pads, flat tire and others. The table should include count and cumulated count (preferably in percentage terms) of individual data.
Then, create a chart. This should be a column chart.
The chart is unreadable, because the data value is very different. Add a second series of data to increase the transparency of the chart. Click on one of the red column as a percentage of the cumulative amount of (tick all) and select ‘Format data series’ (keyboard shortcut CTRL + 1). A dialog box appears, select the ‘Plot series on’ and ‘Secondary axis’.
Now, Pareto Diagram looks a little bit better.
Red columns are still selected. Do not uncheck them, because they change their type. Change the chart type of a column on a line chart. Simply right-click on any of the red column and select Change Series Chart Type. Now select the line chart and confirm.
Pareto diagram is almost ready. You can even add data labels. Right-click the data series and select “Add data labels”.
You can do some more thing. Add a title, format axis and move labels on the bottom.
This is the Pareto Diagram ready. The columns show the number of occurrence of damages. Line chart summarizes the various damages. You can see that only 3 most common damages are as much as 75% of the total. In just a few moments created a really advanced form of a chart in Excel. Pareto diagram is a lot of applications, so this is a useful skill, especially for professionals involved in the control and for the students to various activities and projects.
Template
Further reading: Basic concepts Getting started with Excel Cell References
