Excel Function Reference

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Searches the first column of a table and returns a value from a column to its right. Always pass FALSE as the fourth argument unless you deliberately want an approximate match.

=VLOOKUP(A2,$D$2:$F$500,3,FALSE)

Available: All versions

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The horizontal version of VLOOKUP: searches the first row and returns a value from a row below it.

=HLOOKUP(B$1,$A$2:$H$10,3,FALSE)

Available: All versions

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Searches any column and returns a value from any other column — left or right — with a built-in not-found value and exact match by default.

=XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500,"Not found")

Available: Microsoft 365, Excel 2021

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Returns the position of a value in a range, with exact, wildcard, next-smaller and next-larger match modes plus reverse search.

=XMATCH("EMP-104",$A$2:$A$500)

Available: Microsoft 365, Excel 2021

INDEX(array, row_num, [column_num])

Returns the value at a given row and column inside a range. Combined with MATCH it builds a lookup that does not depend on column order.

=INDEX($B$2:$B$500,MATCH(A2,$D$2:$D$500,0))

Available: All versions

MATCH(lookup_value, lookup_array, [match_type])

Returns the position of a value in a one-dimensional range. Use 0 for an exact match — the default 1 assumes sorted data.

=MATCH(A2,$D$2:$D$500,0)

Available: All versions

CHOOSE(index_num, value1, [value2], …)

Picks a value from a list by position. Useful for small lookup tables and for selecting between calculation options.

=CHOOSE(B2,"Low","Medium","High")

Available: All versions

OFFSET(reference, rows, cols, [height], [width])

Returns a range shifted from a starting cell. Powerful but volatile — it recalculates on every change, which slows large models.

=SUM(OFFSET(A2,0,0,10,1))

Available: All versions

INDIRECT(ref_text, [a1])

Turns a text string into a live reference, letting you build sheet names or cell addresses dynamically. Also volatile, and it breaks when the target is renamed.

=INDIRECT("'"&B2&"'!A1")

Available: All versions

ROW([reference])

Returns the row number of a reference, or the current row when no reference is given.

=ROW(A15)

Available: All versions

ROWS(array)

Counts the rows in a range — handy for sequence numbers and dynamic ranges.

=ROWS($A$2:$A$100)

Available: All versions

COLUMN([reference])

Returns the column number of a reference: A is 1, Z is 26, AA is 27.

=COLUMN(AB1)

Available: All versions

COLUMNS(array)

Counts the columns in a range, often used to drive INDEX or dynamic-array formulas.

=COLUMNS($A$1:$D$1)

Available: All versions

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Builds a cell address as text. With abs_num 4 it returns a relative reference such as AB1, which is useful for converting column numbers to letters.

=ADDRESS(1,28,4)

Available: All versions

HYPERLINK(link_location, [friendly_name])

Creates a clickable link to a URL, file or cell. `#` links jump to a location inside the same workbook.

=HYPERLINK("#Sheet2!A1","Go to summary")

Available: All versions

SUM(number1, [number2], …)

Adds numbers or ranges. It ignores text and blanks, so it is safe over a mixed column — but it will not catch text-numbers that look like numbers.

=SUM(B2:B100)

Available: All versions

SUMIF(range, criteria, [sum_range])

Adds the cells that meet one condition, such as a single product, region or month.

=SUMIF($B$2:$B$500,"North",$D$2:$D$500)

Available: All versions

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Adds values that meet several conditions at once. Note the argument order: the sum range comes first, unlike SUMIF.

=SUMIFS($D$2:$D$500,$B$2:$B$500,"North",$C$2:$C$500,">1000")

Available: Excel 2007+

SUMPRODUCT(array1, [array2], …)

Multiplies matching items across arrays and adds the results. It also handles conditional maths without Ctrl+Shift+Enter.

=SUMPRODUCT((B2:B100="North")*(D2:D100))

Available: All versions

COUNT(value1, [value2], …)

Counts cells that contain numbers. Text, blanks and logical values in ranges are ignored.

=COUNT(A2:A100)

Available: All versions

COUNTA(value1, [value2], …)

Counts non-empty cells, including text and errors — useful for checking how many rows were filled.

=COUNTA(A2:A100)

Available: All versions

COUNTBLANK(range)

Counts empty cells in a range. Cells containing an empty string returned by a formula also count.

=COUNTBLANK(A2:A100)

Available: All versions

COUNTIF(range, criteria)

Counts cells that meet one condition. Use operators in quotes (">100") and wildcards (* and ?) for partial text.

