Google Sheets Guides
97 plain-English guides to the functions you use most. Every guide ends with free exercises you can check instantly.
No guide matches that search. Try a function name such as SUMIF.
Lookup and reference
8 guidesFind a value in a table and bring it back.
VLOOKUP
Syntax, a worked example, common errors, and two free exercises.
Beginner to intermediateXLOOKUP
Syntax, looking to the left, XLOOKUP vs VLOOKUP, and two free exercises.
BeginnerHLOOKUP
Syntax, the row index, exact match, how it relates to VLOOKUP, and two free exercises.
IntermediateINDEX and MATCH
How to combine INDEX and MATCH, look left, avoid the default match type, and two free exercises.
IntermediateHow to VLOOKUP to the Left in Google Sheets
Why VLOOKUP cannot look left, and three fixes: XLOOKUP, INDEX and MATCH, or an array. Two free exercises.
Beginner to intermediateXLOOKUP vs VLOOKUP in Google Sheets
A side-by-side comparison of XLOOKUP and VLOOKUP, with conversion examples and two free exercises.
BeginnerVLOOKUP #N/A Error in Google Sheets: Causes and Fixes
Why VLOOKUP returns #N/A in Google Sheets, how to fix each cause, and how to hide it with IFNA. Two free exercises.
IntermediateHow to Use the MATCH Function in Google Sheets
Find the position of a value in a list: exact match, approximate match and the search types. Two free exercises.
Sum, count and average
27 guidesAdd up, count and summarize data, with or without conditions.
SUMIF
Syntax, operators, wildcards, common mistakes, and two free exercises.
IntermediateSUMIFS
Syntax, multiple conditions, argument order, common mistakes, and two free exercises.
IntermediateSUMIF With Multiple Criteria in Google Sheets
How to sum with several conditions: AND with SUMIFS, OR by adding SUMIFs, and between two values. Two free exercises.
BeginnerCOUNTIF
Syntax, operators, wildcards, common mistakes, and two free exercises.
IntermediateCOUNTIFS
Syntax, several conditions, matching range sizes, and two free exercises.
BeginnerCOUNT/COUNTA
The difference between counting numbers and counting values, plus two free exercises.
BeginnerHow to Count Unique Values in Google Sheets
Count distinct values with COUNTUNIQUE, and count items that appear only once. Two free exercises.
BeginnerHow to Count Cells With Text in Google Sheets
Count text cells with a wildcard, count cells that contain a word, and tell COUNT from COUNTA. Two free exercises.
BeginnerAVERAGE
Syntax, averaging ranges and single values, how text is treated, and two free exercises.
BeginnerHow to Use the ROUND Function in Google Sheets
Round to decimals, whole numbers or the nearest hundred, and how negative places work. Two free exercises.
BeginnerHow to Use the MOD Function in Google Sheets
Get the remainder of a division and use it for every-Nth-row rules and divisibility checks. Two free exercises.
BeginnerHow to Use ISEVEN and ISODD in Google Sheets
Test whether a number is even or odd, and label rows with IF. Two free exercises.
BeginnerHow to Use ROUNDUP and ROUNDDOWN in Google Sheets
Always round up or always round down, to decimals or to the nearest hundred. Two free exercises.
BeginnerHow to Use the RANK Function in Google Sheets
Rank a value within a list, highest or lowest first, and fill the formula down safely. Two free exercises.
IntermediateHow to Use the SUBTOTAL Function in Google Sheets
Total, count or average only what is visible after filtering or hiding rows. Two free exercises.
IntermediateHow to Use the MAXIFS Function in Google Sheets
Find the largest value among the rows that meet one or more conditions. Two free exercises.
BeginnerHow to Use the ABS Function in Google Sheets
Get the size of a number without its sign, handy for differences and variances. Two free exercises.
BeginnerHow to Remove Decimals in Google Sheets With TRUNC and INT
Cut off decimals or round down to a whole number, and see where the two differ. Two free exercises.
IntermediateHow to Use the AVERAGEIFS Function in Google Sheets
Average numbers that meet two or more conditions at once. Two free exercises.
BeginnerHow to Use the MEDIAN Function in Google Sheets
Find the middle value of a range and see why it beats AVERAGE when one number is extreme. Two free exercises.
IntermediateHow to Use the PERCENTILE Function in Google Sheets
Find the value that a chosen share of your data falls below, such as the 90th percentile. Two free exercises.
IntermediateHow to Use the STDEV Function in Google Sheets
Measure how spread out your numbers are, and know when to use STDEV or STDEVP. Two free exercises.
BeginnerHow to Count Blank Cells in Google Sheets With COUNTBLANK
Count empty cells in one or more ranges, and learn that empty strings count as blank too. Two free exercises.
IntermediateHow to Use the SUMPRODUCT Function in Google Sheets
Multiply two ranges row by row and add the results in one formula. Two free exercises.
BeginnerAVERAGEIF
Syntax, the optional average range, operators, and two free exercises.
BeginnerMIN/MAX
Syntax, finding the range of a data set, text errors, and two free exercises.
BeginnerLARGE/SMALL
Syntax, the nth largest and smallest value, reading n from a cell, and two free exercises.
Logic and error handling
11 guidesMake a sheet decide, and keep errors out of sight.
IF
Syntax, comparison operators, nested IF, common mistakes, and two free exercises.
Beginner to intermediateIFS
Syntax, ordering conditions, the #N/A trap, a catch-all, and two free exercises.
Beginner to intermediateSWITCH
Syntax, cases and results, the optional default, and two free exercises.
Beginner to intermediateIF With Multiple Conditions in Google Sheets
Combine AND and OR inside IF, when to nest, and when to switch to IFS. Two free exercises.
BeginnerIFERROR
Syntax, wrapping VLOOKUP, fixing #DIV/0!, what to watch out for, and two free exercises.
BeginnerHow to Use the IFNA Function in Google Sheets
Replace only #N/A errors, keep other errors visible, and clean up VLOOKUP results. Two free exercises.
BeginnerHow to Use the ISBLANK Function in Google Sheets
Check whether a cell is empty and use it inside IF, and why an empty string counts as not blank. Two free exercises.
BeginnerHow to Use the ISNUMBER Function in Google Sheets
Check whether a value is a real number, and spot numbers stored as text. Two free exercises.
BeginnerHow to Use the ISTEXT Function in Google Sheets
Check whether a value is text, and learn why an empty string counts as text. Two free exercises.
BeginnerHow to Use the ISERROR Function in Google Sheets
Detect any error value, then show a friendly message with IF. Two free exercises.
BeginnerHow to Use AND, OR and NOT in Google Sheets
Combine several true-or-false tests, alone or inside IF. Two free exercises.
Text
19 guidesSplit, join, clean and change text.
SPLIT
Syntax, delimiters, split_by_each, empty text, and two free exercises.
Beginner to intermediateTEXTJOIN
Syntax, delimiters, ignoring empty cells, and two free exercises.
BeginnerCONCATENATE
Syntax, joining cells and text, adding separators, and two free exercises.
BeginnerHow to Use the JOIN Function in Google Sheets
Combine a column or row of cells into one text, with any separator between them. Two free exercises.
BeginnerHow to Use the HYPERLINK Function in Google Sheets
Create clickable links with a formula, with custom label text or links built from cells. Two free exercises.
BeginnerLEFT/RIGHT/MID
Syntax for all three, positions, extracting codes, and two free exercises.
BeginnerFIND
Syntax, case sensitivity, the starting position, #VALUE!, and two free exercises.
BeginnerSUBSTITUTE
Syntax, replacing all or one occurrence, text output, and two free exercises.
BeginnerHow to Split Text Into Columns in Google Sheets
Split text with the Data menu or the SPLIT formula, and handle multi-character separators. Two free exercises.
BeginnerTRIM
Syntax, what TRIM removes, fixing lookups that fail, and two free exercises.
BeginnerHow to Use the CLEAN Function in Google Sheets
Strip non-printable characters from imported or pasted text. Two free exercises.
BeginnerHow to Use the REPT Function in Google Sheets
Repeat text a set number of times, including tiny in-cell bar charts. Two free exercises.
IntermediateHow to Use the REGEXEXTRACT Function in Google Sheets
Pull numbers, words or codes out of text with a pattern, capture groups, and VALUE. Two free exercises.
IntermediateHow to Use the REGEXMATCH Function in Google Sheets
Test whether text matches a pattern and use the TRUE or FALSE result inside IF. Two free exercises.
IntermediateHow to Use the REGEXREPLACE Function in Google Sheets
Replace every match of a pattern, remove characters and rewrite text with REGEXREPLACE. Two free exercises.
BeginnerHow to Use the LEN Function in Google Sheets
Count the characters in a cell, spot hidden spaces, and check text length. Two free exercises.
IntermediateHow to Use the TEXT Function in Google Sheets
Format numbers and dates as text: leading zeros, decimals, month names and more. Two free exercises.
BeginnerHow to Convert Text to Numbers in Google Sheets With VALUE
Turn numbers stored as text into real numbers, and dates into serial numbers. Two free exercises.
BeginnerHow to Change Text Case in Google Sheets
UPPER, LOWER and PROPER: syntax, what each one does, limits of PROPER, and two free exercises.
Data and arrays
19 guidesFilter, query and reshape whole ranges, or pull data from other files.
FILTER
Syntax, multiple conditions, OR logic, #N/A handling, and two free exercises.
IntermediateQUERY
Syntax, select and where, group by, order by, and two free exercises.
BeginnerUNIQUE
Syntax, removing duplicates, exactly_once, hidden-space pitfalls, and two free exercises.
BeginnerHow to Remove Duplicates in Google Sheets
Remove duplicate rows with the menu or with UNIQUE, and flag repeats with COUNTIF. Two free exercises.
IntermediateARRAYFORMULA
What it does, one formula for a whole column, array math, common mistakes, and two free exercises.
IntermediateIMPORTRANGE
Syntax, allowing access, using cell references, common errors, and two free exercises.
BeginnerHow to Use the SORT Function in Google Sheets
Sort a range with a formula, by one or several columns, ascending or descending. Two free exercises.
AdvancedHow to Use the SORTN Function in Google Sheets
Sort a range and return only the top or bottom N rows with one formula. Two free exercises.
AdvancedHow to Use MAP and LAMBDA in Google Sheets
Define your own mini formula with LAMBDA and apply it to every value in a range with MAP. Two free exercises.
AdvancedHow to Use the LET Function in Google Sheets
Give a calculation a name, compute it once and reuse it in a longer formula. Two free exercises.
BeginnerHow to Use the TRANSPOSE Function in Google Sheets
Swap rows and columns with one formula and keep the result linked to the source. Two free exercises.
BeginnerHow to Use the ROW Function in Google Sheets
Return the row number of a cell, and use ROW to number rows that stay correct. Two free exercises.
AdvancedHow to Use the OFFSET Function in Google Sheets
Point to a cell or range a set number of rows and columns away from a starting cell. Two free exercises.
BeginnerHow to Use the CHOOSE Function in Google Sheets
Pick one value from a list by its position, a tidy alternative to nested IFs. Two free exercises.
IntermediateHow to Use the INDIRECT Function in Google Sheets
Turn text into a cell reference, including sheet names built from other cells. Two free exercises.
BeginnerHow to Use the SEQUENCE Function in Google Sheets
Generate lists and grids of numbers with start, step and direction, no typing or dragging. Two free exercises.
IntermediateHow to Use the FLATTEN Function in Google Sheets
Turn several columns and rows into a single column, and control the order of the result. Two free exercises.
IntermediateHow to Highlight Duplicates in Google Sheets
Color repeated values with a conditional formatting formula, or only the repeats after the first. Two free exercises.
IntermediateHow to Highlight a Row Based on a Cell in Google Sheets
Color a whole row when one cell says Done or passes a limit, using a custom formula and a locked column. Two free exercises.
Dates
13 guidesBuild dates, count the gap between them, and calculate with the calendar.
TODAY
Syntax, date arithmetic, volatility, how it recalculates, and two free exercises.
BeginnerHow to Insert the Current Date and Time in Google Sheets With NOW
Show the current date and time with NOW, format it, and know when it recalculates. Two free exercises.
BeginnerHow to Use the TIME and HOUR Functions in Google Sheets
Build a time from hours, minutes and seconds, and read the hour back out. Two free exercises.
BeginnerHow to Use the DATE Function in Google Sheets
Build a date from numbers, roll over months and days, and avoid text errors. Two free exercises.
BeginnerHow to Calculate Days Between Dates in Google Sheets
Subtract, DAYS or DATEDIF: three ways to count the days between two dates. Two free exercises.
IntermediateHow to Use DATEDIF in Google Sheets
Whole years, months and days between dates, the six unit codes, and calculating age. Two free exercises.
BeginnerHow to Add Months to a Date in Google Sheets
Add or subtract months with EDATE, avoid the typed-date trap, and handle month ends. Two free exercises.
BeginnerHow to Calculate Working Days in Google Sheets
Count working days with NETWORKDAYS, exclude holidays, and handle other weekends. Two free exercises.
BeginnerHow to Use the WORKDAY Function in Google Sheets
Find a due date a set number of working days away, skip holidays, and count backwards. Two free exercises.
BeginnerHow to Get the Last Day of the Month in Google Sheets
Return the last day of this month, next month or any month with EOMONTH. Two free exercises.
BeginnerHow to Use the WEEKDAY Function in Google Sheets
Turn a date into a day-of-week number, choose Monday or Sunday as day 1, and spot weekends. Two free exercises.
BeginnerHow to Get the Day Name From a Date in Google Sheets
Show Monday, Tuesday or Mon, Tue from a date with TEXT. Month names too. Two free exercises.
BeginnerHow to Extract the Year, Month and Day From a Date in Google Sheets
Pull the year, month or day out of a date with YEAR, MONTH and DAY, and rebuild dates with DATE. Two free exercises.