Google Sheets practice exercises with answers, from SUM to QUERY

These Google Sheets practice exercises are the ones that come up at work: total a column, count open tickets, find a price, list the distinct customers, pull a number out of a text cell. Each task below gives you the small table it uses, the question, the formula that answers it and why that formula is built the way it is. Every task links to a drill that runs in your browser and checks your formula on the data you see and again on changed data, so a typed-in number or a lucky reference does not pass.

How to use these Google Sheets practice exercises

Work in order within a level, but feel free to skip a level you already know. Read the question, write the formula yourself before reading the answer, and only then compare.

The levels follow the five levels on Sheetsdrill: basics, conditional counting and summing, lookups, arrays and FILTER, then text and dates. Each level adds one or two ideas on top of the last, which is why the QUERY tasks come after FILTER and the REGEX tasks come after SPLIT.

Level 1: basic Google Sheets exercises (SUM, AVERAGE, ROUND, IF)

Six marketing channels and their monthly spend sit in A2:B7: Search 1,240; Social 860.50; Video 415; Email 120; Display 302.25; Podcast 95. The total goes in B8. The answer is =SUM(B2:B7), which returns 3,032.75, and it keeps returning the right total when any channel changes, which a typed-in number would not.

An invoice total of 1,847.50 in B1 covers 37 units in B2. The unit price, rounded to cents, is =ROUND(B1/B2,2), or 49.93. Number formatting would only hide the extra decimals; ROUND changes the value itself.

Prices in dollars are in B2:B3 and the exchange rate is in F1. The euro price in C2 is =B2*$F$1. The dollar signs make F1 an absolute reference, so when the formula is filled down, B2 becomes B3 but F1 stays F1. One rate cell drives the whole column.

A project has a budget in B2 and an actual cost in C2. The status in D2 is =IF(C2>B2,"Over","OK"). Text results go inside double quotes; the comparison itself does not.

Level 2: COUNTIFS, SUMIFS and IFS practice

A ticket log has priorities in B2:B11 and statuses in C2:C11. The number of open high-priority tickets is =COUNTIFS(B2:B11,"High",C2:C11,"Open"). Each condition is a range followed by a criterion, and a row has to pass every pair to count.

A sales table has reps in A2:A11, months in B2:B11 and amounts in C2:C11, with the rep to report on in F1 and the month in F2. The total for that rep and month is =SUMIFS(C2:C11,A2:A11,F1,B2:B11,F2). The sum range comes first in SUMIFS, which trips up anyone coming from SUMIF, where it comes last.

Order dates are in B2:B11 and the window is E1 to E2. Orders inside the window: =COUNTIFS(B2:B11,">="&E1,B2:B11,"<="&E2). The operator is text, so it is joined to the cell with an ampersand; writing ">=E1" in quotes compares against the letters E1, not the date.

Customers with annual spend in B2 get a tier: 5,000 or more is Gold, 1,000 or more is Silver, anything else Bronze. =IFS(B2>=5000,"Gold",B2>=1000,"Silver",TRUE,"Bronze"). IFS stops at the first true condition, so the thresholds must run from largest to smallest, and the final TRUE pair is the catch-all; without it, a value under 1,000 returns #N/A.

The reference pages at /google-sheets-functions/sumifs and /google-sheets-functions/countifs cover wildcards and the date criteria in more detail.

Level 3: VLOOKUP, XLOOKUP and INDEX MATCH exercises

A product list in A2:C8 has SKU, name and price, and the SKU to look up is in F1. The price is =VLOOKUP(F1,A2:C8,3,FALSE). The 3 counts columns from the left edge of the table you gave VLOOKUP, and FALSE asks for an exact match. Leave FALSE out and VLOOKUP assumes a sorted table and quietly returns the wrong row.

The same table, but you have an email address in F1 (column C) and need the name in column A. VLOOKUP cannot look left, so use =XLOOKUP(F1,C2:C8,A2:A8): lookup value, the column to search, the column to return, in any direction.

When the value may be missing, XLOOKUP's fourth argument replaces #N/A: =XLOOKUP(E1,A2:A6,B2:B6,"Not found"). For a VLOOKUP that feeds a total, return zero instead: =IFNA(VLOOKUP(E1,A2:B7,2,FALSE),0). IFNA catches only #N/A, so a real #REF! from a broken range still shows, which is what you want.

Shipping rates come in weight bands: D2:D6 holds the lowest weight of each band, sorted ascending, and E2:E6 the rate. For a parcel weight in B1, =VLOOKUP(B1,D2:E6,2,TRUE) returns the rate of the largest band start that does not exceed the weight. That is what approximate match is for; it is wrong for codes and names and right for tiers.

INDEX with MATCH, =INDEX(A2:A7,MATCH(F1,C2:C7,0)), does the same job as XLOOKUP and still matters because older sheets and shared templates are full of it.

Level 4: FILTER, UNIQUE, SORTN and QUERY practice

Regions are in A2:A11, amounts in C2:C11 and the region to total is in F1. =SUM(FILTER(C2:C11,A2:A11=F1)) returns the total for that region. FILTER returns only the rows where the condition is true, and SUM adds what is left. Two conditions are two more arguments: =COUNTA(FILTER(A2:A11,B2:B11=F1,C2:C11>F2)) counts the rows that pass both.

