Your spreadsheet filled up automatically thanks to Google Forms — and now? A hundred rows of raw data don’t speak on their own. This article teaches you the three moves that turn any spreadsheet into a working data analyst: conditional formulas (COUNTIF, SUMIF, AVERAGEIF), pivot tables, and dashboards. All inside Google Sheets, all free, zero code required. Second installment of the “Digital Stack 2026” series.
You connected Google Forms to Sheets, responses are flowing in automatically, the Timestamp column updates on its own. Perfect. And now? You look at the spreadsheet and see a hundred rows — dates, codes, categories, numbers. Data, yes. But not yet answers.
The problem isn’t the volume of data. A raw spreadsheet is like a library without a catalog: everything is there, but finding what you need takes hours. Conditional formulas, pivot tables, and charts are the catalog. Learning them doesn’t require being a developer — it requires knowing what questions you want to ask your data.
The Starting Point: a Structured Dataset Is Half the Work
Before opening a formula, look at your dataset. The example above shows 9 columns and 12 rows of operational data: Date in date format, a unique Code, Age as a number, Citizenship with fixed values, Request Type with predefined categories, How They Found Us with enumerated options, Urgency on a 1-5 scale, Support Hours as a number, and Notes as the only free-text field.
Why Standardization Is the Key
This dataset works well because almost every analytical column uses predefined values — not free text. That’s exactly the principle applied when building the form in the previous article: multiple choice instead of open answer, numeric scale instead of generic text field. The result: Sheets aggregates everything automatically, without any preliminary cleanup.
The Notes column is the only intentional exception: it captures qualitative context that dropdowns can’t collect. But for quantitative analysis — how many hours, which service, what urgency level — that column stays out of the formulas entirely.
Conditional Formulas: From Numbers to Answers
Three formulas cover 90% of daily operational analysis. Learning them takes less than twenty minutes. Using them well requires understanding one rule: exact matching.
COUNTIF — The Formula That Answers “How Many?”
Syntax: =COUNTIF(range, criterion). Counts how many cells in a range satisfy a condition. On the example dataset:
- =COUNTIF(E2:E13,”Legal”) → returns 4 (four legal support requests)
- =COUNTIF(D2:D13,”Italian”) → returns 4 (four records with Italian citizenship)
- =COUNTIF(G2:G13,”>=4″) → returns 5 (five cases with high or maximum urgency)
The critical rule: the text criterion must match exactly what’s in the cell. =COUNTIF(D2:D13,”Italy”) returns 0, not 4 — because the actual cell value is “Italian”, not “Italy”. One letter of difference, completely wrong result. No error message: just a silent zero. Always verify your criteria against actual column values before trusting any result. This silent failure is one of the most common spreadsheet mistakes in practice.
SUMIF and AVERAGEIF — Aggregating with Conditions
COUNTIF counts. SUMIF sums. AVERAGEIF calculates an average. The structure always has three parameters: =SUMIF(condition_range, criterion, sum_range).
Applied to the dataset: =SUMIF(G2:G13,”>=4″,H2:H13) looks at the Urgency column (G), finds all rows with value ≥4, and sums the corresponding Support Hours (H). Result: 47 — exactly the “High Urgency Hours” KPI visible in the dashboard. High-urgency hours (47) versus total hours (90): more than half of all resources absorbed by 42% of cases. A number that drives resource allocation decisions.
Even more powerful: =AVERAGEIF(G2:G13,”>=4″,C2:C13) calculates the average age only for high-urgency cases. Result: 26.8 years — against an overall average of 42. That single number tells a story: cases requiring immediate response involve significantly younger individuals. The kind of insight that shapes how you staff and prioritize.
Pivot Tables: Complex Analysis in 4 Clicks
Formulas answer specific questions you already know to ask. Pivot tables answer questions you haven’t thought of yet. They take the entire dataset and reorganize it along dimensions you choose — revealing patterns that are completely invisible when you read row by row.
How to Build Your First Pivot (Step by Step)
Select your entire dataset including headers. Go to Insert → Pivot table → New sheet. A blank sheet opens with the pivot editor on the right. Drag Citizenship to Rows, Request Type to Columns, Code to Values with COUNTA as the function. In 30 seconds you get the table shown above.
What does that pivot say? Legal support (4 cases) and psychological support (4 cases) are the most frequent request types. Foreign nationals concentrate almost entirely on legal support — 3 out of 4 cases. Italian citizens focus primarily on psychological support (3 out of 4). This kind of insight drives decisions about which professionals to involve, which services to strengthen, where to allocate resources. All from four clicks on a dataset you already had.
Conditional Formatting: Automatic Traffic Lights for Your Data
Select the totals column of your pivot, go to Format → Conditional formatting, set green for values above average and red for values below. Anomalies and peaks become visible at a glance without reading every number. In datasets with 50, 100, or 500 rows, colors guide the eye exactly where it needs to go — including the situations you weren’t looking for but need to see.
From Spreadsheet to Dashboard: When Numbers Become Decisions
The dashboard above is the end result of the entire process. Six KPIs calculated with formulas — Total 12, Italian citizens 0 (check your formula — see the exact matching section above), Average age 42, Average age for urgent cases 26.8, Total support hours 90, High-urgency hours 47 — plus three automatically generated charts: a bar chart for urgency level distribution, a line chart for the temporal trend of new cases (with the visible spike on January 8th — 4 cases in a single day), and a donut chart for citizenship distribution — Italian and Foreign at 33.3% each, French and Spanish at 16.7% each.
Which Chart for Which Question
The choice of chart type isn’t aesthetic — it’s semantic. Bars for comparing categories: “which service type is most common?”. Line for showing change over time: “are new contacts increasing week over week?”. Donut or pie for proportions of a whole — but only with a maximum of 5-6 slices, beyond that it becomes unreadable. Never use 3D decorative charts: they distort visual proportions and cause misreading even in simple datasets.
To create a chart from a pivot: select the pivot cells, go to Insert → Chart. Sheets automatically suggests the most appropriate type. Customize title, colors, and data labels in the chart editor. Export as PNG via the three-dot menu — ready to drop into a Word report or PowerPoint presentation with no further processing.
AI as Co-Analyst: Gemini and ChatGPT
Google Workspace with Gemini integrated lets you query data in natural language directly inside Sheets: “help me write a formula to sum hours for rows where urgency is high”, “suggest a chart for this data”. It doesn’t replace formulas — it accelerates them, especially when you can’t remember the exact syntax for something you do less often.
ChatGPT and Claude work differently but complementarily: upload aggregated, anonymized data — never individual records, GDPR — and ask for interpretations. “This dataset shows: overall average age 42, average age for urgent cases 26.8, 52% of hours consumed by 42% of cases. What does this suggest for resource allocation?”. AI can identify correlations you wouldn’t have explicitly searched for. The golden rule is non-negotiable: aggregates only, never personal data.
From Raw Data to Decisions: What Comes Next
The workflow is complete. Google Forms collects clean, standardized data. Google Sheets transforms it into insights with conditional formulas, pivot tables, and charts. The dashboard is the endpoint: six numbers, three charts, all key answers on a single screen. No Python, no installations, zero additional cost beyond what you’re already using.
In the next article of the Digital Stack 2026 series, we shift gears: when your data has complex relationships that a spreadsheet can’t model — social networks, supply chains, knowledge graphs — a graph database like Neo4j becomes the right tool. But for 90% of daily operational analysis, what you’ve learned here already covers everything you need.
