BLOG – PRODUCTIVITY TOOLS AND MORE…

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

Jun 13, 2021 | Google Sheets Tutorial

This step-by-step tutorial teaches you how to create a Pareto Chart in Google Sheets in a quick, simple, professional, and scalable manner.  In a hurry? Click here to download a Google Sheets Pareto Chart template. It’s free and ready for immediate use.  Click here to watch the video explaining how to build a Pareto Chart in Google Sheets.

Google Sheets Pareto Chart Tutorial (Step-by-Step)

1 – Prepare the dataset for the Pareto Chart
1.1 – Build a 3-column dataset
  • 1 column for the causes’ description – Here labeled as “Cause”
  • 1 column for the absolute effect of each cause with a numerical value – Here labeled as “Complaints”
  • 1 column for the relative effect of each cause with a percentage value – Here labeled as “% of Total Complaints”

3-column dataset example 

Google Sheets - GSheet - Easy Pareto Chart Tutorial

For a scalable approach, the dataset above can be generated automatically via a unique formula that puts everything in order regardless of your raw data input. To do so, 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, summarizes, and sorts the “Causes” the right way. It assumes that your “Raw Data” input is located in columns A & B of a separate sheet called “RawData,” like in the screenshot below. It conveniently handles the data regardless of how the data is input into your spreadsheet.
  • 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 - GSheet - Pareto Chart Tutorial - Raw Data

Dataset generated via the formula located in cell B5 of the “DataPreparation” sheet

Google Sheets - GSheet - Easy Pareto Chart Tutorial

If you use the Google Sheet Query formula above, the data is already sorted the right way for a Pareto Chart or Pareto Diagram. If you use a manual method to build this dataset, ensure that data is sorted by “Effect” descending order and that all the relevant data input is captured. In this example, the “Effect” is the number of “Complaints” for each product (“Causes”).

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

={“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:

Select the 3 columns required to draw the Pareto Chart: 

  1. The causes’ descriptions
  2. The absolute effect of each cause
  3. The cumulative relative effect of each cause

3-column dataset ready for a Pareto Chart:

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

Pareto Chart in Google Sheets – Ready-to-use  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 – 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 the very least, a Pareto Chart is helpful to detect any significant trend that may exist in your data. By looking at a Pareto Graph, you may find beneficial insights, quick wins, 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 relatively 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”


Why building a Pareto Chart With Google Sheets?

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.

To create a Pareto Chart or Pareto Diagram with Google Sheets, log in to docs.google.com/spreadsheets/ then follow this tutorial.

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
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…
  3. Create a Pareto Chart in Excel in 2 steps - Easy Tutorial - […] that could lead to better time or budget management in your business operations.  To make a Pareto Chart with…
  4. The Powerful Pareto Analysis or 20/80 Rule in Excel: FAQ - One-click Excel Pareto Analysis Add-In - […] The same dataset is also used in our Google Sheets Pareto Chart tutorial: […]
  5. The Powerful 80/20 Rule | Pareto Law | Pareto Rule by Ray Dalio - […] learn how to build a Pareto Diagram in Google Sheets and visualize any 80/20 Pareto Distribution in Google Sheets…
  6. The Powerful 80/20 Principle: How to Achieve More with Less - […] you prefer building a Pareto Chart in Google Sheet, this tutorial explains to you how to build a Pareto…

Create a Pareto Chart in Excel in 2 steps – Easy Tutorial

The tutorial shows how to create a Pareto Chart in Excel in 2 simple steps after explaining the basics of the Pareto Principle or 20 80 rule.

Time Management: 1 Simple Eisenhower Matrix to Get it Right

The Eisenhower Matrix or Urgent-Important Matrix is a highly effective time management tool that uses 4 quadrants to prioritize tasks and increase productivity

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...

3 Tricks To Turn The Shiny Object Syndrome To Your Advantage

The shiny object syndrome is bad for productivity. Focus is key. Execution is everything. How do you stay focus and execute with all these new ideas in mind?

Why Your Best Time management Tool is Useless? 1 explanation

The Best Time Management Tools provide optimal task planning. That’s a great start, but that’s not enough. Learn here how to make your ambitions come true.

The Powerful Pareto Analysis Approach and 20/80 Rule: FAQ

All about the Pareto Principle, Pareto Analysis, 80/20 Rule, Pareto Law, and the Law of the Vital Few. Free Microsoft Excel & Google Sheets Tutorials & Templates

Work Smarter – Get Help Right Now From an Excel VBA Expert

No time to learn VBA for your Excel project? Get it done: Ask a Microsoft Excel VBA Expert. Live Excel Help, Online Excel Assistance, Overnight Project Delivery

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.

Pin It on Pinterest