Every function in Sheets, with a worked example
The 167 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 does not have the dynamic array functions (FILTER, UNIQUE, SORT, SEQUENCE), LET or LAMBDA, and it does not take array constants such as {1,2,3}. 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. - No arithmetic on whole ranges.
(B2:B6="North")*C2:C6uses only the first row, so the SUMPRODUCT idiom built on it gives the wrong total. SUMIFS and COUNTIFS do the same job and give the right one. - No array constants.
=SUM({1,2})is an error; put the values in cells.
Everything else in the tables below gave the answer Excel gives for the same formula when this page was built.
Math & Trig (45)
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 |
| 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 a whole range is not supported, so the Excel idiom =SUMPRODUCT((B2:B6="North")*C2:C6) uses only the first row and gives 120, not 270. SUMIFS gives the right answer: =SUMIFS(C2:C6,B2:B6,"North"). | =SUMPRODUCT(C2:C6,E2:E6) | 1640 |
| 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 (13)
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 |
| 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 |
| 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 |
Logical (11)
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 |
| 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 |
| 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 |
| 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
- FILTER, UNIQUE, SORT, SORTBY, SEQUENCE. Excel's dynamic array functions, which spill a list of results into the cells below. Sheets has no spilled results.
- XMATCH. Use MATCH, or XLOOKUP when you want the value rather than its position.
- LET, LAMBDA. Named values and your own functions inside a formula.
- TEXTSPLIT, VSTACK, HSTACK, CHOOSECOLS, TAKE, DROP. The array-shaping functions added to Excel 365.
- 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.