How to Analyze Google Forms Data in Google Sheets (Pivot Tables & More)

Updated July 2026 · 10 min read

Google Forms' own Responses tab gives you a quick per-question summary, but it can't cross-reference two questions, calculate custom breakdowns, or build the kind of chart you'd actually put in a report. That's where Google Sheets comes in — once your responses land in a spreadsheet, you have the full power of formulas, pivot tables, and charts to work with. This guide walks through the core techniques, from simple counts to a full pivot table setup.

Where your responses actually land

Open your form's Responses tab and click the green Sheets icon. This creates (or links to) a Google Sheet where every submission appears as a new row, with one column per question. This linked sheet is the raw material for everything in this guide — all the formulas, pivot tables, and charts below work directly off this data, or a copy of it.

Quick counts with COUNTIF

For a fast answer to "how many people picked X," use COUNTIF. If column C holds answers to a Yes/No question, =COUNTIF(C2:C100, "Yes") counts how many respondents answered "Yes." This works for any single-select question — multiple choice, dropdown, or checkboxes — as long as you match the exact answer text. It's the fastest way to sanity-check a number without building a full pivot table.

Averaging Linear Scale and numeric answers

For rating-style questions built with the Linear Scale type, AVERAGE gives you the mean score directly: =AVERAGE(D2:D100) for a column of 1–5 ratings. This is often the single most useful number in a satisfaction or feedback form. See our guide on using the Linear Scale question type for how to set these questions up well in the first place, since the scale range and labels affect how meaningful the average ends up being.

Sorting and filtering responses

Beyond formulas, plain sorting and filtering go a long way. Select your data range and use Data > Create a filter to add dropdown filters to every column header — this lets you isolate responses from a specific date range, or every response where a particular answer was given, without writing any formula at all. Sorting by the timestamp column is the simplest way to see responses in submission order, which matters if you're checking for a burst of activity or a trend over time.

Building a pivot table

Pivot tables are where Sheets analysis really pulls ahead of anything Forms shows natively. A common use case: cross-tabbing two questions, like "which department" against "satisfaction rating," to see whether one team is systematically less happy than another.

1. Click any cell inside your response data (or select the full range including headers).
2. Go to Insert > Pivot table, and choose to place it in a new sheet.
3. In the pivot table editor on the right, add the question you want to group by — e.g. "Department" — under Rows.
4. If you're cross-tabbing, add a second question — e.g. "Satisfaction Rating" — under Columns.
5. Add a value under Values: use COUNTA to simply count responses in each cell, or AVERAGE if the column is numeric and you want a mean instead of a count.
6. Adjust the "Summarize by" setting and sort order until the breakdown reads the way you need.

The resulting table shows you, at a glance, how responses break down across two dimensions at once — something no setting in Forms' own Responses tab can do.

Charts built from your own ranges

Forms' Summary tab already generates basic bar and pie charts per question — our guide on Google Forms response summary charts covers those. But building your own chart in Sheets, referencing a specific range or a pivot table you just built, lets you go further: combine two questions into one chart, filter out incomplete responses first, or style the chart to match a report you're sharing externally. Select the data (or your pivot table), go to Insert > Chart, and pick the chart type — bar and pie remain the most common for this kind of survey data, but a line chart works well if you're tracking responses over time.

Flagging outliers with conditional formatting

Use Format > Conditional formatting to visually flag responses worth a second look — for example, highlighting rows where a required-feeling field was left blank, or where a numeric answer falls far outside the expected range. This is especially useful for longer forms where scrolling through every row manually isn't realistic; a quick color highlight draws your eye straight to what needs attention.

Exporting a clean summary for stakeholders

Once you've built your pivot tables and charts, it's worth creating a separate, cleaned-up sheet tab that only shows the finished summary — not the raw response data — before sharing with stakeholders who don't need to dig through every row. You can then share that specific sheet view, or use File > Download to export it as a PDF or Excel file for people who prefer a static document over a live spreadsheet link.

Checking responses while you're away from your desk

If you want to glance at incoming responses without opening the full spreadsheet, see our guide on viewing Google Forms responses on your phone — useful for keeping an eye on a form while the deeper analysis waits until you're back at a computer.

FAQ

Related guides

• Google Forms response summary charts explained
• Using the Linear Scale question type
• Viewing Google Forms responses on your phone