=COUNTIF($B$2:$B$500,"North")

Available: All versions

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Counts rows that satisfy several conditions at once, such as region plus month plus status.

=COUNTIFS($B$2:$B$500,"North",$C$2:$C$500,">1000")

Available: Excel 2007+

AVERAGE(number1, [number2], …)

Returns the arithmetic mean of numeric cells. Blank cells are ignored, which can hide gaps in data.

=AVERAGE(B2:B100)

Available: All versions

AVERAGEIF(range, criteria, [average_range])

Averages the cells that meet one condition.

=AVERAGEIF($B$2:$B$500,"North",$D$2:$D$500)

Available: Excel 2007+

AVERAGEIFS(average_range, criteria_range1, criteria1, …)

Averages the cells that meet several conditions at once.

=AVERAGEIFS($D$2:$D$500,$B$2:$B$500,"North",$C$2:$C$500,">0")

Available: Excel 2007+

MAX(number1, [number2], …)

Returns the largest number in a set of values or ranges.

=MAX(D2:D100)

Available: All versions

MIN(number1, [number2], …)

Returns the smallest number in a set of values or ranges.

=MIN(D2:D100)

Available: All versions

MAXIFS(max_range, criteria_range1, criteria1, …)

Returns the largest value among rows that meet one or more conditions.

=MAXIFS($D$2:$D$500,$B$2:$B$500,"North")

Available: Microsoft 365, Excel 2019

MINIFS(min_range, criteria_range1, criteria1, …)

Returns the smallest value among rows that meet one or more conditions.

=MINIFS($D$2:$D$500,$B$2:$B$500,"North")

Available: Microsoft 365, Excel 2019

MEDIAN(number1, [number2], …)

Returns the middle value of a dataset. Less distorted by outliers than AVERAGE, which makes it useful for salaries and house prices.

=MEDIAN(B2:B100)

Available: All versions

LARGE(array, k)

Returns the k-th largest value — LARGE(A1:A10,1) is the maximum, 2 the runner-up.

=LARGE(B2:B100,3)

Available: All versions

SMALL(array, k)

Returns the k-th smallest value in a dataset.

=SMALL(B2:B100,3)

Available: All versions

ROUND(number, num_digits)

Rounds a number to a set number of decimals using normal rounding. Use 0 for whole numbers, -1 for tens and -2 for hundreds.

=ROUND(1234.567,2)

Available: All versions

ROUNDUP(number, num_digits)

Always rounds away from zero, regardless of the next digit.

=ROUNDUP(12.01,0)

Available: All versions

ROUNDDOWN(number, num_digits)

Always rounds toward zero — effectively truncating at the chosen decimal place.

=ROUNDDOWN(12.99,0)

Available: All versions

MROUND(number, multiple)

Rounds a number to the nearest multiple, such as the nearest 5 or 0.25.

=MROUND(47,5)

Available: Excel 2007+

CEILING(number, significance)

Rounds a number up to the nearest multiple of a significance value.

=CEILING(47,5)

Available: All versions

FLOOR(number, significance)

Rounds a number down to the nearest multiple of a significance value.

=FLOOR(47,5)

Available: All versions

ABS(number)

Returns the absolute value — the size of a number without its sign.

=ABS(-42)

Available: All versions

MOD(number, divisor)

Returns the remainder after division. Commonly used to flag every nth row and to fix overnight time differences.

=MOD(17,5)

Available: All versions

INT(number)

Rounds a number down to the nearest integer, including for negative numbers.

=INT(-2.5)

Available: All versions

TRUNC(number, [num_digits])

Cuts off decimals without rounding. Unlike INT, it truncates negative numbers toward zero.

=TRUNC(-2.5)

Available: All versions

POWER(number, power)

Raises a number to a power. The ^ operator does the same job with fewer keystrokes.

=POWER(1.08,10)

Available: All versions

SQRT(number)

Returns the positive square root of a non-negative number.

=SQRT(144)

Available: All versions

PRODUCT(number1, [number2], …)

Multiplies all the numbers in the arguments and returns the product.

=PRODUCT(B2:B6)

Available: All versions

SUBTOTAL(function_num, ref1, [ref2], …)

Runs SUM, AVERAGE, COUNT and similar functions while optionally ignoring filtered-out rows. Function number 9 is SUM, 109 excludes hidden rows too.

=SUBTOTAL(109,B2:B100)

Available: All versions

