Pareto charts are visual tools that provide a sharp understanding of what matters the most in your datasets, which enables you to focus on critical factors.
In this article, along with understanding what the Pareto Chart is and what it is used for, we’re going to learn how to create them in Google Sheets. Not only that, but we’re also going to teach you how to read Pareto charts.
Our step-by-step guide walks you through the process of transforming raw data into a compelling visual story. Whether you’re a business analyst aiming to optimize processes, a project manager targeting bottlenecks, or simply someone who wants to make better-informed decisions, mastering Pareto charts will amplify your data-driven capabilities.
What is a Pareto Chart, and what are its uses?
Pareto charts are a hybrid of bar charts and line charts and use the concept of the Pareto principle. According to Pareto principle, roughly 80% of effects stem from 20% of causes. That means if we address a mere 20% of identified causes, a significant proportion of issues can be effectively tackled.
The Chart leverages the frequency distribution of a variable alongside its cumulative percentage. Doing so provides a visual grasp of which categories (causes) predominantly contribute to the outcome.
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
The frequencies of distinct categories (causes) in the Pareto chart are illustrated in columns (bars) in descending order. On the same Chart, there’s a line chart that outlines the cumulative percentage of the entire event’s occurrences, reflecting the proportional impact of each cause.
Understanding the Pareto chart may seem intimidating at first, but once understood, it is very beneficial for making data-driven decisions.
So, let’s first understand — how to read a Pareto chart.
Reading Pareto chart
As previously mentioned, the Chart comprises both a bar chart and a line chart. The Chart’s horizontal axis (x-axis) represents various categories or causes, while the vertical axis on the left side showcases the bar chart. Each bar signifies the frequency of occurrence for a specific category.
A secondary vertical axis is positioned on the right side of the Pareto chart, designated for the line chart. This axis represents the cumulative percentage of the total occurrences for each category.
In this configuration, each point on the line corresponds to the percentage of the total occurrences that a particular category contributes.
For instance, if the line chart indicates a cumulative percentage of 10% for a given category, it signifies that this category accounts for 10% of the total occurrences.
To explain it further, consider the following Pareto chart that illustrates diverse product defects. This Chart showcases the frequency of occurrence for each defect, alongside the cumulative percentage of their overall impact.
Each bar in the Chart represents the frequency of a specific defect, and each point on the line chart reflects the cumulative percentage attributed to that defect type.
Importantly, the bars are arranged in descending order, with the tallest bar denoting the most frequent defect. Notably, the cumulative percentage line exhibits a steep incline until it intersects with the “Headrest” defect. During this steep segment, defects exert a substantial cumulative effect, signifying their pronounced impact and the need for heightened attention.
Conversely, as the line starts to level off, the corresponding defects exhibit diminished significance, warranting less attention to addressing them.
Making Pareto Chart in Google Sheets
Now, let’s delve into the process of creating a Pareto chart using Google Sheets. Imagine we possess the following frequency table outlining the occurrences of various defects in an office chair production line.
Our objective is to construct a Pareto chart, unveiling the pivotal defects that hold the greatest impact and contribute significantly to the overall losses.
Step 1: Get the source data ready
Before creating the Pareto chart, we need preliminary data preparation to facilitate Google Sheet’s seamless interpretation and conversion into a coherent Pareto chart.
To start data preparation, condense the data of ‘cause’ or ‘defects.’
Condensing Data
In our dataset, distinct counts are assigned to various defect categories. Hence, the task is to consolidate these counts by defect type.
To achieve this, the QUERY formula in cell D1 can be used as shown below:
=QUERY(A1:B,”Select A,SUM(B) where B is not null group by A”)
In this formula, the QUERY function is used to select columns A and B, arrange them by A (defect type), and compute the cumulative sum of column B (frequency) within each grouping.
The desired outcome will be as follows:
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
Because we have taken complete data from columns A and B, any further additions to the data will update in the query result automatically.
Ensure while inserting the QUERY formula into a cell, the corresponding columns should be blank so that you don’t get any errors.
Sorting the data
Next, you need to sort the data by sum Frequency. This is required to show bars in descending order of height.
Doing this is easy. Update the same QUERY formula to:
=QUERY(A1:B,”Select A,SUM(B) where B is not null group by A order by SUM(B) DESC”)
Everything is the same; we just added order by clause to the same QUERY function.
Adding column for cumulative percent
For the line chart in the Pareto Chart, you are required to compute the cumulative percentage.
For computing the cumulative percentage, use a new column, label it as Cumulative Percentage, and insert the following formula in the below cell:
=SUM($E$2:E2)/SUM($E$2:$E)
Drag it down to the last row containing data.
Once done, convert these values to percent.
Click the cells, click on Format, and select Percent from Numbers.
This is the result you’ll get.
The data is now ready for plotting. See that the last cumulative percent is 100%.
Step 2: Inserting Pareto chart
To display the Pareto Chart, follow the below steps:
- Select the data.
- Click on Insert and select Chart
- A chart will appear on the sheet, along with a Chart editor on the right side of the page.
- Google Sheets will automatically show a combo chart (It contains both a bar chart and a line chart). But if it doesn’t, you should follow the below steps:
- Click on the Setup from the Chart editor.
- Click on the Chart type dropdown menu.
- Choose Combo Chart from the chart options.
- Click Customize from the Chart editor
- Click on Series to view all the settings related to Series on the Chart
- Under Series, select Cumulative Percentage
– Change the Axis to the Right Axis. This will make the Cumulative Percentage show on the right axis of the Chart.
Our Pareto Chart is ready. The Chart has bars that represent how often different problems occur, and there’s a red line that shows how the total number of problems adds up as we go through each type of problem.
As you can see from the above Chart, Wheels and Headrest defects make up the major portion of total defects. They, in total, account for 65% of all defects.
If you want, you can change the chart title from the Chart editor. To do that, select the Customize tab and open the Chart and Axis titles. Change the title from the input box under the Title text.
FAQs
How can we explain a Pareto chart?
A Pareto chart is like a mix of bar and line charts. On the Chart’s flat line, we put different reasons or groups. The vertical bars show how many times each reason happens. The line on top shows how all these reasons add up in a percentage.
How can you understand the information presented in a Pareto chart?
To understand a Pareto chart, focus on the line that connects the tops of the initial bars in the bar chart. The areas where this line is the steepest show the most important or noteworthy categories.
Look for the spot where the line begins to curve and become less steep. The categories before this point are the ones that matter the most.
Conclusion
Mastering the creation and interpretation of Pareto chart in Google Sheets is a valuable skill. These charts combine bar and line chart features to visually highlight the most significant factors within a dataset, aligning with the Pareto principle’s insight.
- You may like: How To Create Pareto Chart in Excel
By following the provided steps, one can create these charts in Google Sheets, transforming raw data into actionable insights. In a data-centric world, this skill is crucial for effective decision-making, benefiting analysts, managers, and individuals aiming to optimize processes or prioritize issues.
The ability to convert data into meaningful visual narratives empowers better choices and drives impactful outcomes.