How to Make a Pareto Chart in Excel with ChatGPT

Creating a Pareto chart in Excel can feel like a chore, involving sorting data, calculating cumulative totals, and wrestling with combo chart settings. But by pairing the analytical power of ChatGPT with Excel’s visualization tools, you can handle the complicated data prep in seconds, letting you get straight to the insights. This article will show you exactly how to use ChatGPT to prepare your data and then build a polished Pareto chart in Excel in just a few clicks.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Pareto Chart, Anyway?

A Pareto chart is a simple but powerful tool that combines a bar chart and a line graph to identify the most significant factors in a dataset. It’s based on the Pareto Principle, often called the “80/20 Rule,” which states that for many events, roughly 80% of the effects come from 20% of the causes.

In business, you see this everywhere:

• Around 80% of your revenue comes from 20% of your customers.
• Approximately 80% of customer complaints are related to 20% of your product issues.
• A whopping 80% of a software’s bugs can be traced to 20% of its code.

A Pareto chart visualizes this concept perfectly. The bars represent individual values (like the number of complaints for each category) sorted from largest to smallest. The line represents the cumulative percentage of the total. By looking at this chart, you can quickly spot the "vital few" from the "trivial many," helping you focus your energy where it will have the greatest impact.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Data Prep: The Old Way vs. The ChatGPT Way

Traditionally, making a Pareto chart in Excel required several manual steps that were tedious and prone to error:

1. Manually sort your data from highest to lowest.
2. Create a new column to calculate the cumulative total for each category.
3. Create another column to calculate the cumulative percentage.
4. Build a combo chart with bars for the values and a line for the percentages.
5. Fiddle with the secondary axis to make sure the percentage line maxes out at 100%.

This process felt more like a math assignment than a useful analysis. Today, you can offload nearly all of that data wrangling to ChatGPT. Instead of writing formulas, you can write a simple sentence, letting AI handle the sorting and calculations. This not only saves a ton of time, but also reduces the chance of making a small mistake that throws off your entire chart.

Step 1: Get Your Data Ready

Before you turn to ChatGPT, you need to know what data to give it. A Pareto analysis works best with data structured in two simple columns:

• Category: The cause, source, or type of issue you're measuring (e.g., Complaint Type, Software Bug, Onboarding Step).
• Frequency: The number of occurrences, count, or value associated with each category (e.g., Number of Customer Tickets, Bug Reports, User Drop-offs).

Let's use a common example: customer service complaints. Imagine you've tracked the reason for every support ticket over the last month. Your raw data might look something like this:

• Poor Packaging: 55
• Slow Shipping: 110
• Wrong Item Sent: 25
• Product Defective: 80
• Missing Parts: 15
• Website Usability: 8

This is all you need. You don't have to sort it or do anything else just yet. We'll let ChatGPT handle that.

Step 2: Use ChatGPT to Process Your Data

Now for the fun part. You’re going to give ChatGPT your raw data and ask it to prepare everything you need for the Pareto chart. It's all about the prompt.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Writing the Perfect Prompt

You want to be clear and specific with your instructions. Tell ChatGPT exactly what you have, what you need, and how you want the output formatted. Here’s a great template you can use:

I have the following data on customer service complaints. I want to create a Pareto chart in Excel to analyze them.

Could you please prepare a table that includes:

Please format the entire output as a table that I can easily copy and paste directly into an Excel spreadsheet. Here is my data:

Review and Copy the Output

After you submit the prompt, ChatGPT will generate a perfectly formatted table that includes everything you asked for. It should look something like this:

This table is ready to go. The data is sorted, and the cumulative total and percentage columns are already calculated. Just click the "copy" button in the ChatGPT interface and head over to Excel.

Step 3: Build the Pareto Chart in Excel

With the hard work done, creating the actual chart in Excel is straightforward.

1. Paste Your Data: Open a new Excel sheet and paste the table you copied from ChatGPT into cell A1. You should have four clean columns of data.
2. Select the Data for Charting: This is a key step. You only need to select three of the columns:
3. Insert a Combo Chart: With your data selected, go to the Insert tab on the Excel ribbon. Click on Recommended Charts or go directly to the Combo Chart icon (it looks like a combined bar and line chart). Select the Clustered Column - Line on Secondary Axis option. Excel will immediately generate a draft of your Pareto chart.
4. Format the Secondary Axis: This is the most important formatting step to make your chart accurate. The line chart for the cumulative percentage needs its own axis on the right side, and it must top out at 100%.

Once you hit Enter, the line graph will recalibrate, reaching the top of the chart at the 100% mark. Your chart is now a true Pareto chart.

1. Add Titles and Clean Up: Your chart is functional, but let's make it easy to read.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Analyze Your Pareto Chart

Now that your chart is built, how do you use it? It's simple:

• Identify the "Vital Few": Look at the tallest bars on the left. These are your biggest problems or opportunities. In our example, "Slow Shipping" and "Product Defective" are by far the most frequent complaints.
• Follow the 80/20 Line: Trace the orange cumulative percentage line. Find the point where it crosses the 80% mark. Look down at the bars to the left of this point. These are the categories that make up approximately 80% of your total issues.

In our example, the line crosses 80% around the "Poor Packaging" category. This means that if you fix just three things - Slow Shipping, Product Defects, and Poor Packaging - you can eliminate over 80% of your customer complaints. That’s an incredibly clear and actionable insight that tells you exactly where to focus your resources.

Final Thoughts

By leveraging ChatGPT for data preparation, you can transform the task of creating a Pareto chart in Excel from a manual, multi-step process into a fast and simple one. This allows you to spend less time struggling with formulas and more time interpreting the data to make smarter business decisions.

While this workflow is a huge improvement, jumping between an AI chat and a spreadsheet still involves a few manual steps. To eliminate these steps entirely, we created Graphed. It allows you to connect your data sources directly and ask for the charts you need in plain English — generating a live, interactive visualization instantly. Instead of copying and pasting, you could simply ask, “Show me a Pareto chart of customer complaint types,” and get your answer in seconds.