AGGREGATE(function_num, options, ref1, [ref2], …)

A more flexible SUBTOTAL that can ignore errors and hidden rows, with 19 functions including LARGE, SMALL and PERCENTILE.

=AGGREGATE(9,6,B2:B100)

Available: Excel 2010+

STDEV.S(number1, [number2], …)

Estimates the standard deviation of a sample. Use STDEV.P when you have the whole population.

=STDEV.S(B2:B100)

Available: Excel 2010+

STDEV.P(number1, [number2], …)

Calculates the standard deviation of an entire population.

=STDEV.P(B2:B100)

Available: Excel 2010+

VAR.S(number1, [number2], …)

Estimates the variance of a sample — the square of STDEV.S.

=VAR.S(B2:B100)

Available: Excel 2010+

VAR.P(number1, [number2], …)

Calculates the variance of an entire population.

=VAR.P(B2:B100)

Available: Excel 2010+

RANK.EQ(number, ref, [order])

Returns the rank of a number in a list; omit order (or use 0) for descending ranks.

=RANK.EQ(B2,$B$2:$B$100,0)

Available: Excel 2010+

CORREL(array1, array2)

Returns the correlation coefficient between two datasets, from −1 to 1.

=CORREL(B2:B100,C2:C100)

Available: All versions

SLOPE(known_y's, known_x's)

Returns the slope of the straight line fitted to paired data.

=SLOPE(B2:B100,A2:A100)

Available: All versions

INTERCEPT(known_y's, known_x's)

Returns the y-intercept of the linear regression line through the supplied values.

=INTERCEPT(B2:B100,A2:A100)

Available: All versions

FORECAST.LINEAR(x, known_y's, known_x's)

Predicts a future value by fitting a straight line to existing data.

=FORECAST.LINEAR(A101,B2:B100,A2:A100)

Available: Microsoft 365, Excel 2016

TREND(known_y's, [known_x's], [new_x's], [const])

Returns predicted values along a linear trend — spill-friendly in Microsoft 365.

=TREND(B2:B100,A2:A100,A101:A110)

Available: All versions

FREQUENCY(data_array, bins_array)

Counts how many values fall into each bin. In older Excel, enter it with Ctrl+Shift+Enter.

=FREQUENCY(B2:B100,E2:E6)

Available: All versions

PERCENTILE.INC(array, k)

Returns the k-th percentile of a range, where k is between 0 and 1.

=PERCENTILE.INC(B2:B100,0.9)

Available: Excel 2010+

QUARTILE.INC(array, quart)

Returns a quartile: 0 minimum, 1 Q1, 2 median, 3 Q3 and 4 maximum.

=QUARTILE.INC(B2:B100,3)

Available: Excel 2010+

CONCAT(text1, [text2], …)

Joins text from cells and ranges. Unlike TEXTJOIN it has no delimiter argument.

=CONCAT(A2," ",B2)

Available: Microsoft 365, Excel 2019

CONCATENATE(text1, [text2], …)

The legacy text-joining function. It still works everywhere, but the & operator and CONCAT are easier to read.

=CONCATENATE(A2," ",B2)

Available: All versions

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Joins a range with a delimiter and can skip blanks — the cleanest way to combine a list of names or tags.

=TEXTJOIN(", ",TRUE,A2:A10)

Available: Microsoft 365, Excel 2019

LEFT(text, [num_chars])

Returns the first characters of a text value.

=LEFT("EMP-104",3)

Available: All versions

RIGHT(text, [num_chars])

Returns the last characters of a text value.

=RIGHT("EMP-104",3)

Available: All versions

MID(text, start_num, num_chars)

Extracts characters from the middle of a text value, starting at a given position.

=MID("EMP-104",5,3)

Available: All versions

LEN(text)

Counts the characters in a text value, including spaces.

=LEN("Excel")

Available: All versions

FIND(find_text, within_text, [start_num])

Returns the position of text inside another string and is case-sensitive.

=FIND("-","EMP-104")

Available: All versions

SEARCH(find_text, within_text, [start_num])

Like FIND, but case-insensitive and it accepts wildcards.

=SEARCH("emp","EMP-104")

Available: All versions

SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces specific text inside a string. Use it to remove spaces, line breaks or unwanted punctuation.

=SUBSTITUTE(A2," ","")

Available: All versions

REPLACE(old_text, start_num, num_chars, new_text)

Replaces characters by position rather than by matching content.

=REPLACE("FY2025",3,4,"2026")

Available: All versions

TRIM(text)

Removes leading, trailing and repeated spaces. It does not remove non-breaking spaces copied from web pages.

=TRIM("  Excel   help  ")

Available: All versions

CLEAN(text)

Removes non-printing characters such as line breaks and control codes.

=CLEAN(A2)

Available: All versions

UPPER(text)

Converts text to capitals.

=UPPER("excel")

Available: All versions

LOWER(text)

Converts text to lower case.

=LOWER("EXCEL")

Available: All versions

PROPER(text)

Capitalises the first letter of each word and lowercases the rest.

=PROPER("excel assignment help")

Available: All versions

TEXT(value, format_text)

Formats a number or date as text using a format code — essential when joining a date or currency into a sentence.

=TEXT(TODAY(),"dd mmm yyyy")

Available: All versions

VALUE(text)

Converts a text string that looks like a number into a real number.

=VALUE("1,250.50")

Available: All versions

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Returns everything before a delimiter — cleaner than FIND plus LEFT.

=TEXTBEFORE(A2,"@")

Available: Microsoft 365, Excel 2024

TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Returns everything after a delimiter, with an optional not-found value.

=TEXTAFTER(A2,"@")

Available: Microsoft 365, Excel 2024

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], …)

