Pareto charts can be powerful visualization tools if you understand how to create and interpret them. They are excellent for visualizing cause-effect phenomena and numerous organizations use them to identify problematic areas or scope for growth.
In this tutorial, we will go over everything that you need to know about Pareto charts in Google Sheets. We will cover what they are, how and why they are used, and how to create and customize them in Google Sheets.
Note:Â At the time of this post, Google Sheets does not offer a separate template option for creating Pareto charts.
Related Reading: College Application Spreadsheet Template
 . However, you can easily create one by customizing a Combo chart.
Table of Contents
What is a Pareto Chart and What is it Used for?
Simply put, a Pareto chart is a combination of a bar chart and line chart. It is based on the Pareto principle which states that in most situations, around 80% of the effects are the result of 20% of the causes.
So, if you can address just 20% of the identified causes, you can more or less resolve a big portion of the problem.
A Pareto chart uses the frequency distribution of a variable, along with the cumulative percentage of this frequency distribution to help you clearly see which categories of the variable (causes) account for a large portion of the effect.
The Pareto chart consists of columns (or bars) arranged in descending order, depicting the frequencies of different categories (or causes). It also consists of a line chart that depicts the cumulative percentage of the total number of occurrences in the event. In other words, the line chart shows how much (in percentage) each cause accounts for the total effect.
At first glance, it may not be easy to understand the chart, unless you know how to read it correctly. So let us first help you do this in detail.
How to Read a Pareto Chart?
As mentioned before, the chart consists of a bar chart and a line chart.
The horizontal axis of the chart (the x-axis) represents the different categories or causes. The vertical axis for the bar chart is usually shown on the left side of the Pareto chart. Each bar represents the frequency of occurrence of the given category.
There’s also a secondary vertical axis, placed on the right side of the Pareto chart. This axis is for the line chart and it represents the cumulative percentage of the total number of occurrences (for each category).
So, for each category, a point on the line represents how much percentage of the total occurrences that category accounts for. This means that if for a certain category the line chart shows a cumulative percentage of 10%, it means that 10% of the total number of occurrences are due to that category.
Let us take an example chart to understand this better. The following Pareto chart shows different types of product defects, along with the frequency with which they occur and a cumulative percentage of their impact.
Each bar in this chart represents the frequency or number of occurrences of a particular defect. Each point on the line chart represents how much of the total occurrences each product defect accounts for (or the cumulative percentage of defects of each type).
Note that the bars are represented in descending order (from the tallest to the shortest).
As we can see, the cumulative percentage line is steep up until it reaches the Trimming defect. As long as this line is steep, the types of defects have a high cumulative effect. These are the defects that have the most impact, and as such, require the most attention.
When the line starts to flatten out, the defects in question don’t have much of a significant effect, and as such don’t require much attention.
How to Make a Pareto Chart in Google Sheets
Now let us see how to make a Pareto chart in Google Sheets. Let’s say we have the following frequency table consisting of the frequency of different types of defects for an imaginary textile production line:
Let’s prepare a Pareto chart to see which defects are most significant and account for most of the losses.
Step 1: Prepare the Source Data
Before creating the Pareto chart we need to prepare our data, so that Google Sheets can easily interpret it and convert it to a proper Pareto chart.
The first step in data preparation is to summarize the ‘causes’ or ‘defects’ data.
Step 1.1: Summarize the Data
Our dataset contains individual counts for each type of defect. So, we need to summarize this and group the counts by type of defect.
For this we can use the QUERY formula in cell D1 as follows:
=QUERY(A1:B,"Select A,SUM(B) where B is not null group by A")
The above formula uses the QUERY function to select columns A and B, group the columns by A (defect type), and find the sum of column B (frequency) for each group.
The result we get is as follows:
Since we’ve taken into account all the data in columns A and B, any future additions to the data will automatically get updated in the query result.
Note: When inserting the QUERY formula into a cell make sure the column corresponding to that cell and the next column are both blank, so that the result of the function can spill over both columns without error.
Step 1.2: Sort the Data
Next, we need to sort the dataset by sum Frequency. We do this so that our chart shows the bars in descending order of height.
For this, we can further update the same QUERY formula to:
=QUERY(A1:B,"Select A,SUM(B) where B is not null group by A order by SUM(B) DESC")
We simply added an ‘order by’ clause to the same QUERY function.
We now have the data sorted by sum Frequency.
Step 1.3: Add a Column for Cumulative Percentage
Once you’ve ordered the data by sum-frequency, it’s time to compute the cumulative percentage. We will need the cumulative percentage in order to create the line chart part.
To compute the cumulative percentage, let’s use column F, and give it a header label ‘Cumulative Percentage’. Then, insert the following formula in cell F2:
=SUM($E$2:E2)/SUM($E$2:$E)
Paste this formula down to the last row of the data range.
To convert these values to percentages, select all the cells of column F and click on Format->Number->Percent.
Here’s the result you should get:
Your data is now ready to be plotted. Notice that the last cumulative percentage is 100%.
Step 2: Plot the Pareto Chart
To create the Pareto chart, follow these steps:
- Select your data. In this case, we select the cells D1:F6.
- From the Insert menu, select Chart.
- You should see a chart displayed on your sheet and a Chart editor sidebar on the right side of the browser.
- By default, Google Sheets should show a Combo chart (a combination of a bar and line chart). If it doesn’t then you can convert the chart to a Pareto chart by following these steps:
- Click on the Setup tab of the Chart editor.
- Click on the dropdown under Chart type.
- Select ‘Combo Chart’ from the different chart options shown. You’ll find the Combo chart option either under the Suggested or the Line category.
- Click on the ‘Customize’ tab of the Chart editor.
- Click on the Series section to view all the Series settings for the chart.
- From the dropdown just under ‘Series’, select ‘Cumulative Percentage’.
- Change the Axis to ‘Right Axis’. This will display the Cumulative percentage on the right axis of the chart.
Your Pareto chart is now ready. The bars in the chart show the individual frequencies of different defects and the red line shows how the cumulative percentage of the defects changes with each type of defect.
We can clearly see from the above chart that the Fabric and Trimming defects account for the most part of the total defects (since the line is steepest for these two categories). In fact, they account for more than 65% of all defects.
This means that just by taking care of these two types of defects, you can get rid of around 65% of your production defects!
Note: You can change the Chart title from Chart editor. Select the Customize tab,  and open the Chart & axis titles section. You’ll find the chart title in the input box under Title text. Go ahead and change it to your preferred title.
FAQs
How do I make a Pareto chart in Google Sheets?
To create a Pareto chart in Google Sheets, you need to use (and customize) a Combo chart. We have explained this process in detail, and with an example in the last section of this tutorial.
How do you explain a Pareto chart?
The Pareto chart consists of a combination of a bar and line chart. The horizontal axis of the chart represents the different categories or causes. Each bar in the chart represents the frequency of occurrence of the given category, while the line represents the cumulative percentage of the total number of occurrences (for each category).
How do you read a Pareto chart?
To read a Pareto chart, you need to look at the line corresponding to the first few bars of the bar chart. The categories for which the line is the steepest comprise the ‘problematic’ or significant categories. So, look for the point where the line starts to bend and become less steep. All the categories before this point are the most significant ones.
Conclusion
This tutorial explained what a Pareto chart is, what it is used for, and how to create a Pareto chart in Google Sheets. Based on the 80/20 principle, a Pareto chart can be a great way to identify and resolve issues.
We hope you found this tutorial comprehensive and useful.