Customer names repeat in B2:B12 because each order is a row. The number of distinct customers is =COUNTA(UNIQUE(B2:B12)). UNIQUE drops the repeats and COUNTA counts the survivors; COUNT would return zero, because names are text.

Deal values are in C2:C11 and you need the sum of the three largest: =SUM(SORTN(C2:C11,3,0,1,FALSE)). The arguments are the range, how many rows to keep, the tie mode (0 keeps exactly three), the column to sort by and FALSE for descending.

A table in A1:C11 has Region, Rep and Amount with a header row. The total for the East region with QUERY is =QUERY(A1:C11,"select sum(C) where A = 'East' label sum(C) ''",1). Columns are named by letter, text values sit in single quotes inside the double-quoted query, the final 1 says the first row is a header, and the label clause removes the automatic sum heading. The region with the highest total is a group by: =INDEX(QUERY(A1:C11,"select A, sum(C) group by A order by sum(C) desc limit 1",1),2,1), where INDEX picks row 2, column 1 to skip the header the query returns.

QUERY is the function most people stall on. The reference page at /google-sheets-functions/query lists the clauses in the order they must appear, and /google-sheets-functions/filter covers FILTER with OR conditions.

Level 5: text and date exercises (SPLIT, REGEX, DATEDIF, NETWORKDAYS)

Names are stored as "Last, First" in A2. The first name is =INDEX(SPLIT(A2,", "),1,2). SPLIT returns a row of pieces and INDEX takes the second one. By default SPLIT treats each character of the delimiter as a separator, so the comma and the space are both consumed and the first name comes out without a leading space.

A reference such as "Invoice 4471 (paid)" sits in A2 and you need the number as a number: =VALUE(REGEXEXTRACT(A2,"\d+")). REGEXEXTRACT returns the first run of digits as text, and VALUE converts it so it can be summed or looked up. To strip everything except digits from a phone number, =REGEXREPLACE(A2,"\D","") replaces every non-digit with nothing.

A start date is in B2 and today's date in F1. Full years of service: =DATEDIF(B2,$F$1,"Y"). The "Y" unit counts only completed years, so someone hired 11 months ago shows 0. Working days between B2 and C2, skipping the holidays listed in F2:F4: =NETWORKDAYS(B2,C2,$F$2:$F$4). Both ends are counted, so Monday to Friday of one week is 5.

An invoice dated in A2 is due at the end of the following month: =EOMONTH(A2,1). The second argument is the number of months to move; 0 gives the end of the same month and -1 the end of the previous one.

If the regex patterns are new to you, the sister site Regexdrill practices them on their own, outside a spreadsheet.

How to practice Google Sheets so it sticks

Write every formula from scratch, even when you think you know it. The gap between recognizing a formula and producing one is where most people get stuck in a live sheet, and only typing closes it. When you get #N/A or #VALUE!, read the error before reaching for the answer: #N/A means a lookup found nothing, #VALUE! usually means text where a number was expected, and #REF! means a range was deleted or an array had nowhere to spill.

Keep a one-page list of the functions you reach for and their argument order. The printable Google Sheets formulas cheat sheet at /google-sheets-formulas-cheat-sheet covers everything in these exercises. Once a level feels easy, switch on timed mode on Sheetsdrill, which locks hints and solutions until the clock runs out, and come back a week later to see what you still remember. If your office runs on Excel instead, Formuladrill is the same drill format for Excel formulas.

Practice these exercises with instant checking

All 50 Google Sheets exercises are free to try with no sign-up, with instant checking and hints on every one. Pro adds every reference solution with its explanation, timed mode and completion certificates, for $12 a month or $39 a year, and the annual plan starts with a 7-day free trial.

Start practicing free

Questions

Are these Google Sheets practice exercises free?

Yes. Every exercise on Sheetsdrill can be attempted without an account, with instant checking and a hint. A free account adds three reference solutions a day and saved progress; Pro removes the daily limit.

Do I need a Google account or a Google Sheets file?

No. Each exercise runs on a small spreadsheet in your browser that follows Google Sheets rules for the functions it uses, including arrays that flow from FILTER, SORT or SPLIT into the next function and the Sheets error values such as #N/A and #VALUE!. You can also copy the tables above into a real sheet and work there.

What order should a beginner learn Google Sheets functions in?

SUM, AVERAGE and IF first, then absolute references, then COUNTIFS and SUMIFS, then one lookup function (XLOOKUP is the easiest to learn), then FILTER and UNIQUE, and QUERY last. Text and date functions can be picked up whenever a task needs them.

Why does my formula fail when the number looks right?

Every drill reruns your formula on a second version of the data with different values. A formula that types in the answer, points at the wrong cell or only works on the visible rows passes the first run and fails the second, which is exactly what breaks in a shared sheet after someone edits it.

Are these the kind of tasks used in Google Sheets skills tests?

Yes. Operations, marketing and analyst hiring tests tend to ask for a SUMIFS, a lookup with a missing value handled, a date difference and a filtered list, not exotic functions. The exercises here are the same shape, and timed mode on Sheetsdrill adds the clock.