back arrowAll articles

How to Make a Pareto Chart in Google Sheets

Last Updated: Wed Nov 13 2024

In 7 simple steps, this guide will show you how to make a Pareto chart in Google Sheets.

Visualizing the most significant factors in your dataset is straightforward with this method.

So let’s dive in and create a Pareto chart in Google Sheets!

Steps:

  • Collect and Prepare Your Data
  • Use the QUERY function to summarize and sort your data
  • Calculate Cumulative Frequency
  • Calculate Cumulative Percentage
  • Insert the Combo Chart
  • Customize the Chart
  • Interpret the Pareto Chart

Step 1: Collect and Prepare Your Data

First, gather your data in a Google Sheet.

Suppose you manage a retail store and have collected data on customer complaints over the past month.

Your table might have two columns: “Complaint Type” and ‘Frequency’.

prepared data in google sheet to make pareto chart

Step 2: Use the QUERY function to summarize and sort your data

Enter the following formula in a new cell (e.g., D1):

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

red arrow pointing to QUERY function

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

red rectangle highlights “Sum of Frequencies” in column E

Step 3: Calculate Cumulative Frequency

Next to your sorted data, calculate the cumulative frequency. In the first cell of the new column (e.g., F2), enter:

=SUM($E$2:E2)

red arrow pointing to SUM function

Drag this formula down to apply it to the rest of the column. This will give you the running total of complaint frequencies.

red rectangle highlights Cumulative Frequency in column F

Step 4: Calculate Cumulative Percentage

To calculate the cumulative percentage, add another column. In the first cell of this new column (e.g., G2), enter:

=F2/SUM($F$2:$F$6)

red arrow pointing to a formula for calculate cumulative percentage

Format these cells as percentages by selecting them and clicking on the "Format as Percent" button in the toolbar or navigating to Format > Number > Percent.

red arrow pointing to percent icon in toolbar

Step 5: Insert the Combo Chart

Now, it's time to create the Pareto chart. Highlight your data, including the “Complaint Type”, ‘Frequency’ and “Cumulative Percentage” columns.

column D, f and G are selected for insert Combo Chart

Click on ‘Insert’ from the top menu.

red arrow pointing to ‘Insert’

Select ‘Chart’.

red arrow pointing to ‘Chart’

In the Chart Editor, change the Chart Type to “Combo Chart”.

red arrow pointing to “Combo Chart” in chart Type

Step 6: Customize the Chart

To enhance the readability of your chart, make some customizations:

In the Chart Editor under the ‘Customize’ tab, adjust the ‘Series’ option.

Ensure 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.

For those who find visual data representation helpful, similar techniques can be used when learning how to create a heat map in Google Sheets, where color gradients can highlight patterns or outliers in your data.

red arrow pointing to “Right axis” for ‘Cumulative Percentage’ in ‘Series’ option

Step 7: Interpret the Pareto Chart

Your Pareto chart is now ready! The complaints are ranked by frequency with a cumulative percentage line indicating the proportion of complaints accounted for as you move down the list.

This visual representation helps you identify the most frequent complaints and prioritize which issues to address first.

pareto chart in google sheet

Creating a Pareto chart in Google Sheets is straightforward with these steps.

By visualizing your data in this manner, you can easily identify and focus on the most significant factors impacting your processes.

Back

Finished!

Vector