To create a Pareto chart in Google Sheets, we can use the combo chart feature. A Pareto chart is a combination of a bar chart and a line chart, and it’s used to analyze the most important components or factors in a dataset. However, Google Sheets does not have a built-in Pareto chart option, so we need to use a workaround to create one. In this article, I will walk you through the step-by-step process of creating a Pareto chart in Google Sheets.
Pareto Chart in Data Analysis in Google Sheets
The Pareto chart is named after Vilfredo Pareto, an Italian economist. It is based on the Pareto principle, also known as the 80/20 rule, which states that roughly 80% of the effects come from 20% of the causes. In other words, by addressing a small percentage of the causes, we can resolve the majority of the problems.
What Is Its Purpose?
The Pareto chart is most commonly used in quality control. Its purpose is to highlight the most important components or factors that contribute to a significant overall effect. By categorizing and analyzing the causes, we can focus on the vital few that produce the majority of the problems.
How to Read a Pareto Chart?
A Pareto chart combines bars or columns with a line. The horizontal axis represents the categories or causes, and the bars are arranged in descending order from left to right. The vertical axis represents the frequency of occurrence. There is also a secondary axis that represents the cumulative percentage of total occurrences. By analyzing the chart, we can identify the causes that have the greatest impact.
Data Period and Formatting
Before creating a Pareto chart, it’s important to decide on the period of data to analyze. This can be a day, a week, or any other relevant time frame. The measurement unit can be frequency, cost, quantity, or time, depending on the nature of your work. Once you have recorded the data, you need to summarize it by category.
To summarize the data for the chart, you can use the SQL-like Query function in Google Sheets. This function allows you to group and aggregate the data based on the category. For example, you can calculate the count of defects for each cause. By using this function, you can easily format the data for the Pareto chart.
How to Format the Data for Pareto Chart in Google Sheets?
Assuming you have recorded the defects and their causes in columns A and B, respectively, you need to summarize the data before creating the Pareto chart. To do this, you can use the Query formula in Google Sheets.
First, enter the following formula in cell D1:
=query(A1:B,"Select B,count(B) where B is not null group by B",1)
This formula will summarize the data by grouping it based on the causes. The result will be displayed in columns D and E.
To sort the count of defects in descending order, modify the formula as follows:
=query(A1:B,"Select B,count(B) where B is not null group by B order by count(B) desc",1)
You can also format the label of the second column to “Count” using the Label clause in the Query formula:
=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)
You can now see the summarized data in columns D and E.
Distributing the Percentage of Causes
To create a Pareto chart, you need to calculate the percentage distribution of each cause and the cumulative percentages. Start by entering the label “% distribution” in cell F1. Then, in cell F2, use the following formula:
=E2/sum($E$2:$E)
Copy and paste this formula to cells F3:F6. Next, select the range F2:F6 and format the numbers as percentages.
Alternatively, you can use an array formula to calculate the percentage distribution. Remove the label in cell F1 and the formulas in cells F2:F6, and enter the following array formula in cell F1:
={"% distribution";ArrayFormula(if(len(D2:D),to_percent(E2:E/sum(E2:E)),))}
This formula will automatically calculate the percentage distribution for each cause.
Running Sum of Percentage Distribution
In cell G1, type the label “Cumulative %”. Then, in cell G2, enter the following formula and drag it down to cell G6:
=sum(($F$1:F2))
Format the result in column G as percentages.
If you prefer, you can use an array formula to calculate the cumulative sum. Enter the following formula in cell G1:
={"Cumulative %";ArrayFormula(If(len(D2:D),(SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)),))}
This formula will give you the cumulative percentages for each cause.
How to Plot a Pareto Chart in Google Sheets
There is an easy method to draw a Pareto chart in Google Sheets. Select the data range D1:G6 and go to Insert > Chart. Choose the “Combo” chart under the “Chart type” option. Hide column F, and double click on the chart to open the chart editor panel. In the “Customize” tab, select “Cumulative %” under the series and change the axis to “Right”.
If this method doesn’t work as desired, follow the step-by-step instructions below.
Pareto Chart – Step-by-Step Instructions
Step 1: Enabling Chart Editor
Click on any blank cell, such as D9, and select “Chart” under the Insert menu. This will open the chart editor panel and create a blank chart. Move the chart to a suitable location and resize it to ensure that the data for the Pareto chart (D1:G6) is visible.
Step 2: Adding Series
Under the “Setup” tab in the chart editor, select the “Combo” chart. Click on the green “table” icon. In the popup window, enter the following ranges: D1:D6, E1:E6, and G1:G6. The data in column F is not required.
Step 3: Adding a Secondary Axis to the Pareto Chart
Go to the “Customize” tab in the chart editor. Under the “Series” option, select “Count” and make sure the type is set to “Column”. Then, select the series “Cumulative %” and set the type to “Line”. Change the axis to “Right”.
Step 4: Essential Customization
To add titles to the chart and axes, click on “Charts & Axis titles” in the “Customize” tab.
Congratulations! You have successfully created a Pareto chart in Google Sheets.
Remember, if you want to learn more about charts and their customization, check out our website Crawlan.com for more tips and tricks.
