Sheetsdrill

Google Sheets functions reference

The Google Sheets functions people search for, the Sheets-only ones first (QUERY, ARRAYFORMULA, IMPORTRANGE, FILTER), with the direct answer, syntax, worked examples, common errors and the drills on Sheetsdrill that practice each one.

40 functions, each with the direct answer, syntax, worked examples, the mistakes people make and the drills that practice it. Updated November 2026.

Sheets-only

  • QUERYHow the Google Sheets QUERY function works: the SQL-like syntax, select and where, group by with sum and count, order by, limit, and the errors people hit.
  • ARRAYFORMULAHow ARRAYFORMULA in Google Sheets fills a whole column from one cell, with examples for multiplying columns, IF across a range, and the common errors.
  • IMPORTRANGEHow to use IMPORTRANGE in Google Sheets to import a range from another spreadsheet, allow access, combine it with QUERY or FILTER, and fix the errors.
  • FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
  • SORT and SORTNHow SORT and SORTN in Google Sheets sort data by one or more columns with a formula and return the top or bottom N rows, with examples and common errors.
  • UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
  • SPLITHow SPLIT in Google Sheets breaks text into separate cells by a comma, space or other delimiter, how to keep only the first or last piece, and common errors.
  • JOIN and TEXTJOINHow JOIN and TEXTJOIN in Google Sheets combine many cells into one string with a separator, skip blanks and join only matching rows, with common mistakes.
  • REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.
  • SPARKLINEHow SPARKLINE in Google Sheets draws a small line, bar, column or win-loss chart inside a cell, with the options people use for progress bars and colors.

Lookup

  • VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
  • XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.
  • INDEX and MATCHHow INDEX and MATCH work together in Google Sheets to look up values left or right, do two-way lookups by row and column, and find the last match.
  • HLOOKUPHow HLOOKUP works in Google Sheets to search the first row of a range and return a value from a row below, with examples for month tables and common errors.

Logical

  • IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.
  • IFSHow the IFS function in Google Sheets checks several conditions in order and returns the first match, with examples for grades, tiers and bands, plus errors.
  • IFERRORHow IFERROR in Google Sheets hides errors like #N/A, #DIV/0! and #VALUE! with a blank, a zero or a message, with examples for VLOOKUP and division.
  • IFNAHow the IFNA function in Google Sheets replaces only the #N/A error from lookups, why it is safer than IFERROR, with examples for VLOOKUP and MATCH.
  • SWITCHHow SWITCH in Google Sheets compares one expression against a list of cases and returns the matching value or a default, with examples for codes and regions.
  • AND and ORHow AND and OR work in Google Sheets to test several conditions at once, how to use them in IF, and why they fail inside FILTER and ARRAYFORMULA, with fixes.

Math

  • SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.
  • SUMIFSHow SUMIFS in Google Sheets adds values that meet several conditions at once, with examples for a rep and month, date ranges, not-equal and wildcards.
  • COUNTIFHow COUNTIF in Google Sheets counts cells equal to a value, above a number, containing text or not blank, with examples, wildcards and the errors that give 0.
  • COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.
  • AVERAGEIFHow AVERAGEIF and AVERAGEIFS in Google Sheets average only the cells that meet one or more conditions, with examples for a team, a threshold and dates.
  • SUM and AVERAGEHow SUM and AVERAGE work in Google Sheets, with examples for a column total, average order value, share of total with locked references, and why you get 0.
  • ROUNDHow ROUND, ROUNDUP, ROUNDDOWN and MROUND work in Google Sheets, with examples for two decimals, thousands and nearest 0.05, and why formatting does not round.
  • SUMPRODUCTHow SUMPRODUCT in Google Sheets multiplies two ranges row by row and adds the results, and how it works as a conditional sum with OR logic, with examples.

Text

  • LENHow LEN in Google Sheets counts the characters in a cell, how to find values that are too long or short, count words, and why hidden spaces change the number.
  • LEFT, RIGHT and MIDHow LEFT, RIGHT and MID in Google Sheets extract characters from the start, end or middle of a cell, with FIND for variable positions, and common errors.
  • TRIMHow TRIM in Google Sheets removes leading, trailing and repeated spaces, how to clean a whole column, and how to clear non-breaking spaces and line breaks.
  • PROPER, UPPER and LOWERHow PROPER, UPPER and LOWER change capitalization in Google Sheets, how to fix a whole column of names, and the cases where PROPER gets it wrong.
  • SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.
  • CONCATENATE and &How to combine cells in Google Sheets with CONCATENATE, CONCAT and the & operator, add spaces and separators, keep number and date formats, avoid mistakes.
  • TEXTHow the TEXT function in Google Sheets turns a number or date into formatted text: month labels, currency, percentages and leading zeros, with common codes.

Dates

  • TODAY and NOWHow TODAY and NOW in Google Sheets return the current date and time, how to calculate days until a deadline, age and overdue flags, and why the value changes.
  • DATEDIFHow DATEDIF in Google Sheets returns whole years, months or days between two dates, with the unit codes, examples for age and tenure, and common errors.
  • EOMONTH and EDATEHow EOMONTH and EDATE in Google Sheets return the last day of a month or the same day N months later, with examples for due dates and first of month.
  • NETWORKDAYSHow NETWORKDAYS and NETWORKDAYS.INTL in Google Sheets count working days between two dates, exclude holidays, use custom weekends, and add days with WORKDAY.
  • DATE, YEAR, MONTH and DAYHow DATE, YEAR, MONTH and DAY work in Google Sheets to build a date from parts, pull the year or month out of a date, group by month and fix text dates.

Reading is not practicing

Every function here links to drills that check your answer on live data. All of them are free to try.

Start practicing