To plot a Pareto chart, we can use the combo chart in Google Sheets as it’s (Pareto chart/graph/diagram) is a combination of a bar chart and a line chart.
Pareto chart is not available, at the time of writing this post, under the chart type in Google Sheets.
Since it’s not a built-in chart type, Google has no official documentation on how to format data to make a Pareto chart in Google Sheets.
So other than the step-by-step instructions to create a Pareto chart, this post also covers the data formatting part in detail.
Pareto Chart in Data Analysis in Google Sheets
The name Pareto chart derived from the name of an Italian economist named Vilfredo Pareto (15 Jul 1848 – 19 Aug 1923).
Pareto chart is based on the Pareto principle aka 80/20 rule which states, for many events, roughly 80% of the effects come from 20% of the causes.
In other words, by addressing 20% of the problems (causes) you can resolve the majority of the problems.
What Is Its Purpose?
Pareto chart is most common in use in quality control.
The purpose of a Pareto chart is to highlight/select the most important component or factor. In other words to highlight a limited number of causes (vital few) that produce a significant overall effect.
For example, with a Pareto chart, we can analyze the most frequently occurring problems (defects) by category (causes) in one go. That means by categorizing broad causes we can analyze specific components.
How to Read a Pareto Chart?
As I have already said, this chart is a combination of bars/columns and a line.
The horizontal axis of the chart is for the category (Cause) and the columns/bars are arranged in descending order (highest to lowest) from left to right.
The vertical axis (left axis) represents the frequency of occurrence (here Count).
There will normally be a secondary axis (right axis) representing the cumulative percentage of the total number of occurrences (here Count) or a total of the particular unit of measure.
From this chart, you can understand that 80% of the sales drop is caused by accessibility, product quality, and poor sales staff. How?
Take the sum of the values of the vital few (first 3 columns/bars) and divide that by the total values (all columns/bars).
=(74+62+52)/(74+62+52+17+12+9+6+4)Data Period and Formatting
First, decide the period to cover the data for Pareto analysis. The period can be one day, one week/work week, one work cycle and so on.
The measurement unit can be frequency/cost/quantity/time depending on your nature of work. Record the data and summarize them by category.
Pareto analysis in Google Sheets is easy as we can use the SQL similar Query function to summarize the data. I’ll explain it below.
For those who want more details about the Pareto chart, I recommend reading this Wiki article on the same.
How to Format the Data for Pareto Chart in Google Sheets?
Assume you have recorded the defects (problems reported) one by one in column A and its cause (category) in column B for a particular work period.
From this data, we can’t directly create a Pareto chart. We need to summarize the data based on the category. I mean, the number of defects based on the cause.
Below you can learn how to summarize a dataset to plot a Pareto chart in Google Sheets.
Query in Pareto Analysis for Grouping Defects by Category (Cause)
Sample Data in A1:B:
The data is around 100 rows. I am only showing a few of the rows to make you understand the data structure.
Summarising the Causes (Category)
To summarize the data for the chart, we can use the below Query formula in cell D1 (before inserting the formula make sure columns D and E are blank).
=query(A1:B,"Select B,count(B) where B is not null group by B",1)It covers all the data in columns A and B. I mean the entire columns so any future entry of records in these columns will be automatically included in the summary
I got the following summary.
| Cause | count Cause |
| Cause 1 | 10 |
| Cause 2 | 6 |
| Cause 3 | 33 |
| Cause 4 | 46 |
| Cause 5 | 5 |
Sorting the Count of Defects in Descending Order
We must sort the column ‘count Cause’ in descending (Z-A) order because the Pareto diagram needs the values so. Otherwise, you will end up plotting a normal column chart.
For that (Z-A sorting), we can modify the above Query formula to include the ‘Order by’ Clause which sorts the column.
=query(A1:B,"Select B,count(B) where B is not null group by B order by count(B) desc",1)In the summary report, the label of the second column is ‘count Cause’. Optionally we can format that label to ‘Count’ using the Label clause in Query.
Query Formula in D1:
=query(A1:B,"Select B,count(B) where B is not null group by B order by count(B) desc label count(B) 'Count'",1)Result (summary) in D1:E.
| Cause | Count |
| Cause 4 | 46 |
| Cause 3 | 33 |
| Cause 1 | 10 |
| Cause 2 | 6 |
| Cause 5 | 5 |
You May Like: What is the Correct Clause Order in Google Sheets Query?
To make the Pareto chart in Google Sheets, we need two more columns of data. Column F and G with percentage distribution of ‘Cause’ and cumulative percentages.
Distributing the Percentage of Causes
Enter the label “% distribution” in cell F1. Then in F2 use the below formula to calculate the % of each cause.
=E2/sum($E$2:$E)You must copy-paste this formula to F3:F6.
The next step is to select the range F2:F6 and format the numbers to percentage (Format > Number > Percent).
Want to replace the formulas (F2:F6) above with an array formula? Then remove the label in cell F1 and also remove all the formulas from F2:F6.
After that insert the below formula in cell F1 (do not require to copy-paste down).
={"% distribution";ArrayFormula(if(len(D2:D),to_percent(E2:E/sum(E2:E)),))}
For formula explanation, read this post – Calculating the Percentage of Total in Google Sheets [How To].
Running Sum of Percentage Distribution
In cell G1 type the label “Cumulative %”.
Then in cell G2 key the below formula in and drag to bottom until reach to cell G6.
=sum(($F$1:F2))This time also you may format the result to ‘percent’.
Here is the formula for those who want to use an array formula to get the cumulative sum (for cell G1).
={"Cumulative %";ArrayFormula(If(len(D2:D),(SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)),))}
Formula Explanation: Normal and Array-Based Running Total Formula in Google Sheets.
We are ready with the data formatting to create the Pareto chart in Google Sheets.
How to Plot a Pareto Chart in Google Sheets
There is an easy method to draw a Pareto chart in Google Sheets.
That may or may not work. So I will explain that easy method in a nutshell. You can try it and if not working as desired, jump to my step by step instructions to create the Pareto chart below.
The Quick Way to Draw a Pareto Chart in Google Sheets
- Select the data range D1:G6 and go to Insert > Chart.
- Then select the ‘Combo’ chart under ‘Chart type’.
- Hide column F. It may close the chart editor panel. Double click on the chart (blank chart) to re-open the chart editor panel.
- Go to the ‘Customize’ tab on the chart editor. Under series select ‘Cumulative %’ and change the axis to ‘Right’.
If it doesn’t plot a proper Pareto chart, follow the below step-by-step instructions.
Pareto Chart – Step-by-Step Instructions
Step 1: Enabling Chart Editor
- Click on any blank cell (for example on cell D9).
- Then select ‘Chart’ under the Insert menu to open the chart editor panel as well as a blank chart.
- Move the blank chart to a convenient location as well as resize it to make sure that the data for making the Pareto chart (D1:G6) is visible.
How to Select, Move, and Resize a Chart in Google Sheets
Click on the chart to select it in Google Sheets. Again click and drag to move.
For resizing a Google Sheets chart, first, select the chart and click on any of the squares at the edge and drag.
The above action(s) my hide the chart editor panel. Double click on the chart to unhide it.
Step 2: Adding Series
- Under the ‘Setup’ on the chart editor, select the ‘Combo’ chart.
- Click on the green (active) ‘table’ icon.
- The above last action will popup a tiny window contains a blank field and an ‘Add another range’ button. In the blank filed we can add the required data to make the Pareto chart.
- First, enter D1:D6 and click on the above-said button. Then enter E1:E6, add it, and then finally enter G1:G6. The data in F1:F6 is not required.
Step 3: Adding a Secondary Axis to the Pareto Chart
- Go to the customize tab (chart editor).
- Under the ‘Series’ select ‘Count’. Make sure that the ‘Type’ is ‘Column’.
- Then select the series ‘Cumulative %’. The ‘Type’ must be ‘Line’.
- Under ‘Axis’ select ‘Right’.
You have finished making your first Pareto chart in Google Sheets.
Step 4: Essential Customization
To add the title (chart title and axis titles) click on the ‘Charts & Axis titles’ on the ‘Customize’ tab.
