Overview
Analyze Data to Answer Questions is the fifth required course and, at about 26 listed hours, the longest of the six phase courses. It covers the analyze phase: organizing data with sorts and filters, converting and formatting values, combining data from multiple sources, and performing calculations. Each skill is taught twice, in spreadsheets (SORT, filters, VLOOKUP, SUMIF, COUNTIF, SUMPRODUCT, pivot tables) and in SQL (ORDER BY, WHERE, CONCAT, JOINs, COUNT DISTINCT, subqueries, GROUP BY, HAVING, CASE and temporary tables).
The course also stresses checking your work: validating calculations by computing the same figure two ways and asking colleagues for input when stuck. It turns the clean data from Course 4 into answers to the SMART question from Course 2, and its outputs are what Course 6 visualizes.
How it's tested
Course 5 module challenges are the most hands-on in the certificate. Expect short SQL queries or spreadsheet formulas where you predict the result, pick the right JOIN or function, or spot the error. Scenario items describe two tables and a business question, and the correct answer is the one that returns exactly the rows and values the question needs.
-
Which rows come back?
Two small tables and a JOIN or WHERE clause are shown, and you predict which rows or how many rows the query returns.
-
Pick the function
A calculation need is described, such as total sales for one region or the number of unique customers, and you choose between SUMIF, COUNTIF, COUNT DISTINCT, a pivot table or a GROUP BY query.
-
Fix the lookup or query
A VLOOKUP returns #N/A or wrong values, or a query filters an aggregate with WHERE, and you identify the correction.
A scenario has a customers table and an orders table. Marketing wants every customer listed, including those with no orders, alongside their order count. Candidates choose INNER JOIN because it is the default join. An INNER JOIN drops customers with no matching orders, so they would disappear. A LEFT JOIN from customers to orders keeps every customer and returns nulls where no order exists, which a COUNT of order_id then reports as zero.
Modules 4
Official modules from the Google outline, in outline order.
5.1 Organize data for more effective analysisSorting and filtering in spreadsheets and SQL.
- What it means
- Organizing data means arranging and narrowing it so patterns are visible, through sorting (ordering rows) and filtering (showing only rows that meet a condition).
- What to understand
- In spreadsheets, sorting a range rather than the whole sheet can break rows apart if not all columns are selected, and the SORT function returns a sorted copy without changing the original. In SQL, ORDER BY sorts ascending by default and needs DESC for descending; WHERE filters rows before any grouping.
- How it shows up
- A query or spreadsheet action is described and you predict the order of results or identify why rows became misaligned.
- Common confusion
- Assuming ORDER BY defaults to descending; it is ascending unless DESC is specified.
5.2 Format and adjust dataConverting and formatting data, combining fields and learning from others.
- What it means
- Formatting and adjusting covers converting text to numbers or dates, applying consistent formats, validating entries, and combining values from several fields into one.
- What to understand
- Numbers stored as text will not calculate correctly until converted, and the same date can be stored in several formats that sort differently. CONCATENATE in spreadsheets and CONCAT in SQL join strings; CAST converts types. The module also encourages using communities such as Stack Overflow and asking colleagues for input when stuck.
- How it shows up
- A column will not sum or sort correctly and you identify the formatting cause and the fix.
- Common confusion
- Changing the displayed format and assuming the underlying type has changed; a value can look like a number while still stored as text.
5.3 Aggregate data for analysisCombining data with VLOOKUP, JOINs, COUNT DISTINCT and subqueries.
- What it means
- Aggregation gathers data from multiple sources into one view, using VLOOKUP in spreadsheets and JOINs and subqueries in SQL.
- What to understand
- VLOOKUP(search_key, range, index, FALSE) searches the first column of the range and returns the value from the column at the given index; FALSE forces an exact match. INNER JOIN returns only matching rows, LEFT JOIN keeps all rows from the left table, RIGHT JOIN keeps all from the right, and FULL OUTER JOIN keeps all rows from both. A subquery is a query nested inside another, and the inner query runs first.
- How it shows up
- Two tables and a business need are described and you pick the JOIN, or a VLOOKUP setup is shown and you identify why it fails.
- Common confusion
- Expecting VLOOKUP to return a value from a column to the left of the search column; it cannot.
5.4 Perform data calculationsFormulas, conditional functions, pivot tables, SQL calculations and temporary tables.
- What it means
- Calculations summarize data to answer the question, using conditional functions and pivot tables in spreadsheets and arithmetic, aggregate functions, GROUP BY and temporary tables in SQL.
- What to understand
- SUMIF and COUNTIF apply one condition, SUMIFS and COUNTIFS apply several, and SUMPRODUCT multiplies matching ranges and sums the results. Pivot tables summarize by rows, columns, values and filters. In SQL, GROUP BY groups rows for aggregates, HAVING filters groups after aggregation, and temporary tables, created with WITH or CREATE TEMP TABLE, store intermediate results.
- How it shows up
- A calculation is requested, such as revenue by product where revenue exceeds a threshold, and you choose the function, clause or pivot setup.
- Common confusion
- Using WHERE to filter on an aggregate such as SUM(sales) > 1000; that requires HAVING.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Four phases of analysis
Google describes analysis as organizing data, formatting and adjusting it, getting input from others, and transforming it to find relationships and patterns.
- Remember
- Organize and format come before calculations; input from others guards against blind spots.
- Tested as
- Framing questions at the start of the course.
- Trap
- Treating analysis as calculation only.
-
ORDER BY
Sorts query results by one or more columns.
- Remember
- Ascending is the default; add DESC for descending.
- Tested as
- Result-order items.
- Trap
- Assuming results are sorted without ORDER BY.
-
VLOOKUP
Searches the first column of a range for a value and returns a value from a column to its right in the same row.
- Remember
- Use FALSE as the last argument for an exact match; the index counts from the first column of the range.
- Tested as
- Formula-reading and error items.
- Trap
- Leaving the last argument TRUE or blank and getting approximate matches.
-
JOIN types
INNER keeps matches only; LEFT keeps all left rows; RIGHT keeps all right rows; FULL OUTER keeps all rows from both.
- Remember
- Unmatched columns in outer joins come back as null.
- Tested as
- Core of Module 3 questions.
- Trap
- Choosing INNER when the question needs records with no match.
-
COUNT vs. COUNT DISTINCT
COUNT counts non-null values; COUNT DISTINCT counts unique non-null values.
- Remember
- Use COUNT DISTINCT for unique customers or products in an orders table.
- Tested as
- Counting questions.
- Trap
- Using COUNT and reporting orders as customers.
-
Subquery
A query nested inside another query, in the SELECT, FROM or WHERE clause.
- Remember
- The inner query runs first and its result feeds the outer query.
- Tested as
- Query-reading items in Module 3.
- Trap
- Reading the outer query first and misjudging the result.
-
GROUP BY and HAVING
GROUP BY groups rows for aggregate functions; HAVING filters those groups.
- Remember
- WHERE filters rows before grouping; HAVING filters after aggregation.
- Tested as
- Calculation queries in Module 4.
- Trap
- Putting an aggregate condition in WHERE.
-
SUMIF, COUNTIF and SUMPRODUCT
SUMIF sums cells meeting a condition, COUNTIF counts them, and SUMPRODUCT multiplies matching arrays and adds the products.
- Remember
- SUMPRODUCT(price_range, quantity_range) gives total revenue in one step.
- Tested as
- Spreadsheet calculation items.
- Trap
- Mixing up the order of range and criterion arguments.
-
Pivot table
A summary tool that groups data by rows and columns and aggregates values, with optional filters and calculated fields.
- Remember
- Pivot tables summarize without changing the source data.
- Tested as
- Hands-on and setup items.
- Trap
- Putting the measure in rows instead of values.
-
Temporary table
A table that exists only for the session or query, created with WITH (a common table expression) or CREATE TEMP TABLE.
- Remember
- Temporary tables simplify multi-step queries by storing intermediate results.
- Tested as
- Module 4 items.
- Trap
- Assuming a temporary table is saved permanently.
-
CASE statement
Returns different values based on conditions, like an IF in SQL, ending with END.
- Remember
- CASE WHEN condition THEN value ELSE value END.
- Tested as
- Categorizing values inside queries.
- Trap
- Forgetting END or the ELSE fallback.
-
Data validation of results
Checking a calculation by reproducing it another way, such as matching a pivot table total with a SUMIF.
- Remember
- Two methods agreeing is strong evidence a result is right.
- Tested as
- Verification items.
- Trap
- Trusting the first output because the formula ran without errors.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
INNER vs. LEFT vs. RIGHT vs. FULL OUTER JOIN
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| INNER JOIN | Returns rows with matching keys in both tables | Drops all unmatched rows | Losing customers with no orders |
| LEFT JOIN | Returns all rows from the left table plus matches from the right | Unmatched right-side columns are null | Swapping table order and keeping the wrong side |
| RIGHT JOIN | Returns all rows from the right table plus matches from the left | Mirror image of LEFT JOIN | Thinking it returns different data from a reversed LEFT JOIN |
| FULL OUTER JOIN | Returns all rows from both tables | Nulls fill gaps on either side | Using it when only one side must be complete |
WHERE vs. HAVING
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| WHERE | Filters individual rows before grouping | Cannot use aggregate functions | Writing WHERE SUM(x) > 100 |
| HAVING | Filters groups after GROUP BY aggregation | Works with SUM, COUNT, AVG and similar | Using it for plain row filters |
VLOOKUP vs. JOIN
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| VLOOKUP | Spreadsheet function that pulls one value from a range by key | Searches only the first column and returns the first match | Expecting it to return several matches or look left |
| JOIN | SQL clause that combines tables on matching keys | Returns every matching row combination | Forgetting that one-to-many joins multiply rows |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Default to INNER JOIN
- What candidates pick
- INNER JOIN for a list of all products, including those never sold.
- Why it looks right
- INNER JOIN is the first join taught and the most common.
- Why it's wrong
- Products without sales have no match and are dropped; a LEFT JOIN from products keeps them with null sales.
Clue to watch for Words such as 'all', 'including those with no' or 'even if'.
-
VLOOKUP looking left
- What candidates pick
- A VLOOKUP that searches by product name to return an ID stored in the column before it.
- Why it looks right
- The data is in the same table, so a lookup seems possible.
- Why it's wrong
- VLOOKUP searches the first column of the range and can only return values to its right; the range must start at the search column.
Clue to watch for The return column sits to the left of the search key.
-
Orders counted as customers
- What candidates pick
- COUNT(customer_id) to report the number of customers.
- Why it looks right
- It counts the customer column.
- Why it's wrong
- Customers with several orders are counted repeatedly; COUNT(DISTINCT customer_id) gives unique customers.
Clue to watch for The table has one row per order or transaction.
-
Aggregate in WHERE
- What candidates pick
- WHERE SUM(revenue) > 10000 after GROUP BY region.
- Why it looks right
- WHERE is the familiar filter.
- Why it's wrong
- WHERE runs before aggregation and cannot use SUM; HAVING filters the grouped results.
Clue to watch for The condition involves SUM, COUNT, AVG, MIN or MAX.
-
Text that looks like numbers
- What candidates pick
- Rewrite the SUM formula when a column totals to zero.
- Why it looks right
- The formula seems to be the broken part.
- Why it's wrong
- The values are stored as text; converting them to numbers fixes the total.
Clue to watch for Values aligned left in a numeric column, or imported from a CSV.
-
Unchecked totals
- What candidates pick
- Report the pivot table total straight away.
- Why it looks right
- It ran without errors.
- Why it's wrong
- Errors such as duplicated join rows or a wrong range produce plausible but wrong totals; validate with a second method such as SUMIF.
Clue to watch for The question asks what to do before sharing results.
Answer framework
Use these steps when a Course 5 item asks you to combine, filter or calculate data.
- Restate exactly which rows the business question needs, including whether unmatched or zero-value records must appear.
- Check data types and formats first, converting text to numbers or dates before calculating or sorting.
- Choose the combining method: VLOOKUP for one value by key in a sheet, the JOIN type that keeps the required rows in SQL.
- Choose the calculation: conditional functions or pivot tables in sheets; GROUP BY with aggregates, using WHERE for rows and HAVING for groups, in SQL.
- Validate the result by computing it a second way and checking row counts for duplication before you share it. Compare row counts before and after each join.
Practice question
Want more like this? The Google Data Analytics practice test has questions for every domain.
Checklist
0 of 13
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Plan for this to be the heaviest course so far and pace it over several weeks. Work Modules 1 and 2 quickly but carefully: sorting and formatting look simple, yet text-stored numbers and inconsistent dates cause many wrong answers later.
Module 3 deserves the most attention. Draw two small tables of five rows each on paper, with some keys missing on each side, and work out by hand what every JOIN type returns before running the queries in BigQuery. Do the same for VLOOKUP with the return column on either side of the key. Read every subquery from the inside out.
In Module 4, pair each spreadsheet method with its SQL equivalent: SUMIF with WHERE and SUM, pivot tables with GROUP BY, and conditional filters on totals with HAVING. The commonly underestimated areas are COUNT DISTINCT and temporary tables. Afterward, take a timed practice set of query-reading items and rebuild any query you miss, then move to Course 6 to present the results.
FAQ
Is Course 5 the hardest course in the Google Data Analytics Certificate?
Many learners find it the most demanding of the first six because it combines advanced spreadsheet functions with JOINs, subqueries and temporary tables. Course 7 on Python is similarly long but covers a new language.
How is Course 5 assessed?
Each of the four modules ends with a graded module challenge, supported by knowledge checks and many hands-on activities in spreadsheets and BigQuery.
Which JOIN types should I know?
INNER, LEFT, RIGHT and FULL OUTER. Know which rows each returns and where nulls appear, since questions often hinge on whether unmatched records are kept.
Why does my VLOOKUP return #N/A?
The search key was not found in the first column of the range, often because of extra spaces, a different data type or a missing record. It also fails if the value you want is to the left of the search column.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY, so it is used for conditions such as SUM(amount) > 50000.
How long should I plan for Course 5?
Coursera lists about 26 hours. Our editorial planning estimate is 35 to 50 hours, because SQL practice takes longer than the videos.
Does Course 5 use R or Python?
Neither. Course 5 uses spreadsheets and SQL. Programming is taught in Course 7, which in the current program is Introduction to Data Analysis Using Python.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 5 knowledge
You have covered sorting, formatting, VLOOKUP, JOINs, subqueries, pivot tables and temporary tables. Test whether you can predict query results and choose the right calculation.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Google Data Analytics AI tutor Get any concept explained your way