How to Make a Pareto Chart in Google Sheets (Step-By-Step Guide)

Hi, in this article, we will learn how to make a Pareto chart in google sheets.

A Pareto chart displays visual representation to identify the problematic areas in the data. The data can be of anything that is categorized and contributes to the total such as defects in a product. Its mostly used by manufacturing companies to analyze their top defects, top problematic areas, etc. The Pareto chart does not come in google sheets separately as a standalone chart. But it can be created using a combination of bar chart and line chart. The Pareto chart has both so it can be created using a built-in Combo chart in google sheets.

Pareto Chart in Google Sheets

Assume an example to understand the reasons to use the Pareto chart. In a manufacturing company, we have multiple products in the production lines and a QA team inspects the final product and brings out the in-line defects from the very first point of manufacturing. We need to put up the findings in a chart or graph that can show the best picture of the defects and can show the trends in the defects. We can analyze which is the most occurring defect, and which is the most obvious contributor to the overall defects of daily production. For such purposes, we need to learn how to make a Pareto chart in google sheets, because a Pareto chart is ideally made for such kind of analysis and for studying the trends and habitual problems in the data.

• To show the frequency of the defects or problems
• To show the cumulative impact of the defects
• It helps us to find bigger defects and help us to prioritize them

How to Make a Pareto Chart in Google Sheets

We will first see what we need to create a Pareto chart, what kind of data is required to plot on the Pareto chart and how should the data be sorted and organized in the right way to make it ready to use in the Pareto. Here, we will also see how to calculate the cumulative percentage for the group of defects sorted in ascending order.

How to Make a Pareto Chart in Google Sheets – The Data

I am using hypothetical data for this example, I have some common manufacturing defects, their frequency, and their DHU. We will see how using this data we can a very good Pareto chart for better data analysis and a better understanding of the real alarming areas in the overall chart.

Step 1

Sample defects data

Step 2

Sort frequency column in descending order

Step 3

Calculating cumulative percentage

Step 4

Sum Function on Frequency column

Step 5

Divide it with the same the entire column

Step 6

Use absolute and relative references properly

Step 7

Hit enter, change the format to percentage

Step 8

Drag the formula to the entire column

Step 9

Final data

What is the cumulative percentage?

The cumulative percentage is a way of showing the percentage of related contributors in an overall dataset. It calculates the sum of each interval with the previous so we have the cumulative percentage value increasing on each step as move ahead.

Note: The formula is designed as if there are new defects’ frequency added in the frequency column, they will be added in the range, as we have used the entire B column.

Now you have all the required data to be added to the chart, let’s create a combo chart, and then we will customize it a little bit to make it exactly like the Pareto chart.

How to Make a Pareto Chart in Google Sheets – Creating the Chart

Now having all the data, we are ready to create the Pareto chart. For creating simply follow the below steps

Step 1

Select the data

Step 2

Go to Insert > Chart

Step 3

In the Line chart section > Select Combo Chart, your chart is added

Now there may be some problems, we will fix them using the chart editor box in the next section.

How to Make a Pareto Chart in Google Sheets – Chart Editor

Right after you create a chart in google sheets, you need the customization and editing power, we all have different data and we all need to have customizations to make our chart more specific and goal-centric. Here we will learn some basic editing and customization tools in general, and of course, we will also see the things to correct to make this combo chart more like a Pareto chart.

Step 1

Double click anywhere on the chart to open the chart editor

Step 2

Click on customize tab

Step 3

Go to Series > Click on the drop-down button “Apply to all series”

Step 4

Changed it, and select “cumulative percentage”

Step 5

In the same section, below you have a dropdown button with the name Axis, click on it.

Step 6

Change it from left axis to right axis

Step 7

Now you have got a perfect series “line” on the right axis.

So, this is how you can create a Pareto chart using a combo chart in google sheets. that gives you a combination of a line chart and a column chart.

How to Make a Pareto Chart in Google Sheets – Reading a Pareto Chart

Let’s first break down the chart, we have defects on the x-axis with their frequency column height, on the y-axis left side we have the scale of the frequency, and on the left side most importantly we have the cumulative percentage of the right-side scale.

You can see the highest bars and their combined percentage that is taking around 70 percent of the overall 100 percent of the defects, and that’s how this chart helps us and gives us motivation, seriously we can improve our manufacturing quality up to 70 percent just by controlling the top 2 defects.

Without the Pareto chart, we knew the number of defects, their frequency, etc., DHU, and all that, but we never thought in that way, like how important it is to remove the top contributors.

Now in manufacturing companies, this defects data may change every day. Daily we have new top 5 defects (which can also be the same), but the change in the top five defects is an indication that the Quality team is doing their work properly.

The Pareto chart gives us more power to identify the real gaps between the defects and the real achievement we can get in the overall KPI by addressing the correct problematic area.

You can see the red line, the line indicating the series, and a little curve indicating it all we simply need to understand the biggest culprits to make a big change in the overall performance score.

Download/Copy Google Sheets Workbook

Notes

FAQ

How to make a Pareto chart in google sheets?

We can make a Pareto chart in google sheets using a combo chart, we need to have accurate data and a perfectly calculated cumulative percentage for each data point (discussed in the tutorial). We firstly set up our data then we select all and go to insert > chart and pick up the combo chart from the line chart family. After adding the chart, we go to chart editor > customize > series and in select all series we select the column that has cumulative percentages. This is to change the line, then below we choose the series line axis to be on the right side in the axis section. This is how we can make a very good Pareto chart in google sheets

Conclusion

Wrapping up how to make a Pareto chart in google sheets. Pareto chart as I discussed is a more specific and more particularly expressive chart to display the data. It’s mainly used to track the top 5 or top 3 or top 10 defects/problems/errors in any product, system, or workflow. The Pareto chart tells us more specifically which area is covered first. It helps us prioritize the errors or the problems that are coming in a huge quantity and along with a nearing problem or two, they take up a big part of the overall problems and thus we are informed and prepared about the solution, we are informed about the most problematic area then we go down there and analyze the problem and similarly in this iterative procedure we end up reducing a big part of the defective percentage. Pareto chart is not directly available in google sheets, but as we have made many other charts that were not directly available in google sheets, we will make a Pareto chart as well using some other related charts. So we will use a combo chart that is a combination of a line and bar chart. We have two main things in our Pareto chart a series line and data bars, both we can get in the Combo chart. From data POV we need to calculate the cumulative percentage for each product or task, and it should be calculated properly (as I have described above in the tutorial).

So I hope you find this article helpful and that you have learned something new from it. That’s all from how to make a Pareto chart in google sheets, will see you soon with another new tutorial, and till then, take care. Have a nice day. Keep learning with Office Demy.