How to Make a Pareto Chart in Google Sheets with AI

A Pareto chart is one of the most powerful tools for finding the "vital few" issues causing the most problems. It visualizes the 80/20 rule, showing you exactly where to focus your energy for the biggest impact. This article will walk you through exactly how to create a Pareto chart in Google Sheets, step-by-step, and explore how AI can streamline the process.

What Exactly Is a Pareto Chart?

The Pareto chart is named after Vilfredo Pareto, an economist who noticed that about 80% of the land in Italy was owned by 20% of the population. This principle, often called the 80/20 rule, appears everywhere:

• 80% of sales come from 20% of customers.

• 80% of customer complaints relate to 20% of the product's issues.

• 80% of software bugs are caused by 20% of the code.

A Pareto chart is a special type of chart that combines both a bar chart and a line graph. Here’s what each part does:

• The bars represent individual problems or causes, arranged in descending order from highest to lowest frequency.

• The line represents the cumulative percentage of the total, showing how the individual problems add up.

By putting these two together, you can quickly identify the select few categories (the "vital few") that contribute to the majority of the total (the "trivial many"). This helps you stop wasting time on minor issues and start solving the problems that truly matter.

Step 1: Get Your Data Ready for Analysis

Before you can build an effective chart, you need clean and simple data. For a Pareto chart, your data structure in Google Sheets should consist of two columns:

1. Category: The qualitative cause, reason, or source of an issue (e.g., "Login Problems," "Billing Issues").

2. Frequency: The quantitative count or measurement associated with that category (e.g., the number of support tickets for each issue).

Let's imagine you run a customer support team and want to figure out what's driving most of the support tickets. Your raw data in Google Sheets might look something like this:

Example Dataset: Weekly Customer Support Tickets

This is a good start, but it's not ready for charting just yet. We need to add a few calculations to make it work.

Step 2: Manually Building a Pareto Chart in Google Sheets

While we eagerly await a one-click "Make Pareto Chart" button, the process in Google Sheets is currently manual. But don't worry - if you follow these steps carefully, you'll have a professional-looking chart in minutes.

Part 1: Sort Your Data

The first rule of a Pareto chart is that the bars must be in descending order. Select your entire data range (A1:B7 in our example), go to Data > Sort range > Advanced range sorting options. Make sure "Data has header row" is checked, then sort by "Number of Tickets" (or whatever you've named your frequency column) in Z → A order (descending).

Your table should now look like this:

Part 2: Calculate Cumulative Totals

Next, we need to create columns for the cumulative frequency (the running total) and the cumulative percentage.

To calculate the cumulative frequency:

1. In cell C1, add the header "Cumulative Count".

2. In cell C2, reference the first frequency value. Enter:=B2

3. In cell C3, add the current row's count to the cumulative count above. Enter:=C2+B3

4. Drag the fill handle from C3 down to the last row of your data to apply the formula.

To calculate the cumulative percentage:

1. In cell D1, add the header "Cumulative Percentage".

2. Calculate the total number of tickets in an empty cell, for example in B8:=SUM(B2:B7)In our example, total is 259.

3. In cell D2, compute the percentage for the first cumulative count:=C2/$B$8(Note the dollar signs to lock the total cell reference).

4. Drag this formula down through all rows.

5. Select column D, then format as a percentage via Format > Number > Percent.

Your completed table should look like this:

Part 3: Create and Configure the Combo Chart

Now for the fun part! It's time to build the visual.

1. Select the columns for the chart: A (Cause), B (Number of Tickets), and D (Cumulative Percentage) by holding down Ctrl (Cmd on Mac) while clicking each column letter.

2. Go to Insert > Chart. Google Sheets might select a default chart type.

3. In the Chart Editor pane, go to the Setup tab.

4. Change the Chart type to Combo chart (look for the icon with bars and a line).

5. Switch to the Customize tab.

6. Under Series, you’ll see options for each data series:

   • For "Number of Tickets" (bars), confirm its type is "Column" and that its axis is "Left axis".

   • For "Cumulative Percentage" (line), change its type to "Line" and set its axis to "Right axis".

7. Optionally, adjust axis titles, chart title, and colors for clarity. Title your chart "Pareto Analysis of Customer Support Issues".

After these steps, you'll have a clear, professional Pareto chart in your Google Sheet.

Can You Use AI to Create a Pareto Chart in Google Sheets?

This is where things get interesting. The promise of AI is to automate tedious workflows, and creating a Pareto chart involves quite a bit of manual data manipulation. So can AI do it for you within Google Sheets? The answer is... not directly in a single step.

Google has integrated AI features like the "Explore" button, which can analyze your data and suggest charts. However, if you select your raw data and ask it to "create a Pareto chart," it will likely fail. Creating a proper Pareto chart requires intermediate steps: sorting the data, calculating running totals, then generating a combo chart with two axes.

The AI in Google Sheets isn't (yet) a natural language data analyst that can perform multi-step procedural tasks. You can't just give it raw inputs and expect a complex, derivative output. This kind of intelligence—understanding context and process—is exactly what newer, specialized BI tools are designed for.

How to Interpret Your New Pareto Chart

Congratulations, you've built the chart! Now, how do you use it?

1. Look at the Line: Find the 80% mark on the right-axis.

2. Follow It Over: Trace a horizontal line from the 80% point to where it intersects the cumulative percentage line.

3. Look Down at the Bars: From the intersection point, look down at the bars. Those to the left are your "vital few"—categories creating the majority of issues.

In our example, the cumulative percentage line crosses 80% at "How-to Question." This indicates that just three categories—Billing Issues, Login Problems, and How-to Questions—are responsible for over 80% of tickets. Your team can then prioritize fixing these top issues for maximum impact.

Final Thoughts

Creating a Pareto chart in Google Sheets involves manual steps but provides clear priorities once complete. It transforms raw data into visual insights that enable smarter decision-making.

While the internal AI tools haven't yet automated multi-step analysis like this, the process highlights the amount of manual effort often required. At Graphed, we focus on delivering a seamless, end-to-end experience: connect your data and simply ask, "Create a Pareto chart of our Zendesk ticket types for the last quarter." Our system builds the chart automatically, allowing you to skip the manual setup and focus on data-driven decisions.