Looking to learn how to make a Pareto Chart? You’ve come to the right place.
The Pareto Chart is a graphical tool to map and grade business process problems from the most recurrent to the least frequent. In other words, this helps to identify the most frequently occurring problems or separate the vital few from the trivial many.
The Pareto Chart is a combination of both the Bar chart and line chart. The bar chart indicates the frequency of occurrence or defects, whereas the line graph indicates the cumulative data percentage in descending order.
How to construct a Pareto Chart
The Pareto chart’s primary purpose is to identify the most frequent defects, errors that can be count and categorize. Below are the steps to create a Pareto chart using the Pareto Analysis.
- Determine the categories that wish to monitor or impacts the business
- Establish a measurement system, and also important to use the same measure for all contributors.
- Determine the timeline that data covers for the Pareto for all the categories.
- Collect data for all the categories in two columns, categories (example: type of defects) in the first column and corresponding frequency (total count of each defect category) in the second column.
- Draw a bar graph with categories (example: type of defects) in the x-axis and frequency on the y-axis.
- Re-order the categories in descending order (from largest to smallest categories).
- Then, calculate the cumulative percentage of each category in descending order. i.e Individual defect count/ total defects count *100.
- Draw another vertical axis on the right side of the graph with values from 0 to 100%.
- Plot the cumulative percentage of each category on the x-axis.
- Now join all the points, it will form a curve.
- Analyze the graph. Identify the top 80% on the Y-axis. This point on the x-axis divides the vital few defects (left side) and trivial many defects on the right side.
Example of a Pareto Chart in the DMAIC Process
Example: A XYZ group runs franchise hotels across USA. Hotel management decided to collect customer stay experience from top eight revenue generated locations via online survey. Team analysed the results using Pareto analysis.
Following are the results from eight locations
How to Make a Pareto Chart in Excel
Step 1: Write “Complaint Type” in cell B3 and “Count” in next cell i.e C3. Below them, enter all the customer complaint categories and enter their respective total count in the column C.
Step 2: Select the whole range (including titles). Go to Data ==> select sort make sure that “My list has header row” is checked. In “Sort By” select “Count” and in “Order” drop-down select “Largest to Smallest”, then click on “Ok”.Now it will sort the count in descending order.
Step 3: Add the total categories count in cell C12. Then add the “Cumulative percentage” column next to the Count column and calculate the cumulative percentage as shown as per the formula in D4 cell, and drag D4 below rows to apply the same formula for all the categories.
Step 4: Again, select complaint type, count, and cumulative percentage columns including titles.
Step 5: Go to Insert ==> Chart. Make sure that “Column” is selected as chart type, and and then select “Clustered column” chart from 2-D Column chart.
Step6: Then, from the two bar graphs, click on the cumulative percentage bar graph and right-click on it ==>select “change series chart type”.
Step 7: On the “Change Chart Type” ==> Choose “Line with a marker” chart under Line chart and then clike on “ok”.
Step8: Select the line graph and right-click on it ==> select “Format data series”
Step 9: Under “Series Option” ==>select “Secondary Axis” radio button . Also right-click on the secondary axis ==>select “Format Axis”. Under “Axis Options” change the “Maximum” option to “Fixed” and then enter the value as 100.
Step10: Add titles on the chart and also include axis labels. Analyze the data: Identify top customer complaints. In other words, use the Pareto Analysis i.e 80% of customer complaints.
Interpret the Pareto graph:
From the above diagram, it is evident that the top three categories (Bad smell, no hot water, and also breakfast food quality) contribute 80% of customer complaints.