How to Create a Summary Report in Google Sheets

Transforming rows of messy data into a clean, understandable summary report is a common challenge for anyone working with spreadsheets. That giant table of daily sales, website visits, or project tasks holds valuable insights, but they're hidden until you summarize them. This guide will walk you through three powerful methods for creating summary reports in Google Sheets, from simple formulas to dynamic, interactive tables.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Makes a Good Summary Report?

Before jumping into the "how," let's quickly define what we're trying to build. A great summary report doesn't just show numbers, it tells a story. It should always be:

• High-Level: It provides a bird's-eye view, focusing on totals, averages, and key aggregations rather than individual line items.
• Relevant: It highlights the most important key performance indicators (KPIs) that matter to you or your team, such as total revenue, conversion rate, or average deal size.
• Easy to Understand: A good report is scannable. Someone should be able to glance at it and immediately grasp the main takeaways without needing a deep dive into the raw data.
• Actionable: The insights from the report should help you make better decisions. You should see what’s working, what isn't, and where you might need to focus your attention.

For example, if you have a spreadsheet with 5,000 individual sales transactions, the raw data itself is hard to interpret. A summary report would roll that up to show you total sales per product category, revenue by region, or top-performing salespeople for the month.

Before You Begin: Prepping Your Data for Success

The quality of your report depends entirely on the quality of your source data. A few minutes of preparation can save you hours of frustration. Before building your summary, make sure your data is structured as a proper table:

• Use Consistent Headers: Each column must have a unique, clear header in the first row (e.g., "Date," "Product," "Sales," "Region").
• No Blank Rows or Columns: Ensure there are no completely empty rows or columns within your data set, as this can break formulas and pivot tables.
• One Record Per Row: Each row should represent a single entry or transaction (e.g., one sale, one website session, one lead).
• Format Data Consistently: Make sure dates are formatted as dates, numbers as numbers or currency, and text is spelled consistently. Inconsistent spellings like "CA," "California," and "california" will be treated as three separate categories.

Cleaning up your dataset first ensures that Google Sheets can accurately read, sort, and calculate your information. Now, let’s build the report.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: Creating a Simple Summary with Formulas

For quick and straightforward summaries, you don't always need a complex tool. Google Sheets' built-in formulas, like SUMIF, COUNTIF, and AVERAGEIF, are perfect for creating static summary tables. This method involves creating a separate, small table and using formulas to pull in the aggregated data.

Step 1: Get Total Values with SUMIF

The SUMIF function adds up numbers in a range that meet a specific single condition. It's ideal for questions like, "What are the total sales for Product A?"

The syntax is: =SUMIF(range, criterion, [sum_range])

• range: The column you want to check for your condition (e.g., the "Product" column).
• criterion: The value you're looking for (e.g., "Product A").
• sum_range: The column containing the numbers you want to add up (e.g., the "Sales" column).

Let's imagine you have a sales table in columns A through C. Column A has the Product Name and Column C has the Sale Amount. To find the total sales for "Hoodies," you would use this formula:

=SUMIF(A2:A100, "Hoodies", C2:C100)

Step 2: Count Occurrences with COUNTIF

The COUNTIF function counts how many times a certain value appears in a range. This is perfect for questions like, "How many times did we sell 'Hoodies'?"

The syntax is simpler: =COUNTIF(range, criterion)

Using the same sales table, to count the number of sales transactions that involved "Hoodies," you'd write:

=COUNTIF(A2:A100, "Hoodies")

By creating a small table with your unique products and using these formulas for each one, you can build a clean summary report quickly. If you need to check multiple criteria (e.g., sales of "Hoodies" in the "West" region), you can use the more powerful SUMIFS and COUNTIFS functions.

Method 2: Building a Dynamic Report with Pivot Tables

When you have multiple questions to ask of your data, or if you want an interactive way to explore it, formulas can become cumbersome. This is where Pivot Tables shine. A pivot table is an interactive tool that lets you summarize large datasets with an easy-to-use drag-and-drop editor.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Create a Pivot Table in Google Sheets

