Beginner 15 min read
Google Sheets Formulas Cheat Sheet (100+)
A quick-reference list of 100+ Google Sheets formulas organized by category. Includes syntax, one-line descriptions, and links to step-by-step tutorials.
SB
Sheets Bootcamp
March 15, 2026 · Updated April 1, 2026
Google Sheets formulas let you calculate, transform, and analyze data directly in your spreadsheet. Whether you need to add a column of numbers, pull data from another sheet, or clean up messy text, there is a formula for it.
This cheat sheet covers 100+ Google Sheets formulas organized by category. Each entry includes the syntax and a one-line description. For functions with detailed tutorials on this site, you will find a direct link to the full guide.
How to Use This Cheat Sheet
Jump to any category using the links below. Each table shows the formula name, its syntax, and what it does.
- Lookup Functions
- Logical Functions
- Math Functions
- Statistical Functions
- Text Functions
- Date and Time Functions
- Filter and Sort Functions
- Array Functions
- Information Functions
- Financial Functions
- Web and Import Functions
- FAQ
✦ Tip
Bookmark this page. Press Ctrl+D (Cmd+D on Mac) to save it for quick access whenever you need to look up a formula.
Lookup Functions
Lookup functions find and return data from other parts of your spreadsheet.
| Formula | Syntax | Description |
|---|---|---|
| VLOOKUP | =VLOOKUP(search_key, range, index, [is_sorted]) | Searches the first column of a range and returns a value from a specified column |
| HLOOKUP | =HLOOKUP(search_key, range, index, [is_sorted]) | Same as VLOOKUP but searches across the first row instead of down the first column |
| XLOOKUP | =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) | Searches a range and returns a result from a corresponding range, works in any direction |
| INDEX | =INDEX(reference, row, [column]) | Returns the value at a specific row and column position in a range |
| MATCH | =MATCH(search_key, range, [search_type]) | Returns the relative position of a value within a range |
| INDEX MATCH | =INDEX(range, MATCH(search_key, lookup_range, 0)) | Combines INDEX and MATCH for flexible lookups in any direction |
| INDIRECT | =INDIRECT(cell_reference_as_string) | Returns the reference specified by a text string |
| OFFSET | =OFFSET(cell_reference, rows, cols, [height], [width]) | Returns a range offset from a starting cell by a given number of rows and columns |
| ROW | =ROW([cell_reference]) | Returns the row number of a cell |
| COLUMN | =COLUMN([cell_reference]) | Returns the column number of a cell |
| ROWS | =ROWS(range) | Returns the number of rows in a range |
| COLUMNS | =COLUMNS(range) | Returns the number of columns in a range |
| LOOKUP | =LOOKUP(search_key, search_range, [result_range]) | Searches a sorted row or column for a key and returns a value from a result range |
| ADDRESS | =ADDRESS(row, column, [absolute_mode], [a1_notation], [sheet]) | Returns a cell reference as a text string |
| CHOOSECOLS | =CHOOSECOLS(array, col_num1, [col_num2, ...]) | Returns specified columns from an array |
| CHOOSEROWS | =CHOOSEROWS(array, row_num1, [row_num2, ...]) | Returns specified rows from an array |
ℹ Note
VLOOKUP only searches left to right. If you need to return values from a column to the left of your search column, use XLOOKUP or INDEX MATCH instead.
Logical Functions
Logical functions test conditions and return different results based on whether those conditions are true or false.
| Formula | Syntax | Description |
|---|---|---|
| IF | =IF(condition, value_if_true, value_if_false) | Returns one value if a condition is true and another if it is false |
| IFS | =IFS(condition1, value1, condition2, value2, ...) | Tests multiple conditions and returns the value for the first true condition |
| IFERROR | =IFERROR(value, value_if_error) | Returns a custom value if a formula results in an error, otherwise returns the formula result |
| IFNA | =IFNA(value, value_if_na) | Returns a custom value only if the formula results in #N/A, passes through other errors |
| AND | =AND(condition1, condition2, ...) | Returns TRUE if all conditions are true |
| OR | =OR(condition1, condition2, ...) | Returns TRUE if any condition is true |
| NOT | =NOT(condition) | Reverses a logical value. TRUE becomes FALSE and FALSE becomes TRUE |
| SWITCH | =SWITCH(expression, case1, value1, [case2, value2, ...], [default]) | Tests an expression against a list of cases and returns the matching value |
| TRUE | =TRUE() | Returns the logical value TRUE |
| FALSE | =FALSE() | Returns the logical value FALSE |
| XOR | =XOR(condition1, condition2, ...) | Returns TRUE if an odd number of conditions are true |
| LET | =LET(name1, value1, [name2, value2, ...], formula) | Assigns names to intermediate values, reducing repetition in formulas |
| LAMBDA | =LAMBDA([name1, ...], formula) | Creates a custom function that can be reused |
Math Functions
Math functions handle calculations, rounding, and conditional totals.
| Formula | Syntax | Description |
|---|---|---|
| SUM | =SUM(value1, [value2, ...]) | Adds all values in a range |
| SUMIF | =SUMIF(range, criterion, [sum_range]) | Adds values that meet a single condition |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | Adds values that meet multiple conditions |
| SUMPRODUCT | =SUMPRODUCT(array1, [array2, ...]) | Multiplies corresponding elements in arrays, then sums the results |
| PRODUCT | =PRODUCT(value1, [value2, ...]) | Multiplies all values together |
| ABS | =ABS(value) | Returns the absolute value of a number |
| ROUND | =ROUND(value, [places]) | Rounds a number to a specified number of decimal places |
| ROUNDUP | =ROUNDUP(value, [places]) | Rounds a number up, away from zero |
| ROUNDDOWN | =ROUNDDOWN(value, [places]) | Rounds a number down, toward zero |
| CEILING | =CEILING(value, [factor]) | Rounds a number up to the nearest multiple of a specified factor |
| FLOOR | =FLOOR(value, [factor]) | Rounds a number down to the nearest multiple of a specified factor |
| INT | =INT(value) | Rounds a number down to the nearest integer |
| MOD | =MOD(dividend, divisor) | Returns the remainder after division |
| POWER | =POWER(base, exponent) | Raises a number to a power |
| SQRT | =SQRT(value) | Returns the square root of a number |
| RAND | =RAND() | Returns a random number between 0 and 1 |
| RANDBETWEEN | =RANDBETWEEN(low, high) | Returns a random integer between two values |
| QUOTIENT | =QUOTIENT(dividend, divisor) | Returns the integer portion of a division |
| SIGN | =SIGN(value) | Returns 1 for positive, -1 for negative, 0 for zero |
| LOG | =LOG(value, [base]) | Returns the logarithm of a number |
| SUBTOTAL | =SUBTOTAL(function_code, range) | Calculates a subtotal, optionally ignoring filtered or hidden rows |
Statistical Functions
Statistical functions count, average, and analyze data distribution.
| Formula | Syntax | Description |
|---|---|---|
| AVERAGE | =AVERAGE(value1, [value2, ...]) | Returns the arithmetic mean of a set of values |
| AVERAGEIF | =AVERAGEIF(range, criterion, [average_range]) | Returns the average of values that meet a condition |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | Returns the average of values that meet multiple conditions |
| COUNT | =COUNT(value1, [value2, ...]) | Counts cells containing numbers |
| COUNTA | =COUNTA(value1, [value2, ...]) | Counts all non-empty cells |
| COUNTBLANK | =COUNTBLANK(range) | Counts empty cells in a range |
| COUNTIF | =COUNTIF(range, criterion) | Counts cells that meet a single condition |
| COUNTIFS | =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | Counts cells that meet multiple conditions |
| MIN | =MIN(value1, [value2, ...]) | Returns the smallest value |
| MAX | =MAX(value1, [value2, ...]) | Returns the largest value |
| MINIFS | =MINIFS(range, criteria_range1, criterion1, ...) | Returns the minimum value among cells matching conditions |
| MAXIFS | =MAXIFS(range, criteria_range1, criterion1, ...) | Returns the maximum value among cells matching conditions |
| MEDIAN | =MEDIAN(value1, [value2, ...]) | Returns the middle value in a dataset |
| MODE | =MODE(value1, [value2, ...]) | Returns the most frequently occurring value |
| STDEV | =STDEV(value1, [value2, ...]) | Returns the standard deviation of a sample |
| VAR | =VAR(value1, [value2, ...]) | Returns the variance of a sample |
| LARGE | =LARGE(data, n) | Returns the nth largest value in a dataset |
| SMALL | =SMALL(data, n) | Returns the nth smallest value in a dataset |
| RANK | =RANK(value, data, [is_ascending]) | Returns the rank of a value within a dataset |
| PERCENTILE | =PERCENTILE(data, percentile) | Returns the value at a given percentile in a dataset |
Text Functions
Text functions manipulate, combine, and extract text from cells. For a full guide, see Google Sheets text functions.
| Formula | Syntax | Description |
|---|---|---|
| CONCATENATE | =CONCATENATE(string1, [string2, ...]) | Joins multiple text strings into one |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, string1, [string2, ...]) | Joins text strings with a delimiter between each |
| LEFT | =LEFT(string, [number_of_characters]) | Returns characters from the start of a text string |
| RIGHT | =RIGHT(string, [number_of_characters]) | Returns characters from the end of a text string |
| MID | =MID(string, starting_at, extract_length) | Returns characters from the middle of a text string |
| LEN | =LEN(text) | Returns the number of characters in a text string |
| TRIM | =TRIM(text) | Removes leading, trailing, and extra spaces from text |
| CLEAN | =CLEAN(text) | Removes non-printable characters from text |
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [occurrence]) | Replaces occurrences of a text string with a new string |
| REPLACE | =REPLACE(text, position, length, new_text) | Replaces part of a text string based on position |
| UPPER | =UPPER(text) | Converts text to uppercase |
| LOWER | =LOWER(text) | Converts text to lowercase |
| PROPER | =PROPER(text) | Capitalizes the first letter of each word |
| SPLIT | =SPLIT(text, delimiter, [split_by_each], [remove_empty]) | Splits text into separate cells based on a delimiter |
| FIND | =FIND(search_for, text_to_search, [starting_at]) | Returns the position of a substring within text (case-sensitive) |
| SEARCH | =SEARCH(search_for, text_to_search, [starting_at]) | Returns the position of a substring within text (case-insensitive) |
| TEXT | =TEXT(number, format) | Converts a number to text in a specified format |
| VALUE | =VALUE(text) | Converts a text string that represents a number into a number |
| EXACT | =EXACT(string1, string2) | Checks if two text strings are identical (case-sensitive) |
| REPT | =REPT(text, number_of_times) | Repeats a text string a given number of times |
| REGEXMATCH | =REGEXMATCH(text, regular_expression) | Returns TRUE if text matches a regular expression |
| REGEXEXTRACT | =REGEXEXTRACT(text, regular_expression) | Extracts the first matching substring using a regular expression |
| REGEXREPLACE | =REGEXREPLACE(text, regular_expression, replacement) | Replaces text matching a regular expression |
✦ Tip
REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE are Google Sheets exclusives. They do not exist in Excel. Use them for pattern matching that FIND and SEARCH cannot handle.
Date and Time Functions
Date and time functions calculate differences between dates, extract date parts, and work with timestamps. For the full guide, see Google Sheets date functions.
| Formula | Syntax | Description |
|---|---|---|
| TODAY | =TODAY() | Returns the current date (updates automatically) |
| NOW | =NOW() | Returns the current date and time |
| DATE | =DATE(year, month, day) | Creates a date from individual year, month, and day values |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | Returns the difference between two dates in days, months, or years |
| DATEVALUE | =DATEVALUE(date_string) | Converts a date string to a date serial number |
| YEAR | =YEAR(date) | Returns the year from a date |
| MONTH | =MONTH(date) | Returns the month from a date (1-12) |
| DAY | =DAY(date) | Returns the day of the month from a date (1-31) |
| WEEKDAY | =WEEKDAY(date, [type]) | Returns the day of the week as a number |
| WEEKNUM | =WEEKNUM(date, [type]) | Returns the week number of the year |
| EDATE | =EDATE(start_date, months) | Returns a date a given number of months before or after a start date |
| EOMONTH | =EOMONTH(start_date, months) | Returns the last day of a month a given number of months before or after a start date |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of working days between two dates |
| WORKDAY | =WORKDAY(start_date, num_days, [holidays]) | Returns a date a given number of working days from a start date |
| HOUR | =HOUR(time) | Returns the hour from a time value (0-23) |
| MINUTE | =MINUTE(time) | Returns the minute from a time value (0-59) |
| SECOND | =SECOND(time) | Returns the second from a time value (0-59) |
| TIME | =TIME(hour, minute, second) | Creates a time value from hours, minutes, and seconds |
| DAYS | =DAYS(end_date, start_date) | Returns the number of days between two dates |
⚠ Important
DATEDIF does not appear in the Google Sheets autocomplete menu, but it works. The syntax is =DATEDIF(start_date, end_date, "Y") for years, "M" for months, or "D" for days. See the DATEDIF tutorial for all unit options.
Filter and Sort Functions
These functions filter, sort, and deduplicate data using formulas instead of menu tools.
| Formula | Syntax | Description |
|---|---|---|
| FILTER | =FILTER(range, condition1, [condition2, ...]) | Returns rows from a range that meet one or more conditions |
| SORT | =SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...]) | Sorts the rows of a range by one or more columns |
| SORTN | =SORTN(range, [n], [display_ties_mode], [sort_column, is_ascending, ...]) | Returns the top N rows from a sorted range |
| UNIQUE | =UNIQUE(range) | Returns only the unique rows from a range, removing duplicates |
| QUERY | =QUERY(data, query, [headers]) | Runs a SQL-like query against a data range |
ℹ Note
FILTER, SORT, and UNIQUE return dynamic arrays. The results spill into adjacent cells automatically and update when the source data changes.
Array Functions
Array functions apply a formula to an entire range at once or reshape data layouts.
| Formula | Syntax | Description |
|---|---|---|
| ARRAYFORMULA | =ARRAYFORMULA(formula) | Applies a formula to an entire range, returning multiple results from a single cell |
| FLATTEN | =FLATTEN(range1, [range2, ...]) | Collapses multiple rows and columns into a single column |
| TRANSPOSE | =TRANSPOSE(range) | Swaps rows and columns in a range |
| MAP | =MAP(array, LAMBDA(element, formula)) | Applies a LAMBDA function to each element in an array |
| REDUCE | =REDUCE(initial_value, array, LAMBDA(accumulator, element, formula)) | Reduces an array to a single value by applying a LAMBDA function |
| BYROW | =BYROW(array, LAMBDA(row, formula)) | Applies a LAMBDA function to each row and returns a single value per row |
| BYCOL | =BYCOL(array, LAMBDA(col, formula)) | Applies a LAMBDA function to each column and returns a single value per column |
| MAKEARRAY | =MAKEARRAY(rows, cols, LAMBDA(row, col, formula)) | Creates an array of specified size using a LAMBDA function |
| WRAPCOLS | =WRAPCOLS(range, wrap_count, [pad_with]) | Wraps a row or column into multiple columns of a specified size |
| WRAPROWS | =WRAPROWS(range, wrap_count, [pad_with]) | Wraps a row or column into multiple rows of a specified size |
| TOCOL | =TOCOL(range, [ignore], [scan_by_column]) | Converts a range into a single column |
| TOROW | =TOROW(range, [ignore], [scan_by_column]) | Converts a range into a single row |
Information Functions
Information functions check the type or status of a cell value.
| Formula | Syntax | Description |
|---|---|---|
| ISBLANK | =ISBLANK(value) | Returns TRUE if the cell is empty |
| ISERROR | =ISERROR(value) | Returns TRUE if the value is any error |
| ISNA | =ISNA(value) | Returns TRUE if the value is #N/A |
| ISNUMBER | =ISNUMBER(value) | Returns TRUE if the value is a number |
| ISTEXT | =ISTEXT(value) | Returns TRUE if the value is text |
| ISLOGICAL | =ISLOGICAL(value) | Returns TRUE if the value is TRUE or FALSE |
| ISFORMULA | =ISFORMULA(cell) | Returns TRUE if the cell contains a formula |
| TYPE | =TYPE(value) | Returns a number indicating the data type (1=number, 2=text, 4=logical, 16=error) |
| N | =N(value) | Converts a value to a number |
| CELL | =CELL(info_type, reference) | Returns information about a cell (format, address, contents, etc.) |
| ERROR.TYPE | =ERROR.TYPE(reference) | Returns a number corresponding to the error type |
Financial Functions
Financial functions handle loan payments, investment values, and depreciation.
| Formula | Syntax | Description |
|---|---|---|
| PMT | =PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) | Calculates the periodic payment for a loan or investment |
| FV | =FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning]) | Returns the future value of an investment |
| PV | =PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]) | Returns the present value of a series of future payments |
| NPV | =NPV(discount, cashflow1, [cashflow2, ...]) | Returns the net present value of a series of cash flows |
| IRR | =IRR(cashflow_amounts, [rate_estimate]) | Returns the internal rate of return for a series of cash flows |
| RATE | =RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_estimate]) | Returns the interest rate per period for a loan or investment |
| NPER | =NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) | Returns the number of periods for a loan or investment |
| SLN | =SLN(cost, salvage, life) | Returns straight-line depreciation of an asset for one period |
Web and Import Functions
These functions pull data from external sources and other spreadsheets.
| Formula | Syntax | Description |
|---|---|---|
| IMPORTRANGE | =IMPORTRANGE(spreadsheet_url, range_string) | Imports a range of cells from another Google Sheets spreadsheet |
| IMPORTDATA | =IMPORTDATA(url) | Imports data from a URL in CSV or TSV format |
| IMPORTHTML | =IMPORTHTML(url, query, index) | Imports a table or list from an HTML page |
| IMPORTXML | =IMPORTXML(url, xpath_query) | Imports data from an XML or HTML source using an XPath query |
| IMPORTFEED | =IMPORTFEED(url, [query], [headers], [num_items]) | Imports an RSS or Atom feed |
| IMAGE | =IMAGE(url, [mode], [height], [width]) | Inserts an image into a cell from a URL |
| GOOGLEFINANCE | =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval]) | Returns current or historical financial data from Google Finance |
| SPARKLINE | =SPARKLINE(data, [options]) | Creates a miniature chart within a single cell |
⚠ Important
IMPORTRANGE requires you to grant access the first time you connect two spreadsheets. See the IMPORTRANGE access permission guide for the fix if you see a #REF! error.
Related Google Sheets Tutorials
- VLOOKUP complete guide - Learn the most popular lookup function from the basics to advanced use cases
- IF statements in Google Sheets - Build conditional logic with IF, nested IFs, AND, OR, and related functions
- QUERY function guide - Filter and summarize data using SQL-like syntax
- Text functions guide - Combine, split, clean, and transform text in your spreadsheet
- Date functions guide - Calculate differences, format dates, and work with timestamps
- Function Reference - Individual function pages with syntax, examples, and error troubleshooting
Frequently Asked Questions
How many formulas does Google Sheets have?
Google Sheets has over 400 built-in functions. Most users rely on 20-30 of them regularly. This cheat sheet covers the 100+ most useful formulas across all major categories.
What are the most used Google Sheets formulas?
The most commonly used formulas are SUM, IF, VLOOKUP, COUNTIF, AVERAGE, CONCATENATE, and INDEX MATCH. These cover the core needs of adding values, making decisions, looking up data, counting, and combining text.
What is the difference between a formula and a function in Google Sheets?
A function is a built-in command like SUM or VLOOKUP. A formula is any expression that starts with an equals sign. A formula can contain one or more functions. For example, =SUM(A1:A10) is a formula that uses the SUM function.
How do I see all formulas in a Google Sheets spreadsheet?
Press Ctrl+` (backtick) to toggle formula view. This shows the formulas in every cell instead of their results. Press the same shortcut again to switch back to the normal view.
Can I use Excel formulas in Google Sheets?
Most Excel formulas work in Google Sheets. Core functions like SUM, IF, VLOOKUP, INDEX, and MATCH use identical syntax. Some Excel-specific functions like XLOOKUP, LET, and LAMBDA are also available in Google Sheets, though a few advanced Excel functions have no Sheets equivalent.