Google Sheets practice exercises
Every exercise opens a grid that works like Google Sheets, right in your browser. Type the formula, press Enter and you see at once whether it works. Start with QUERY, the function only Sheets has, or warm up with SUMIF and VLOOKUP.
Your progress
0 of 52 lessons done
Your progress is saved in this browser. Create a free account to keep it on every device and start a streak.
Create free accountTopics
Only in Google Sheets
0/14- QUERYPremiumPick columns with selectKeep rows with whereMatch text in whereTotals per group with group byTop results with order by and limit
- ARRAYFORMULAPremiumOne formula for a whole columnIF for every rowJoin two columns for every row
- SPLIT and JOINPremiumSplit a name with SPLITSplit a date into numbersJoin a list with JOIN
- REGEX functionsPremiumTest text with REGEXMATCHPull out a number with REGEXEXTRACTKeep only digits with REGEXREPLACE
Logic
0/5Conditional sums and counts
0/18- COUNTIFPremiumCOUNTIF with a text conditionCOUNTIF with a number conditionCOUNTIF with the limit in a cellCount everything except one valueCount with a wildcard
- SUMIFPremiumSUMIF with a text criterionSUMIF with a number conditionSUMIF with a separate sum_rangeSUMIF with the criterion in a cellSUMIF with a wildcard
- SUMIFSPremiumSUMIFS with two conditionsSUMIFS with the conditions in cellsSUMIFS between two datesSUMIFS that leaves one value out
- COUNTIFSPremiumCOUNTIFS with two conditionsCount between two numbers with COUNTIFSCount the orders between two datesCOUNTIFS with the conditions in cells
Lookups
0/15- VLOOKUPPremiumLook up a price with VLOOKUPVLOOKUP a number keyVLOOKUP with an approximate matchBring prices into an order list
- INDEX and MATCHPremiumFind a position with MATCHLook up a price with INDEX and MATCHLook to the left with INDEX and MATCHA two-way lookup with INDEX and two MATCHes
- XLOOKUPPremiumLook up a price with XLOOKUPXLOOKUP with a not-found valueFind the latest match with XLOOKUPThe smallest box that fits with XLOOKUP
- IFERRORPremiumHide #N/A with IFERRORAvoid #DIV/0! with IFERRORCatch only "not found" with IFNA
Functions in Google Sheets
Only in Google Sheets
Functions Excel does not have. Each page has the syntax, worked examples and an exercise.
Same as in Excel
These work the way they do in Excel. Each page says what to watch for in Sheets.
Good to know
Why practice Google Sheets here
Most Google Sheets tutorials are videos or templates you copy into your own Drive. You watch someone else type a formula and never find out whether you could write it yourself. Here every exercise is a small sheet with a task, and the formula you type is checked against the answer, against the shape of the right formula and against fresh data, so a typed-in number or a lucky guess does not pass.
The grid behaves like Google Sheets: the same functions, the same blue selection, F2 to edit, Enter and Tab to move, Ctrl+D to fill down and F4 to switch between relative and absolute references. There is nothing to install, and you do not need a Google account to start.
What works the same as in Excel
SUM, IF, SUMIF, COUNTIF, VLOOKUP, XLOOKUP, INDEX and MATCH take the same arguments in Google Sheets as in Excel and give the same answers. That is why the exercises in those topics are the same as on our Excel pages: practice them once and you can use them in both.
Your progress is shared too. A lesson you finish here counts on the Excel pages and the other way around, so you never solve the same sheet twice to earn the points.
What is different in Google Sheets
Google Sheets has functions Excel does not: QUERY asks a range questions in a language much like SQL, ARRAYFORMULA runs one formula down a whole column, SPLIT and JOIN take text apart and put it back together, and REGEXMATCH, REGEXEXTRACT and REGEXREPLACE find patterns in text. They have their own topics at the top of this page.
A few errors look different. When a result has no room to spill into the cells next to it, Sheets shows #REF! where Excel shows #SPILL!. FILTER with no matches gives #N/A instead of #CALC!, and empty cells in a spilled result stay empty instead of showing 0.
Plain formulas over ranges behave differently too. In Excel 365, =B2:B10*C2:C10 spills on its own; in Google Sheets you wrap it in ARRAYFORMULA. Our grid accepts both, but the ARRAYFORMULA exercises ask for it, because that is what you will need in a real sheet.
Function names and separators
Google Sheets shows function names in the language of your Google account, unless you tick Always use English function names under File > Settings. These pages use the English names and commas between arguments, as Sheets does in an English or US locale. In a locale that writes decimals with a comma, Sheets puts semicolons between arguments instead.
How the exercises work
Each lesson is one task on a small sheet, with the answer cell marked. Some open with a few steps that explain the idea before you write the formula. If your answer is wrong you get a hint that says what is wrong, not just that it is.
The first lesson of every topic is free, with no account. Create a free account to keep your progress on every device; premium opens every lesson and the timed job tests.
Questions
Are Google Sheets formulas the same as Excel formulas?
Mostly. Everyday functions such as SUM, IF, SUMIF, VLOOKUP and XLOOKUP take the same arguments and give the same results. Sheets adds functions Excel does not have, such as QUERY, ARRAYFORMULA, SPLIT and the REGEX functions, and shows a few errors differently.
Do I need a Google account to practice?
No. The exercises run in your browser on our own grid, which works like Google Sheets. You only need an account with us if you want to keep your progress on more than one device.
What does ARRAYFORMULA do?
It makes a formula work on whole ranges at once, so one formula in the top cell fills the whole column. =ARRAYFORMULA(B2:B10*C2:C10) multiplies every row and spills the results down.
Is QUERY hard to learn?
Not if you know a little SQL, and not much harder if you don't. A query is a short sentence such as select A, sum(C) group by A. Start with select and where, then add group by and order by; the QUERY topic goes in that order.
Are the Google Sheets exercises free?
The first lesson of every topic is free, with no account. Premium opens every lesson on both the Excel and the Google Sheets pages.