How to Make a Pareto Chart in Google Sheets with ChatGPT

A Pareto chart is a powerful way to instantly see which problems are causing the most trouble so you can focus your energy where it counts. Creating one in Google Sheets isn't straightforward, but by using ChatGPT as your formula-writing assistant, you can build a professional-looking chart without getting lost in spreadsheet functions. This tutorial will walk you through setting up your data, prompting ChatGPT for the right formulas, and building and customizing your Pareto chart in Google Sheets, step by step.

What is a Pareto Chart, and When Should You Use One?

A Pareto chart is based on the Pareto Principle, also known as the 80/20 rule. In business, this principle often means that about 80% of your results come from just 20% of your efforts. A Pareto chart puts this idea into a visual format, combining a bar chart with a line graph to quickly identify those critical few inputs that are having the biggest impact on your outcomes.

The chart displays bars in descending order of frequency or impact, allowing you to instantly see the most significant categories. Overlaid is a line graph representing the cumulative percentage. This lets you quickly determine how many categories you need to address to solve, say, 80% of the total problem.

This type of chart is incredibly useful in various business scenarios:

Sales Analysis: Identify the 20% of products that generate 80% of your revenue.
Customer Service: Find the most common customer complaints that make up 80% of your support tickets.
Project Management: Pinpoint the main causes of project delays or budget overruns.
Marketing: Analyze which marketing channels are driving the majority of your website traffic or conversions.
Quality Control: Determine the most frequent types of product defects on a manufacturing line.

Essentially, if you have a list of problems, causes, or categories and you want to prioritize them by impact, a Pareto chart is the perfect tool for the job.

Step 1: Get Your Data Ready for Analysis

Before you can build the chart, your data needs to be organized in a simple, structured way. A Pareto chart is built from a summary of your data, not the raw, individual records. What you need are two columns:

A column for the Cause or Category you are measuring.
A column for the corresponding Frequency, Count, or Impact (e.g., number of incidents, revenue, time spent).

Let's use a common example: a customer support manager wants to analyze the main reasons for support tickets. After reviewing the tickets for a month, they’ve summarized their findings into the following table in Google Sheets.

Create a simple table with two columns, just like this:

Initial Data Table

Column A: Complaint TypeColumn B: Number of Incidents

Shipping Delay: 112
Damaged Product: 54
Wrong Item Sent: 28
Website Technical Issue: 15
Payment Processing Error: 9
Return Policy Question: 6
Other: 3

Your sheet should look clean, with clear headers in the first row. The order of the rows doesn't matter at this stage, we will sort them in the next step.

Step 2: Use ChatGPT to Create Your Pareto Columns

Once your raw categories and counts are in Google Sheets, you need to add one more column to make the Pareto chart work: the cumulative percentage. This calculation can be tricky, involving running totals and absolute references. This is where ChatGPT becomes a massive time-saver.

Sorting Your Data First

The first and most important rule of building a Pareto chart is that your data must be sorted by the frequency column in descending order (highest to lowest). This arranges the bars of your chart from tallest to shortest, visually emphasizing the most significant categories.

This step is simple enough to do manually in Google Sheets:

Highlight the entire data range, including your headers (e.g., cell A1 to B8).
Go to the menu and click Data > Sort range > Advanced range sorting options.
Check the box that says "Data has header row."
In the "Sort by" dropdown, select your frequency column (in our example, "Number of Incidents").
Make sure the order is set to Z → A (descending).
Click Sort.

Your table should now be ordered with "Shipping Delay" at the top.

Generating the Cumulative Percentage Formula

With your data sorted, you now need to calculate the cumulative percentage. This shows the running total of the frequencies as a percentage of the total. Instead of manually architecting a complex formula, you can ask ChatGPT to do it for you.

Open ChatGPT and use a clear, concise prompt. The more specific you are, the better the result. Provide context about your sheet, including column letters and row numbers.

Here's a great prompt to use:

ChatGPT will likely respond with the correct formula and an explanation of how it works. The formula should look something like this:

Understanding the Formula

SUM($B$2:B2): This part calculates a "running total." The first $B$2 uses a dollar sign to lock the starting point of the sum. The second B2 (without a dollar sign on the row number) changes as you drag the formula down. So, in cell C3 it becomes SUM($B$2:B3), in C4 it's SUM($B$2:B4), and so on.
/SUM($B$2:$B$8): This part calculates the grand total of all incidents. Both references use dollar signs to lock the range, ensuring that every row divides by the same total amount.

