How to Make a Pareto Chart in Google Sheets

Ever feel like you’re putting in 100% effort for just 20% of the reward? The Pareto principle, better known as the 80/20 rule, suggests that for many outcomes, roughly 80% of the results come from 20% of the causes. Building a Pareto chart in Google Sheets is the simplest way to visualize this principle with your own data, helping you pinpoint exactly where to focus for maximum impact. This guide will walk you through creating a detailed Pareto chart from scratch, step by step.

What Exactly is a Pareto Chart and Why Should You Use One?

A Pareto chart is a special hybrid chart that contains both a column chart and a line graph. The columns show the individual values for different categories, sorted in descending order, while the line graph superimposed on top shows the cumulative percentage total of these categories. This combination makes it exceptionally easy to identify the "vital few" - the handful of categories that contribute the most to the overall effect - from the "trivial many."

The beauty of this chart is its simplicity and broad application. It transforms raw numbers into a clear, actionable story about your business. You can use it to answer critical questions like:

• Marketing: Which 20% of my blog posts are driving 80% of organic traffic? Which ad campaigns generate the bulk of our conversions?
• Sales: Do 80% of our sales come from 20% of our customers? Which product lines represent the majority of our revenue?
• E-commerce: What are the top product categories that account for most of our returns?
• Operations: What are the top 20% of causes for shipping delays or customer complaints?

By answering these questions visually, you can stop spreading your resources thin and start investing them where they will deliver the most significant results.

Preparing Your Data for Analysis

Before you build the chart, you need to set up your data correctly in a Google Sheet. All you need are two columns: one for your categories (the "causes") and one for your metric (the "effect"). Let’s use a common marketing example: tracking website traffic sources.

Let's say your raw data looks like this:

Column A: Source Column B: Sessions

The Critical First Step: Sort Your Data

A Pareto chart requires that your data be sorted in descending order by the metric column (in our case, the 'Sessions' column). This is a non-negotiable step, as it arranges the columns from highest impact to lowest.

Here’s how to sort it:

1. Highlight your entire data range, including the headers (e.g., A1:B7).
2. Go to the menu bar and click Data > Sort range > Advanced range sorting options.
3. Check the box that says "Data has header row."
4. Under "Sort by," choose the name of your metric column (e.g., "Sessions").
5. Select Z → A to sort in descending order.
6. Click Sort.

After sorting, your data should look like this, with the highest session count at the top:

• Organic Search: 4,250
• Direct: 2,100
• Social Media: 950
• Referral: 520
• Paid Search: 300
• Email: 120

Step-by-Step: Building Your Pareto Chart in Google Sheets

With your data sorted, you're ready to build the chart. This process involves adding a couple of helper columns to perform a few calculations and then setting up the combo chart. Let's dive in.

Step 1: Calculate the Cumulative Total

Your first new column will calculate a running total of your sessions. Call this column Cumulative Total.

1. In cell C1, type the header Cumulative Total.
2. In cell C2, type =B2.
3. In cell C3, type =C2+B3.
4. Drag the fill handle from C3 down to the end of your data rows to apply this formula to all rows.

Your sheet now has a column that shows the cumulative sum building up with each data source.

Step 2: Calculate the Cumulative Percentage

Your second new column will turn that cumulative total into a percentage of the whole. This calculated percentage will form the line graph on your Pareto chart.

1. In an empty cell below the 'Sessions' data (e.g., B8), type =SUM(B2:B7) to get the grand total of all sessions.
2. In cell D1, type Cumulative Percentage.
3. In cell D2, enter =C2/$B$8 (assuming B8 has the grand total, if using SUM(B2:B7) directly, use =C2/SUM(B2:B7)).
4. Drag the formula from D2 down to all rows.
5. Select all values in column D, and format as percentage via the toolbar.

Now, your data includes the original categories, their values, the cumulative totals, and the cumulative percentages.

Step 3: Create the Combo Chart

1. Select columns A, B, and D (categories, values, and cumulative percentages). Hold down Ctrl (or Cmd on Mac) to select multiple ranges if necessary.
2. Go to Insert > Chart.
3. In the Chart editor, set the Chart Type to Combo Chart.
4. Under the Setup tab, ensure that the series for "Sessions" is set to Columns on the Left axis, and "Cumulative Percentage" is set to Line on the Right axis.

Step 4: Configure the Chart Axes

1. In the Chart editor, click the Customize tab.
2. Expand Series.
3. For "Sessions," ensure the Type is Columns and Axis is Left.
4. For "Cumulative Percentage," set Axis to Right.
5. Click on Vertical Axis (Right) and set the Max value to 1 (which represents 100%) for clarity.

Your chart now properly displays bars for each source and a line representing the cumulative percentage climbing toward 100%.

Step 5: Add Finishing Touches

1. Double-click the chart title to rename it, e.g., "Pareto Analysis of Website Traffic."
2. Under Chart & axis titles in the Customize tab, label axes appropriately:
3. To make the chart clearer, ensure the right axis max is 1 (100%).

How to Read and Use Your New Pareto Chart

Follow the red cumulative percentage line from left to right until it crosses the 80% mark. Look down to see which categories fall to the left of that point. These are your "vital few."

In this example, the line crosses the 80% threshold after only two categories: Organic Search and Direct traffic. This indicates these two sources are responsible for over 80% of all website sessions. Focusing efforts on these can maximize impact.

Final Thoughts

Creating a Pareto chart in Google Sheets is a fantastic exercise to understand the powerful simplicity of the 80/20 rule, helping you identify and prioritize the factors that truly drive your business. By sorting your data and using helper columns in combination with a combo chart, you transform a confusing table of numbers into clear, actionable insights.

While building these charts manually is great for learning, it can be time-consuming with multiple data sources from platforms like Google Analytics, Shopify, and ad managers. For quick insights without endless formula-writing, modern reporting tools like Graphed make it effortless to connect your data and generate real-time dashboards and reports in seconds, not hours.