Google Sheets Guides

97 plain-English guides to the functions you use most. Every guide ends with free exercises you can check instantly.

Lookup and reference

8 guides

Find a value in a table and bring it back.

Sum, count and average

27 guides

Add up, count and summarize data, with or without conditions.

Beginner

SUMIF

Syntax, operators, wildcards, common mistakes, and two free exercises.

Intermediate

SUMIFS

Syntax, multiple conditions, argument order, common mistakes, and two free exercises.

Intermediate

SUMIF 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.

Beginner

COUNTIF

Syntax, operators, wildcards, common mistakes, and two free exercises.

Intermediate

COUNTIFS

Syntax, several conditions, matching range sizes, and two free exercises.

Beginner

COUNT/COUNTA

The difference between counting numbers and counting values, plus two free exercises.

Beginner

How to Count Unique Values in Google Sheets

Count distinct values with COUNTUNIQUE, and count items that appear only once. Two free exercises.

Beginner

How 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.

Beginner

AVERAGE

Syntax, averaging ranges and single values, how text is treated, and two free exercises.

Beginner

How 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.

Beginner

How 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.

Beginner

How to Use ISEVEN and ISODD in Google Sheets

Test whether a number is even or odd, and label rows with IF. Two free exercises.

Beginner

How 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.

Beginner

How 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.

Intermediate

How to Use the SUBTOTAL Function in Google Sheets

Total, count or average only what is visible after filtering or hiding rows. Two free exercises.

Intermediate

How to Use the MAXIFS Function in Google Sheets

Find the largest value among the rows that meet one or more conditions. Two free exercises.

Beginner

How 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.

Beginner

How 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.

Intermediate

How to Use the AVERAGEIFS Function in Google Sheets

Average numbers that meet two or more conditions at once. Two free exercises.

Beginner

How 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.

Intermediate

How 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.

Intermediate

How 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.

Beginner

How 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.

Intermediate

How to Use the SUMPRODUCT Function in Google Sheets

Multiply two ranges row by row and add the results in one formula. Two free exercises.

Beginner

AVERAGEIF

Syntax, the optional average range, operators, and two free exercises.

Beginner

MIN/MAX

Syntax, finding the range of a data set, text errors, and two free exercises.

Beginner

LARGE/SMALL

Syntax, the nth largest and smallest value, reading n from a cell, and two free exercises.

Logic and error handling

11 guides

Make a sheet decide, and keep errors out of sight.

Text

19 guides

Split, join, clean and change text.

Beginner

SPLIT

Syntax, delimiters, split_by_each, empty text, and two free exercises.

Beginner to intermediate

TEXTJOIN

Syntax, delimiters, ignoring empty cells, and two free exercises.

Beginner

CONCATENATE

Syntax, joining cells and text, adding separators, and two free exercises.

Beginner

How 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.

Beginner

How 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.

Beginner

LEFT/RIGHT/MID

Syntax for all three, positions, extracting codes, and two free exercises.

Beginner

FIND

Syntax, case sensitivity, the starting position, #VALUE!, and two free exercises.

Beginner

SUBSTITUTE

Syntax, replacing all or one occurrence, text output, and two free exercises.

Beginner

How 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.

Beginner

TRIM

Syntax, what TRIM removes, fixing lookups that fail, and two free exercises.

Beginner

How to Use the CLEAN Function in Google Sheets

Strip non-printable characters from imported or pasted text. Two free exercises.

Beginner

How to Use the REPT Function in Google Sheets

Repeat text a set number of times, including tiny in-cell bar charts. Two free exercises.

Intermediate

How 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.

Intermediate

How 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.

Intermediate

How to Use the REGEXREPLACE Function in Google Sheets

Replace every match of a pattern, remove characters and rewrite text with REGEXREPLACE. Two free exercises.

Beginner

How to Use the LEN Function in Google Sheets

Count the characters in a cell, spot hidden spaces, and check text length. Two free exercises.

Intermediate

How to Use the TEXT Function in Google Sheets

Format numbers and dates as text: leading zeros, decimals, month names and more. Two free exercises.

Beginner

How 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.

Beginner

How 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 guides

Filter, query and reshape whole ranges, or pull data from other files.

Beginner to intermediate

FILTER

Syntax, multiple conditions, OR logic, #N/A handling, and two free exercises.

Intermediate

QUERY

Syntax, select and where, group by, order by, and two free exercises.

Beginner

UNIQUE

Syntax, removing duplicates, exactly_once, hidden-space pitfalls, and two free exercises.

Beginner

How to Remove Duplicates in Google Sheets

Remove duplicate rows with the menu or with UNIQUE, and flag repeats with COUNTIF. Two free exercises.

Intermediate

ARRAYFORMULA

What it does, one formula for a whole column, array math, common mistakes, and two free exercises.

Intermediate

IMPORTRANGE

Syntax, allowing access, using cell references, common errors, and two free exercises.

Beginner

How 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.

Advanced

How 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.

Advanced

How 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.

Advanced

How 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.

Beginner

How 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.

Beginner

How 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.

Advanced

How 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.

Beginner

How 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.

Intermediate

How to Use the INDIRECT Function in Google Sheets

Turn text into a cell reference, including sheet names built from other cells. Two free exercises.

Beginner

How 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.

Intermediate

How 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.

Intermediate

How to Highlight Duplicates in Google Sheets

Color repeated values with a conditional formatting formula, or only the repeats after the first. Two free exercises.

Intermediate

How 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 guides

Build dates, count the gap between them, and calculate with the calendar.

Beginner

TODAY

Syntax, date arithmetic, volatility, how it recalculates, and two free exercises.

Beginner

How 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.

Beginner

How 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.

Beginner

How 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.

Beginner

How to Calculate Days Between Dates in Google Sheets

Subtract, DAYS or DATEDIF: three ways to count the days between two dates. Two free exercises.

Intermediate

How to Use DATEDIF in Google Sheets

Whole years, months and days between dates, the six unit codes, and calculating age. Two free exercises.

Beginner

How 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.

Beginner

How to Calculate Working Days in Google Sheets

Count working days with NETWORKDAYS, exclude holidays, and handle other weekends. Two free exercises.

Beginner

How 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.

Beginner

How 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.

Beginner

How 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.

Beginner

How 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.

Beginner

How 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.