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:
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:
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.
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:
=E2/SUM($E$2:$E$6)
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.
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.
In another column, calculate the cumulative sum of percentages. In the first cell under this new column, use:
=SUM($F$2:F2)
Drag down the fill handle to apply it to the rest of the column. Again, format these cells as percentages.
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".
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.
