The spreadsheet skills most useful for Google Data Analytics coursework are cleaning a rectangular dataset, choosing the right formula, joining a lookup value accurately, and checking an aggregate against the source. A formula that returns a number is not enough: you should explain what that number counts and which rows it excludes.
Google lists spreadsheets among the tools in its Data Analytics Certificate. The current course sequence also includes SQL, visualization, Python, and a capstone. This worksheet is original practice for the spreadsheet portion. It is not a list of official quiz questions or a claim that every listed formula appears in a particular assessment.
1. Build a worksheet you can check by hand
Enter the following synthetic data in cells A1:E6. Keep one row per order and one header row. Do not insert merged cells or subtotal rows inside the data. Store the amounts as numbers; currency symbols can be a display format. All revenue amounts in this example are USD and exclude tax.
| Order | Region | Status | Revenue | Product |
|---|---|---|---|---|
| O1 | East | Complete | 40 | P1 |
| O2 | West | Complete | 60 | P2 |
| O3 | East | Cancelled | 20 | P1 |
| O4 | East | Complete | 30 | P2 |
| O5 | West | Complete | 50 | P1 |
Before using formulas, predict the answers. All five recorded amounts total 200. Four completed orders total 180. East completed revenue is 40 + 30 = 70, and West completed revenue is 60 + 50 = 110. These checks catch a wrong range or missing status filter immediately.
2. Calculate totals with a defined population
=SUM(D2:D6) returns 200 because it includes the cancelled order. To answer “How much revenue came from completed orders?”, use =SUMIF(C2:C6,"Complete",D2:D6). The status range provides the condition and the revenue range provides the amounts; the result is 180.
For two conditions, use =SUMIFS(D2:D6,B2:B6,"East",C2:C6,"Complete"). SUMIFS puts the sum range first, followed by each condition range and condition. The answer is 70. Confusing the SUMIF and SUMIFS argument order is a common source of errors when adapting a formula.
=COUNTIF(C2:C6,"Complete") returns 4. It counts matching cells, not dollars or unique customers. Divide the completed revenue by the completed-order count: 180 / 4 = 45 average revenue per completed order. The cancelled order does not belong in either numerator or denominator. If the question were average recorded value across all orders, 200 / 5 = 40 would answer that different question.
3. Use an exact-match lookup
Create a second table in H1:I3 with headers Product and Name. Enter P1 / Notebook and P2 / Pen set. In F2 enter =VLOOKUP(E2,$H$2:$I$3,2,FALSE). E2 contains P1; VLOOKUP searches the first column of the lookup range and returns column two, so the result is Notebook.
The dollar signs keep the lookup range fixed when you fill the formula through F6. The lookup cell changes from E2 to E3, E4, and so on. Without the absolute references, the small lookup range can slide downward and lose the P1 record. FALSE requests an exact match, which is appropriate for product identifiers.
Google's VLOOKUP reference explains match behavior and range requirements. Approximate matching has legitimate uses, such as a carefully sorted threshold table, but it is a poor default for IDs. Also inspect the lookup table for duplicate product IDs: a returned label does not prove the relationship is one-to-one.
Change E6 to P9 as a deliberate error. The lookup should fail because P9 is absent. An explicit “Unknown product” marker can help reporting, but do not hide every lookup error with an empty string. Keep an error count and fix the source or mapping; otherwise an apparently clean dashboard conceals missing product information.
4. Separate cleaned text from the raw value
If a region is entered as “ east ”, =UPPER(TRIM(B2)) creates a normalized EAST value when applied to that cell. Put the cleaned value in a helper column instead of overwriting the raw import immediately. Compare category counts before and after normalization to confirm that EAST and WEST are the only intended regions.
TRIM addresses ordinary spacing, not every possible invisible character or misspelling. A region of EATS remains incorrect after changing case. Record a mapping only when you have evidence for the intended value. For numeric columns, changing a cell's format to Number is not always the same as converting imported text into a numeric value; verify that sums and counts behave as expected.
5. Reconcile a pivot table
Build a pivot from the full A1:E6 range. Set Region as rows, Revenue as values summarized by SUM, and Status as a filter limited to Complete. The rows should show East 70 and West 110, with grand total 180. A total of 200 means the cancelled order is still included.
If the pivot shows 2 for each region, inspect the aggregation: it may be counting revenue entries rather than summing their amounts. If new orders are missing later, inspect whether the source range includes them and whether the pivot needs refreshing in your spreadsheet tool. A correct first result is not evidence that future imports will be covered automatically.
For a second check, remove the filter temporarily and confirm East becomes 90 while West remains 110. Restore the Complete filter before sharing. Name the output “Completed order revenue by region,” so the reader does not mistake it for all-order revenue.
6. Diagnose references and averages
Use relative references for row-specific calculations and absolute references for fixed assumptions or lookup ranges. A copied formula should change only the parts you intend. Click one result near the bottom of a filled range and read its references; this catches errors that the first row cannot reveal.
A regional average cannot always be averaged again without weights. Suppose one region has two orders averaging $40 and another has eight averaging $20. Combined revenue is 2 × 40 + 8 × 20 = 240 across ten orders, so the overall average is $24. Averaging the two averages yields $30 and gives the small region too much influence. Preserve the counts needed to weight summaries.
7. Prepare the workbook for someone else
Keep tabs for raw data, cleaning rules, calculations, and the report. Add units, date coverage, and the definition of Complete. Test the filters, inspect missing values, and remove private source data before publishing. A screenshot of the pivot alone does not show whether cancelled orders or duplicate records were handled correctly.
Verified October 14, 2026: spreadsheet inclusion checked against Google; exact-match lookup behavior checked against Google Sheets documentation. The worksheet, totals, and practice sequence are original examples, not official grading requirements.
Practice next
Use the Google Data Analytics Certificate overview to place this topic in the current course sequence. Connect the worksheet checks to the data-cleaning example and the chart-selection exercise.
The Google Data Analytics study guide PDF includes spreadsheet formula references, cleaning notes, and practice questions. The 242-page expanded edition includes current Python labs, 120 multiple-choice questions, 69 applied prompts, and companion practice files. It is an independent supplement to the official coursework. For questions as you practice, explore the separate SimpuTech Google Data Analytics study coach.