How to Build a Pareto Chart in Google Sheets

Have you ever wondered which problems or tasks deserve your attention the most? Maybe you’re dealing with frequent customer complaints or recurring issues at work. A Pareto chart can help you pinpoint the biggest culprits by showing which factors have the greatest impact. Creating this powerful visual tool with Google Sheets is easier than you might think.

What Is a Pareto Chart?

A Pareto chart combines a bar graph and a line graph. The bars show data in descending order of importance, while the line tracks the cumulative percentage. It’s based on the 80/20 rule, where 80% of effects often come from 20% of the causes. For example, 80% of customer complaints might come from 20% of your service issues.

Step 1: Gather Your Data

Start with your data. You’ll need two columns:

• Category: The items you want to analyze (examples: product defects or customer complaints).
• Frequency: The count for each category (how often each occurs).

Step 2: Sort the Data

Highlight your data and sort it in descending order based on frequency. This will make the biggest contributors appear first. Select the data range (excluding headers). Go to the menu: Data > Sort range > Advanced range sorting options. Choose “Frequency” and set it to sort from Z → A.

Step 3: Add a Cumulative Percentage Column

The cumulative percentage shows how much each category adds up to the total. Add two new columns.

Total Frequency

Add a cell below the frequency column to calculate the sum using =SUM(B2:B5) (adjust range as needed).

Cumulative Percentage

Calculate the running total:

For the first category: =B2/$B$6 (divide the frequency by the total).

For the next rows: =C2+B3/$B$6 (add the percentage of the current category to the cumulative total above).

Drag this formula down for all rows. Format this column as a percentage for better readability.

Step 4: Create the Pareto Chart

Highlight the Category and Frequency columns. Go to the menu: Insert > Chart. In the Chart editor, set the chart type to “Column chart.”

Add the Cumulative Line

Click on the chart to open the editor. Under Setup, click Add series, and choose the Cumulative Percentage column.

Customize the Line:

Under the Customize tab in the Chart editor, go to Series and select the cumulative percentage line. Change it to a line chart and adjust the color and style.

Step 5: Adjust the Axes

Under Customize > Vertical axis, adjust the range to fit your data (0-100%). Add a second axis for the cumulative percentage: Go to Customize > Series, click on the cumulative percentage, and select “Right axis.”

Get the Free Pareto Chart Template

Get a copy of the free Pareto Chart Template. I’ve already populated some cells as examples, but you can customize them as needed.

Final Thoughts

Data tells a story, but how you present it shapes your actions. A Pareto chart isn’t just a graph—it’s a lens to focus on what truly matters. Use it as a reminder that small, strategic changes can lead to big results. Start with what’s in front of you, and let the data guide your next steps.

Frequently Asked Questions

Can I add data labels to my Pareto chart?

Yes, to make your chart more informative. In the Chart Editor, go to Customize > Series. Toggle on Data Labels for the bars and the cumulative line. Adjust their font size, color, or position as needed.

What formula is used for calculating cumulative percentages?

To calculate the cumulative percentage for each row. In the first cell of the cumulative percentage column (e.g., C2), enter =B2/SUM($B$2:$B$5). This divides the row’s value by the total sum of all values. Drag the formula down to apply it to all rows. Format the column as percentages using the % button in the toolbar.

How do I interpret a Pareto chart?

Focus on the tallest bars, as they represent the most impactful categories. Use the cumulative line to see what percentage of the total is covered by the most significant categories. This helps you identify the “vital few” from the “trivial many.”