How to Make a Pareto Chart in Google Sheets (Static & Interactive)

As a business owner or manager, we are sure you’re always looking for ways to increase and boost your organization’s performance. One of the best ways to achieve this goal is using the Pareto chart, a powerful visualization tool offered by Google Sheets, to help users identify the most critical factors impacting their success.

What’s special about Pareto charts is that they are perfect for visualizing cause-effect phenomena. This explains why organizations of different sizes use them to identify areas of their business that need improvement.

By leveraging the Pareto chart in Google Sheets, you can focus on the most important issues relating to your business and achieve the desired result.

Today’s guide will give you a comprehensive overview of all you need to know about the Pareto chart in Google Sheets. We will start with what a Pareto chart is, how and why you should use them, and how to create and customize a Pareto chart in Google Sheets.

Understanding the dynamics of a Pareto chart in Google Sheets

In simple terms, a Pareto chart is a powerful visualization tool that combines a bar chart and a line chart.

For those just hearing about this type of chart for the first time, it would interest you to know that a Pareto chart is based on the Pareto principle, which clearly states that in most cases, around 80% of the effects are triggered by 20% of the causes.

This simply means that if you can resolve 20% of the identified causes, you’ll be able to resolve most of the problem.

Unlike line chart and bar chart, a Pareto chart in Google Sheets uses the frequency of distribution of a variable and the cumulative percentage of the same frequency to help you visualize which categories of the variable (causes) is responsible for the large portion of the effect.

Since a Pareto chart is a combo of a bar chart and a line chart, it’s easy to see why it has bars arranged in descending order, depicting the frequencies of different categories. Besides the bars, a Pareto chart also features a line chart depicting the cumulative percentage of the total occurrences in the event. Simply put, the line chart depicts how much (in percentage) each cause contributes to the total effect.

If you’re dealing with a Pareto chart for the first time, you may not understand it at first glance, except, of course, you know how to read it correctly. Thankfully, we will show you how to read a Pareto chart in today’s guide.

Reading a Pareto chart: Everything you should know

As we mentioned right from the get-go, a Pareto chart features a bar chart and a line chart. While the horizontal axis of the Pareto chart (the x-axis) features data representing the different categories or causes, the vertical axis of the bar chart typically sits on the left side of the chart. Each bar on the vertical axis represents the frequency of occurrence of a given category.

Let us also quickly add that a Pareto chart has a secondary vertical axis on the right side. It’s important to add that this axis is for the line chart and represents the cumulative percentage of the total number of occurrences for each category.

So in simple terms, it means that for a category, there is a point on the line chart that clearly depicts how much percentage of the total occurrence that category accounts for. For instance, if a particular category of the line chart shows a cumulative percentage of 20%, it means that 20% of the total number of occurrences is due to that category.

We are sure this sounds a bit confusing now, but as we delve deeper into this guide’s practical aspects, you’ll better understand how to read a Pareto chart.

Making a Pareto chart in Google Sheets

Having given you some insight into what a Pareto chart is and how you can read it, we now want to take things further by showing you how to make a Pareto chart in Google Sheets. Whether you’re a complete novice or have some experience with Google Sheets, our guide will simplify the entire process.

For this tutorial, we will use a data set that features the frequency of different defects for a car part production line.

We will use this data set to create a Pareto chart in Google Sheets.

Using the data in the above spreadsheet, we will make a Pareto chart in Google Sheets. The goal is to determine which defects are the most significant and account for the most losses.

Step 1: Prepare the data source

Before we can make a Pareto chart in Google Sheets, we first need to prepare our data. The reason for doing this is so Google Sheets can easily interpret our data and convert it to a Pareto chart.

To prepare our data source, we need to summarize the causes or defects data. Here is how to approach that:

If you look closely at our dataset, you’ll notice it features individual counts for each type of defect. To this end, we need to summarize the data set and group the counts by the type of defects.

For a smooth and seamless experience, we will use the QUERY formula in cell D1. Here is what the formula looks like:

=QUERY(A1:B, “Select A,SUM(B) where B is not null group by A”)

The formula above leverages the QUERY function to select the dataset in columns A and B and then proceeds to group column A, which represents the defect type. Finally, it finds the sum of column B (frequency) for each group.

Let’s show you how to apply the QUERY function to our data set.

To apply the QUERY function to our data set, we first need to select the cell where we want the result displayed. For our guide, we will use cell D1, so we will go ahead and do that.

Type the following formula in the formula bar:

=QUERY(A1:B, “Select A,SUM(B) Where B is not null group by A”)

Finally, click enter and watch the magic unfold.

