If you want to make a Pareto chart in Google Sheets—that is a pretty simple thing to do.
With a little know-how, this can be easily done.
In order to make a Pareto chart, we need to get our data ready and in the right format, and then we create the chart with the data.
In this tutorial, I will show you how to make a Pareto chart in Google Sheets.
Table of Contents
What is a Pareto Chart?
A Pareto chart is a type of graph that contains bars and lines. So in a way, it is like both a bar graph and a line chart.
In a Pareto chart, the individual values are displayed by the bars and the lines represent the cumulative total.
Pareto charts are useful for showing the most important factors in a large data set.
Here is an example of a Pareto chart:
How to Make a Pareto Chart
Making a Pareto chart in Google Sheets is easy if our data is in the right format.
So the first step in creating a Pareto chart is to make sure the data is in the proper format that the chart will need to be created. After we have the data in the right format, then I will create the chart.
I will be creating my chart with this example data, where I have various products and the number of negative feedback complaints per each product.
Here are the steps to format the data and create a Pareto chart:
1. First we need to sort our data so that the values in column B are in descending order. Highlight all of the data that will be used to create the chart
2. In the top menu select Data>Advanced range sorting options
3. Set the sort rules to Sort by Column B from Z to A. Select Sort when you are finished
4. Once your data is sorted properly, you need to add a new column to calculate a cumulative percentage total of the values in our dataset. I have created this new column in column C. In the first cell, we can enter a formula to calculative our cumulative total percentage. The formula I have used to do this is =SUM($B$2:B2)/SUM($B$2:$B$8).
5. After you have entered the formula in the first cell, copy and paste it down the column. Each step down a cell, the formula will add on the percentage of the cells above it until you reach the total 100% on the bottom cell.
6. Now we have the data that we need and we can create our chart. Highlight the data in all three columns and in the top menu select Insert>Chart. You can also select the Insert chart button from the toolbar
7. In the Chart editor, make sure it is set to Combo chart. It will usually automatically set it to this chart type, but sometimes you have to do it manually
8. You will notice that the bar graph part of your Pareto chart is now showing up, however, the chart is still missing its line. We need to now add the line to our graph. In the Chart editor, select the Customize tab, and select Series
9. In the first drop-down set it to the Cumulative Percentage option
10. Navigate down to the Axis setting and set it to Right axis
11. You will now have your completed Pareto chart. The blue bars display the individual data (negative feedback) for each product, and the red line displays the cumulative values for the products.
Reading a Pareto Chart
Reading or interpreting a Pareto chart depends on the data that is used, but usually, the vertical bars represent the “count “or “cost” of each data point.
The left axis contains the total value of the vertical bars (count or cost).
Pareto charts are always placed in rank order so that the leftmost bar has the highest value.
The right axis on a Pareto chart contains the cumulative percent totals. The cumulative line adds the percentage of each bar to the total, making it so you can easily see how much each bar contributes to the total percentage.
A steep line tends to imply that a few problems are contributing a high percentage of the total, whereas a flat line will tell you that each problem is contributing about a similar amount to the total problems.
Pareto charts are created by using a combo chart and adding a line to it for the right axis. The key is to get your data in the right format by creating the cumulative total percentage column.
Once you have your data, you can create your chart in a few clicks.
Hopefully, you found this tutorial helpful, and make sure to watch the video if you are having trouble with any of the steps!