Splits text into columns or rows, spilling the parts into neighbouring cells.

=TEXTSPLIT(A2,"-")

Available: Microsoft 365, Excel 2024

EXACT(text1, text2)

Compares two strings case-sensitively and returns TRUE or FALSE.

=EXACT("Excel","excel")

Available: All versions

REPT(text, number_times)

Repeats text a set number of times — useful for in-cell bars and padding.

=REPT("|",5)

Available: All versions

TODAY()

Returns today's date as a serial number. It recalculates whenever the workbook opens.

=TODAY()

Available: All versions

NOW()

Returns the current date and time. Use TODAY when you only need the date.

=NOW()

Available: All versions

DATE(year, month, day)

Builds a date from separate year, month and day values. It rolls over invalid dates, so DATE(2026,13,1) returns January 2027.

=DATE(2026,10,29)

Available: All versions

DAY(serial_number)

Returns the day of the month from a date.

=DAY(DATE(2026,10,29))

Available: All versions

MONTH(serial_number)

Returns the month number from a date, from 1 to 12.

=MONTH(DATE(2026,10,29))

Available: All versions

YEAR(serial_number)

Returns the four-digit year from a date.

=YEAR(DATE(2026,10,29))

Available: All versions

DATEVALUE(date_text)

Converts a date stored as text into a real Excel date serial number.

=DATEVALUE("2026-10-29")

Available: All versions

EDATE(start_date, months)

Returns the same day a number of months before or after a date — useful for contract renewals.

=EDATE(A2,12)

Available: All versions

EOMONTH(start_date, months)

Returns the last day of a month, offset by a number of months.

=EOMONTH(A2,0)

Available: All versions

DATEDIF(start_date, end_date, unit)

Returns the difference between two dates in years (Y), months (M) or days (D). It still works, though Excel no longer lists it in the function gallery.

=DATEDIF(A2,TODAY(),"Y")

Available: All versions (undocumented)

DAYS(end_date, start_date)

Returns the number of days between two dates.

=DAYS(B2,A2)

Available: Excel 2013+

NETWORKDAYS(start_date, end_date, [holidays])

Counts working days between two dates, excluding Saturdays, Sundays and any holiday dates you supply.

=NETWORKDAYS(A2,B2,$F$2:$F$20)

Available: All versions

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Counts working days with a custom weekend pattern — for example Friday–Saturday in the Gulf or a six-day week.

=NETWORKDAYS.INTL(A2,B2,7,$F$2:$F$20)

Available: Excel 2010+

WORKDAY(start_date, days, [holidays])

Returns the date a number of working days away, skipping weekends and holidays.

=WORKDAY(A2,10,$F$2:$F$20)

Available: All versions

WORKDAY.INTL(start_date, days, [weekend], [holidays])

The WORKDAY equivalent for custom weekend patterns.

=WORKDAY.INTL(A2,10,7,$F$2:$F$20)

Available: Excel 2010+

WEEKNUM(serial_number, [return_type])

Returns the week number of a date. Return type 2 starts weeks on Monday; ISO weeks need ISOWEEKNUM.

=WEEKNUM(A2,2)

Available: All versions

WEEKDAY(serial_number, [return_type])

Returns the day of the week as a number. Return type 2 gives Monday = 1 through Sunday = 7.