1. Select Your Data: Click anywhere inside your data table. It's usually best to select the entire dataset, including headers (e.g., A1:D5000).
2. Insert Pivot Table: Go to the menu and click Insert > Pivot table.
3. Choose Destination: Google Sheets will ask if you want to insert the pivot table on a "New sheet" or an "Existing sheet." Choosing a new sheet is recommended to keep your workbook organized. Click Create.
4. Build Your Report: A new blank sheet will appear with the Pivot table editor on the right side. This is where you bring your report to life.

The editor has four main sections:

• Rows: The data you drag here will create the row labels of your summary table. (e.g., Product Category, Sales Rep)
• Columns: This creates the column headers, great for breaking data down further. (e.g., Month, Region)
• Values: This is where you put the metric you want to calculate. (e.g., Sales, Revenue, Units Sold)
• Filters: This lets you add a filter to focus on a specific subset of your data. (e.g., filter to only show data for the year 2023).

Example: Building a Sales Summary

Let’s use our sales data example. To create a report showing total sales for each product, broken down by region, you would:

1. Drag the "Product" field to the Rows section.
2. Drag the "Region" field to the Columns section.
3. Drag the "Sales" field to the Values section. By default, it will calculate the 'SUM'.

Instantly, Google Sheets will generate a perfect summary table. The best part is that it's dynamic. You can easily drag 'Region' from Columns and swap in 'Month' to see a new summary - no new formulas required.

Method 3: Maximum Flexibility with the QUERY Function

The QUERY function is arguably the most powerful function in Google Sheets. It lets you use database-style language (a simplified version of SQL) to pull, filter, group, and organize your data with a single formula. It has a steeper learning curve but offers incredible control over your report's output.

The basic syntax is: =QUERY(data_range, "query_statement")

• data_range: Your full dataset (e.g., 'Sales Data'!A1:D5000).
• query_statement: The instructions you give the function, written in quotes.

Basic QUERY Clauses

Your "query_statement" is built using clauses. Here are the most common ones for summary reports:

• SELECT: Specifies which columns you want to output. You can also perform calculations like SUM(C) or AVG(D). (Here, C and D refer to column letters.)
• WHERE: Filters your data to only include rows that meet specific criteria (e.g., WHERE D = 'West').
• GROUP BY: Aggregates rows based on a specific column. This is essential for summary reports.
• ORDER BY: Sorts your results in ascending (asc) or descending (desc) order.

Example: Recreating the Sales Summary with QUERY

Imagine your data is in a sheet named 'Sales Data', with Product in column B and Sales Amount in column C. To create a summary showing the total sales for each product, sorted from highest to lowest, you would use this powerful formula:

=QUERY('Sales Data'!A1:C5000, "SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC")

Let's break this down:

• SELECT B, SUM(C): "Show me column B and the sum of column C."
• GROUP BY B: "Calculate that sum for each unique value in column B."
• ORDER BY SUM(C) DESC: "Sort the results based on the total sales, from highest to lowest."

The result is a clean, two-column report that automatically updates whenever your source data in 'Sales Data' changes.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Bringing It All to Life: Visualizing Your Summary

A numbers-only report is good, but a visual report is better. Charts and graphs make your summarized data instantly digestible. Once you’ve created your summary table using any of the three methods above, you can easily create a chart from it.

1. Select the data in your summary table (including the headers).
2. Go to the menu and click Insert > Chart.
3. Google Sheets will generate a recommended chart type. You can easily change it using the Chart editor that appears on the right.

A quick tip for chart selection:

• Use a Bar or Column Chart to compare categories (e.g., sales per product).
• Use a Line Chart to show trends over time (e.g., monthly revenue).
• Use a Pie Chart to illustrate a share of the total (e.g., sales contribution by region).

Final Thoughts

Whether you're using simple formulas like SUMIF, flexible pivot tables, or the powerful QUERY function, Google Sheets provides a full set of tools to turn raw data into a clear summary report. Mastering these techniques will empower you to find actionable insights buried in your spreadsheets and communicate them effectively.

Building these reports in spreadsheets is a valuable skill, but it can still be a time-consuming process, especially when you need to pull in information from separate tools like Google Analytics, Shopify, or your HubSpot CRM. We built Graphed to remove this friction entirely. After connecting your apps (which takes just a few clicks), you can use plain English to ask for the report you need - for instance, "Create a report showing our top-selling products from Shopify compared to our ad spend on Facebook for last month." The report is created for you in seconds, with live data that's always up to date and ready for you and your team to use.