Google Sheets Formulas List: 200+ Functions Explained (With Examples)
If you’ve ever typed = into a Google Sheets cell and wondered what to type next, this is the guide you need. Below is a complete Google Sheets formulas list organized into 13 categories, with the correct syntax, a plain-English explanation, and a real example for every function. Skip to any category using the table of contents, or download the free PDF cheat sheet at the bottom of the page.
Google Sheets currently has 500+ built-in functions. This guide covers the 200+ you’ll actually use, from everyday basics like SUM and IF to advanced tools like QUERY, ARRAYFORMULA, and the newer LAMBDA family of functions.
Formula vs. Function: What’s the Difference?
These two terms get used interchangeably, but they’re not the same thing.
- A formula is anything you write in a cell that starts with = and performs a calculation. =A1+B1 is a formula. =A1*0.18 is a formula.
- A function is a pre-built, named operation like SUM, VLOOKUP, or AVERAGE that you plug into a formula. In =SUM(A1:A10), SUM is the function; =SUM(A1:A10) is the whole formula.
In short: every function lives inside a formula, but not every formula uses a function.
How to Use a Formula in Google Sheets
- Click the cell where you want the result to appear.
- Type =, then the function name (Sheets will show autocomplete suggestions as you type).
- Enter the arguments inside parentheses cell references, ranges, numbers, or text separated by commas, then press Enter.
Example: to add up cells A1 through A10, click an empty cell, type =SUM(A1:A10), and hit Enter.
Text Formulas
Text (or “string”) formulas manipulate words, letters, and characters combining, trimming, searching, or reformatting text in a cell.
| Formula | Syntax | What It Does & Example |
| CHAR | =CHAR(table_number) | Returns the character associated with a Unicode number. =CHAR(65) returns “A”. |
| CLEAN | =CLEAN(text) | Removes non-printable characters from text. =CLEAN(A1) strips hidden formatting junk from pasted data. |
| CONCATENATE | =CONCATENATE(string1, [string2, …]) | Joins two or more text strings into one. =CONCATENATE(A1,” “,B1) combines a first and last name. |
| EXACT | =EXACT(string1, string2) | Checks whether two strings are identical (case-sensitive). =EXACT(“Sheets”,”sheets”) returns FALSE. |
| FIND | =FIND(search_for, text_to_search, [starting_at]) | Finds the position of text within a string (case-sensitive). =FIND(“e”,”Sheets”) returns 3. |
| JOIN | =JOIN(delimiter, value_or_array1, [value_or_array2, …]) | Joins values with a delimiter between each. =JOIN(“, “, A1:A5) produces a comma-separated list. |
| LEFT | =LEFT(string, [number_of_characters]) | Returns characters from the start of a string. =LEFT(“Google”,3) returns “Goo”. |
| LEN | =LEN(text) | Returns the number of characters in a string. =LEN(“Sheets”) returns 6. |
| MID | =MID(string, starting_at, extract_length) | Extracts characters from the middle of a string. =MID(“Google Sheets”,8,6) returns “Sheets”. |
| UPPER | =UPPER(text) | Converts text to all uppercase. =UPPER(“sheets”) returns “SHEETS”. |
| PROPER | =PROPER(text_to_capitalize) | Capitalizes the first letter of each word. =PROPER(“john doe”) returns “John Doe”. |
| LOWER | =LOWER(text) | Converts text to all lowercase. =LOWER(“SHEETS”) returns “sheets”. |
| REPLACE | =REPLACE(text, position, length, new_text) | Replaces part of a string based on position. =REPLACE(“123456″,1,2,”AB”) returns “AB3456”. |
| REPT | =REPT(text_to_repeat, number_of_repetitions) | Repeats text a set number of times. =REPT(“-“,5) returns “—–“. |
| RIGHT | =RIGHT(string, [number_of_characters]) | Returns characters from the end of a string. =RIGHT(“Google”,3) returns “gle”. |
| SEARCH | =SEARCH(search_for, text_to_search, [starting_at]) | Same as FIND but case-insensitive. =SEARCH(“SHEETS”,”Google Sheets”) returns 8. |
| SUBSTITUTE | =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) | Replaces existing text with new text. =SUBSTITUTE(“Hi there”,”there”,”Sheets”) returns “Hi Sheets”. |
| TEXT | =TEXT(number, format) | Formats a number as text using a specified format pattern. =TEXT(0.5,”0%”) returns “50%”. |
| TRIM | =TRIM(text) | Removes extra spaces from text. =TRIM(” Sheets “) returns “Sheets”. |
| VALUE | =VALUE(text) | Converts a text string that looks like a number into an actual number. =VALUE(“123”) returns 123. |
Statistical Formulas
Statistical formulas summarize and analyze sets of numeric data averages, counts, extremes, and correlations.
| Formula | Syntax | What It Does & Example |
| AVERAGE | =AVERAGE(value1, [value2, …]) | Returns the arithmetic mean of a set of numbers. =AVERAGE(A1:A10). |
| AVERAGEIF | =AVERAGEIF(criteria_range, criterion, [average_range]) | Averages values that meet one condition. =AVERAGEIF(B1:B10,”>50″). |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, …]) | Averages values that meet multiple conditions. |
| CORREL | =CORREL(data_y, data_x) | Calculates the correlation coefficient between two data sets. |
| COUNT | =COUNT(value1, [value2, …]) | Counts the number of cells containing numbers. =COUNT(A1:A10). |
| COUNTA | =COUNTA(value1, [value2, …]) | Counts the number of non-empty cells (numbers or text). |
| MAX | =MAX(value1, [value2, …]) | Returns the largest value in a data set. |
| MAXA | =MAXA(value1, [value2, …]) | Same as MAX but also considers text and logical values as 0. |
| MAXIFS | =MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …]) | Returns the maximum value that meets one or more conditions. |
| MEDIAN | =MEDIAN(value1, [value2, …]) | Returns the median (middle value) of a data set. |
| MIN | =MIN(value1, [value2, …]) | Returns the smallest value in a data set. |
| MINIFS | =MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …]) | Returns the minimum value that meets one or more conditions. |
| MODE | =MODE(value1, [value2, …]) | Returns the most frequently occurring value in a data set. |
| PERCENTILE | =PERCENTILE(data, percentile) | Returns the value at a given percentile of a data set. =PERCENTILE(A1:A20,0.9) returns the 90th percentile. |
Math Formulas
Core arithmetic and rounding functions the most-used category for everyday calculations.
| Formula | Syntax | What It Does & Example |
| ABS | =ABS(value) | Returns the absolute (positive) value of a number. =ABS(-8) returns 8. |
| ACOS | =ACOS(value) | Returns the inverse cosine of a value, in radians. |
| ACOSH | =ACOSH(value) | Returns the inverse hyperbolic cosine of a number. |
| ACOT | =ACOT(value) | Returns the inverse cotangent of a value, in radians. |
| BASE | =BASE(value, base, [min_length]) | Converts a number to text in another base (e.g., binary). |
| CEILING | =CEILING(value, [factor]) | Rounds a number up to the nearest multiple of a factor. =CEILING(4.2,1) returns 5. |
| COMBIN | =COMBIN(n, k) | Returns the number of ways to choose k items from n without regard to order. |
| COMBINA | =COMBINA(n, k) | Same as COMBIN but allows repetition. |
| COS | =COS(angle) | Returns the cosine of an angle in radians. |
| COUNTBLANK | =COUNTBLANK(range) | Counts empty cells in a range. |
| COUNTIF | =COUNTIF(range, criterion) | Counts cells that meet a single condition. =COUNTIF(A1:A10,”>10″). |
| COUNTIFS | =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …]) | Counts cells that meet multiple conditions. |
| COUNTUNIQUE | =COUNTUNIQUE(value1, [value2, …]) | Counts the number of unique values in a range. |
| DECIMAL | =DECIMAL(value, base) | Converts a text representation of a number in another base to decimal. |
| DEGREES | =DEGREES(angle) | Converts radians to degrees. |
| INT | =INT(value) | Rounds a number down to the nearest integer. |
| SUMIF | =SUMIF(range, criterion, [sum_range]) | Sums values that meet one condition. =SUMIF(B1:B10,”Yes”,C1:C10). |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …]) | Sums values that meet multiple conditions. |
| MROUND | =MROUND(value, factor) | Rounds a number to the nearest multiple of a specified factor. |
| POWER | =POWER(base, exponent) | Raises a number to a power. =POWER(2,3) returns 8. |
| PRODUCT | =PRODUCT(factor1, [factor2, …]) | Multiplies a series of numbers together. |
| RAND | =RAND() | Returns a random decimal between 0 and 1. |
| RANDBETWEEN | =RANDBETWEEN(low, high) | Returns a random integer between two values. |
| ROUND | =ROUND(value, [places]) | Rounds a number to a specified number of decimal places. |
| ROUNDDOWN | =ROUNDDOWN(value, [places]) | Rounds a number down, toward zero. |
| ROUNDUP | =ROUNDUP(value, [places]) | Rounds a number up, away from zero. |
| SUM | =SUM(value1, [value2, …]) | Adds a series of numbers or a range. =SUM(A1:A10). |
Date & Time Formulas
Handle dates, timestamps, durations, and business-day calculations.
| Formula | Syntax | What It Does & Example |
| DATE | =DATE(year, month, day) | Builds a date from year, month, day values. =DATE(2026,8,14). |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | Calculates the difference between two dates in days, months, or years. |
| DATEVALUE | =DATEVALUE(date_string) | Converts a date stored as text into a date value. |
| DAY | =DAY(date) | Returns the day of the month from a date. |
| DAYS | =DAYS(end_date, start_date) | Returns the number of days between two dates. |
| DAYS360 | =DAYS360(start_date, end_date, [method]) | Returns days between two dates using a 360-day year (used in some financial calcs). |
| EDATE | =EDATE(start_date, months) | Returns a date a specified number of months before/after a given date. |
| EOMONTH | =EOMONTH(start_date, months) | Returns the last day of the month, a set number of months in the future/past. |
| HOUR | =HOUR(time) | Returns the hour component of a time value. |
| MINUTE | =MINUTE(time) | Returns the minute component of a time value. |
| MONTH | =MONTH(date) | Returns the month from a date. |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Counts working days (excludes weekends) between two dates. |
| NETWORKDAYS.INTL | =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) | Same as NETWORKDAYS with a customizable weekend schedule. |
| NOW | =NOW() | Returns the current date and time. |
| SECOND | =SECOND(time) | Returns the second component of a time value. |
| TIME | =TIME(hour, minute, second) | Builds a time value from hour, minute, second. |
| TIMEVALUE | =TIMEVALUE(time_string) | Converts a time stored as text into a time value. |
| TODAY | =TODAY() | Returns today’s date. |
| WEEKDAY | =WEEKDAY(date, [type]) | Returns the day of the week as a number. |
| WEEKNUM | =WEEKNUM(date, [type]) | Returns the week number of the year for a given date. |
| WORKDAY | =WORKDAY(start_date, num_days, [holidays]) | Returns a date a set number of working days from a start date. |
| WORKDAY.INTL | =WORKDAY.INTL(start_date, num_days, [weekend], [holidays]) | Same as WORKDAY with a customizable weekend schedule. |
| YEAR | =YEAR(date) | Returns the year from a date. |
Array Formulas
Array formulas operate on entire ranges at once instead of single cells essential for advanced automation.
| Formula | Syntax | What It Does & Example |
| ARRAY_CONSTRAIN | =ARRAY_CONSTRAIN(input_range, num_rows, num_cols) | Limits an array result to a specified number of rows/columns. |
| ARRAYFORMULA | =ARRAYFORMULA(array_formula) | Applies a formula across an entire range at once instead of copying it down. =ARRAYFORMULA(A1:A10*B1:B10). |
| BYCOL | =BYCOL(array_or_range, LAMBDA) | Applies a LAMBDA function to each column of an array and returns a result per column. |
| BYROW | =BYROW(array_or_range, LAMBDA) | Applies a LAMBDA function to each row of an array and returns a result per row. |
| FREQUENCY | =FREQUENCY(data, classes) | Calculates how often values fall within specified ranges (a histogram). |
| GROWTH | =GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) | Fits an exponential growth trend and predicts new values. |
| LAMBDA | =LAMBDA(parameter1, …, formula_expression) | Creates a custom, reusable function using your own formula logic — no Apps Script required. |
| LINEST | =LINEST(known_data_y, [known_data_x], [calculate_b], [verbose]) | Returns statistics for a linear regression line. |
| LOGEST | =LOGEST(known_data_y, [known_data_x], [b], [verbose]) | Returns statistics for an exponential regression curve. |
| MAP | =MAP(array1, [array2, …], LAMBDA) | Maps each value in one or more arrays to a new value using a LAMBDA. |
| MDETERM | =MDETERM(square_matrix) | Returns the matrix determinant of a square array. |
| MINVERSE | =MINVERSE(square_matrix) | Returns the multiplicative inverse of a square matrix. |
| MMULT | =MMULT(matrix1, matrix2) | Returns the matrix product of two arrays. |
| REDUCE | =REDUCE(initial_value, array_or_range, LAMBDA) | Reduces an array to a single accumulated result using a LAMBDA. |
| SCAN | =SCAN(initial_value, array_or_range, LAMBDA) | Like REDUCE, but returns the running/intermediate results at each step. |
| SUMPRODUCT | =SUMPRODUCT(array1, [array2, …]) | Multiplies corresponding array elements and sums the results. |
| TRANSPOSE | =TRANSPOSE(array_or_range) | Flips rows into columns (or vice versa). |
| TREND | =TREND(known_data_y, [known_data_x], [new_data_x], [b]) | Fits a linear trend and predicts new values. |
Database Formulas
“D-functions” run calculations on a range formatted as a database (with column headers) that matches a set of criteria.
| Formula | Syntax | What It Does & Example |
| DAVERAGE | =DAVERAGE(database, field, criteria) | Averages a field across records matching criteria. |
| DCOUNT | =DCOUNT(database, field, criteria) | Counts numeric values in a field matching criteria. |
| DCOUNTA | =DCOUNTA(database, field, criteria) | Counts non-empty values in a field matching criteria. |
| DGET | =DGET(database, field, criteria) | Returns a single value from a field matching criteria. |
| DMAX | =DMAX(database, field, criteria) | Returns the maximum value in a field matching criteria. |
| DMIN | =DMIN(database, field, criteria) | Returns the minimum value in a field matching criteria. |
| DPRODUCT | =DPRODUCT(database, field, criteria) | Multiplies values in a field matching criteria. |
| DSTDEV | =DSTDEV(database, field, criteria) | Returns the sample standard deviation of a field matching criteria. |
| DSUM | =DSUM(database, field, criteria) | Sums values in a field matching criteria. |
| DVAR | =DVAR(database, field, criteria) | Returns the sample variance of a field matching criteria. |
Engineering Formulas
Number-base conversions, bitwise operations, and complex-number math.
| Formula | Syntax | What It Does & Example |
| BIN2DEC | =BIN2DEC(signed_binary_number) | Converts binary to decimal. |
| BIN2HEX | =BIN2HEX(signed_binary_number, [significant_digits]) | Converts binary to hexadecimal. |
| BIN2OCT | =BIN2OCT(signed_binary_number, [significant_digits]) | Converts binary to octal. |
| BITAND | =BITAND(value1, value2) | Bitwise AND of two numbers. |
| BITLSHIFT | =BITLSHIFT(value, shift_amount) | Shifts a number’s bits left. |
| BITOR | =BITOR(value1, value2) | Bitwise OR of two numbers. |
| BITRSHIFT | =BITRSHIFT(value, shift_amount) | Shifts a number’s bits right. |
| BITXOR | =BITXOR(value1, value2) | Bitwise XOR of two numbers. |
| COMPLEX | =COMPLEX(real_part, imaginary_part, [suffix]) | Creates a complex number from real and imaginary parts. |
| DEC2BIN | =DEC2BIN(decimal_number, [significant_digits]) | Converts decimal to binary. |
| DEC2HEX | =DEC2HEX(decimal_number, [significant_digits]) | Converts decimal to hexadecimal. |
| DEC2OCT | =DEC2OCT(decimal_number, [significant_digits]) | Converts decimal to octal. |
| DELTA | =DELTA(number1, [number2]) | Tests whether two numbers are equal (returns 1 or 0). |
| ERF | =ERF(lower_bound, [upper_bound]) | Returns the error function integrated between two values. |
| ERF.PRECISE | =ERF.PRECISE(lower_bound, [upper_bound]) | Higher-precision version of ERF. |
| GESTEP | =GESTEP(value, [step]) | Tests whether a number is greater than or equal to a step value. |
| HEX2BIN | =HEX2BIN(signed_hexadecimal_number, [significant_digits]) | Converts hexadecimal to binary. |
| HEX2DEC | =HEX2DEC(signed_hexadecimal_number) | Converts hexadecimal to decimal. |
| HEX2OCT | =HEX2OCT(signed_hexadecimal_number, [significant_digits]) | Converts hexadecimal to octal. |
| IMABS | =IMABS(number) | Returns the absolute value of a complex number. |
| IMAGINARY | =IMAGINARY(complex_number) | Returns the imaginary coefficient of a complex number. |
| IMARGUMENT | =IMARGUMENT(number) | Returns the angle (argument) of a complex number. |
| IMCOS | =IMCOS(number) | Returns the cosine of a complex number. |
| IMSQRT | =IMSQRT(complex_number) | Returns the square root of a complex number. |
Financial Formulas
Loan payments, depreciation, interest rates, and investment returns.
| Formula | Syntax | What It Does & Example |
| DB | =DB(cost, salvage, life, period, [month]) | Calculates depreciation using the fixed-declining balance method. |
| DDB | =DDB(cost, salvage, life, period, [factor]) | Calculates depreciation using the double-declining balance method. |
| DISC | =DISC(settlement, maturity, price, redemption, [day_count_convention]) | Calculates the discount rate of a security. |
| DOLLARDE | =DOLLARDE(fractional_price, unit) | Converts a fractional dollar price to a decimal price. |
| DOLLARFR | =DOLLARFR(decimal_price, unit) | Converts a decimal dollar price to a fractional price. |
| DURATION | =DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) | Calculates the annual duration of a security with periodic interest. |
| EFFECT | =EFFECT(nominal_rate, periods_per_year) | Calculates the effective annual interest rate given a nominal rate. |
| FV | =FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning]) | Calculates the future value of an investment. =FV(0.05/12,60,-200) for a monthly savings plan. |
| FVSCHEDULE | =FVSCHEDULE(principal, rate_schedule) | Calculates future value using a series of variable interest rates. |
| INTRATE | =INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention]) | Calculates the interest rate of a fully invested security. |
| IPMT | =IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) | Calculates the interest portion of a loan payment for a given period. |
| IRR | =IRR(cashflow_amounts, [rate_guess]) | Calculates the internal rate of return for a series of cash flows. |
| ISPMT | =ISPMT(rate, period, number_of_periods, present_value) | Calculates interest paid during a specific loan period. |
| MIRR | =MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate) | Calculates modified internal rate of return with different financing/reinvestment rates. |
| NOMINAL | =NOMINAL(effective_rate, periods_per_year) | Calculates the nominal annual interest rate given an effective rate. |
| NPER | =NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) | Calculates the number of payment periods for a loan or investment. |
| NPV | =NPV(discount, cashflow1, [cashflow2, …]) | Calculates the net present value of a series of cash flows. |
| PMT | =PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) | Calculates the periodic payment for a loan. =PMT(0.05/12,360,-300000) for a mortgage payment. |
| PRICE | =PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention]) | Calculates the price of a security paying periodic interest. |
| PV | =PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]) | Calculates the present value of an investment. |
| RATE | =RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess]) | Calculates the interest rate per period of a loan/investment. |
| XIRR | =XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) | Calculates internal rate of return for cash flows on irregular dates. |
| YIELD | =YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention]) | Calculates the yield of a security paying periodic interest. |
Filter & Sort Formulas
Dynamically filter, sort, and de-duplicate data without touching the original range.
| Formula | Syntax | What It Does & Example |
| FILTER | =FILTER(range, condition1, [condition2, …]) | Returns only rows/columns that meet given conditions. =FILTER(A2:B10,B2:B10>50). |
| SORT | =SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2]) | Sorts a range by one or more columns. |
| SORTN | =SORTN(range, [n], [display_ties_mode], sort_column1, is_ascending1, …) | Returns the top N rows of a sorted range. |
| UNIQUE | =UNIQUE(range) | Returns only unique (non-duplicate) rows from a range. |
Google-Specific Formulas
Functions unique to Google Sheets not available in desktop spreadsheet software.
| Formula | Syntax | What It Does & Example |
| AI | =AI(prompt, [range]) | Generates text or extracts insights using AI directly inside a cell. |
| ARRAYFORMULA | =ARRAYFORMULA(array_formula) | (See Array Formulas above.) |
| DETECTLANGUAGE | =DETECTLANGUAGE(text_or_range) | Detects the language of the given text. |
| GOOGLEFINANCE | =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]) | Fetches current or historical securities information from Google Finance. |
| GOOGLETRANSLATE | =GOOGLETRANSLATE(text, [source_language], [target_language]) | Translates text between languages. |
| IMAGE | =IMAGE(url, [mode], [height], [width]) | Inserts an image into a cell from a URL. |
| IMPORTRANGE | =IMPORTRANGE(spreadsheet_url, range_string) | Imports a range of cells from a different Google Sheets file. |
| QUERY | =QUERY(data, query, [headers]) | Runs SQL-like queries on a range — one of the most powerful functions in Sheets. =QUERY(A1:C100,”SELECT A,B WHERE C>50″). |
| SPARKLINE | =SPARKLINE(data, [options]) | Creates a tiny in-cell chart. |
Info Formulas
Return information about a cell or value its type, error status, or emptiness.
| Formula | Syntax | What It Does & Example |
| CELL | =CELL(info_type, reference) | Returns information about a cell (formatting, location, contents). |
| ERROR.TYPE | =ERROR.TYPE(reference) | Returns a number corresponding to a specific error type. |
| ISBLANK | =ISBLANK(value) | Checks whether a cell is empty. |
| ISDATE | =ISDATE(value) | Checks whether a value is a date. |
| ISEMAIL | =ISEMAIL(value) | Checks whether a value is a valid email address. |
| ISERR | =ISERR(value) | Checks for an error value (excluding #N/A). |
| ISERROR | =ISERROR(value) | Checks for any error value, including #N/A. |
| ISFORMULA | =ISFORMULA(cell) | Checks whether a cell contains a formula. |
| ISLOGICAL | =ISLOGICAL(value) | Checks whether a value is TRUE or FALSE. |
| ISNA | =ISNA(value) | Checks whether a value is the #N/A error. |
| ISNONTEXT | =ISNONTEXT(value) | Checks whether a value is not text. |
| ISNUMBER | =ISNUMBER(value) | Checks whether a value is a number. |
| ISREF | =ISREF(value) | Checks whether a value is a valid cell reference. |
| ISTEXT | =ISTEXT(value) | Checks whether a value is text. |
| N | =N(value) | Converts a value to a number. |
| NA | =NA() | Returns the #N/A error value. |
| TYPE | =TYPE(value) | Returns a code representing a value’s data type. |
Logical & Lookup Formulas
Conditional logic and the functions used to find values across a sheet.
| Formula | Syntax | What It Does & Example |
| AND | =AND(logical_expression1, [logical_expression2, …]) | Returns TRUE only if every condition is TRUE. |
| FALSE | =FALSE() | Returns the logical value FALSE. |
| IF | =IF(logical_expression, value_if_true, value_if_false) | Returns one value if a condition is true, another if false. =IF(A1>50,”Pass”,”Fail”). |
| IFERROR | =IFERROR(value, [value_if_error]) | Returns a fallback value if a formula errors out. |
| IFNA | =IFNA(value, value_if_na) | Returns a fallback value specifically for #N/A errors. |
| IFS | =IFS(condition1, value1, [condition2, value2, …]) | Tests multiple conditions in sequence and returns the first match. |
| NOT | =NOT(logical_expression) | Reverses a logical value (TRUE becomes FALSE). |
| OR | =OR(logical_expression1, [logical_expression2, …]) | Returns TRUE if at least one condition is TRUE. |
| SWITCH | =SWITCH(expression, case1, value1, [default_or_case2, value2, …]) | Matches an expression against a list of cases and returns the corresponding value. |
| TRUE | =TRUE() | Returns the logical value TRUE. |
| ADDRESS | =ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) | Returns a cell reference as text. |
| CHOOSE | =CHOOSE(index, choice1, [choice2, …]) | Returns a value from a list based on an index number. |
| COLUMN | =COLUMN([cell_reference]) | Returns the column number of a given cell. |
| COLUMNS | =COLUMNS(range) | Returns the number of columns in a range. |
| FORMULATEXT | =FORMULATEXT(cell) | Returns the formula in a cell as text. |
| HLOOKUP | =HLOOKUP(search_key, range, index, [is_sorted]) | Looks up a value across a horizontal row. |
| INDEX | =INDEX(reference, [row], [column]) | Returns the value at a given row/column position in a range. |
| INDIRECT | =INDIRECT(cell_reference_as_string, [is_A1_notation]) | Converts a text string into a cell reference. |
| LOOKUP | =LOOKUP(search_key, search_range|search_result_array, [result_range]) | Looks up a value in a one-row or one-column range and returns a value from the same position in another range. |
| MATCH | =MATCH(search_key, range, [search_type]) | Returns the relative position of a value within a range. |
| OFFSET | =OFFSET(cell_reference, offset_rows, offset_columns, [height], [width]) | Returns a range shifted a set number of rows/columns from a starting cell. |
| ROW | =ROW([cell_reference]) | Returns the row number of a given cell. |
| ROWS | =ROWS(range) | Returns the number of rows in a range. |
| VLOOKUP | =VLOOKUP(search_key, range, index, [is_sorted]) | Looks up a value in the first column of a range and returns a value from another column in the same row. =VLOOKUP(“Apple”,A2:C50,3,FALSE). |
| XLOOKUP | =XLOOKUP(search_key, search_range, results_range, [missing_value], [match_mode], [search_mode]) | A more flexible, modern replacement for VLOOKUP/HLOOKUP — searches in any direction and returns any column. |
Operator Formulas
Function equivalents of standard math operators useful inside ARRAYFORMULA and QUERY.
| Formula | Syntax | What It Does & Example |
| ADD | =ADD(value1, value2) | Returns the sum of two numbers (equivalent to +). |
| CONCAT | =CONCAT(value1, value2) | Joins two values into one string (equivalent to &). |
| DIVIDE | =DIVIDE(dividend, divisor) | Divides one number by another (equivalent to /). |
| EQ | =EQ(value1, value2) | Tests whether two values are equal (equivalent to =). |
| GT | =GT(value1, value2) | Tests whether value1 is greater than value2 (equivalent to >). |
| GTE | =GTE(value1, value2) | Tests whether value1 is greater than or equal to value2 (equivalent to >=). |
| LT | =LT(value1, value2) | Tests whether value1 is less than value2 (equivalent to <). |
| LTE | =LTE(value1, value2) | Tests whether value1 is less than or equal to value2 (equivalent to <=). |
| MINUS | =MINUS(value1, value2) | Subtracts value2 from value1 (equivalent to -). |
| MULTIPLY | =MULTIPLY(value1, value2) | Multiplies two numbers (equivalent to *). |
| NE | =NE(value1, value2) | Tests whether two values are not equal (equivalent to <>). |
| UMINUS | =UMINUS(value) | Returns a number with the sign reversed (equivalent to unary -). |
| UNARY_PERCENT | =UNARY_PERCENT(percentage) | Divides a number by 100 (equivalent to %). |
| UPLUS | =UPLUS(value) | Returns a number unchanged (equivalent to unary +). |
Common Formula Errors (and How to Fix Them)
| Formula/Shortcut | Syntax/Key | Description/Action |
| #REF! | #REF! | A formula references a cell that’s been deleted or moved. Rewrite the reference, or use IFERROR to catch it. |
| #N/A | #N/A | A lookup function couldn’t find a match. Check for typos/extra spaces, or wrap in IFNA/IFERROR. |
| #VALUE! | #VALUE! | A formula is receiving the wrong data type (e.g., text where a number is expected). Confirm the referenced cells contain the expected data type. |
| #DIV/0! | #DIV/0! | A formula is dividing by zero or an empty cell. Wrap the formula in IFERROR or check the denominator first. |
| #NAME? | #NAME? | Google Sheets doesn’t recognize a function name — usually a typo. Double-check spelling and that the function exists in Sheets. |
| #NUM! | #NUM! | A formula received an invalid numeric value (e.g., a negative number where only positive is valid). Check the input range for out-of-bounds values. |
Download the Free Google Sheets Formulas PDF
Want this entire list offline? Download the free Google Sheets Formulas PDF to keep every formula, syntax, and example on hand — no internet connection required.
FAQs
What is the difference between a formula and a function in Google Sheets?
A formula is any expression starting with = that calculates a result; a function is a named, pre-built operation (like SUM or VLOOKUP) used inside a formula.
How do I see a full list of Google Sheets formulas?
Use this guide, or click Insert > Function inside Google Sheets to browse the built-in function menu by category.
What is the most useful Google Sheets formula for beginners?
SUM, IF, and VLOOKUP cover the majority of everyday spreadsheet tasks and are the best starting point.
Can I use Excel formulas in Google Sheets?
Most core formulas work the same in both, but some like Google’s IMPORTRANGE, QUERY, and GOOGLEFINANCE have no direct Excel equivalent, and vice versa.
How many formulas does Google Sheets have?
Google Sheets has 500+ built-in functions across categories including Text, Math, Date, Statistical, Lookup, Financial, Array, and Google-specific functions.
What does #REF! mean in Google Sheets and how do I fix it?
It means a formula is pointing to a cell that no longer exists (usually because it was deleted). Rewrite the reference to point to a valid cell, or wrap the formula in IFERROR.
Is there a free downloadable PDF of Google Sheets formulas?
Yes see the download link above.
Conclusion
That’s the complete Google Sheets formulas list 200+ functions across every category, from everyday basics like SUM and IF to advanced tools like QUERY, ARRAYFORMULA, and the newer LAMBDA family. You don’t need to memorize all of them. Bookmark this page, start with the handful of formulas that solve your immediate problem, and come back whenever you hit a new one.
If there’s one habit worth building: before you copy a formula off the internet, check its syntax against a reliable source like this guide or Google’s own documentation small errors in argument order or missing brackets are the #1 cause of #REF!, #VALUE!, and #N/A errors.
Want this list on hand without an internet connection? Download the free Google Sheets Formulas PDF above and keep it as a permanent cheat sheet.
READ NEXT:
