Prepare data with categories and values. 2. Sort data in descending order by value. 3. Calculate cumulative sum and percentage. 4. Select count data and insert Pareto chart from Statistic Chart options. 5. Customize title, axes, and style as needed. To create a Pareto chart in Excel, organize your data with categories and frequencies, sort it in descending order, add cumulative sum and percentage columns, use the built-in Pareto chart feature under Insert > Statistic Chart to automatically generate the chart with sorted bars and a cumulative percentage line, then customize the chart elements such as title, axes, and formatting for clarity, ensuring the final chart displays the vital few factors contributing to the majority of issues, with bars in descending order and a clear dual-axis visualization, completing the process efficiently in Excel 2016 or later.

Creating a Pareto chart in Excel is straightforward, especially if you already have your data organized. A Pareto chart combines a bar chart (sorted in descending order) and a line graph showing cumulative percentage. It’s commonly used in quality control to identify the most significant factors in a dataset.

Here’s how to make one in Excel (steps apply to Excel 2016 and later, including Microsoft 365):

1. Prepare Your Data

Start with a table that includes:

• Categories (e.g., types of defects, causes of delays)
• Frequencies or values for each category

Example:

2. Sort Data in Descending Order

Before creating the chart, sort your data by count (largest to smallest):

• Select your data range
• Go to Data > Sort
• Sort by "Count" in Descending order

This ensures the Pareto principle (80/20 rule) is visually represented correctly.

3. Calculate Cumulative Percentage

Add two new columns:

• Cumulative Sum
• Cumulative Percentage

Steps:

1. In the first row of Cumulative Sum, enter the first count.
2. For the next rows, add the current count to the previous cumulative sum.
3. In Cumulative Percentage, divide each cumulative sum by the total sum and format as percentage.

Example formula setup:

Drag formulas down. Format the % column as Percentage with 1 decimal place.

4. Insert the Pareto Chart (Easiest Method)

If you're using Excel 2016 or later:

• Select your original Count data (just the values, not cumulative columns)
• Go to Insert > Insert Statistic Chart > Pareto

? Excel automatically:

• Sorts bars in descending order
• Adds a cumulative percentage line
• Creates dual axes (bars on left, line on right)

Note: This built-in option does the sorting and percentage calculation automatically. You don’t need to pre-calculate cumulative percentages if you use this method.

5. Customize the Chart (Optional)

After inserting:

• Title: Edit or remove the default title
• Axes: Right-click to format scale if needed
• Legend: Usually unnecessary — you can remove it
• Line Style: Right-click the cumulative line > Format Data Series > change color or make it smoother

You can also:

• Add data labels to bars
• Adjust gap width (right-click bars > Format Data Series > Gap Width)

Alternative: Manual Combo Chart (For Older Excel Versions)

If you don’t have the built-in Pareto option:

1. Select your data including Count and Cumulative %
2. Go to Insert > Combo Chart > Custom Combination
3. Set:
   • Count → Clustered Column (bar chart)
   • Cumulative % → Line with Secondary Axis
4. Click OK

Then sort the categories manually in descending order by count.

Final Tips

• The Pareto chart works best with categorical data and numerical frequencies.
• Always check that the bars are in descending order.
• Use it to highlight the "vital few" causes that contribute to most of the issues.

Basically, Excel does most of the work — just organize your data, and let the built-in Pareto chart do the rest.

The above is the detailed content of How to create a Pareto chart in Excel. For more information, please follow other related articles on the PHP Chinese website!