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.
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:
The mobile-phone company wants to come up with the best offer but doesn’t want to harm its profitability. Hence, they decide to make 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:
- It shouldn’t have any duplicates.
- 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:
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.
Next, format the cells into percentages. To do so select all the cells in column F, go to Format → Numbers → select Percent.
This is how the table should look now:
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
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:
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.
Creating the cumulative frequency line
- After clicking the customise tab, go to the series section to view the series settings for the chart.
- 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).
- Change the axis to “Right axis”. This will show the cumulative percentage on the right side of the 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.
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