Now, go back to your Google Sheet:

In cell C1, add the header "Cumulative Percentage."
In cell C2, paste the formula you received from ChatGPT.
Press Enter. You'll see a value like 0.5022....
Format this column as a percentage by selecting column C and clicking the % icon in the toolbar.
Click on the small blue square in the bottom-right corner of cell C2 and drag it down to the last row of your data.

Your sheet should now have a third column that correctly calculates the cumulative percentage, with the last row showing 100%.

Step 3: Build the Combo Chart in Google Sheets

With all your data calculations complete, you are ready to build the chart itself. We'll use a Combo Chart type, which allows us to have both bars and a line on the same chart.

Select Your Data: Click and drag to highlight all three columns of data you want to visualize — from your first header down to your last data point (e.g., A1 to C8). Make sure you include the headers.
Insert the Chart: In the menu, go to Insert > Chart. Google Sheets will automatically insert a chart it thinks is best, which is usually not a Pareto chart. Don't worry, we'll fix this.
Choose the Right Chart Type: The Chart editor pane should appear on the right side of your screen. Under the "Setup" tab, find the "Chart type" dropdown. Scroll down and select the Combo chart.

At this point, you'll have a chart with two series displayed as bars. The final, critical piece is to tell Google Sheets to display the cumulative percentage as a line on a separate axis.

Step 4: Customize Your Chart for Clarity

Your combo chart is almost there, but a true Pareto chart visualizes the cumulative line on a secondary (right) axis that runs from 0% to 100%.

Putting the Line on the Right Axis

In the Chart editor, click on the Customize tab.
Expand the Series section.
You'll see a dropdown that lets you apply settings to a specific series. Select your "Cumulative Percentage" series.
Under its format options, you will see a dropdown for "Axis." Change it from "Left axis" to "Right axis."

Instantly, a new vertical axis will appear on the right side of your chart, and your cumulative percentage data will be re-plotted as a line against that axis. You've officially created the structure of a Pareto chart!

Final Formatting Touches

Now, let's polish it up for a professional look:

Chart & Axis Titles: Under Customize > Chart & axis titles, give your chart a clear title, like "Analysis of Customer Complaints." Add titles for your horizontal axis ("Complaint Type"), left vertical axis ("Number of Incidents"), and right vertical axis ("Cumulative Percentage").
Set the Right Axis to 100%: Your right axis might extend beyond 100%, which doesn’t make sense for percentages. To fix this, go to Customize > Vertical axis. In the dropdown at the top of the pane, switch to the Right Vertical Axis. Enter 0 for the Min value and 1 for the Max value (Google Sheets understands 1 as 100% in this context).
Adjust Colors and Labels: You can change the bar and line colors in the Series section to match your brand or improve contrast. Consider adding data labels to the line or bars to make the exact values easier to read without hovering.

Reading Your Pareto Chart for Actionable Insights

Your chart is now complete and ready to deliver insights. So, how do you read it?

Start on the right vertical axis (the percentage axis) and find the 80% mark.
Move your eyes horizontally to the left until you find where the 80% line intersects with your cumulative percentage line.
From that intersection point, look straight down to the bar chart.

The bars to the left of that vertical drop-down point are your "vital few" — the small number of categories that account for the vast majority of your problems. In our customer complaint example, you would quickly see that "Shipping Delay" and "Damaged Product" are likely the two key drivers that together account for close to 80% of all support tickets. This gives the team a clear directive: focus efforts on improving shipping and packaging, as that will solve the largest share of customer issues.

Final Thoughts

By breaking down the process into manageable steps, you can create a powerful Pareto chart in Google Sheets that turns complex data into clear, actionable priorities. Using ChatGPT to handle the formula creation is an excellent shortcut that takes the guesswork out of calculations, allowing you to concentrate on what the data is actually telling you.

Connecting your business data and creating insightful reports shouldn't have to involve endless tutorials and spreadsheet wrestling. At Graphed, we've designed a platform that automates this entire process. You can connect your marketing and sales data sources in seconds, and instead of manually building charts, you can simply ask for them using natural language. For instance, you could simply tell Graphed, "Show me my top 5 products by revenue this quarter as a Pareto chart," and get back an interactive dashboard that instantly highlights your vital few — saving you countless hours while providing deeper insights.