How To Create a Pareto Chart In Google Sheets

How To Create a Pareto Chart In Google Sheets
Reading Time: 5 minutes

Quick Guide
How to create a Pareto Chart in Google Sheets in a few minutes:
1. Enter the data
2. Enter =query(A1:B9,” Select A, sum(B) where B is not null group by A order by sum(B) DESC, 2)
3. Select the new data, navigate to Insert→ Select chart
4. Using the customise tab, under the series heading, select the third option
5. Change its axis from left to right
Hurray! You have successfully created a beautiful Pareto chart in Google Sheets effortlessly.

Pareto charts are a combination of bar graphs, arranged in descending order and a line chart expressing the cumulative percentage of the occurrence of an event. It’s based on the Pareto principle which states that “for many outcomes, roughly 80% of consequences come from 20% of the causes.” These charts prove to be critical in helping users to identify critical areas to prioritise in the process of improvement.

By the end of this article, you will be able to create a Pareto chart in Google Sheets and use it to express your data more clearly and efficiently.

We begin by taking the example of a firm that purchases second-hand phones from its customers. The following Pareto chart shows the type of product defects, the frequency with which they occur and a cumulative percentage to show their impact. 

An example of a Pareto chart created in Google Sheets
Pareto Chart in Google Sheets

You can make a copy of this spreadsheet and follow along with the tutorial. The data sample as well as the formulas are mentioned.

Steps to create a Pareto Chart in Google Sheets

Imagine that after due research, the second-hand mobile phone company came up with the following data with regard to the phones they bought from their customers:

Sample Dataset

The mobile-phone company wants to come up with the best offer but doesn’t want to harm its profitability. Hence, they decide to create a Pareto chart in Google Sheets to figure out how people use their phones and what kind of defects impels them to upgrade their handset. To do so, the company follows the steps mentioned below.

Step 1: Prepare the data set

Cleaning the data

Pareto chart can only be created for data that satisfies the following criteria:

  1. It shouldn’t have any duplicates.
  2. It should be in descending order.

To satisfy the aforementioned criteria, we navigate to a new cell where we want the cleaned data to be and input the following function:

=QUERY(A1:B9, "Select A, Sum(B) where B is not null group by A order by Sum(B) DESC")

Note: To learn more about the query function click here.

Once, the formula is inputted, the following table should be visible on the sheet:

Using the Query function to clean the data
Cleaning the data

Insert the column for cumulative frequency

To construct the line that traces the cumulative percentage of defects, we need another column with the heading “Cumulative %”. In the example, we have used the cell F1 for the heading.

Input the following formula into the cell below the heading and copy it to the last row of the data range to calculate the cumulative percentage of defects as shown below:

=Sum($E$2:E2)/sum($E$2:$E$6)

To understand the logic behind the formula for cumulative %, click here.

Calculating the cumulative %

Next, format the cells into percentages. To do so select all the cells in column F, go to Format → Numbers → select Percent.

Changing the formal to %
Formatting the cumulative %

This is how the table should look now:

Result after using the Query functionq
Final Data

Step 2: To create a Pareto chart in Google sheets

Creating the bar chart

Once the data is finalised, we first create a bar chart using the following method:

  • Select the cells which consist of the data, in our case it’s D1:F6
  • Click on the insert tab and click on the chart option OR click the icon shown in the second image
Ways to insert a chart in Google Sheets
How to insert a chart

You should see a bar chart with an orange line at the bottom displayed on your screen. On double-clicking the chart, a tab on the right will pop up. Navigate to the customise tab and follow the instructions below:

Identifying the Customise tab, the chart and the cumulative % line
Labelling the sheet

By default, Google sheets should show a Combo chart, in case it doesn’t, navigate to Setup→ Select Combo Chart→ choose the chart shown below.

Selecting the combo chart option
Creating a combo chart

Creating the cumulative frequency line

  • After clicking the customise tab, go to the series section to view the series settings for the chart.
Locating the series section in the customise tab
Navigating to the Series section
  • From the drop-down just under series, change the setting from “Apply to all slides” to “Cumulative %” (Or whatever is the name of the column that comprises the cumulative % of frequencies).
Selecting the cumulative & line
Choosing the cumulative % line
  • Change the axis to “Right axis”. This will show the cumulative percentage on the right side of the chart.
Changing the axis of a line chart
Finalising the Pareto Chart

Voila! Your Pareto chart is now ready. The bars in the chart show individual frequencies of defects and the red line traces the cumulative percentage of the defect that arises in mobile phones.

Note: You can change the heading of the chart by navigating to the same customise tab>> open chart and axis titles>> Under the Chart title option, you will find the title text, wherein you can input your preferred text for the title of the chart.

Pareto Chart in Google Sheets

Conclusion

A Pareto chart is used to ascertain those defects that occur most frequently. In our example, we find that defects such as scratches on the body and cracked camera account for more than 50% of the total which speaks volumes about how carelessly people use their phones.

In conclusion, to create a Pareto chart in google sheets, nothing but a clean set of data and a few operations are the only things required. Furthermore, one can also use google sheets to create other charts such as area charts, column charts, scatter diagrams, pie charts and so on. To learn how to create such charts, keep yourself updated with our blog.

See also

Refer to the articles below to gain a more comprehensive understanding of the different types of charts that can be created in Google sheets.

How to create a funnel chart in Google Sheets

How to make a double bar graph in Google Sheets

How to create a semi-log graph in Google Sheets

How to Create a Burndown Chart in Google Sheets

One response to “How To Create a Pareto Chart In Google Sheets”

Leave a Reply