Excel Functions Cheat Sheet
Every one of the 202 functions in our reference, with its syntax and what it actually does, in one alphabetical table.
Built in your browser when you click. No signup, free for commercial use.
| Function | Syntax | What it does |
|---|---|---|
| ABS | =ABS(number) | Strips the sign and returns the magnitude, so both 42 and -42 return 42. |
| ADDRESS | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) | Builds a cell reference as TEXT, so =ADDRESS(3, 2) returns the string "$B$3". |
| AGGREGATE | =AGGREGATE(function_num, options, ref1, ...) | Sums the range while ignoring any error values in it, which a plain SUM cannot do. |
| AREAS | =AREAS(reference) | Counts how many separate ranges make up a reference, so =AREAS((A1:B4, D1:D8)) returns 2. |
| ARRAYTOTEXT and VALUETOTEXT | =ARRAYTOTEXT(array, [format]) and =VALUETOTEXT(value, [format]) | Renders a whole range as a single readable string, which is how you inspect what an array formula actually produced. |
| AVEDEV and DEVSQ | =AVEDEV(number1, [number2], ...) and =DEVSQ(number1, [number2], ...) | A spread measure that, unlike standard deviation, is not inflated by outliers. |
| AVERAGE | =AVERAGE(number1, [number2], ...) | Returns the arithmetic mean of the numbers, ignoring blanks and text rather than counting them as zero. |
| AVERAGEA | =AVERAGEA(value1, [value2], ...) | Averages like AVERAGE but counts text as 0 and TRUE as 1, so non-numeric entries drag the mean down instead of being skipped. |
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) | Averages the rows matching one condition, so =AVERAGEIF(A2:A500, "West", D2:D500) gives the mean for the West only. |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criteria1, ...) | Averages only the rows that satisfy every condition you list. |
| BASE and DECIMAL | =BASE(number, radix, [min_length]) and =DECIMAL(text, radix) | =BASE(number, radix) to convert a decimal number into another base, and =DECIMAL(text, radix) to convert it back. |
| BINOM.DIST | =BINOM.DIST(number_s, trials, probability_s, cumulative) | =BINOM.DIST(successes, trials, p, TRUE) for "at most this many successes" and FALSE for exactly that many. |
| BYROW and BYCOL | =BYROW(array, lambda) and =BYCOL(array, lambda) | Applies a calculation to each row and returns one result per row, with no helper column. |
| CEILING | =CEILING(number, significance) | Rounds up to the next multiple of your step, so =CEILING(213, 50) returns 250. |
| CELL | =CELL(info_type, [reference]) | Reports metadata about a cell, so =CELL("filename", A1) returns the workbook path and sheet name. |
| CHAR and CODE | =CHAR(number) and =CODE(text) | CHAR(10) is a line break, CHAR(160) a non-breaking space. |
| CHISQ.TEST | =CHISQ.TEST(actual_range, expected_range) | Tests whether observed COUNTS differ from expected ones, which is the categorical counterpart to the t-test. |
| CHOOSE | =CHOOSE(index_num, value1, [value2], ...) | Returns the nth item from a list, so =CHOOSE(3, "Low", "Mid", "High") returns "High". |
| CHOOSECOLS and CHOOSEROWS | =CHOOSECOLS(array, col_num1, [col_num2], ...) | Returns just those columns, in the order you list them, from anywhere in the range. |
| CLEAN | =CLEAN(text) | Removes non-printable control characters, the invisible debris that survives a copy from a PDF or a mainframe export. |
| COLUMN and COLUMNS | =COLUMN([reference]) and =COLUMNS(array) | =COLUMN() for the current column number and =COLUMNS(range) for how many columns a range spans — A is 1, not 0. |
| COMBIN, PERMUT and FACT | =COMBIN(number, number_chosen) and =PERMUT(number, number_chosen) | =COMBIN(49, 6) gives the 13,983,816 lottery combinations. |
| CONCATENATE | =CONCATENATE(text1, [text2], ...) | Joins several text items into one string; in modern Excel the same job is done by CONCAT or simply the & operator. |
| CONFIDENCE | =CONFIDENCE.NORM(alpha, standard_dev, size) | Returns the margin of error for a 95 % confidence interval around a mean. |
| CONVERT | =CONVERT(number, from_unit, to_unit) | Converts between measurement units, so =CONVERT(5, "mi", "km") returns 8.047. |
| CORREL | =CORREL(array1, array2) | Returns a correlation coefficient from -1 to 1, where 0 means no linear relationship. |
| COUNT | =COUNT(value1, [value2], ...) | Counts numeric cells only, so text and blanks are excluded and a column of text-stored numbers returns 0. |
| COUNTA | =COUNTA(value1, [value2], ...) | Counts every cell that is not empty, whatever it contains, which makes it the function for sizing a list. |
| COUNTBLANK | =COUNTBLANK(range) | Counts empty cells, and also counts cells whose formula returns an empty string. |
| COUNTIF | =COUNTIF(range, criteria) | Counts how many cells in range meet a single condition you specify. |
| COUNTIFS | =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Counts the rows that satisfy every condition you list, combining the criteria with AND logic. |
| COVARIANCE | =COVARIANCE.S(array1, array2) | Measures whether two variables move together, in units that make the number hard to interpret alone. |
| CUMIPMT and CUMPRINC | =CUMIPMT(rate, nper, pv, start_period, end_period, type) | =CUMIPMT(rate, nper, pv, start, end, type) for total interest across a span of periods, and CUMPRINC for the principal repaid over the same span. |
| DATE | =DATE(year, month, day) | Builds a real date from three numbers, and deliberately rolls over out-of-range values. |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | Returns the difference between two dates in the unit you ask for: "d" for days, "m" for whole months or "y" for whole years. |
| DATEVALUE and TIMEVALUE | =DATEVALUE(date_text) and =TIMEVALUE(time_text) | Converts a date stored as text into a real date serial number, which is what makes sorting and filtering work again. |
| DAYS | =DAYS(end_date, start_date) | Returns the calendar days between two dates, with the LATER date first, which is the reverse of most date functions. |
| DB and DDB | =DDB(cost, salvage, life, period, [factor]) | Front-loads depreciation, charging most of an asset's value in the early periods. |
| DELTA and GESTEP | =DELTA(number1, [number2]) and =GESTEP(number, [step]) | =DELTA(a, b) to get 1 when two values are equal and 0 otherwise, and =GESTEP(x, threshold) for 1 when x is at or above the threshold. |
| DSUM, DGET and the database functions | =DSUM(database, field, criteria) | The conditions live in a separate criteria RANGE on the sheet, not inside the formula. |
| DURATION and MDURATION | =DURATION(settlement, maturity, coupon, yld, frequency, [basis]) | Modified duration estimates the percentage price change of a bond for a 1 % move in yield. |
| EDATE | =EDATE(start_date, months) | Adds or subtracts whole months while keeping the same day number, clamping where the target month is shorter. |
| EFFECT and NOMINAL | =EFFECT(nominal_rate, npery) | Converts a stated rate into the true annual rate once compounding is counted, so 12 % monthly becomes 12.68 %. |
| EOMONTH | =EOMONTH(start_date, months) | Returns the last day of a month n months away, handling leap years and 30/31-day months for you. |
| EVEN and ODD | =EVEN(number) and =ODD(number) | Each rounds away from zero to the next integer of that parity, so =EVEN(3.1) returns 4 and =ODD(3.1) returns 5. |
| EXACT | =EXACT(text1, text2) | Compares two values case-SENSITIVELY, where the = operator does not. |
| EXP | =EXP(number) | Raises the constant e (about 2.71828) to your exponent, so =EXP(1) returns 2.718281828. |
| EXPAND | =EXPAND(array, rows, [columns], [pad_with]) | Pads an array out to a fixed size, which is how you stop a variable-length result breaking a fixed layout. |
| EXPON.DIST, WEIBULL.DIST and HYPGEOM.DIST | =EXPON.DIST(x, lambda, cumulative) | =EXPON.DIST(x, lambda, TRUE) for waiting times between events, WEIBULL.DIST for failure rates that change with age, and HYPGEOM.DIST for sampling without replacement. |
| F.TEST | =F.TEST(array1, array2) | Tests whether two samples have different VARIANCES, not different means. |
| FILTER | =FILTER(array, include, [if_empty]) | A dynamic-array function that returns every row of array where the include condition is TRUE, spilling the matches automatically. |
| FILTERXML and ENCODEURL | =FILTERXML(xml, xpath) and =ENCODEURL(text) | =FILTERXML(xml, xpath) to pull values out of an XML string with XPath, and =ENCODEURL(text) to make text safe inside a URL. |
| FIND | =FIND(find_text, within_text, [start_num]) | Returns the character position of a match, is case-SENSITIVE, and returns #VALUE! when nothing is found. |
| FIXED and DOLLAR | =FIXED(number, [decimals], [no_commas]) and =DOLLAR(number, [decimals]) | Both return formatted TEXT, so the result can no longer be summed. |
| FLOOR | =FLOOR(number, significance) | Rounds down to the previous multiple of your step, so =FLOOR(213, 50) returns 200. |
| FORECAST | =FORECAST.LINEAR(x, known_ys, known_xs) | Predicts a single value from a straight-line fit through your historical points. |
| FORMULATEXT | =FORMULATEXT(reference) | Returns the formula in another cell as visible text, which is how you document a model inside itself. |
| FREQUENCY | =FREQUENCY(data_array, bins_array) | Counts how many values fall into each bin and returns one more result than there are bins. |
| FV | =FV(rate, nper, pmt, [pv], [type]) | Projects what regular contributions plus a starting balance will grow to. |
| GCD and LCM | =GCD(number1, [number2], ...) and =LCM(number1, [number2], ...) | =GCD(24, 36) is 12 and =LCM(4, 6) is 12. |
| GEOMEAN and HARMEAN | =GEOMEAN(number1, [number2], ...) | =GEOMEAN(range) for the average of growth factors and =HARMEAN(range) for the average of rates — the arithmetic mean is wrong for both. |
| GETPIVOTDATA | =GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...) | Reads a specific value from a PivotTable by name rather than by cell position. |
| GROUPBY | =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]) | Produces a live grouped summary, a PivotTable that never needs refreshing. |
| GROWTH | =GROWTH(known_ys, [known_xs], [new_xs], [const]) | Is TREND's exponential twin, fitting a curve where each period multiplies rather than adds. |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Searches across the top row of a table for a value and returns a value from a row below it, with FALSE forcing an exact match. |
| HOUR, MINUTE and SECOND | =HOUR(serial_number), =MINUTE(serial_number) and =SECOND(serial_number) | Each extracts one component, and HOUR always returns 0 to 23 even for durations over a day. |
| HSTACK and VSTACK | =VSTACK(array1, [array2], ...) and =HSTACK(array1, [array2], ...) | Consolidates twelve monthly sheets into one live table with a single formula. |
| HYPERLINK | =HYPERLINK(link_location, [friendly_name]) | Builds a clickable link from a formula, so the destination can be calculated rather than typed. |
| IF | =IF(logical_test, value_if_true, value_if_false) | Tests a condition and returns one value when the test is TRUE and another when it is FALSE. |
| IFERROR | =IFERROR(value, value_if_error) | Runs a formula and returns your fallback instead of any error message like #N/A, #DIV/0! or #VALUE!. |
| IFNA | =IFNA(value, value_if_na) | Catches #N/A only, letting genuine formula errors surface instead of hiding them. |
| IFS | =IFS(logical_test1, value1, [logical_test2, value2], ...) | Tests conditions in order and returns the first result that matches, with no nesting. |
| IMAGE | =IMAGE(source, [alt_text], [sizing], [height], [width]) | Puts a picture INSIDE a cell, so it sorts, filters and moves with the row. |
| INDEX | =INDEX(array, row_num, [column_num]) | Returns the value sitting at that position inside the range, counting from the range's own first cell. |
| INDEX/MATCH | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | MATCH finds the row number of the lookup value and INDEX returns the value in that row from any column, left or right. |
| INFO | =INFO(type_text) | Environment facts a formula cannot otherwise reach. |
| INT | =INT(number) | Returns the next integer at or below the value, so 7.9 becomes 7 and -7.1 becomes -8. |
| INTRATE and RECEIVED | =INTRATE(settlement, maturity, investment, redemption, [basis]) | Returns the annualised rate on a discount security that pays no coupons. |
| IPMT | =IPMT(rate, per, nper, pv, [fv], [type]) | Returns the interest portion of one payment, which is largest in period one and falls from there. |
| IRR | =IRR(values, [guess]) | Returns the discount rate at which the project's NPV is exactly zero, with the initial outlay included in the range. |
| ISBLANK | =ISBLANK(value) | Returns TRUE only for a truly empty cell, and FALSE for a formula that returns an empty string. |
| ISERROR | =ISERROR(value) | Returns TRUE for any error at all, which makes it powerful and easy to misuse. |
| ISEVEN and ISODD | =ISEVEN(number) and =ISODD(number) | They return TRUE or FALSE, unlike EVEN and ODD which round. |
| ISFORMULA | =ISFORMULA(reference) | Returns TRUE when a cell holds a formula, which is how you find hard-coded numbers inside a model. |
| ISNA | =ISNA(value) | Returns TRUE only for #N/A, ignoring every other error type. |
| ISNUMBER | =ISNUMBER(value) | Returns TRUE for real numbers and FALSE for numbers stored as text, which is how you find a broken import. |
| ISREF, ISLOGICAL and ISNONTEXT | =ISREF(value), =ISLOGICAL(value) and =ISNONTEXT(value) | Is how you detect a deleted range before the #REF! propagates. |
| ISTEXT | =ISTEXT(value) | Returns TRUE for text values, which is how you catch a stray label in a column that should hold only numbers. |
| LAMBDA | =LAMBDA(parameter1, [parameter2], ..., calculation) | Defined in Name Manager it becomes a reusable custom function you can call by name, with no VBA. |
| LARGE | =LARGE(array, k) | Returns the kth largest value, so =LARGE(B2:B100, 3) gives the third-highest number. |
| LEFT/RIGHT/MID | =LEFT(text, num_chars) · =RIGHT(text, num_chars) · =MID(text, start_num, num_chars) | =LEFT(text, n) to take the first n characters, =RIGHT(text, n) to take the last n, and =MID(text, start, n) to take n characters beginning at a position — the three text-extraction functions. |
| LEN | =LEN(text) | Counts every character including spaces, so =LEN("hello world") returns 11. |
| LET | =LET(name1, value1, [name2, value2], ..., calculation) | Names an intermediate result so a long formula computes it once and reads like prose. |
| LINEST | =LINEST(known_ys, [known_xs], [const], [stats]) | Returns the full regression output as an array, including coefficients, R² and standard errors. |
| LN | =LN(number) | Returns the natural logarithm (base e), so =LN(2.718281828) returns 1 and =LN(1) returns 0. |
| LOG | =LOG(number, [base]) | Base 10 by default, so =LOG(1000) returns 3, and =LOG(8, 2) returns 3 for base 2. |
| LOGEST | =LOGEST(known_ys, [known_xs], [const], [stats]) | Fits an exponential curve y = b·m^x and returns its coefficients with diagnostics. |
| LOOKUP | =LOOKUP(lookup_value, lookup_vector, [result_vector]) | The oldest lookup function, which always approximates and requires sorted data. |
| MAKEARRAY | =MAKEARRAY(rows, columns, lambda) | Builds a grid where each cell is computed from its own row and column position. |
| MAP, REDUCE and SCAN | =MAP(array1, lambda), =REDUCE(initial_value, array, lambda) and =SCAN(initial_value, array, lambda) | =MAP(array, lambda) to transform every element, =REDUCE to collapse them to one value, and =SCAN to return the running total at each step. |
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Returns the POSITION of a value in a row or column, not the value itself, and the 0 forces an exact match. |
| MAX | =MAX(number1, [number2], ...) | Returns the largest number in the range, ignoring text and blanks entirely. |
| MAXIFS and MINIFS | =MAXIFS(max_range, criteria_range1, criteria1, ...) | Returns the largest value among the rows meeting your conditions, with MINIFS doing the same for the smallest. |
| MEDIAN | =MEDIAN(number1, [number2], ...) | Returns the middle value once the numbers are ordered, which outliers cannot drag around the way they drag a mean. |
| MIN | =MIN(number1, [number2], ...) | Returns the smallest number in the range, and doubles as a ceiling: =MIN(100, A2) caps a value at 100. |
| MIRR | =MIRR(values, finance_rate, reinvest_rate) | Fixes IRR's assumption that interim cash is reinvested at the IRR itself. |
| MMULT, MINVERSE and MDETERM | =MMULT(array1, array2) | Array1's column count must equal array2's row count, or the result is #VALUE!. |
| MOD | =MOD(number, divisor) | Returns the remainder after division, so =MOD(17, 5) returns 2. |
| MODE | =MODE.SNGL(number1, [number2], ...) | Returns the most frequently occurring number, and #N/A when no value repeats at all. |
| MONTH/YEAR/DAY | =MONTH(serial_number), =YEAR(serial_number), =DAY(serial_number) | Each returns a NUMBER, so January is 1, not "Jan". |
| MROUND | =MROUND(number, multiple) | Rounds to the nearest multiple of your choice in either direction, so =MROUND(17, 5) returns 15 and =MROUND(18, 5) returns 20. |
| N and T | =N(value) and =T(value) | Each returns an empty result when the value is the other kind. |
| NA | =NA() | Deliberately returns #N/A, which is how you mark data as genuinely missing rather than zero. |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Counts the whole working days (Monday to Friday) between two dates, excluding weekends and any holidays you list. |
| NORM.DIST and NORM.INV | =NORM.DIST(x, mean, standard_dev, cumulative) | =NORM.DIST(x, mean, sd, TRUE) for the probability of being at or below x, and =NORM.INV(probability, mean, sd) to go the other way. |
| NOT | =NOT(logical) | Reverses TRUE and FALSE, so =NOT(A2>100) is TRUE whenever A2 is 100 or less. |
| NPER | =NPER(rate, pmt, pv, [fv], [type]) | Returns how many periods are needed, so it answers "how long until this is paid off". |
| NPV | =NPV(rate, value1, [value2], ...) | Excel's NPV discounts every value it is given, so the day-zero outlay must be added outside the brackets. |
| OR / AND | =OR(logical1, [logical2], …) · =AND(logical1, [logical2], …) | =OR(A2>100, B2="urgent") when ANY condition being true is enough, and =AND(A2>100, B2="urgent") when ALL of them must be true — both usually sit inside an IF. |
| PERCENTILE | =PERCENTILE.INC(array, k) | =PERCENTILE.INC(B2:B500, 0.9) returns the value below which 90 % of the data falls. |
| PERCENTRANK | =PERCENTRANK.INC(array, x, [significance]) | Is the inverse of PERCENTILE, returning where a value sits in the distribution as a decimal from 0 to 1. |
| PI | =PI() | Returns π to 15 significant digits, 3.14159265358979, with no arguments needed. |
| PIVOTBY | =PIVOTBY(row_fields, col_fields, values, function, ...) | Is GROUPBY with a second dimension, producing a live cross-tab. |
| PMT | =PMT(rate, nper, pv, [fv], [type]) | Returns the level payment that clears a loan, so =PMT(5%/12, 300, 200000) gives about -1,169 a month. |
| POISSON.DIST | =POISSON.DIST(x, mean, cumulative) | Models counts of events per interval, such as calls per hour or defects per batch. |
| POWER | =POWER(number, power) | Raises a base to an exponent, so =POWER(2, 10) returns 1024. The ^ operator does the same thing: =2^10. |
| PPMT | =PPMT(rate, per, nper, pv, [fv], [type]) | Returns the principal portion of one specific payment, which grows every period as the interest share shrinks. |
| PRICE, YIELD and ACCRINT | =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) | Prices a coupon bond per 100 of face value from its dates and rates. |
| PROB | =PROB(x_range, prob_range, [lower_limit], [upper_limit]) | Totals the probabilities of outcomes falling in a range, from a discrete distribution you supply. |
| PRODUCT | =PRODUCT(number1, [number2], ...) | Multiplies every number in the range together, so =PRODUCT(A2:A5) is A2*A3*A4*A5 in one call. |
| PV | =PV(rate, nper, pmt, [fv], [type]) | Discounts a stream of equal future payments back to what they are worth today. |
| QUARTILE | =QUARTILE.INC(array, quart) | Pass 1 for the first quartile, 2 for the median and 3 for the third, so =QUARTILE.INC(B2:B200, 3) returns the 75th percentile. |
| QUOTIENT | =QUOTIENT(numerator, denominator) | Returns only the whole-number part of a division, so =QUOTIENT(17, 5) returns 3 and the remainder is discarded. |
| RAND | =RAND() | Returns a random decimal from 0 up to (but not including) 1, recalculating on every change in the workbook. |
| RANDARRAY | =RANDARRAY([rows], [columns], [min], [max], [whole_number]) | Fills a whole block with random numbers from one formula, where RANDBETWEEN needs one per cell. |
| RANDBETWEEN | =RANDBETWEEN(bottom, top) | Returns a random whole number in that inclusive range, so =RANDBETWEEN(1, 100) gives 1 to 100. |
| RANK | =RANK.EQ(number, ref, [order]) | Returns the position of a value within a range, ranking largest first unless you pass a non-zero order argument. |
| RATE | =RATE(nper, pmt, pv, [fv], [type], [guess]) | Works backwards from a payment schedule to the interest rate implied by it, per period. |
| REGEXEXTRACT, REGEXREPLACE and REGEXTEST | =REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) | Regular expressions in a cell, without VBA, for pattern matching that FIND and SEARCH cannot express. |
| REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces by POSITION, unlike SUBSTITUTE which replaces by content. |
| REPT | =REPT(text, number_times) | Repeats a string n times, which is how you build an in-cell bar chart with no chart object at all. |
| ROMAN and ARABIC | =ROMAN(number, [form]) and =ARABIC(text) | =ROMAN(number) to convert to Roman numerals and =ARABIC(text) to convert back — valid from 1 to 3999. |
| ROUND | =ROUND(number, num_digits) | Standard half-up rounding, so =ROUND(2.345, 2) returns 2.35 and the cell holds the rounded value, not just a shorter display. |
| ROUNDDOWN | =ROUNDDOWN(number, num_digits) | Always rounds toward zero, so 9.99 with 0 digits becomes 9 however close to 10 it was. |
| ROUNDUP | =ROUNDUP(number, num_digits) | Always rounds away from zero, so 4.01 with 0 digits becomes 5 no matter how small the fraction is. |
| ROW and ROWS | =ROW([reference]) and =ROWS(array) | Singular gives a position, plural gives a count. |
| ROWS | =ROWS(array) | Returns how many rows a range spans, counting the range itself rather than the data in it. |
| RRI | =RRI(nper, pv, fv) | Returns the equivalent growth rate per period, which is exactly the CAGR when the periods are years. |
| RSQ and STEYX | =RSQ(known_ys, known_xs) and =STEYX(known_ys, known_xs) | =RSQ(known_ys, known_xs) for the share of variance a regression explains, and =STEYX for the typical prediction error in the original units. |
| SEARCH | =SEARCH(find_text, within_text, [start_num]) | Returns a position like FIND but ignores case and accepts * and ? as wildcards. |
| SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | Generates a spilled block of consecutive numbers from one formula. |
| SHEET and SHEETS | =SHEET([value]) and =SHEETS([reference]) | =SHEET() for the current sheet's index and =SHEETS() for how many sheets the workbook holds. |
| SIGN | =SIGN(number) | Returns 1 for positive, -1 for negative and 0 for zero, reducing any value to its direction. |
| SKEW and KURT | =SKEW(number1, [number2], ...) and =KURT(number1, [number2], ...) | Positive means a long right tail — and =KURT(range) to measure how heavy the tails are. |
| SLN | =SLN(cost, salvage, life) | Returns the same depreciation charge for every period, which is (cost minus salvage) divided by life. |
| SLOPE and INTERCEPT | =SLOPE(known_ys, known_xs) and =INTERCEPT(known_ys, known_xs) | Together they give the two coefficients of the best-fit straight line, y = slope × x + intercept. |
| SMALL | =SMALL(array, k) | Returns the kth smallest value, so =SMALL(B2:B100, 2) gives the second-lowest number. |
| SORT | =SORT(array, [sort_index], [sort_order], [by_col]) | Returns a sorted COPY that updates itself, unlike the Sort button which rearranges your data once. |
| SORTBY | =SORTBY(array, by_array1, [sort_order1], ...) | Sorts one range by the values in ANOTHER, which SORT cannot do. |
| SQRT | =SQRT(number) | Returns the positive square root, so =SQRT(144) returns 12. Negative input returns #NUM!. |
| STANDARDIZE | =STANDARDIZE(x, mean, standard_dev) | Returns the z-score, the number of standard deviations a value sits from the mean. |
| STDEV | =STDEV.S(number1, [number2], ...) | Measures how far values typically sit from the mean. |
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Finds a piece of text by its content and swaps every occurrence for new text, or just the Nth occurrence if you add a fourth argument. |
| SUBTOTAL | =SUBTOTAL(function_num, ref1, ...) | Sums only the visible rows, so a filtered table's total updates as you filter instead of staying stuck on the full set. |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | Adds up the numbers in sum_range only for the rows where range meets your single condition. |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Adds the numbers in sum_range only for rows that satisfy every condition you list. |
| SUMPRODUCT | =SUMPRODUCT(array1, [array2], ...) | Multiplies the ranges row by row and adds the results, so quantities × prices becomes a single order total. |
| SUMSQ and SERIESSUM | =SUMSQ(number1, [number2], ...) | Squares every value and totals the results, which is the core of every distance and least-squares calculation. |
| SWITCH | =SWITCH(expression, value1, result1, [value2, result2], ..., [default]) | Matches one value against a list of exact cases. |
| SYD | =SYD(cost, salvage, life, per) | Sum-of-years-digits depreciation, which front-loads the charge more gently than declining balance. |
| T.DIST and F.DIST | =T.DIST(x, deg_freedom, cumulative) and =F.DIST(x, deg_freedom1, deg_freedom2, cumulative) | =T.DIST(x, df, TRUE) to turn a t-statistic into a probability, and =T.INV to go the other way and find a critical value. |
| T.TEST | =T.TEST(array1, array2, tails, type) | Returns a p-value, and below 0.05 is the conventional threshold for calling a difference significant. |
| TAKE and DROP | =TAKE(array, rows, [columns]) and =DROP(array, rows, [columns]) | =TAKE(array, n) for the first n rows and =DROP(array, n) to remove them — negative counts work from the end. |
| TEXTBEFORE and TEXTAFTER | =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | They replace the whole LEFT-with-FIND idiom with one readable call. |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | Joins a whole range into one string, putting your delimiter between items and optionally skipping blank cells. |
| TEXTSPLIT | =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) | Splits a string across columns and spills the parts live, unlike the one-off Text to Columns wizard. |
| TIME | =TIME(hour, minute, second) | Builds a time value from three numbers, and rolls over out-of-range parts the way DATE does. |
| TOCOL and TOROW | =TOCOL(array, [ignore], [scan_by_column]) | Flattens a two-dimensional grid into a single column, skipping blanks and errors as it goes. |
| TODAY and NOW | =TODAY() and =NOW() | Both are volatile and update on every recalculation. |
| TREND | =TREND(known_ys, [known_xs], [new_xs], [const]) | Returns a whole array of linear predictions at once, spilling down the sheet in modern Excel. |
| TRIM | =TRIM(text) | Removes leading and trailing spaces and collapses every run of internal spaces down to a single space. |
| TRIMMEAN | =TRIMMEAN(array, percent) | Drops the most extreme 20 % of values, half from each end, then averages what remains. |
| TRIMRANGE | =TRIMRANGE(range, [trim_rows], [trim_cols]) | Shrinks a whole-column reference to just the cells that actually hold data. |
| TRUNC | =TRUNC(number, [num_digits]) | Chops off the decimals without rounding at all, so 9.99 returns 9 and -9.99 returns -9. |
| TYPE | =TYPE(value) | Returns a code for what kind of value a cell holds: 1 number, 2 text, 4 logical, 16 error, 64 array. |
| UNICHAR and UNICODE | =UNICHAR(number) and =UNICODE(text) | The full range, where CHAR stops at 255. |
| UNIQUE | =UNIQUE(array, [by_col], [exactly_once]) | A dynamic-array function that returns a distinct list from a range and spills the result down or across automatically. |
| UPPER, LOWER and PROPER | =UPPER(text), =LOWER(text) and =PROPER(text) | PROPER capitalises the first letter of every word, which is right for names and wrong for almost everything else. |
| VALUE | =VALUE(text) | Converts a number stored as text into a real number, so SUM and lookups stop ignoring it. |
| VAR | =VAR.S(number1, [number2], ...) | The variance is the square of the standard deviation. |
| VDB | =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) | Declining-balance depreciation over any span, including part-periods, with an automatic switch to straight-line. |
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Finds a value in the leftmost column of a table and returns a value from a column to its right, with FALSE forcing an exact match. |
| WEEKDAY | =WEEKDAY(serial_number, [return_type]) | Returns the day of the week as a number, where return_type 2 numbers Monday as 1 through Sunday as 7. |
| WEEKNUM | =WEEKNUM(serial_number, [return_type]) | The plain default uses a US convention that disagrees with European reporting. |
| WORKDAY | =WORKDAY(start_date, days, [holidays]) | Adds working days, skipping weekends and any holiday dates you supply. |
| WRAPROWS and WRAPCOLS | =WRAPROWS(vector, wrap_count, [pad_with]) | Folds a long single column into an n-wide grid, the exact inverse of TOCOL. |
| XIRR | =XIRR(values, dates, [guess]) | Returns the annualised return of cash flows that happened on real dates rather than at even intervals. |
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | Searches one column and returns the matching value from another, in any direction, with an exact match by default. |
| XMATCH | =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) | Returns the position of a value and defaults to an exact match, unlike MATCH. |
| XNPV | =XNPV(rate, values, dates) | Discounts cash flows using their real dates instead of assuming equal periods. |
| YEARFRAC | =YEARFRAC(start_date, end_date, [basis]) | Returns the fraction of a year between two dates, and the basis argument matters more than it looks. |
| Z.TEST | =Z.TEST(array, x, [sigma]) | Returns the one-tailed probability that the sample mean exceeds a hypothesised value x. |
About this sheet
Generated from the same function-reference registry that powers the individual function pages, which means it cannot fall out of date with them — a corrected argument list or a new profile appears here on the next build. The purpose column is the one-line answer from each reference page rather than a separate summary, so what you read here is what the full page opens with.
Use this on your own site
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Cite it
Questions
Is this cheat sheet free?
Yes, and there is no email gate. The whole table is on this page, and the download is built in your browser when you click it.
Can I print it?
Yes — the print layout drops the navigation and page furniture and keeps the tables, so it comes out as a clean reference rather than a screenshot of a web page.
What formats can I download?
Excel (.xlsx) and CSV. Both hold exactly the rows shown on this page.
Is the function list complete?
It covers all 202 functions in our reference, which is every function with measurable search demand in our keyword corpus. It is generated from those pages, so it stays in step with them.