How to Make a Pareto Chart in Excel

A Pareto chart is a simple but powerful tool for finding the biggest opportunities for improvement in your business. Grounded in the 80/20 rule, it helps you visually separate the "vital few" problems from the "trivial many." This tutorial will walk you through exactly how to prepare your data and create a professional-looking Pareto chart in Microsoft Excel.

What Exactly is a Pareto Chart?

A Pareto chart combines a bar chart and a line graph into one visualization. The bars represent individual values or frequencies (like the number of customer complaints per category), sorted in descending order from left to right. The line represents the cumulative percentage of the total. Its purpose is to highlight the most significant factors in a dataset.

The chart is named after Vilfredo Pareto, an economist who observed that about 80% of the land in Italy was owned by 20% of the population. This concept, now known as the "Pareto principle" or the "80/20 rule," applies to a surprising number of business scenarios:

• Roughly 80% of your sales revenue comes from 20% of your customers.
• Roughly 80% of your website traffic comes from 20% of your articles.
• Roughly 80% of software bugs are caused by 20% of the code defects.

By plotting your data in a Pareto chart, you can immediately see which sources are contributing most to the overall effect. This makes it incredibly easy to prioritize your efforts. Instead of trying to fix a dozen small issues, you can focus on the one or two big ones that will make the most impact.

Step 1: Set Up Your Data in Excel

Before you can make the chart, you need to structure your data correctly. A Pareto chart requires two columns of data:

1. A column listing the Categories you are measuring (e.g., Complaint Type, Product SKU, Traffic Source).
2. A column with the corresponding Numerical Value for each category (e.g., Number of Complaints, Units Sold, Website Sessions).

Let's use a real-world example. Imagine you run an e-commerce store and want to analyze customer support tickets to reduce complaints. You’ve collected data on the reasons customers have contacted support over the last month.

Your raw data in Excel might look something like this:

There are two methods to create a Pareto chart from this data. The first is a fast, automated option available in newer versions of Excel. The second is a manual method that works in all versions and gives you a better understanding of how the chart works.

Method 1: Using Excel’s Built-in Pareto Chart (The Easy Way)

If you're using Excel for Microsoft 365, Excel 2021, Excel 2019, or Excel 2016, you’re in luck. There’s a built-in feature that creates a Pareto chart in just a few clicks.

1. Select Your Data

Click and drag your mouse to highlight the cells containing your categories and their counts, including the headers. In our example, that would be cells A1:B7.

2. Insert the Chart

Go to the Insert tab on the Ribbon. In the Charts group, click on the small icon labeled “Insert Statistic Chart.”

3. Choose the Pareto Chart

A dropdown menu will appear. Under the Histogram section, select the Pareto chart option.

That's it! Excel automatically sorts your data from largest to smallest, calculates the cumulative percentages, and generates a formatted Pareto chart for you. It groups the smaller categories into an "Other" bin to keep the chart clean.

Method 2: Building a Pareto Chart Manually (For All Excel Versions)

If you have an older version of Excel, or if you want more control over how your chart is built, you can create one manually. This process has a few more steps but gives you a deeper understanding of the components.

We'll start with the same raw data as before.

1. Sort Your Data

First, you need to sort your data by the numerical value column in descending order (largest to smallest). To do this:

• Highlight your entire data range (e.g., A1:B7).
• Go to the Data tab and click the Sort button.
• In the Sort dialog box, set "Sort by" to "Number of Tickets" and "Order" to "Largest to Smallest."
• Click OK.

2. Calculate the Cumulative Percentage

Next, you need to add a helper column to calculate the running total as a percentage. Title a new column "Cumulative %". In the first cell of this column (C2), type the following formula and press Enter:

=SUM($B$2:B2)/SUM($B$2:$B$7)

Let's quickly break this down:

• SUM($B$2:B2): This part calculates the running total. The first $B$2 is an absolute reference (locked with $ signs), while B2 is relative. When you drag the formula down, the second reference expands (e.g., to $B$2:B3, $B$2:B4), creating a cumulative sum.
• SUM($B$2:$B$7): This calculates the grand total of all tickets. It's locked as an absolute reference so it doesn't change when you drag the formula down.

Now, click on cell C2, grab the fill handle at the bottom-right corner, and drag it down to the last row of your data. Format column C as a Percentage to make it easier to read.

3. Create a Combo Chart

Now we’ll build the visual itself.

1. Select all three columns of data, including headers (e.g., A1:C7).
2. Go to the Insert tab and find the Charts group.
3. Click the icon for Insert Combo Chart.

Select the option called Clustered Column - Line on Secondary Axis. Before clicking OK, you need to configure the chart data series:

• The "Number of Tickets" series should be a Clustered Column.
• The "Cumulative %" series should be a Line, and you must check the box for Secondary Axis.

Click OK. Excel will generate a basic combo chart.

4. Tweak the Secondary Axis

You’ll notice the cumulative line doesn't extend to the top of the chart—the secondary axis (on the right) might go up to 120%. Let's fix that:

• Right-click on the secondary vertical axis numbers.
• Choose Format Axis.
• In the Format Axis pane, go to Bounds under Axis Options.
• Change the Maximum to 1.0 and press Enter.

The secondary axis will now correctly cap at 100%, and your cumulative percentage line will stretch across the top of the chart, completing your Pareto analysis.

Step 3: Customize Your Chart for Clarity

Your chart is functional, but a few final touches can make it much more professional and easier for your team to understand.

• Add a Clear Title: Click on the chart title and rename it to something like "Analysis of Customer Complaints - Last 30 Days."
• Label Your Axes: Click the chart, then click the green "+" icon that appears on the top right. Check "Axis Titles" and edit accordingly (e.g., "Reason for Contact," "Number of Tickets," "Cumulative Percentage").
• Adjust the Bar Gap: Right-click any of the blue bars, choose Format Data Series, and reduce Gap Width to 0% or 10% for touching bars.

After these adjustments, your finished, professional Pareto chart is ready for your report or presentation:

From one look at this chart, you can clearly communicate that "Late Delivery" and "Incorrect Item Received" account for nearly 75% of all support tickets. This gives your team a clear, data-driven priority for where to focus their problem-solving efforts.

Final Thoughts

Building a Pareto chart in Excel is an excellent way to turn raw numbers into actionable business insights. Whether you use the speedy built-in tool or the more involved manual method, it provides a clear roadmap for prioritizing work and maximizing your impact by focusing on the most significant issues.

While mastering charts in spreadsheets is a valuable skill, it's often a detour from your main goal: getting answers from your data. The process of exporting CSVs, cleaning them, building pivot tables, and formatting charts still takes time away from acting on insights. This is where we designed Graphed to help. By connecting directly to your tools like Shopify, Google Analytics, or Salesforce, we allow you to create similar data visualizations and dashboards just by describing what you need in plain English. There’s no more sorting data or configuring combo charts—just real-time answers to guide your next move.