Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 29 ยท Projects, Practice and Interviews

Practice Exercises with Solutions

Practice Google Sheets with exercises on formulas, lookups, SUMIFS, QUERY and pivot tables. Try each one yourself, then compare with the solution.

You learn spreadsheets with your hands, not your eyes. This lesson gives you a set of exercises on formulas, text, lookups, conditional totals, QUERY and pivot tables. Each one comes with a small data set, a task, and a solution that you should only open after you have tried it yourself in a real sheet.

The formulas are read-only here because Google Sheets cannot run on this page. The TryIt blocks are Python stand-ins that model the same data, so you can check the answer your formula should produce.

How to practice

Copy the data Paste it into a new sheet.
Try first Spend ten minutes before peeking.
Compare Match your result to the stand-in.
Vary it Change the data and rerun.

A different formula that gives the same answer is fine.

1 / 2

Data set

Paste this into cells A1:D9 of a blank sheet. All exercises use it.

A: Name B: Region C: Product D: Units
Ana Ruiz West Pen 40
ben ortiz East Notebook 25
Cara Lee West Notebook 60
Dev Patel North Pen 15
Eli Stone East Pen 55
Fay Chen West Pen 30
Gus Brown North Notebook 20
Hana Ito East Notebook 45

Level 1: formulas and text

  1. Total units in all rows.
  2. Average units, rounded to one decimal place.
  3. A clean name column: trim spaces and capitalize each word (ben ortiz becomes Ben Ortiz).
  4. A first name column using SPLIT or LEFT with FIND.
  5. A flag column: "Big" when units are 50 or more, otherwise "Small".

Solutions

TEXT
1.  =SUM(D2:D9)
2.  =ROUND(AVERAGE(D2:D9), 1)
3.  =PROPER(TRIM(A2))
4.  =INDEX(SPLIT(A2, " "), 1, 1)      or   =LEFT(A2, FIND(" ", A2) - 1)
5.  =IF(D2>=50, "Big", "Small")

Remember PROPER fixes the capital letters, and TRIM also removes doubled inner spaces (Working with Text, IF, AND, OR and IFS).

Python Stand-in: answers for Level 1

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Level 2: conditional totals

  1. Units sold in the West.
  2. Number of Notebook sales.
  3. Average units for Pen in the East or West (hint: two separate formulas, or a SUMIFS ratio).
  4. Units for Notebook in the East.
  5. Names starting with a letter between A and C (count). Hint: wildcards.

Solutions

TEXT
6.  =SUMIF(B2:B9, "West", D2:D9)
7.  =COUNTIF(C2:C9, "Notebook")
8.  =(SUMIFS(D2:D9, C2:C9, "Pen", B2:B9, "East") + SUMIFS(D2:D9, C2:C9, "Pen", B2:B9, "West"))
     / (COUNTIFS(C2:C9, "Pen", B2:B9, "East") + COUNTIFS(C2:C9, "Pen", B2:B9, "West"))
9.  =SUMIFS(D2:D9, C2:C9, "Notebook", B2:B9, "East")
10. =COUNTIF(A2:A9, "A*") + COUNTIF(A2:A9, "B*") + COUNTIF(A2:A9, "C*")

COUNTIF is not case-sensitive, so ben ortiz is counted by "B*". Details in SUMIF, COUNTIF, AVERAGEIF and Friends.

Python Stand-in: answers for Level 2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Level 3: lookups

Create a second tab Prices with A1:B3:

Product Price
Pen 1.25
Notebook 4.50
  1. In column E of the data tab, show the unit price of each row.
  2. In column F, show the revenue (units ร— price).
  3. Show the name of the person with the most units in one cell.
  4. If a product is missing from Prices, show "No price" instead of an error.

Solutions

TEXT
11. E2:  =XLOOKUP(C2, Prices!$A$2:$A$3, Prices!$B$2:$B$3, "No price")
12. F2:  =IFERROR(D2*E2, "")
13. =INDEX(A2:A9, MATCH(MAX(D2:D9), D2:D9, 0))
14. Already handled by the fourth argument of XLOOKUP in 11.

The key habits are exact matches and absolute references (XLOOKUP, INDEX and MATCH, Relative, Absolute and Mixed References). Without a fallback, D2*E2 on a missing price would raise #VALUE! because "No price" is text, which is why exercise 12 wraps it in IFERROR.

Python Stand-in: answers for Level 3

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Level 4: QUERY and pivot tables

  1. List name and units for everyone in the West, largest first.
  2. Show total units per region, sorted by total.
  3. Build a pivot table with Region in rows, Product in columns and the sum of Units as values.

Solutions

TEXT
15. =QUERY(A1:D9, "select A, D where B = 'West' order by D desc", 1)
16. =QUERY(A1:D9, "select B, sum(D) group by B order by sum(D) desc label sum(D) 'Total units'", 1)

For 17, choose Insert > Pivot table, put Region under Rows, Product under Columns and Units under Values with "SUM" (Pivot Tables). Learn the query language in The QUERY Function.

Python Stand-in: QUERY and pivot results

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Stretch challenges

  • Make a dropdown of regions and show its total next to it.
  • Highlight rows where units are above the average with a custom formula rule.
  • Rebuild exercise 16 with SUMIF and UNIQUE instead of QUERY.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Which formula returns the total units for the West region?

    SUMIF checks the criteria range (regions) and adds the matching cells in the sum range (units).
  2. 2. What does the label clause in QUERY change?

    label renames output columns, for example 'Total units' instead of 'sum Units'.
  3. 3. Why is it wise to give XLOOKUP a fourth argument in the price exercise?

    The missing_value argument replaces #N/A, so one unknown product does not produce an error.

Summary

  • Practice with a single shared data set, and try each exercise before reading the solution.
  • Text exercises need TRIM, PROPER, SPLIT, LEFT and FIND.
  • Conditional totals use SUMIF, COUNTIF and their ...S versions, with wildcards when needed.
  • Lookups use exact matches, absolute references and a fallback value.
  • QUERY and pivot tables answer grouping questions in one step.

Next: Interview Questions