The Excel templates and examples on this site provide everything you need to create your own Pareto charts, quickly and easily.
Pareto Charts are a powerful process improvement tool that show which factors contribute the most to a given problem. The power of Pareto Charts lies in the Pareto Principle, which states that a small number of factors will typically control a given outcome the most. Identifying and focusing on these critical factors will maximize results, oftentimes with far less effort than would be required if all factors were treated equally.
Here is a typical Pareto Chart (see the Downloads tab for the files that generated these images):
Pareto categories –
Left-hand vertical axis –
Right-hand vertical axis –
Creating a Pareto Chart
The following steps can be used to create a Pareto Chart.
1. Define the Problem Scope
Clearly state the problem or goal that the Pareto chart will address. Goal statements like, “Improve product quality in the Final Assembly area,” are too general and will not provide the focus needed for good data collection. Instead, focus on a specific metric like, “Reduce the final audit defect rate from 17,000 PPM to 9,000 PPM in Final Assembly.”
2. First Look for Existing Data
Oftentimes there is sufficient data already available to create a top-level Pareto Chart for the problem being addressed. In the case of our assembly defect rate example, the production floor will likely have historical data showing defect types and associated quantities for whatever time period is needed. Unless there is a specific timeframe of interest, retrieve historical data for at least one month, and preferably three months. Too short a timeframe (less than one month) may not show the complete picture of all causes, and too long a time frame (more than three months) may dilute the current set of problems with past problems that have already been solved.
3. Plan for Data Collection if Needed
If sufficient data does not already exist, then a data collection plan must be implemented. Structuring the data collection effort with appropriate check-sheets or forms will go a long way toward a successful outcome.
4. Assemble Data Into Pareto Categories
A typical Pareto Chart will have ten or fewer problem categories, and data should be grouped to achieve something in this range. If there are too many categories then the chart will be difficult to read. Too few problem categories (typically less than five) are an indication that problem categories are not specific enough to create an “actionable” Pareto Chart.
5. Arrange the Pareto Categories in Descending Order, and Enter Into the Spreadsheet
Pareto data is usually organized by frequency of occurrence (number of defects over a given timeframe for a given defect type). Sort the groups in descending order, and you are ready to enter your data into the Excel spreadsheet.
Pareto Charts are simple but powerful tools for problem solving – give them a try and they will likely find a permanent place in your problem solving arsenal.
The total COPQ is published as a Supply Chain metric each month.
The corporate quality manager has been tasked with leading a process improvement team and reducing COPQ by $700,000 in 12 months (from $15.2M to $14.5M).
Here is a graphical summary that shows the three-deep Pareto analysis, and the descriptions and conclusions from each chart are noted below.
Top-Level Pareto Chart
In this example, the overall COPQ metric has inputs from multiple locations and business functions. As the first step in the analysis, the quality manager creates a top level Pareto chart showing the year-to-date COPQ dollar amounts in each major category –
Alternate Top-Level Pareto Chart
Here is a more informative top-level Pareto chart that shows COPQ results by business unit (here is the Excel file) –
Note that the above chart conveys the same top-level data as the first chart, but shows the added dimension of business unit performance.
Second Level Pareto Chart for Returns
Seeing that product returns are the biggest opportunity area across the business ($4.7M year-to-date), the quality manager works with the customer service team and finds the top four return reasons for the three businesses that incur the majority of the return dollars (note that the Pareto chart above is already helping the quality manager work more efficiently – there is no need to investigate return reasons in the Specialty business, since the payback on this effort would be insignificant).
Here is the second level Pareto for returns, also known as a sub-Pareto chart –
Learnings Thus Far
- Across the entire business, Returns and Scrap are the biggest opportunity areas
- Product Liability and Waste Haul are much smaller opportunity areas, and should be addressed after Returns and Scrap are improved
- Three of the four business units contribute the majority of the Returns and Scrap costs: Incandescent, LED, and Fluorescent
- Across the above three business units, there are three major reasons for customer returns: (1) “Did not Order,” (2) “Damaged Packaging,” and (3) “Damaged Product.”
- “Did not Order” and “Damaged Packaging” errors are the single biggest opportunity areas across the businesses, inside the Customer Returns category (and there is a good chance that this statement applies to the overall business).
This is a great deal of valuable information coming from only two charts (we will ignore the first chart since the second chart conveys the same information, and more)!
Take a look at the “COPQ Results by Business” chart above. Based on this data, where would you focus the team’s efforts in the scrap category?
Scrap in the LED business far exceeds scrap in the other business units. In fact, scrap in the LED business exceeds all other business units combined. So a sub-Pareto chart completed on LED Scrap would be the highest payback activity in the scrap area. We will stay on the returns investigation path for this example.
Continuing with the Returns Investigation
All of the above analysis was done with existing data residing in the customer service and manufacturing databases. Now that the team knows where to focus, the manual data collection effort will be much easier since the scope is narrowed to three business units (Incandescent, LED, and Fluorescent) and three customer return reasons (“Did not Order,” “Damaged Packaging,” and “Damaged Product).”
Since Customer Service talks with customers and processes returns, the quality manager sets up a recurring weekly meeting with the customer service leaders. This meeting will focus on planning and executing data collection for the third-level Pareto charts.
After recording data for one month, a third-level Pareto chart is generated for the top return reasons, with results broken down by business unit. Note that the total dollar amounts are much lower since there is only one month of data involved.
As is often the case, the third level Pareto chart has produced a specific project opportunity: in this case the reduction of warehouse pick errors. Note that all other project opportunities pale in comparison to pick errors. The team can now move forward with a Six Sigma project focused on pick errors, knowing that they will make the largest impact possible on customer returns.
A similar analysis was done for scrap (results not shown here), which generated two additional projects, and the team delivered its $700K savings goal with only three focused projects, thanks to the up-front Pareto analysis work done by the team.
1. Sort your data in descending order. You should have two columns of data at this point – one column for the cause categories and another for the values of each category. Values can be counts (like this example), dollars, or whatever unit of measure is appropriate for what is being studied.
2. Open the Excel file and go to the Data tab
3. Enter the cause categories in the first column of data, which is column B on the spreadsheet.
4. Enter the values in the second column, which is column C on the spreadsheet.
5. Erase any rows that are not needed. The chart will be automatically populated.