BLOG – PRODUCTIVITY TOOLS AND MORE…

How to build a Pareto Chart in Google Sheets in 2 Easy Steps

Oct 16, 2020 | Google Sheets Tutorial

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 

Google Sheets - GSheet - Easy Pareto Chart Tutorial

The dataset above is generated automatically by the query formula located in cell B5 of the “DataPreparation” sheet

Google Sheets - GSheet - Easy Pareto Chart Tutorial

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

Google Sheets - Pareto Chart Tutoria

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:

={“Cumulative %”;ArrayFormula(If(len(B6:B),(SUMIF(ROW(D6:D),”<=”&ROW(D6:D),D6:D)),))}

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

Pareto Chart Tutorial Google Sheet
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.

Google Shee Pareto Chart Tutorial
2 – Create the Pareto Chart from the dataset
2.1 – Select the dataset:
Google Sheets Pareto Chart Tutorial
2.2 – Select “Insert” then “Chart” in the Menu

In the Menu, select “Insert” then “Chart”

Google Sheets Pareto Chart Tutorial

The initial Pareto Chart  has a format that needs to be adjusted via the Chart editor

Unformatted Pareto Chart in Google Sheets
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

Google Sheets - GSheet - Pareto Chart Tutorial

Final Pareto Chart

Pareto Chart in Google Sheets - ParetoAnalysis.tools Tutorial

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 Google Sheets – Video Tutorial

Pareto Chart Google Sheets – Template

The Google Sheets Pareto Chart template used in this tutorial can be downloaded here:

  >> Access and Make a Copy of the Google Sheets Pareto Chart Template <<

?
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:

How to make a copy of a Google Sheets document - 2-steps Tutorial - ParetoAnalysis.tools
 Pareto Chart In Google Sheets – Conclusion

A Pareto Chart or Pareto Diagram is not a built-in chart type in Google Sheet unlike Excel. It remains easy and quick to create a Pareto Chart in GSheet though. Therefore, it’s a good practice to always have a look at the data you are working with via a Pareto Chart. You may find insightful results and “quick wins” to optimize your business operations or a great and interesting business fact to start a professional presentation.

“A good business strategy is an operational execution driven by a Pareto Analysis”


What Is Google Sheets and Why building a Pareto Chart With Google Sheets?

Google Sheets is a cloud-based spreadsheet software meaning that you can access it from everywhere as long as you have an internet connexion. You can access Google Sheets with a free Google account (for personal use) or G Suite account (for business use) here: docs.google.com/spreadsheets/

A Pareto Chart or Pareto Diagram 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.

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 will 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
Google Sheets Pareto Chart Tutorial

0 Comments

Trackbacks/Pingbacks

  1. Parkinson's Law - Why We work better with tight deadlines - 1 Explanation - […] The Pareto Law, more commonly known as the Pareto Principle, works perfectly in tandem with Parkinson’s Law: execute only…
  2. Pareto Efficiency / Pareto Optimality: 1 Perfect Allocation - […] also known as the 80/20 rule or Pareto Law. The Pareto Principle is used across industries with Pareto Charts…

Submit a Comment

Your email address will not be published. Required fields are marked *

Pareto Efficiency / Pareto Optimality: 1 Perfect Allocation

Pareto Efficiency / Pareto Optimality is the highest efficiency level resulting from an optimal allocation where any change to it would make something worse off

Murphy’s Law: 1 Gloomy View On How Things Usually Turn Out

Murphy’s Law is the adage that “Anything that can go wrong will go wrong.” People give Murphy’s Law relevance when things go awry as a reason to why it happened

Laborit’s Law / Law of the Least Effort: 1 clear explanation

Laborit’s Law or the Law of Least Effort suggests that humans prefer to carry out simple tasks that give immediate satisfaction to avoid stress or inconvenience

Illich’s Law or Law of Diminishing Returns: 1 simple concept

Illich’s Law or Law of Diminishing Returns suggests that optimal productivity is reached with an appropriate balance between working time and resting time.

Parkinson’s Law – Why We work better with tight deadlines – 1 Explanation

What is Parkinson's Law? Parkinson's Law states that "Work expands to fill the time available for its completion." In short, the more time is allowed to complete a task, the more time this task will take. Parkinson's...

Carlson’s Law: 1 Simple yet Powerful Productivity Concept

Carlson's Law is about the negative impact of involuntary breaks. Taking much-needed and deserved intentional breaks is one thing - getting involuntarily distracted is another. It is proven: interruption seriously...

Hofstadter’s Law and the Planning Fallacy

Hofstadter’s Law falls into the Planning Fallacy category by stating “It always takes longer than you expect, even when you take into account Hofstadter’s Law”.

How to build a Pareto Chart in Google Sheets in 2 Easy Steps

This easy step-by-step tutorial shows how to build a Pareto Chart in Google Sheets. Download now the Free Google Sheets Pareto Chart template for immediate use.

Best Microsoft Excel Guide Books – Top 3 for Data Analysis

These 3 Best Microsoft Excel Guide Books provide Excel users with Powerful Data & Business Analysis techniques that go beyond Pareto Analysis and Pareto Charts.

Optimize Easily your Well-being with the 80/20 rule Approach

Happiness, the feeling of positivity, really is the foundation of productivity. - Miguel McKelvey Everyone deserves to have a happy and fulfilled life. Take control now of your well-being by applying the simple yet...

Pareto Analysis Excel Add-In

Buying the Pareto Analysis Excel Add-In is a risk-free experience. If you are not happy with it, you simply get your money back. No questions asked.

Execute Better.  Execute Faster. Automate any Excel Pareto Analysis.

Pin It on Pinterest