After typing in the formula, you should have a spreadsheet that looks like this:

Since we took the opportunity to include all the data featured in columns A and B, it means that any future additions to our spreadsheet will automatically be updated in the query result.

Note: When using the QUERY function, you must ensure that the column opposite the cell you intend to generate the result is blank. This way, the result of the function will spill over both columns without returning an error.

Step 1.3: Sort the data

After applying the QUERY function to our data set, we need to sort the data set by the sum of Frequency. The reason we want to do this is so the bars in our Pareto chart appear in descending order of height.

To sort the data, we must update the earlier QUERY function we used. Here is a formula we will use to sort the dataset:

=QUERY(A1:B,”Select A,SUM(B) where B is not null group by A order by SUM(B) DESC”)

You can see that the above formula looks very much like the QUERY function. The only difference is that we have now added an “order by” clause, which is intended to sort our dataset.

Applying the updated QUERY Function to our data set

To sort our data set, we need to apply the updated QUERY function to cell D1, so navigate to cell D1.
Now, head to the formula bar and type in the following formula:

=QUERY(A1:B,”Select A,SUM(B) where B is not null group by A order by SUM(B) DESC”)

Finally, hit enter and wait for Google Sheets to make the changes.

Here is what our data should look like after sorting by sum Frequency:

Step 1.3: Include a column for the cumulative percentage

Having sorted our data by applying the sum frequency, we now want to jump into specifics. This time, we want to compute the cumulative percentage. The objective of this is to create a line chart path.

To compute the cumulative percentage for our data set, we will use column F. Also, we need to create a header label that reads “Cumulative Percentage. After that, we will apply the following formula to cell F2:

=SUM($E$2:E2)/SUM($E$2:$E)

Here is how to apply this formula to compute the cumulative percentage of our data set:

Since we will use cell F2 to compute the cumulative percentage, click on the cell.

With that done, type in the below formula to the formula bar.

=SUM($E$2:E2)/SUM($E$2:$E)

Finally, hit enter and wait for the changes to be effected.

If you followed the steps exactly as we showed you above, you should have something that looks like this:

From the image above, you can see that the computation for the cumulative percentage we executed is only for cell F2. So we need to do the same for other cells beneath that.

But instead of going at it manually, we can use the Google Sheets auto-fill option to simplify our work. Here is a short video demo showing you how to approach that:

Looking at the video above, you’ll notice that the values generated in column F aren’t shown in percentage. So we need to change that. To convert these values to percentages, we need to select all the values in column F, click on the Format option, select Numbers and then choose Percent.

The video below simplifies the entire process:

If you did everything right, here is the result you should get:

If your spreadsheet looks like the image above, you are now ready to make a Pareto chart in Google Sheets, and we will show you how to do that shortly.

Step 2: Plotting your Pareto chart

Creating a Pareto chart in Google Sheets is a walk in the park. And as we mentioned right from the get-go, you don’t need a ton of experience to do this.

To start with, you need to select your data. And no, we don’t mean the entire spreadsheet. For our Pareto chart, we will work with data in Cell D1:F6. So go ahead and highlight the data in those cells.

With your data highlighted, head to the Insert menu and select Chart.

If you did everything exactly as we showed you, Google Sheets would automatically create a Pareto chart in your spreadsheet. Here, take a look at what our Pareto chart looks like:

Step 3: Customizing the Pareto chart

With our Pareto chart now created, we need to customize it, so it visualizes our data even better. Here is how to customize your Pareto chart.

Head to the chart editor on the extreme right of your spreadsheet and select the option for Customize.

After clicking on the Customize tab, you’ll find different options. From the options available, select Series.

Selecting the Series option will present you with a dropdown menu with various customization options. Here is an image showing you all the available options.

From the dropdown menu underneath Series, select the option for Cumulative Percentage.

Change the Axis option to Right Axis. By doing this, the Cumulative percentage will now be displayed on the right axis of the chart.

After applying the changes as shown in the video above, you should notice some changes in the Pareto chart we created earlier. Here is what our Pareto chart now looks like after switching the Axis to the Right axis.

A look at the Pareto chart above clearly shows that Headlamp and Brake fluid defects account for most of the total defects. This means that by addressing these two defects, you’ll be able to resolve most of the production defect issues you have.

Conclusion

If you have always wanted to create a Pareto chart in Google Sheets but don’t know how to go about it, we are sure today’s guide has made it easy for you to do it.

In today’s Google Sheets tutorial, we gave you a detailed run down, including pictures and images of how to create a Pareto chart in Google Sheets.

We hope you found this guide helpful.