=WEEKDAY(A2,2)

Available: All versions

YEARFRAC(start_date, end_date, [basis])

Returns the fraction of a year between two dates, using a day-count basis for finance conventions.

=YEARFRAC(A2,B2,3)

Available: All versions

IF(logical_test, value_if_true, [value_if_false])

Returns one value when a condition is true and another when it is false. Deeply nested IFs are better replaced by IFS or a lookup table.

=IF(B2>=60,"Pass","Fail")

Available: All versions

IFS(logical_test1, value1, [logical_test2, value2], …)

Tests several conditions in order and returns the value for the first true one — no nesting required.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")

Available: Microsoft 365, Excel 2019

IFERROR(value, value_if_error)

Replaces any error with a fallback value. Wrap the smallest possible expression — wrapping a whole column hides real problems.

=IFERROR(VLOOKUP(A2,$D$2:$F$500,3,FALSE),"Not found")

Available: Excel 2007+

IFNA(value, value_if_na)

Replaces only #N/A errors, leaving other errors visible so you notice genuine faults.

=IFNA(XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500),"No match")

Available: Excel 2013+

AND(logical1, [logical2], …)

Returns TRUE only when every condition is true.

=AND(B2>=60,C2="Submitted")

Available: All versions

OR(logical1, [logical2], …)

Returns TRUE when at least one condition is true.

=OR(B2="Urgent",B2="Overdue")

Available: All versions

NOT(logical)

Reverses a TRUE/FALSE value.

=NOT(ISBLANK(A2))

Available: All versions

XOR(logical1, [logical2], …)

Returns TRUE when an odd number of the conditions are true — an exclusive OR.

=XOR(B2,C2)

Available: Excel 2013+

SWITCH(expression, value1, result1, [value2, result2], …, [default])

Compares one expression against a list of values and returns the matching result — cleaner than nested IFs for categories.

=SWITCH(B2,1,"Low",2,"Medium",3,"High","Unknown")

Available: Microsoft 365, Excel 2019

LET(name1, value1, [name2, value2], …, calculation)

Names intermediate results inside a formula, so a long calculation can reuse them instead of repeating the same expression.

=LET(rate,0.08,total,SUM(B2:B100),total*(1+rate))

Available: Microsoft 365, Excel 2021

LAMBDA([parameter1, parameter2, …], calculation)

Creates a reusable custom function from a formula. Name it in the Name Manager to call it like a built-in function.

=LAMBDA(x,y,x*y)(3,4)

Available: Microsoft 365

FILTER(array, include, [if_empty])

Returns only the rows that meet a condition, spilling the result into the cells below. The modern replacement for filter-plus-copy.

=FILTER(A2:D500,C2:C500="North","No rows")

Available: Microsoft 365, Excel 2021

SORT(array, [sort_index], [sort_order], [by_col])

Returns a sorted copy of a range. Sort order 1 is ascending, −1 is descending.

=SORT(A2:D500,4,-1)

Available: Microsoft 365, Excel 2021

SORTBY(array, by_array1, [sort_order1], …)

Sorts a range by one or more separate columns without including them in the output.

=SORTBY(A2:B500,B2:B500,-1)

Available: Microsoft 365, Excel 2021

UNIQUE(array, [by_col], [exactly_once])

Returns the distinct values from a range. Set exactly_once to TRUE to keep only values that appear once.

=UNIQUE(B2:B500)

Available: Microsoft 365, Excel 2021

SEQUENCE(rows, [columns], [start], [step])

Generates a sequence of numbers that spills into a block of cells.

=SEQUENCE(12,1,1,1)

Available: Microsoft 365, Excel 2021

TAKE(array, rows, [columns])

Returns the first or last rows (and columns) of an array. Negative numbers take from the end.

=TAKE(A2:D500,5)

Available: Microsoft 365

DROP(array, rows, [columns])

Removes rows or columns from the start or end of an array.

=DROP(A2:D500,1)

Available: Microsoft 365

VSTACK(array1, [array2], …)

Stacks ranges vertically into one spilled array — useful for combining monthly sheets.

=VSTACK(Jan!A2:D100,Feb!A2:D100)

Available: Microsoft 365

HSTACK(array1, [array2], …)

Stacks ranges side by side horizontally.

=HSTACK(A2:B100,D2:E100)

Available: Microsoft 365

TOCOL(array, [ignore], [scan_by_column])

Flattens a two-dimensional range into one column, optionally skipping blanks or errors.

