This step-by-step tutorial teaches you how to create a Pareto Chart in Google Sheets in a quick, simple, and scalable manner. In a hurry? Click here to download a Google Sheets Pareto Chart template. Click here to watch the video explaining how to build a Pareto Chart in Google Sheets. It’s free and ready for immediate use. Wondering why you should create a Pareto Chart in Google Sheet or simply what Google Sheets is? If yes, click-here otherwise keep reading!
Pareto Chart In Google Sheets – Step by Step Tutorial
1 – Prepare the dataset
1.1 – Build a 3-column dataset
- 1 column for the causes
- 1 column for the total effect of each cause with a numerical value – Here labelled as “Complaints”
- 1 column for the relative effect of each cause with a percentage value – Here labelled as “% of total Complaints”
3-column dataset example
The dataset above is generated automatically by the query formula located in cell B5 of the “DataPreparation” sheet
To build automatically the dataset above, use this query formula in Google Sheet:
=QUERY(RawData!A1:B,“SELECT A,SUM(B), SUM(B)/” & SUM(RawData!B:B) &” WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(B) DESC LABEL SUM(B) ‘Complaints’, SUM(B)/” & SUM(RawData!B:B) & ” ‘% of to Total Complaints’ “)
What does this formula do?
- It extracts, consolidates and sorts the “Raw Data” the right way: It assumes that your “Raw Data” is located in columns A & B of a distinct sheet called “RawData” like below.
- It tailors the columns’ names using ‘LABEL’: Feel free to change it your way in the formula depending on your context.
“Raw Data” located in columns A and B of the “RawData” sheet
If you use the Google Sheet Query formula above, the data is already sorted the right way. If you use a different method to build this dataset, ensure that data is sorted by “Effect” descending order. In this example, the “Effect” is the number of “Complaints” for each product (“Cause”).
1.2 – Extend The 3-Column Dataset With a “% Cumulative” Column
To do so apply the Google Sheet array formula below (in the cell E5 of the Pareto Chart template):
What does this formula do?
- Like the first formula, it generates the rights numbers and adjusts automatically to the size of your data.
- It calculates the cumulative effect of each cause: sum of the relative effect of the cause with the relative effects of the previous causes having a greater effect.
“% Cumulative” column in the “DataPreparation” sheet is generated automatically
by the array formula located in the cell E5
1.3 – Hide the Third Column of the Dataset
Hide the column D “% of total effect” (Labelled here as “% of total Complaints”) as this data is not required for the Pareto Chart.
2 – Create the Pareto Chart from the dataset
2.1 – Select the dataset:
2.2 – Select “Insert” then “Chart” in the Menu
In the Menu, select “Insert” then “Chart”
The initial Pareto Chart has a format that needs to be adjusted via the Chart editor
2.3 – Adjust the Pareto Chart format for the series “Cumulative %”
In the “Chart Editor”, under the right tab “Customize”, select “Cumulative %” under > “Series”. Then select “Right axis” under “Axis”
Adjust the Format: Chart Editor > Customize > Series > Cumulative % > Axis > Right Axis
Final Pareto Chart
Congratulations for making it till the end!
Feel free to reuse the template used in this Google Sheets Pareto Chart tutorial by using the link below.
Pareto Chart in Google Sheets – Ready-to-use Template
The Google Sheets Pareto Chart template used in this tutorial can be downloaded here:
How to make a copy of a Google Sheets document?
In the top left corner: click on ‘File’ then ‘Make a copy’
See the picture below:
Pareto Chart In Google Sheets – Tutorial Conclusion
A Pareto Chart or Pareto Diagram in Google Sheets is not a built-in chart type, unlike Excel. It remains easy and quick to make a Pareto Chart in Google Sheets, and Pareto Charts are excellent tools to get a quick grasp of key business drivers – at least – to get an understanding of any significant trend that may exist in your data. By looking at the data, you may find helpful insights, quick wins, and easy fixes to improve your company’s operational efficiency and even exciting news to use in professional presentations! Insights given by your Pareto Chart can help you to decide rationally what business projects really matter relative to others. This discrimination step provides you with a solid basis for proven project prioritization methods, such as the Eisenhower Matrix.
“A good business strategy is an operational execution driven by a Pareto Analysis”
A Pareto Diagram or Pareto Chart in Google Sheets helps to turn data into clear visualizable actionable insights. It highlights what really matters by putting emphasis on the most important drivers of a phenomenon.
Using Google Sheets to make a Pareto Chart or Pareto Diagram is a robust choice. Google Sheets is cloud-based spreadsheet software that conveniently allows you to access it from anywhere, as long as you have an internet connection. It also provides advanced collaborative features and makes your life easier to share your business insights. You can use Google Sheets with Google’s free account for personal use or a G Suite account for business use.
A Pareto Chart illustrates the Pareto distribution that may exist with your data. A Pareto Distribution is observed when the Pareto Principle holds. The Pareto Principle (or “20-80 rule” or “Law of the Vital Few”) states that, for many events, roughly 80% of the effects come from 20% of the causes. As an example, illustrations in this tutorial show that 80% of the customer complaints ensue from only 20% of the products. For more details about the Pareto Principle, please refer to the FAQ by clicking here.
Pareto Chart In Google Sheets – Example
TABLE OF CONTENT
- Pareto Chart In Google Sheets – Step by Step Tutorial
- 1 – Prepare the dataset
- 1.1 – Build a 3-column dataset
- 1.2 – Extend The 3-Column Dataset With a “% Cumulative” Column
- 1.3 – Hide the Third Column of the Dataset
- 2 – Create the Pareto Chart from the dataset
- 2.1 – Select the dataset:
- 2.2 – Select “Insert” then “Chart” in the Menu
- 2.3 – Adjust the Pareto Chart format for the series “Cumulative %”
- Pareto Chart in Google Sheets – Video Tutorial
- Pareto Chart in Google Sheets – Ready-to-use Template
- How to make a copy of a Google Sheets document?
- Pareto Chart In Google Sheets – Tutorial Conclusion
- Pareto Chart In Google Sheets – Example