A Pareto chart is a type of chart that combines columns and a line graph to highlight the most significant factors in a dataset. It is based on the Pareto Principle (80/20 rule), which suggests that roughly 80% of outcomes come from 20% of causes.
In Excel, a Pareto chart is commonly used for quality control, business analysis, defect tracking, and decision-making. It helps you quickly identify which categories contribute the most to a problem or result.
Below are the step-by-step methods to create a Pareto chart in Excel.
Why Use a Pareto Chart in Excel
A Pareto chart helps you prioritize actions by visually separating the “vital few” from the “trivial many.” Instead of analyzing all factors equally, the chart makes it easy to focus on the most impactful categories.
Excel allows you to create Pareto charts either automatically (newer versions) or manually (older versions), making it accessible for all users.
How to Create a Pareto Chart in Excel
Follow the steps below based on your Excel version.
1. Prepare Your Data Properly
Before creating a Pareto chart, your data must be structured correctly.
What your data should include:
- One column for categories (e.g., defect types, reasons, products)
- One column for values (e.g., frequency, count, cost)
Example structure:
- Column A: Issue Type
- Column B: Number of Occurrences
Ensure there are no blank rows or columns in the dataset.
2. Sort the Data in Descending Order
Pareto charts require data to be sorted from highest to lowest values.
- Select the entire dataset
- Go to the Data tab
- Click Sort
- Sort by the value column
- Choose Largest to Smallest
- Click OK
Sorting ensures the chart correctly highlights the most significant categories first.
3. Create a Pareto Chart (Excel 2016 and Later
If you are using Excel 2016, Excel 2019, Excel 2021, or Microsoft 365, Excel includes a built-in Pareto chart option.
- Select your sorted data
- Go to the Insert tab
- Click Insert Statistic Chart
- Select Pareto
Excel will automatically create:
- A column chart showing values
- A cumulative percentage line
4. Add a Pareto Chart Manually (Older Excel Versions)
If your Excel version does not have a built-in Pareto chart, you can create one manually.
- Add a new column for Cumulative Total
- In the first row, enter the first value
- In the next row, add the current value to the previous total
- Drag the formula down for all rows
- Add another column for Cumulative Percentage
- Divide each cumulative total by the grand total
- Format the result as a percentage
Now:
- Select categories, values, and cumulative percentage
- Go to Insert > Combo Chart
- Set values as Clustered Column
- Set cumulative percentage as Line
- Assign the percentage line to the Secondary Axis
- Click OK
5. Format the Pareto Chart
Formatting improves readability and presentation.
- Set the secondary axis maximum to 100%
- Add Data Labels to columns
- Rename chart title (e.g., Pareto Analysis of Defects)
- Adjust colors for clarity
Clear formatting helps viewers understand insights quickly.
6. Add the 80% Reference Line (Optional)
An 80% reference line highlights the cutoff point for the most impactful categories.
- Add a new column with a constant value of 80%
- Add it to the chart as a Line
- Assign it to the Secondary Axis
- Format the line for visibility
This line visually shows which categories fall within the 80% impact range.
7. Interpret the Pareto Chart
Understanding the chart is just as important as creating it.
- Bars represent individual category impact
- The line shows cumulative contribution
- Categories before the 80% mark are the most critical
These insights help prioritize improvements and decision-making.
8. Update the Pareto Chart Automatically
If your data changes, updating the chart ensures accuracy.
- Convert data into an Excel Table
- Charts linked to tables update automatically
- Re-sort data if new values are added
This keeps your Pareto analysis current without rebuilding the chart.
Final Thoughts
Creating a Pareto chart in Excel is a powerful way to analyze data and focus on the most important factors affecting outcomes. Whether using Excel’s built-in tools or manual methods, Pareto charts provide clear, actionable insights.
Once set up correctly, the chart becomes an essential part of data-driven decision-making.
