In this article:

How to Make a Pareto Chart in Google Sheets (2024 Update)

May 8, 2024

How to Make a Pareto Chart in Google Sheets

A Pareto chart combines a bar chart and a line graph, displaying categories and their cumulative frequencies to help identify the most significant factors in a dataset.

Here's how to make a Pareto chart in Google Sheets:

Step 1: Collect and Prepare Your Data

Suppose you have a factory that produces widgets. Over the past month, there have been reports of defects in the products. We have collected data on the type of defects and their frequency. 

Our goal is to create a Pareto chart to identify the most common defects that will help us prioritize which issues to address first.

First, you need a table with two columns: one for the Defect Type and another for Frequency. Here's an example dataset:

pareto chart google sheets

Step 2: Summarize the Data

Use the QUERY function to summarize and sort your data. In a new cell, enter:

=QUERY(A1:B6,"select A, sum(B) where B is not null group by A order by sum(B) desc", 1)

This formula groups your defects by type, sums up the frequencies, and sorts them in descending order.

how to make a pareto chart in google sheets

Step 3: Calculate Cumulative Percentage

Next to your sorted data, calculate the cumulative percentage. In the first cell of the new column (assuming your sorted data is in D1:E6), enter:


Drag this formula down to apply it to the rest of the cells in the column by clicking on the blue circle at the bottom-right corner of the cell. 

pareto chart in google sheets
google sheets pareto chart

Then, format these cells as percentages by clicking on the "Format as Percent" button in the toolbar.

You can also go to Format > Number > Percent.

Step 4: Create the Cumulative Sum

In another column, calculate the cumulative sum of percentages. In the first cell under this new column, use:


Drag down the fill handle to apply it to the rest of the column. Again, format these cells as percentages.

Step 5: Insert the Combo Chart

Select and organize your data: "Defect Type", "Frequency", and the "Cumulative Percentage" columns. 

Click on the Insert menu, then select Chart.

 In the Chart Editor that appears on the right, change the Chart Type to "Combo Chart".

Step 6: Customize the Chart

In the Chart Editor under the Customize tab, adjust the Series option. Make sure the "Cumulative Percentage" data series is set to use the right axis. This will display your frequencies as bars and the cumulative percentage as a line on the same chart.

Your Pareto chart is now ready. You should see the defects ranked by frequency with a cumulative percentage line indicating the proportion of defects accounted for as you move down the list. 

This visual representation will help you easily identify which defects are most frequent and, therefore, where efforts to improve quality should be focused.

We hope that this article has helped you and given you a better understanding of how to make a Pareto chart in Google Sheets. If you enjoyed this article, you might also like our articles on how to create a map in Google Sheets and what to do if your Google Sheets tabs disappeared.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->