Every function in Sheets, with a worked example
The 182 functions the Sheets formula engine works out, in the categories Excel's Formulas tab uses. Each has its arguments, what it does, and an example that a test types into Sheets and checks against the answer Excel gives.
Sheets has XLOOKUP, VLOOKUP, INDEX and MATCH, SUMIFS and COUNTIFS, IFS and SWITCH, TEXTJOIN, TEXT with its format codes, NETWORKDAYS, and the loan functions PMT, RATE, IPMT and PPMT. It has Excel's dynamic arrays: FILTER, UNIQUE, SORT, SORTBY and SEQUENCE spill their results into the cells beside them, arithmetic works on whole ranges, array constants such as {1,2,3} can be typed, and A2# is the whole block a cell spilled. A formula shows #SPILL! when something is already in a cell it would spill into. LET and LAMBDA work inside a formula, with MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY. A workbook opened from .xlsx shows the value Excel saved for any formula Sheets cannot work out, and keeps that value in place of the formula.
The table the examples read
The examples that refer to cells read this table, typed into A1 of an empty sheet. The rest stand on their own and can be pasted into any cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Region | Amount | Date | Units | Cash flow |
| 2 | Pens | North | 120 | 2026-01-15 | 3 | -1000 |
| 3 | Paper | South | 80 | 2026-02-01 | 5 | 400 |
| 4 | Ink | North | 150 | 2026-02-20 | 2 | 400 |
| 5 | Stapler | East | 40 | 2026-03-05 | 9 | 400 |
| 6 | Folders | South | 220 | 2026-03-31 | 1 |
Where Sheets gives a different answer from Excel
- Dates are kept as text. DATE, EDATE, EOMONTH, WORKDAY and TODAY give a date written
yyyy-mm-dd, where Excel gives a serial number formatted as a date. Arithmetic and comparisons still treat it as a date (=D6-D2is 75), but=ISNUMBER(TODAY())is FALSE, and a date with days added to it shows as its serial number, 46067, until the cell is given a date format or the formula is wrapped in TEXT. - A time on its own, typed as text, is not a time.
=HOUR("14:35")gives 0. A date and time together,"2026-03-05 14:35", and a fraction of a day both work. - An empty cell in a spilled result shows empty.
=A2:A6over a column with a gap spills the gap as an empty cell, where Excel shows 0, as Sheets does for=A3on its own. - Excel's binary searches search in order. XLOOKUP and XMATCH with a search mode of 2 or -2 find what 1 and -1 find, which on a sorted list is the same answer.
Everything else in the tables below gave the answer Excel gives for the same formula when this page was built.
Math & Trig (46)
Arithmetic, rounding, and the adding-up functions with conditions: SUMIF, SUMIFS and SUBTOTAL.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ABS | ABS(number) Returns the absolute value of a number, a number without its sign. | =ABS(-7.5) | 7.5 |
| ACOS | ACOS(number) Returns the arccosine of a number, in radians. | =ROUND(ACOS(0.5),6) | 1.047198 |
| ASIN | ASIN(number) Returns the arcsine of a number, in radians. | =ROUND(ASIN(1),6) | 1.570796 |
| ATAN | ATAN(number) Returns the arctangent of a number, in radians. | =ROUND(ATAN(1),6) | 0.785398 |
| ATAN2 | ATAN2(x_num, y_num) Returns the arctangent of the specified x- and y-coordinates, in radians. | =ROUND(ATAN2(-1,0),6) | 3.141593 |
| CEILING | CEILING(number, significance) Rounds a number up, to the nearest multiple of significance. | =CEILING(22,5) | 25 |
| CEILING.MATH | CEILING.MATH(number, [significance]) Rounds a number up, to the nearest integer or to the nearest multiple of significance. | =CEILING.MATH(-4.3) | -4 |
| COMBIN | COMBIN(number, number_chosen) Returns the number of combinations for a given number of items. | =COMBIN(8,2) | 28 |
| COS | COS(number) Returns the cosine of an angle. | =COS(0) | 1 |
| DEGREES | DEGREES(angle) Converts radians to degrees. | =DEGREES(PI()) | 180 |
| EVEN | EVEN(number) Rounds a positive number up and a negative number down to the nearest even integer. | =EVEN(3.2) | 4 |
| EXP | EXP(number) Returns e raised to the power of a given number. | =ROUND(EXP(1),6) | 2.718282 |
| FACT | FACT(number) Returns the factorial of a number, equal to 1*2*3*...*number. | =FACT(5) | 120 |
| FLOOR | FLOOR(number, significance) Rounds a number down to the nearest multiple of significance. | =FLOOR(22,5) | 20 |
| FLOOR.MATH | FLOOR.MATH(number, [significance]) Rounds a number down, to the nearest integer or to the nearest multiple of significance. | =FLOOR.MATH(-4.3) | -5 |
| GCD | GCD(number1, [number2], ...) Returns the greatest common divisor. | =GCD(24,36) | 12 |
| INT | INT(number) Rounds a number down to the nearest integer. | =INT(-3.7) | -4 |
| LCM | LCM(number1, [number2], ...) Returns the least common multiple. | =LCM(4,6) | 12 |
| LN | LN(number) Returns the natural logarithm of a number. | =ROUND(LN(10),6) | 2.302585 |
| LOG | LOG(number, [base]) Returns the logarithm of a number to the base you specify. | =LOG(8,2) | 3 |
| LOG10 | LOG10(number) Returns the base-10 logarithm of a number. | =LOG10(1000) | 3 |
| MOD | MOD(number, divisor) Returns the remainder after a number is divided by a divisor. | =MOD(-7,3) | 2 |
| MROUND | MROUND(number, multiple) Returns a number rounded to the desired multiple. | =MROUND(17,5) | 15 |
| ODD | ODD(number) Rounds a positive number up and a negative number down to the nearest odd integer. | =ODD(2.1) | 3 |
| PI | PI() Returns the value of Pi, 3.14159265358979, accurate to 15 digits. | =ROUND(PI(),5) | 3.14159 |
| POWER | POWER(number, power) Returns the result of a number raised to a power. | =POWER(2,10) | 1024 |
| PRODUCT | PRODUCT(number1, [number2], ...) Multiplies all the numbers given as arguments. | =PRODUCT(2,3,4) | 24 |
| QUOTIENT | QUOTIENT(numerator, denominator) Returns the integer portion of a division. | =QUOTIENT(-7,2) | -3 |
| RADIANS | RADIANS(angle) Converts degrees to radians. | =ROUND(RADIANS(180),5) | 3.14159 |
| RAND | RAND() Returns a random number greater than or equal to 0 and less than 1, evenly distributed. It changes on recalculation. | =AND(RAND()>=0,RAND()<1) | TRUE |
| RANDBETWEEN | RANDBETWEEN(bottom, top) Returns a random number between the numbers you specify. | =RANDBETWEEN(3,3) | 3 |
| ROUND | ROUND(number, num_digits) Rounds a number to a specified number of digits. | =ROUND(2.345,2) | 2.35 |
| ROUNDDOWN | ROUNDDOWN(number, num_digits) Rounds a number down, toward zero. | =ROUNDDOWN(-3.789,1) | -3.7 |
| ROUNDUP | ROUNDUP(number, num_digits) Rounds a number up, away from zero. | =ROUNDUP(3.211,2) | 3.22 |
| SEQUENCE | SEQUENCE(rows, [columns], [start], [step]) Returns an array of numbers counting on, such as 1, 2, 3, 4, spilling across and down.On its own, =SEQUENCE(4,5) spills four rows of five numbers, 1 to 20. Anything already in the cells it would fill stops it, and it shows #SPILL!. | =TEXTJOIN(",",TRUE,SEQUENCE(1,5,10,-2)) | 10,8,6,4,2 |
| SIGN | SIGN(number) Returns the sign of a number: 1 if positive, zero if zero, -1 if negative. | =SIGN(-12) | -1 |
| SIN | SIN(number) Returns the sine of an angle. | =ROUND(SIN(PI()/6),6) | 0.5 |
| SQRT | SQRT(number) Returns the square root of a number. | =SQRT(144) | 12 |
| SUBTOTAL | SUBTOTAL(function_num, ref1, [ref2], ...) Returns a subtotal in a list or database, leaving out the rows a filter hides. | =SUBTOTAL(9,C2:C6) | 610 |
| SUM | SUM(number1, [number2], ...) Adds all the numbers in a range of cells. | =SUM(C2:C6) | 610 |
| SUMIF | SUMIF(range, criteria, [sum_range]) Adds the cells specified by a given condition or criteria. | =SUMIF(B2:B6,"North",C2:C6) | 270 |
| SUMIFS | SUMIFS(sum_range, criteria_range1, criteria1, ...) Adds the cells specified by a given set of conditions or criteria. | =SUMIFS(C2:C6,B2:B6,"South",C2:C6,">100") | 220 |
| SUMPRODUCT | SUMPRODUCT(array1, [array2], ...) Returns the sum of the products of corresponding ranges or arrays.Arithmetic on whole ranges works place by place, so the Excel idiom of multiplying a condition by a range gives the total of the North rows, as SUMIFS(C2:C6,B2:B6,"North") does. | =SUMPRODUCT((B2:B6="North")*C2:C6) | 270 |
| SUMSQ | SUMSQ(number1, [number2], ...) Returns the sum of the squares of the arguments. | =SUMSQ(3,4) | 25 |
| TAN | TAN(number) Returns the tangent of an angle. | =ROUND(TAN(PI()/4),6) | 1 |
| TRUNC | TRUNC(number, [num_digits]) Truncates a number to an integer by removing the decimal, or fractional, part of the number. | =TRUNC(-3.789,1) | -3.7 |
Statistical (30)
Counting, averages, spread and rank, with and without conditions.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| AVERAGE | AVERAGE(number1, [number2], ...) Returns the average (arithmetic mean) of its arguments. | =AVERAGE(C2:C6) | 122 |
| AVERAGEIF | AVERAGEIF(range, criteria, [average_range]) Finds the average of the cells specified by a given condition or criteria. | =AVERAGEIF(B2:B6,"South",C2:C6) | 150 |
| AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criteria1, ...) Finds the average of the cells specified by a given set of conditions or criteria. | =AVERAGEIFS(C2:C6,B2:B6,"North",C2:C6,">100") | 135 |
| COUNT | COUNT(value1, [value2], ...) Counts the number of cells in a range that contain numbers. | =COUNT(A1:C6) | 5 |
| COUNTA | COUNTA(value1, [value2], ...) Counts the number of cells in a range that are not empty. | =COUNTA(A1:A6) | 6 |
| COUNTBLANK | COUNTBLANK(range) Counts the number of empty cells in a specified range of cells. | =COUNTBLANK(G1:G6) | 6 |
| COUNTIF | COUNTIF(range, criteria) Counts the number of cells within a range that meet the given condition. | =COUNTIF(C2:C6,">100") | 3 |
| COUNTIFS | COUNTIFS(criteria_range1, criteria1, ...) Counts the number of cells specified by a given set of conditions or criteria. | =COUNTIFS(B2:B6,"South",C2:C6,">100") | 1 |
| LARGE | LARGE(array, k) Returns the k-th largest value in a data set. | =LARGE(C2:C6,2) | 150 |
| MAX | MAX(number1, [number2], ...) Returns the largest value in a set of values. | =MAX(C2:C6) | 220 |
| MAXIFS | MAXIFS(max_range, criteria_range1, criteria1, ...) Returns the maximum value among cells specified by a given set of conditions or criteria. | =MAXIFS(C2:C6,B2:B6,"North") | 150 |
| MEDIAN | MEDIAN(number1, [number2], ...) Returns the median, or the number in the middle of the set of given numbers. | =MEDIAN(C2:C6) | 120 |
| MIN | MIN(number1, [number2], ...) Returns the smallest number in a set of values. | =MIN(C2:C6) | 40 |
| MINIFS | MINIFS(min_range, criteria_range1, criteria1, ...) Returns the minimum value among cells specified by a given set of conditions or criteria. | =MINIFS(C2:C6,B2:B6,"South") | 80 |
| MODE | MODE(number1, [number2], ...) Returns the most frequently occurring value in a range of data. | =MODE(3,5,5,7) | 5 |
| PERCENTILE | PERCENTILE(array, k) Returns the k-th percentile of values in a range. | =PERCENTILE(C2:C6,0.25) | 80 |
| PERCENTILE.INC | PERCENTILE.INC(array, k) Returns the k-th percentile of values in a range, where k is in the range 0 to 1, inclusive. | =PERCENTILE.INC(C2:C6,0.9) | 192 |
| QUARTILE | QUARTILE(array, quart) Returns the quartile of a data set. | =QUARTILE(C2:C6,3) | 150 |
| QUARTILE.INC | QUARTILE.INC(array, quart) Returns the quartile of a data set, based on percentile values from 0 to 1, inclusive. | =QUARTILE.INC(C2:C6,1) | 80 |
| RANK | RANK(number, ref, [order]) Returns the rank of a number in a list of numbers. | =RANK(150,C2:C6) | 2 |
| RANK.EQ | RANK.EQ(number, ref, [order]) Returns the rank of a number in a list of numbers; if more than one value has the same rank, the top rank of that set is returned. | =RANK.EQ(150,C2:C6,1) | 4 |
| SMALL | SMALL(array, k) Returns the k-th smallest value in a data set. | =SMALL(C2:C6,2) | 80 |
| STDEV | STDEV(number1, [number2], ...) Estimates standard deviation based on a sample. | =ROUND(STDEV(C2:C6),4) | 68.7023 |
| STDEV.P | STDEV.P(number1, [number2], ...) Calculates standard deviation based on the entire population given as arguments. | =ROUND(STDEV.P(C2:C6),4) | 61.4492 |
| STDEV.S | STDEV.S(number1, [number2], ...) Estimates standard deviation based on a sample. | =ROUND(STDEV.S(C2:C6),4) | 68.7023 |
| STDEVP | STDEVP(number1, [number2], ...) Calculates standard deviation based on the entire population given as arguments. | =ROUND(STDEVP(C2:C6),4) | 61.4492 |
| VAR | VAR(number1, [number2], ...) Estimates variance based on a sample. | =VAR(C2:C6) | 4720 |
| VAR.P | VAR.P(number1, [number2], ...) Calculates variance based on the entire population. | =VAR.P(C2:C6) | 3776 |
| VAR.S | VAR.S(number1, [number2], ...) Estimates variance based on a sample. | =VAR.S(C2:C6) | 4720 |
| VARP | VARP(number1, [number2], ...) Calculates variance based on the entire population. | =VARP(C2:C6) | 3776 |
Lookup & Reference (19)
Finding a value in a table, and working with where a cell is rather than what it holds.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ADDRESS | ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Creates a cell reference as text, given row and column numbers. | =ADDRESS(2,3) | $C$2 |
| CHOOSE | CHOOSE(index_num, value1, [value2], ...) Chooses a value from a list of values, based on an index number. | =CHOOSE(2,"red","amber","green") | amber |
| COLUMN | COLUMN([reference]) Returns the column number of a reference. | =COLUMN(D1:D6) | 4 |
| COLUMNS | COLUMNS(array) Returns the number of columns in an array or reference. | =COLUMNS(A1:D6) | 4 |
| FILTER | FILTER(array, include, [if_empty]) Filters a range or array to the rows or columns you choose with a condition, spilling the result.On its own, =FILTER(A2:C6,B2:B6="North") spills the two North rows, three columns wide, into the cells below and to the right. With nothing kept it is #CALC!, or its third argument. | =TEXTJOIN(", ",TRUE,FILTER(A2:A6,B2:B6="North")) | Pens, Ink |
| HLOOKUP | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Looks for a value in the top row of a table and returns the value in the same column from a row you specify. | =HLOOKUP("Amount",A1:D6,3,FALSE) | 80 |
| INDEX | INDEX(array, row_num, [column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. | =INDEX(A2:C6,3,3) | 150 |
| INDIRECT | INDIRECT(ref_text, [a1]) Returns the reference specified by a text string. | =INDIRECT("C"&4) | 150 |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value in a specified order. | =MATCH("Ink",A2:A6,0) | 3 |
| OFFSET | OFFSET(reference, rows, cols, [height], [width]) Returns a reference to a range that is a given number of rows and columns from a given reference. | =SUM(OFFSET(C1,1,0,3,1)) | 350 |
| ROW | ROW([reference]) Returns the row number of a reference. | =ROW(C4) | 4 |
| ROWS | ROWS(array) Returns the number of rows in a reference or an array. | =ROWS(A2:C6) | 5 |
| SORT | SORT(array, [sort_index], [sort_order], [by_col]) Sorts the contents of a range or array, spilling the result.On its own it spills the sorted rows. Numbers sort before text and text before TRUE and FALSE, as in Excel. | =TEXTJOIN(", ",TRUE,SORT(A2:A6)) | Folders, Ink, Paper, Pens, Stapler |
| SORTBY | SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...) Sorts the contents of a range or array by the values in other ranges or arrays. | =TEXTJOIN(", ",TRUE,SORTBY(A2:A6,C2:C6,-1)) | Folders, Ink, Pens, Paper, Stapler |
| TRANSPOSE | TRANSPOSE(array) Returns a vertical range of cells as a horizontal range, or a horizontal one as vertical.On its own it spills: a column becomes a row. No Ctrl+Shift+Enter is needed. | =TEXTJOIN(",",TRUE,TRANSPOSE(E2:E4)) | 3,5,2 |
| UNIQUE | UNIQUE(array, [by_col], [exactly_once]) Returns the different values in a range or array, each once.Text is compared regardless of case, as in Excel. =UNIQUE(B2:B6,,TRUE) keeps only the values there once: East. | =TEXTJOIN(", ",TRUE,UNIQUE(B2:B6)) | North, South, East |
| VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. | =VLOOKUP("Ink",A2:C6,3,FALSE) | 150 |
| XLOOKUP | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Searches a range for a match and returns the corresponding item from a second range. | =XLOOKUP("South",B2:B6,A2:A6,"none",0,-1) | Folders |
| XMATCH | XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) Returns the position of an item in a range or array, searching as XLOOKUP does. | =XMATCH("Ink",A2:A6) | 3 |
Logical (19)
Conditions, and the functions that choose between answers.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| AND | AND(logical1, [logical2], ...) Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. | =AND(C2>100,B2="North") | TRUE |
| BYCOL | BYCOL(array, lambda(column)) Applies a LAMBDA to each column and returns an array of the results, one for each column.BYCOL on its own spills one value to each column, along a row. | =TEXTJOIN(",",TRUE,BYCOL(E2:F6,LAMBDA(c,SUM(c)))) | 20,200 |
| BYROW | BYROW(array, lambda(row)) Applies a LAMBDA to each row and returns an array of the results, one for each row.BYROW on its own spills one value to each row, down a column. | =TEXTJOIN(",",TRUE,BYROW(E2:F4,LAMBDA(r,MAX(r)))) | 3,400,400 |
| FALSE | FALSE() Returns the logical value FALSE. | =FALSE() | FALSE |
| IF | IF(logical_test, [value_if_true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. | =IF(C2>100,"over","within") | over |
| IFERROR | IFERROR(value, value_if_error) Returns value_if_error if the expression is an error and the value of the expression itself otherwise. | =IFERROR(1/0,"n/a") | n/a |
| IFNA | IFNA(value, value_if_na) Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. | =IFNA(MATCH("Glue",A2:A6,0),"not stocked") | not stocked |
| IFS | IFS(logical_test1, value_if_true1, ...) Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition. | =IFS(C5>=200,"high",C5>=100,"medium",TRUE,"low") | low |
| LAMBDA | LAMBDA([parameter1, parameter2, ...], calculation) Makes a function of your own inside a formula, to call at once or to give to LET, MAP and the others.Written into a formula and called there, or named with LET and handed to MAP, REDUCE, SCAN, BYROW, BYCOL or MAKEARRAY. Named LAMBDAs in Name Manager are not supported yet. | =LAMBDA(x,y,x*y)(C2,E2) | 360 |
| LET | LET(name1, name_value1, [name2, name_value2, ...], calculation) Gives names to values or to results worked out along the way, and uses them in a calculation. | =LET(total,SUM(C2:C6),n,COUNT(C2:C6),total/n) | 122 |
| MAKEARRAY | MAKEARRAY(rows, columns, lambda(row, column)) Returns an array of the size you give, each value worked out by a LAMBDA from its row and column.On its own it spills the array it makes: here two rows of 1, 2, 3 and 2, 4, 6. | =SUM(MAKEARRAY(2,3,LAMBDA(r,c,r*c))) | 18 |
| MAP | MAP(array1, [array2, ...], lambda) Returns an array made by applying a LAMBDA to each value of the arrays.On its own it spills an array the shape of the one it is given. | =TEXTJOIN(",",TRUE,MAP(C2:C4,LAMBDA(v,v*2))) | 240,160,300 |
| NOT | NOT(logical) Changes FALSE to TRUE, or TRUE to FALSE. | =NOT(C5>100) | TRUE |
| OR | OR(logical1, [logical2], ...) Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. | =OR(B2="East",C2>200) | FALSE |
| REDUCE | REDUCE([initial_value], array, lambda(accumulator, value)) Reduces an array to one value by applying a LAMBDA to each value and carrying the result forward. | =REDUCE(0,C2:C6,LAMBDA(acc,v,IF(v>100,acc+v,acc))) | 490 |
| SCAN | SCAN([initial_value], array, lambda(accumulator, value)) Applies a LAMBDA to each value of an array and returns every result along the way.On its own it spills the running results, in the shape of the array. | =TEXTJOIN(",",TRUE,SCAN(0,E2:E6,LAMBDA(a,v,a+v))) | 3,8,10,19,20 |
| SWITCH | SWITCH(expression, value1, result1, [default_or_value2], ...) Evaluates an expression against a list of values and returns the result corresponding to the first matching value. | =SWITCH(B3,"North","N","South","S","other") | S |
| TRUE | TRUE() Returns the logical value TRUE. | =TRUE() | TRUE |
| XOR | XOR(logical1, [logical2], ...) Returns a logical Exclusive Or of all arguments. | =XOR(TRUE,TRUE,TRUE) | TRUE |
Text (28)
Cutting text up, putting it together, and showing a number or a date as text in a given format.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| CHAR | CHAR(number) Returns the character specified by the code number. | =CHAR(65) | A |
| CLEAN | CLEAN(text) Removes all nonprintable characters from text. | =LEN(CLEAN("a"&CHAR(9)&"b")) | 2 |
| CODE | CODE(text) Returns a numeric code for the first character in a text string. | =CODE("A") | 65 |
| CONCAT | CONCAT(text1, [text2], ...) Joins a list or range of text strings. | =CONCAT(A2,"-",B2) | Pens-North |
| CONCATENATE | CONCATENATE(text1, [text2], ...) Joins several text strings into one text string. | =CONCATENATE("Q",1," ",2026) | Q1 2026 |
| EXACT | EXACT(text1, text2) Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive. | =EXACT("Ink","ink") | FALSE |
| FIND | FIND(find_text, within_text, [start_num]) Returns the starting position of one text string within another. FIND is case-sensitive. | =FIND("n","Banana",4) | 5 |
| LEFT | LEFT(text, [num_chars]) Returns the specified number of characters from the start of a text string. | =LEFT("Stapler",4) | Stap |
| LEN | LEN(text) Returns the number of characters in a text string. | =LEN("Folders") | 7 |
| LOWER | LOWER(text) Converts all letters in a text string to lowercase. | =LOWER("NORTH") | north |
| MID | MID(text, start_num, num_chars) Returns the characters from the middle of a text string, given a starting position and length. | =MID("Stapler",2,3) | tap |
| NUMBERVALUE | NUMBERVALUE(text, [decimal_separator], [group_separator]) Converts text to a number in a way that does not depend on the locale. | =NUMBERVALUE("1.234,5",",",".") | 1234.5 |
| PROPER | PROPER(text) Converts a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase. | =PROPER("o'neill and SONS") | O'Neill And Sons |
| REPLACE | REPLACE(old_text, start_num, num_chars, new_text) Replaces part of a text string with a different text string. | =REPLACE("2026-Q1",6,2,"Q2") | 2026-Q2 |
| REPT | REPT(text, number_times) Repeats text a given number of times. | =REPT("-",5) | ----- |
| RIGHT | RIGHT(text, [num_chars]) Returns the specified number of characters from the end of a text string. | =RIGHT("INV-1042",4) | 1042 |
| SEARCH | SEARCH(find_text, within_text, [start_num]) Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive). | =SEARCH("INK","Printer ink") | 9 |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces existing text with new text in a text string. | =SUBSTITUTE("07700 900 123"," ","") | 07700900123 |
| T | T(value) Checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it is not. | =T(A2) | Pens |
| TEXT | TEXT(value, format_text) Converts a value to text in a specific number format. | =TEXT(D5,"dddd d mmmm yyyy") | Thursday 5 March 2026 |
| TEXTAFTER | TEXTAFTER(text, delimiter, [instance_num]) Returns the text that comes after a delimiter. | =TEXTAFTER("invoice-2026-0042","-") | 2026-0042 |
| TEXTBEFORE | TEXTBEFORE(text, delimiter, [instance_num]) Returns the text that comes before a delimiter. | =TEXTBEFORE("invoice-2026-0042","-",2) | invoice-2026 |
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, ...) Joins a list or range of text strings using a delimiter. | =TEXTJOIN(", ",TRUE,A2:A4) | Pens, Paper, Ink |
| TRIM | TRIM(text) Removes all spaces from a text string except for single spaces between words. | =TRIM(" two spaces ") | two spaces |
| UNICHAR | UNICHAR(number) Returns the Unicode character referenced by the given numeric value. | =UNICHAR(8364) | € |
| UNICODE | UNICODE(text) Returns the number (code point) corresponding to the first character of the text. | =UNICODE("€") | 8364 |
| UPPER | UPPER(text) Converts a text string to all uppercase letters. | =UPPER("south") | SOUTH |
| VALUE | VALUE(text) Converts a text string that represents a number to a number. | =VALUE("1,250.50") | 1250.5 |
Date & Time (18)
Dates are counted in days from 1900, as in Excel, so a date can be taken from a date.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| DATE | DATE(year, month, day) Returns the date for the given year, month and day.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =DATE(2026,2,30) | 2026-03-02 |
| DATEDIF | DATEDIF(start_date, end_date, unit) Returns the number of days, months or years between two dates. | =DATEDIF(D2,D6,"M") | 2 |
| DATEVALUE | DATEVALUE(date_text) Converts a date in the form of text to a number that represents the date. | =DATEVALUE("2026-03-09") | 46090 |
| DAY | DAY(serial_number) Returns the day of the month, a number from 1 to 31. | =DAY(D6) | 31 |
| DAYS | DAYS(end_date, start_date) Returns the number of days between the two dates. | =DAYS(D6,D2) | 75 |
| EDATE | EDATE(start_date, months) Returns the date that is the indicated number of months before or after the start date.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =EDATE("2026-01-31",1) | 2026-02-28 |
| EOMONTH | EOMONTH(start_date, months) Returns the last day of the month before or after a specified number of months.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =EOMONTH("2026-02-10",0) | 2026-02-28 |
| HOUR | HOUR(serial_number) Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).A time written as text on its own, such as "14:35", is not read as a time and gives 0, where Excel's HOUR gives 14. A fraction of a day works, and so does a date and time written together, "2026-03-05 14:35". | =HOUR(875/1440) | 14 |
| MINUTE | MINUTE(serial_number) Returns the minute, a number from 0 to 59.A time written as text on its own, such as "14:35", is not read as a time and gives 0, where Excel's HOUR gives 14. A fraction of a day works, and so does a date and time written together, "2026-03-05 14:35". | =MINUTE(875/1440) | 35 |
| MONTH | MONTH(serial_number) Returns the month, a number from 1 (January) to 12 (December). | =MONTH(D3) | 2 |
| NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole workdays between two dates. | =NETWORKDAYS("2026-09-28","2026-10-09") | 10 |
| NOW | NOW() Returns the current date and time.Gives the date and time as text, yyyy-mm-dd hh:mm, by this computer's clock, to the minute. | =YEAR(NOW())>=2026 | TRUE |
| SECOND | SECOND(serial_number) Returns the second, a number from 0 to 59.A time written as text on its own, such as "14:35", is not read as a time and gives 0, where Excel's HOUR gives 14. A fraction of a day works, and so does a date and time written together, "2026-03-05 14:35". | =SECOND(43209/86400) | 9 |
| TODAY | TODAY() Returns the current date.Gives today as text, yyyy-mm-dd, so =ISNUMBER(TODAY()) is FALSE here and TRUE in Excel. Date arithmetic on it works as usual. | =YEAR(TODAY())>=2026 | TRUE |
| WEEKDAY | WEEKDAY(serial_number, [return_type]) Returns a number from 1 to 7 identifying the day of the week of a date. | =WEEKDAY(D5,2) | 4 |
| WEEKNUM | WEEKNUM(serial_number, [return_type]) Returns the week number in the year.Takes Excel's return types: 1 or 17 for weeks from Sunday, 2 or 11 from Monday, 12 to 16 for Tuesday to Saturday, and 21 for the ISO week. | =WEEKNUM("2026-03-08",2) | 10 |
| WORKDAY | WORKDAY(start_date, days, [holidays]) Returns the date before or after a specified number of workdays.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =WORKDAY(D5,3) | 2026-03-10 |
| YEAR | YEAR(serial_number) Returns the year of a date, an integer in the range 1900 - 9999. | =YEAR(D2) | 2026 |
Financial (9)
Loans, savings and cash flows. Money paid out is negative and rates are per period, as in Excel: a 6% annual rate paid monthly is 0.06/12.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| FV | FV(rate, nper, pmt, [pv], [type]) Returns the future value of an investment based on periodic, constant payments and a constant interest rate. | =ROUND(FV(0.05/12,12,-100),2) | 1227.89 |
| IPMT | IPMT(rate, per, nper, pv, [fv], [type]) Returns the interest payment for a given period for an investment. | =ROUND(IPMT(0.06/12,1,36,10000),2) | -50 |
| IRR | IRR(values, [guess]) Returns the internal rate of return for a series of cash flows. | =ROUND(IRR(F2:F5),4) | 0.097 |
| NPER | NPER(rate, pmt, pv, [fv], [type]) Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | =ROUND(NPER(0.01,-100,1000),2) | 10.59 |
| NPV | NPV(rate, value1, [value2], ...) Returns the net present value of an investment based on a discount rate and a series of future payments and income. | =ROUND(NPV(0.1,F3:F5),2) | 994.74 |
| PMT | PMT(rate, nper, pv, [fv], [type]) Calculates the payment for a loan based on constant payments and a constant interest rate. | =ROUND(PMT(0.06/12,36,10000),2) | -304.22 |
| PPMT | PPMT(rate, per, nper, pv, [fv], [type]) Returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate. | =ROUND(PPMT(0.06/12,1,36,10000),2) | -254.22 |
| PV | PV(rate, nper, pmt, [fv], [type]) Returns the present value of an investment: the total amount that a series of future payments is worth now. | =ROUND(PV(0.05/12,60,-200),2) | 10598.14 |
| RATE | RATE(nper, pmt, pv, [fv], [type], [guess]) Returns the interest rate per period of a loan or an investment. | =ROUND(RATE(36,-304.22,10000)*12,4) | 0.06 |
Information (13)
Questions about a value: whether it is a number, text, blank or an error.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ISBLANK | ISBLANK(value) Checks whether a reference is to an empty cell, and returns TRUE or FALSE. | =ISBLANK(G2) | TRUE |
| ISERR | ISERR(value) Checks whether a value is an error other than #N/A, and returns TRUE or FALSE. | =ISERR(NA()) | FALSE |
| ISERROR | ISERROR(value) Checks whether a value is an error, and returns TRUE or FALSE. | =ISERROR(1/0) | TRUE |
| ISEVEN | ISEVEN(number) Returns TRUE if the number is even. | =ISEVEN(C2) | TRUE |
| ISLOGICAL | ISLOGICAL(value) Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE. | =ISLOGICAL(C2>100) | TRUE |
| ISNA | ISNA(value) Checks whether a value is #N/A, and returns TRUE or FALSE. | =ISNA(NA()) | TRUE |
| ISNONTEXT | ISNONTEXT(value) Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE. | =ISNONTEXT(A2) | FALSE |
| ISNUMBER | ISNUMBER(value) Checks whether a value is a number, and returns TRUE or FALSE. | =ISNUMBER(C2) | TRUE |
| ISODD | ISODD(number) Returns TRUE if the number is odd. | =ISODD(3) | TRUE |
| ISTEXT | ISTEXT(value) Checks whether a value is text, and returns TRUE or FALSE. | =ISTEXT(A2) | TRUE |
| N | N(value) Converts a value that is not a number to a number, dates to serial numbers, TRUE to 1, anything else to 0. | =N(TRUE) | 1 |
| NA | NA() Returns the error value #N/A (value not available). | =NA() | #N/A |
| TYPE | TYPE(value) Returns an integer representing the data type of a value: number = 1; text = 2; logical value = 4; array = 64. | =TYPE("a") | 2 |
Excel functions Sheets does not have yet
- TEXTSPLIT, VSTACK, HSTACK, CHOOSECOLS, TAKE, DROP. The array-shaping functions added to Excel 365. The results of FILTER, SORT and the rest spill, but these are not in the engine yet.
- RANDARRAY, ISOMITTED, named LAMBDAs. A LAMBDA works inside a formula, and with LET; one named in Name Manager, to call from any cell, does not yet.
- TIME, TIMEVALUE, ISOWEEKNUM. Write a time as a fraction of a day (9/24 for 09:00), and use WEEKNUM(date,21) for the ISO week.
- AGGREGATE, CONVERT, HYPERLINK, IMAGE. Less common, and not yet in the engine.
An .xlsx that uses one of these still opens, and each such cell shows the value Excel last saved in it. The formula itself is not kept: the cell holds that value from then on, and saving the workbook writes the value, not the formula. Keep a copy of the original if the formula matters.
Open a new workbook in Sheets. It is free, needs no account, and the workbook is kept in this browser.