How to Create Pareto Chart in Excel - Practical Guide

Learn to create a Pareto chart in Excel to visualize the 80/20 principle. This tutorial covers sorting data, inserting a combination chart, and adding a cumulative percentage line. Pareto charts help identify which factors have the greatest impact, making them essential for quality control, project management, and data-driven decision-making.

Why This Matters

Pareto charts prioritize problems and opportunities, enabling data-driven decisions that focus resources on the most impactful issues. They're vital for quality management, process improvement, and demonstrating business value.

Prerequisites

•Basic Excel knowledge (data entry, formatting)
•Data organized in two columns (categories and values)

Step-by-Step Instructions

Prepare and sort your data

Arrange data in two columns (categories and values). Select both columns, then go to Data > Sort and sort by values in descending order to rank categories by frequency.

Calculate cumulative percentages

In a third column, calculate the cumulative sum of values using =SUM($B$2:B2)/SUM($B$2:$B$100)*100 and drag down to create a cumulative percentage column.

Select data for the chart

Select the category names (column A) and values (column B) including headers. Go to Insert > Charts > Combo Chart to access combination chart options.

Create the combo chart

In the Combo Chart dialog, set column B as Clustered Column and add the cumulative percentage as a secondary axis using Line chart type. Click OK to insert the chart.

Format and add reference line

Right-click the line series and format it with a different color. Add a horizontal reference line at 80% on the secondary axis to highlight the Pareto principle threshold.

Alternative Methods

Using Excel's built-in Pareto template

Go to File > New and search for 'Pareto' to use a pre-built template that automatically calculates percentages and formats the chart.

Manual column chart with overlay

Create a standard column chart, then separately insert a line chart on the same axis to manually combine the two visualizations.

Tips & Tricks

✓Sort data in descending order before creating the chart for proper Pareto visualization.
✓Use contrasting colors for columns and line to make the cumulative percentage clearly visible.
✓Label the secondary axis as 'Cumulative %' for clarity.

Pro Tips

★Add a data table below the chart to show exact values and percentages for precision analysis.
★Use conditional formatting on the cumulative percentage column to highlight values above 80%.
★Create a dynamic Pareto by using named ranges and OFFSET formulas to update automatically as data changes.

Troubleshooting

The cumulative percentage line appears flat or doesn't show correctly

Verify the formula is calculating cumulative sums correctly and that the secondary axis is properly configured. Right-click the line and ensure it's assigned to the secondary axis in Format Data Series.

Chart looks cluttered with too many categories

Group minor categories into an 'Other' category to reduce clutter and maintain focus on the vital few factors.

Percentages don't sum to 100%

Check that your formula divides by the total sum ($B$2:$B$100) with absolute references, not relative references.

Related Excel Formulas

SUM SUMIF OFFSET SORT

Frequently Asked Questions

What is the 80/20 principle in a Pareto chart?

The 80/20 principle (Pareto principle) suggests that approximately 80% of effects come from 20% of causes. Pareto charts visually show which factors account for most of the impact, helping prioritize efforts.

Can I create a Pareto chart in Excel 2016?

Yes, Excel 2016 supports combo charts. However, it requires more manual steps than Excel 365, which has pre-built Pareto templates. Follow the manual combo chart method for Excel 2016.

How do I update the chart when data changes?

If you used proper formulas with absolute references, the cumulative percentage will update automatically. The chart will refresh once you modify the source data values.