=TOCOL(A2:D100,1)

Available: Microsoft 365

CHOOSECOLS(array, col_num1, [col_num2], …)

Returns selected columns from an array in the order you specify.

=CHOOSECOLS(A2:D500,1,3,4)

Available: Microsoft 365

CHOOSEROWS(array, row_num1, [row_num2], …)

Returns selected rows from an array, in the order you specify.

=CHOOSEROWS(A2:D500,1,5,10)

Available: Microsoft 365

PMT(rate, nper, pv, [fv], [type])

Calculates the constant payment for a loan or annuity. Divide an annual rate by 12 and multiply years by 12 for monthly payments.

=PMT(9%/12,5*12,-20000)

Available: All versions

IPMT(rate, per, nper, pv, [fv], [type])

Returns the interest portion of a single loan payment, where per is the payment number.

=IPMT(9%/12,1,5*12,-20000)

Available: All versions

PPMT(rate, per, nper, pv, [fv], [type])

Returns the principal portion of a single payment — PMT minus IPMT for the same period.

=PPMT(9%/12,1,5*12,-20000)

Available: All versions

FV(rate, nper, pmt, [pv], [type])

Returns the future value of an investment or loan after a series of equal payments.

=FV(6%/12,10*12,-200,-1000)

Available: All versions

PV(rate, nper, pmt, [fv], [type])

Returns the present value of a series of future payments — the core of discounted cash flow.

=PV(8%,10,-5000)

Available: All versions

RATE(nper, pmt, pv, [fv], [type], [guess])

Solves for the interest rate per period when the other loan terms are known.

=RATE(60,-415.17,20000)*12

Available: All versions

NPER(rate, pmt, pv, [fv], [type])

Returns the number of periods needed to pay off a loan or reach an investment goal.

=NPER(9%/12,-415.17,20000)

Available: All versions

NPV(rate, value1, [value2], …)

Discounts a series of future cash flows to today's value. Excel discounts the first value by one period, so add an initial outflow separately.

=NPV(10%,B2:B6)+A2

Available: All versions

IRR(values, [guess])

Returns the internal rate of return for a series of cash flows at regular intervals.

=IRR(A2:A10)

Available: All versions

XIRR(values, dates, [guess])

Returns the internal rate of return for cash flows on uneven dates — the realistic version of IRR.

=XIRR(B2:B10,A2:A10)

Available: All versions

SLN(cost, salvage, life)

Returns the straight-line depreciation for one period.

=SLN(30000,3000,5)

Available: All versions

DDB(cost, salvage, life, period, [factor])

Returns double-declining-balance depreciation for a period, with an optional acceleration factor.

=DDB(30000,3000,5,1)

Available: All versions

ISBLANK(value)

Returns TRUE only for a genuinely empty cell. A cell containing an empty string from a formula is not blank.

=ISBLANK(A2)

Available: All versions

ISERROR(value)

Returns TRUE for any Excel error value, including #N/A, #VALUE! and #DIV/0!.

=ISERROR(A2/B2)

Available: All versions

ISNUMBER(value)

Returns TRUE when a value is a number — a quick way to catch numbers stored as text.

=ISNUMBER(A2)

Available: All versions

ISTEXT(value)

Returns TRUE when a value is text.

=ISTEXT(A2)

Available: All versions

ISLOGICAL(value)

Returns TRUE when a value is TRUE or FALSE.

=ISLOGICAL(A2)

Available: All versions

ISREF(value)

Returns TRUE when a value is a valid reference.

=ISREF(A2)

Available: All versions

ISFORMULA(reference)

Returns TRUE when a cell contains a formula rather than a typed value.

=ISFORMULA(B2)

Available: Excel 2013+

NA()

Returns the #N/A error deliberately, often to mark missing data in a chart series.

=IF(B2="","",NA())

Available: All versions

CELL(info_type, [reference])

Returns information about a cell, such as its address, format, colour or contents. Use "filename" to read the workbook path.

=CELL("address",B2)

Available: All versions

TYPE(value)

Returns a number describing the data type: 1 number, 2 text, 4 logical, 16 error, 64 array.

=TYPE("Excel")

Available: All versions

ERROR.TYPE(error_val)

Returns a code for the specific error, so a formula can react differently to #N/A and #DIV/0!.

=ERROR.TYPE(NA())

Available: All versions

SHEET([value])

Returns the sheet number of a reference, or the current sheet when no argument is given.

=SHEET()

Available: Excel 2013+