Knowing which Excel function to use helps you solve your data challenges quickly and accurately. This list provides a quick reference to the functions you’ll use most often in your day-to-day work.
Over the years, Excel’s functions have evolved to simplify complex tasks, improve efficiency, and help you deliver data insights with confidence.
I created this guide to provide a high-level overview of the most commonly used Microsoft Excel functions, categorized into sections for quick reference. Each category has a quick overview, including real-world scenarios to explain when and why you’d use each one. Plus, I’ve included a few top picks for each section that will have the greatest impact on your day-to-day work.
Use the table of contents above to jump to the section you need, or explore the entire list to discover something new.
10 Best Excel Functions
| Function | What It Does | Why It Matters |
|---|---|---|
VLOOKUP or XLOOKUP | Finds and returns a value from a table based on a match. | Essential for joining data, searching lists, or pulling info from reference tables. |
IF | Performs logic-based decisions (e.g., if a condition is true, do one thing, otherwise do another). | Great for automation, conditional logic, and dynamic outputs. |
SUM | Adds up numbers. | Basic but used constantly. Total your data without manual effort. |
COUNTIF | Counts how many cells meet a specific condition. | Useful for summaries, quality checks, and tracking counts. |
TEXT | Formats numbers or dates into readable strings. | Clean up reports and ensure consistent presentation. |
PROPER | Capitalizes the first letter of each word in a string. | Handy for cleaning up names or titles that are in all caps or lowercase. |
FILTER | Returns a filtered list based on criteria. | Powerful for dynamic data exploration without pivot tables. |
SUMIFS | Adds numbers based on multiple conditions. | Excellent for conditional summaries and dashboards. |
LEFT, RIGHT, MID | Extracts specific portions of text from cells. | Ideal for cleaning or organizing messy data like codes or names. |
UNIQUE | Returns a list of distinct values from a range. | Quickly find de-duped lists for dropdowns or analysis. |
Logical Excel Functions
Logical Excel functions, like IF, help you perform conditional operations. These functions allow you to check whether a certain condition is true or false and return specific results based on the outcome. They’re often used in decision-making processes, such as calculating bonuses, applying discounts, or flagging issues in data.
For example, you might use an IF statement to determine whether a salesperson met their target, then assign a bonus accordingly.
Top Picks
- IF: The most commonly used logical function for testing conditions.
- AND/OR: Used to combine multiple conditions in one logical test.
- IFERROR: A handy way to handle errors in formulas and replace them with custom text.
| Function | Description | Syntax |
| AND | Returns TRUE if all conditions are TRUE. | =AND(logical1, [logical2], …) |
| BYCOL | Applies a function to each column in an array or range. | =BYCOL(array, lambda) |
| BYROW | Applies a function to each row in an array or range. | =BYROW(array, lambda) |
| IF | Returns one value if a condition is TRUE, and another if FALSE. | =IF(logical_test, value_if_true, value_if_false) |
| IFERROR | Returns a fallback value if the expression results in an error. | =IFERROR(value, value_if_error) |
| IFNA | Returns a fallback value only if the result is #N/A. | =IFNA(value, value_if_na) |
| IFS | Evaluates multiple conditions and returns the first TRUE result. | =IFS(logical_test1, value1, [logical_test2, value2], …) |
| LAMBDA | Defines a custom function inline without VBA or scripting. | =LAMBDA(parameter1, parameter2, …, calculation) |
| MAKEARRAY | Creates an array by applying a LAMBDA function to each cell. | =MAKEARRAY(rows, columns, lambda) |
| MAP | Transforms one or more arrays by applying a LAMBDA function to each element. | =MAP(array1, [array2], …, lambda) |
| NOT | Reverses a logical value; TRUE becomes FALSE and vice versa. | =NOT(logical) |
| OR | Returns TRUE if any condition is TRUE. | =OR(logical1, [logical2], …) |
| REDUCE | Reduces an array to a single value by iteratively applying a function. | =REDUCE(initial_value, array, lambda) |
| SCAN | Returns an array of intermediate values from iteratively applying a function. | =SCAN(initial_value, array, lambda) |
| SWITCH | Compares one value against a list and returns the first matching result. | =SWITCH(expression, value1, result1, [value2, result2], …, [default]) |
| XOR | Returns TRUE if an odd number of arguments are TRUE. | =XOR(logical1, [logical2], …) |
| FALSE | Returns the logical value FALSE. | =FALSE() |
| TRUE | Returns the logical value TRUE. | =TRUE() |
Date & Time Functions
Date and time functions are essential when dealing with time-based data. These Excel functions help you manipulate dates and times, calculate duration, and even extract specific date parts like day, month, or year.
For example, you can use the TODAY() function to always show the current date in your spreadsheet or calculate the number of days between two dates for project timelines.
Top Picks
- TODAY(): Always returns the current date.
- DATE(): Lets you create a date from year, month, and day values.
- DATEDIF: Perfect for calculating the difference between two dates.
| Function | Description | Syntax |
| DATE | Creates a date from individual year, month, and day values. | =DATE(year, month, day) |
| DATEDIF | Calculates the difference between two dates in years, months, or days. (Undocumented but still supported) | =DATEDIF(start_date, end_date, unit) |
| DATEVALUE | Converts a date stored as text into an actual date value. | =DATEVALUE(date_text) |
| DAY | Returns the day of the month from a date. | =DAY(serial_number) |
| DAYS | Returns the number of days between two dates. | =DAYS(end_date, start_date) |
| DAYS360 | Calculates days between two dates based on a 360-day year (used in accounting). | =DAYS360(start_date, end_date, [method]) |
| EDATE | Returns the same day of the month a specified number of months before or after a given date. | =EDATE(start_date, months) |
| EOMONTH | Returns the last day of the month a certain number of months before or after a start date. | =EOMONTH(start_date, months) |
| HOUR | Extracts the hour (0–23) from a time value. | =HOUR(serial_number) |
| ISOWEEKNUM | Returns the ISO week number of the year for a given date. | =ISOWEEKNUM(date) |
| MINUTE | Extracts the minute from a time value. | =MINUTE(serial_number) |
| MONTH | Returns the month number (1–12) from a date. | =MONTH(serial_number) |
| NETWORKDAYS | Returns the number of working days between two dates (excludes weekends and optionally holidays). | =NETWORKDAYS(start_date, end_date, [holidays]) |
| NETWORKDAYS.INTL | Same as NETWORKDAYS but allows custom weekend settings. | =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
| NOW | Returns the current date and time. | =NOW() |
| SECOND | Extracts the second from a time value. | =SECOND(serial_number) |
| TIME | Creates a time from hour, minute, and second values. | =TIME(hour, minute, second) |
| TIMEVALUE | Converts a time stored as text into an actual time value. | =TIMEVALUE(time_text) |
| TODAY | Returns the current date without the time. | =TODAY() |
| WEEKDAY | Returns a number representing the day of the week. | =WEEKDAY(serial_number, [return_type]) |
| WEEKNUM | Returns the week number of a given date. | =WEEKNUM(serial_number, [return_type]) |
| WORKDAY | Returns a date that is a given number of working days away (excludes weekends and holidays). | =WORKDAY(start_date, days, [holidays]) |
| WORKDAY.INTL | Same as WORKDAY but with customizable weekends. | =WORKDAY.INTL(start_date, days, [weekend], [holidays]) |
| YEAR | Extracts the year from a date. | =YEAR(serial_number) |
| YEARFRAC | Returns the fraction of a year between two dates. | =YEARFRAC(start_date, end_date, [basis]) |
Text Functions
Text functions help you manipulate and format text strings. These Excel functions come in handy when you need to clean up data or extract specific text parts, like extracting the first name from a full name.
You might use LEFT() and RIGHT() to grab portions of text, or UPPER() to standardize the casing in a column of data.
Top Picks
- CONCATENATE (or CONCAT): Combine multiple text strings into one.
- TEXT: Format numbers and dates as text.
- LEFT/RIGHT: Extract a specified number of characters from the start or end of a string.
| Function | Description | Syntax |
| ARRAYTOTEXT | Converts an array or range to a text string. | =ARRAYTOTEXT(array, [format]) |
| ASC | Converts full-width (double-byte) characters to half-width (single-byte). | =ASC(text) |
| BAHTTEXT | Converts a number to Thai text with the Baht currency. | =BAHTTEXT(number) |
| CHAR | Returns the character specified by a number (ASCII code). | =CHAR(number) |
| CLEAN | Removes non-printable characters from text. | =CLEAN(text) |
| CODE | Returns the numeric ASCII code of the first character in a text string. | =CODE(text) |
| CONCAT | Joins multiple text strings into one (replaces CONCATENATE). | =CONCAT(text1, [text2], …) |
| CONCATENATE | Joins two or more text strings (older version; use CONCAT instead). | =CONCATENATE(text1, [text2], …) |
| DBCS | Converts half-width (single-byte) characters to full-width (double-byte). | =DBCS(text) |
| DOLLAR | Converts a number to text using currency formatting. | =DOLLAR(number, [decimals]) |
| EXACT | Compares two text strings and returns TRUE if they are exactly the same. | =EXACT(text1, text2) |
| FIXED | Formats a number as text with a fixed number of decimals. | =FIXED(number, [decimals], [no_commas]) |
| LEFT | Returns a specified number of characters from the start of a text string. | =LEFT(text, [num_chars]) |
| LEFTB | Returns characters from the start of a string based on bytes (for DBCS). | =LEFTB(text, [num_bytes]) |
| LEN | Returns the number of characters in a text string. | =LEN(text) |
| LENB | Returns the number of bytes in a text string (for DBCS). | =LENB(text) |
| LOWER | Converts all characters in a text string to lowercase. | =LOWER(text) |
| NUMBERVALUE | Converts text to a number using custom separators. | =NUMBERVALUE(text, [decimal_separator], [group_separator]) |
| PHONETIC | Extracts phonetic (furigana) characters from text (mainly for East Asian use). | =PHONETIC(reference) |
| PROPER | Capitalizes the first letter of each word in a text string. | =PROPER(text) |
| REPT | Repeats text a given number of times. | =REPT(text, number_times) |
| RIGHT | Returns a specified number of characters from the end of a text string. | =RIGHT(text, [num_chars]) |
| RIGHTB | Returns characters from the end of a string based on bytes (for DBCS). | =RIGHTB(text, [num_bytes]) |
| SUBSTITUTE | Replaces specific text in a string with new text. | =SUBSTITUTE(text, old_text, new_text, [instance_num]) |
| TEXT | Formats a number and converts it to text. | =TEXT(value, format_text) |
| TEXTAFTER | Returns text that comes after a specified delimiter. | =TEXTAFTER(text, delimiter, [instance], [match_mode], [match_end]) |
| TEXTBEFORE | Returns text that comes before a specified delimiter. | =TEXTBEFORE(text, delimiter, [instance], [match_mode], [match_end]) |
| TEXTJOIN | Joins multiple text items with a delimiter, ignoring blanks. | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) |
| TEXTSPLIT | Splits text into rows or columns using delimiters. | =TEXTSPLIT(text, col_delimiter, [row_delimiter], …) |
| TRIM | Removes all extra spaces from text, leaving only single spaces between words. | =TRIM(text) |
| UNICHAR | Returns the Unicode character for a given number. | =UNICHAR(number) |
| UNICODE | Returns the Unicode number for the first character in a string. | =UNICODE(text) |
| UPPER | Converts all characters in a text string to uppercase. | =UPPER(text) |
| VALUE | Converts text that looks like a number into a number. | =VALUE(text) |
| VALUETOTEXT | Converts a value to text and preserves formatting. | =VALUETOTEXT(value, [format]) |
Comparison Functions
Comparison functions let you compare values in Excel. You can use them to check whether two values are equal, greater, or smaller. They’re often used in filtering and conditional formatting.
For example, you can use COUNTIF to check if a value appears more than a certain number of times in a dataset.
Top Picks
- CELL: Retrieve information about a cell’s formatting, location, or contents.
- ISERROR: Identify any type of error in a cell, excluding #N/A, to manage and fix potential issues in your formulas.
- ISBLANK: Quickly check if a cell is empty, perfect for conditional formatting or data validation.
| Function | Description | Syntax |
| CELL | Returns information about the formatting, location, or contents of a cell. | =CELL(info_type, [reference]) |
| ERROR.TYPE | Returns a number corresponding to the type of error in a cell. | =ERROR.TYPE(error_val) |
| INFO | Returns details about the current operating environment (e.g., OS, memory). | =INFO(type_text) |
| ISBLANK | Returns TRUE if the referenced cell is empty. | =ISBLANK(value) |
| ISERR | Returns TRUE if the value is any error except #N/A. | =ISERR(value) |
| ISERROR | Returns TRUE if the value is any Excel error (including #N/A). | =ISERROR(value) |
| ISEVEN | Returns TRUE if a number is even. | =ISEVEN(number) |
| ISFORMULA | Returns TRUE if the referenced cell contains a formula. | =ISFORMULA(reference) |
| ISLOGICAL | Returns TRUE if the value is a logical (TRUE/FALSE). | =ISLOGICAL(value) |
| ISNA | Returns TRUE if the value is #N/A. | =ISNA(value) |
| ISNONTEXT | Returns TRUE if the value is not text (includes numbers, errors, blanks). | =ISNONTEXT(value) |
| ISNUMBER | Returns TRUE if the value is a number. | =ISNUMBER(value) |
| ISODD | Returns TRUE if a number is odd. | =ISODD(number) |
| ISOMITTED | Returns TRUE if a function argument was not provided. (LAMBDA only) | =ISOMITTED(argument) |
| ISREF | Returns TRUE if the value is a valid reference. | =ISREF(value) |
| ISTEXT | Returns TRUE if the value is text. | =ISTEXT(value) |
| SHEET | Returns the sheet number of a reference. | =SHEET([value]) |
| SHEETS | Returns the number of sheets in a reference or workbook. | =SHEETS([reference]) |
| TYPE | Returns a number indicating the data type (number, text, logical, etc.). | =TYPE(value) |
Lookup & Reference Functions
Excel’s lookup and reference functions allow you to search for specific data in a table and return a related value. These are crucial when working with large datasets.
For example, VLOOKUP helps you find a value in a vertical table and return a related value from a specified column, while XLOOKUP offers a more flexible and powerful approach, especially for horizontal data.
Top Picks
- VLOOKUP: The go-to function for looking up data in vertical tables.
- XLOOKUP: More versatile than VLOOKUP and replaces both VLOOKUP and HLOOKUP.
- INDEX/MATCH: A more flexible alternative to VLOOKUP when you need to lookup data in any direction.
| Function | Description | Syntax |
| ADDRESS | Returns a cell address as text based on row and column numbers. | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) |
| AREAS | Returns the number of areas (ranges) in a reference. | =AREAS(reference) |
| CHOOSE | Returns a value from a list based on an index number. | =CHOOSE(index_num, value1, [value2], …) |
| CHOOSECOLS | Returns specific columns from an array or range. | =CHOOSECOLS(array, col_num1, [col_num2], …) |
| CHOOSEROWS | Returns specific rows from an array or range. | =CHOOSEROWS(array, row_num1, [row_num2], …) |
| COLUMN | Returns the column number of a reference. | =COLUMN([reference]) |
| COLUMNS | Returns the number of columns in a reference or array. | =COLUMNS(array) |
| DROPDOWN | Creates a dropdown list in a cell (Excel Labs preview function). | =DROPDOWN(source) |
| EXPAND | Expands an array to a specified number of rows and columns. | =EXPAND(array, rows, columns, [pad_with]) |
| FILTER | Returns only the rows that meet specified conditions. | =FILTER(array, include, [if_empty]) |
| FORMULATEXT | Returns a formula as a text string. | =FORMULATEXT(reference) |
| GETPIVOTDATA | Extracts data from a PivotTable by field and item. | =GETPIVOTDATA(data_field, pivot_table, [field1, item1], …) |
| HLOOKUP | Searches for a value in the first row of a table and returns a value in the same column. | =HLOOKUP(lookup_value, table_array, row_index, [range_lookup]) |
| HSTACK | Stacks values horizontally into a single row. | =HSTACK(array1, [array2], …) |
| HYPERLINK | Creates a clickable link to a document, webpage, or cell. | =HYPERLINK(link_location, [friendly_name]) |
| INDEX | Returns the value of a cell at a specific row and column within a range. | =INDEX(array, row_num, [column_num]) |
| INDIRECT | Returns a cell reference specified by a text string. | =INDIRECT(ref_text, [a1]) |
| LOOKUP | Searches for a value in a one-row or one-column range and returns a result from the same position in another range. | =LOOKUP(lookup_value, lookup_vector, [result_vector]) |
| MATCH | Returns the position of a value within a range. | =MATCH(lookup_value, lookup_array, [match_type]) |
| OFFSET | Returns a reference to a range offset from a starting point. | =OFFSET(reference, rows, cols, [height], [width]) |
| ROW | Returns the row number of a reference. | =ROW([reference]) |
| ROWS | Returns the number of rows in a range or array. | =ROWS(array) |
| RTD | Retrieves real-time data from a COM automation server. | =RTD(prog_id, server, topic1, [topic2], …) |
| SORT | Sorts an array or range in ascending or descending order. | =SORT(array, [sort_index], [sort_order], [by_col]) |
| SORTBY | Sorts an array based on one or more corresponding arrays. | =SORTBY(array, by_array1, [sort_order1], …) |
| TAKE | Returns a specified number of rows or columns from the start or end of an array. | =TAKE(array, rows, [columns]) |
| TOCOL | Converts an array or range into a single column. | =TOCOL(array, [ignore], [scan_by_column]) |
| TOROW | Converts an array or range into a single row. | =TOROW(array, [ignore], [scan_by_column]) |
| TRANSPOSE | Rotates the orientation of a range (rows to columns or vice versa). | =TRANSPOSE(array) |
| UNIQUE | Returns a list of unique values from a range or array. | =UNIQUE(array, [by_col], [exactly_once]) |
| VLOOKUP | Searches for a value in the first column of a table and returns a value in the same row. | =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) |
| VSTACK | Stacks values vertically into a single column. | =VSTACK(array1, [array2], …) |
| WRAPCOLS | Wraps values into columns after a set number of elements. | =WRAPCOLS(vector, wrap_count, [pad_with]) |
| WRAPROWS | Wraps values into rows after a set number of elements. | =WRAPROWS(vector, wrap_count, [pad_with]) |
| XLOOKUP | Searches a range or array and returns an exact or approximate match in any direction. | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
| XMATCH | Returns the relative position of an item in a range (similar to MATCH but more powerful). | =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) |
Math Functions
Math functions are used for performing calculations, from basic arithmetic to complex statistical analysis.
Whether you’re adding up sales figures with SUM() or calculating averages with AVERAGE(), math functions are key to data analysis. You can use ROUND() to adjust decimals or MOD() to find remainders in calculations.
Top Picks
- SUM: Adds together a range of numbers.
- AVERAGE: Calculates the average of a group of numbers.
- ROUND: Rounds a number to a specified number of digits.
| Function | Description | Syntax |
| ABS | Returns the absolute value of a number. | =ABS(number) |
| ACOS | Returns the arccosine of a number. | =ACOS(number) |
| ACOSH | Returns the inverse hyperbolic cosine of a number. | =ACOSH(number) |
| ACOT | Returns the arccotangent of a number. | =ACOT(number) |
| ACOTH | Returns the inverse hyperbolic cotangent of a number. | =ACOTH(number) |
| AGGREGATE | Performs calculations like AVERAGE or MAX while optionally ignoring errors or hidden rows. | =AGGREGATE(function_num, options, array, [k]) |
| ARABIC | Converts a Roman numeral to an Arabic number. | =ARABIC(text) |
| ASIN | Returns the arcsine of a number. | =ASIN(number) |
| ASINH | Returns the inverse hyperbolic sine of a number. | =ASINH(number) |
| ATAN | Returns the arctangent of a number. | =ATAN(number) |
| ATAN2 | Returns the arctangent based on x and y coordinates. | =ATAN2(x_num, y_num) |
| ATANH | Returns the inverse hyperbolic tangent of a number. | =ATANH(number) |
| BASE | Converts a number into a text string with a specified base (radix). | =BASE(number, radix, [min_length]) |
| CEILING | Rounds a number up to the nearest multiple of a specified value. | =CEILING(number, significance) |
| CEILING.MATH | Rounds a number up to the nearest integer or specified multiple. | =CEILING.MATH(number, [significance], [mode]) |
| CEILING.PRECISE | Rounds a number up toward zero to the nearest multiple of a specified value. | =CEILING.PRECISE(number, [significance]) |
| COMBIN | Returns the number of combinations for a given number of items. | =COMBIN(number, number_chosen) |
| COMBINA | Returns the number of combinations with repetitions. | =COMBINA(number, number_chosen) |
| COS | Returns the cosine of an angle. | =COS(number) |
| COSH | Returns the hyperbolic cosine of a number. | =COSH(number) |
| COT | Returns the cotangent of an angle. | =COT(number) |
| COTH | Returns the hyperbolic cotangent of a number. | =COTH(number) |
| CSC | Returns the cosecant of an angle. | =CSC(number) |
| CSCH | Returns the hyperbolic cosecant of a number. | =CSCH(number) |
| DECIMAL | Converts a number in a given base to a decimal. | =DECIMAL(text, radix) |
| DEGREES | Converts radians into degrees. | =DEGREES(angle) |
| EVEN | Rounds a number up to the nearest even integer. | =EVEN(number) |
| EXP | Returns e raised to the power of a number. | =EXP(number) |
| FACT | Returns the factorial of a number. | =FACT(number) |
| FACTDOUBLE | Returns the double factorial of a number. | =FACTDOUBLE(number) |
| FLOOR | Rounds a number down to the nearest multiple of a specified value. | =FLOOR(number, significance) |
| FLOOR.MATH | Rounds a number down toward zero. | =FLOOR.MATH(number, [significance], [mode]) |
| FLOOR.PRECISE | Rounds a number down away from zero. | =FLOOR.PRECISE(number, [significance]) |
| GCD | Returns the greatest common divisor of one or more integers. | =GCD(number1, [number2], …) |
| INT | Rounds a number down to the nearest integer. | =INT(number) |
| ISO.CEILING | Rounds a number up to the nearest multiple, away from zero. | =ISO.CEILING(number, [significance]) |
| LCM | Returns the least common multiple of integers. | =LCM(number1, [number2], …) |
| LET | Assigns names to calculation results and reuses them in a formula. | =LET(name1, name_value1, calculation_or_name2, …) |
| LN | Returns the natural logarithm of a number. | =LN(number) |
| LOG | Returns the logarithm of a number to a specified base. | =LOG(number, [base]) |
| LOG10 | Returns the base-10 logarithm of a number. | =LOG10(number) |
| MDETERM | Returns the determinant of a matrix. | =MDETERM(array) |
| MINVERSE | Returns the inverse of a matrix. | =MINVERSE(array) |
| MMULT | Returns the matrix product of two arrays. | =MMULT(array1, array2) |
| MOD | Returns the remainder after dividing one number by another. | =MOD(number, divisor) |
| MROUND | Rounds a number to the nearest multiple of a specified value. | =MROUND(number, multiple) |
| MULTINOMIAL | Returns the multinomial of a set of numbers. | =MULTINOMIAL(number1, [number2], …) |
| MUNIT | Returns the unit matrix (identity matrix) of a specified dimension. | =MUNIT(dimension) |
| ODD | Rounds a number up to the nearest odd integer. | =ODD(number) |
| PI | Returns the value of π. | =PI() |
| POWER | Raises a number to a specified power. | =POWER(number, power) |
| PRODUCT | Multiplies all the given numbers. | =PRODUCT(number1, [number2], …) |
| QUOTIENT | Returns the integer portion of a division. | =QUOTIENT(numerator, denominator) |
| RADIANS | Converts degrees to radians. | =RADIANS(angle) |
| RAND | Returns a random number between 0 and 1. | =RAND() |
| RANDARRAY | Returns an array of random numbers. | =RANDARRAY([rows], [columns], [min], [max], [whole_number]) |
| RANDBETWEEN | Returns a random integer between two specified values. | =RANDBETWEEN(bottom, top) |
| ROMAN | Converts a number to Roman numerals. | =ROMAN(number, [form]) |
| ROUND | Rounds a number to a specified number of digits. | =ROUND(number, num_digits) |
| ROUNDDOWN | Rounds a number down, toward zero. | =ROUNDDOWN(number, num_digits) |
| ROUNDUP | Rounds a number up, away from zero. | =ROUNDUP(number, num_digits) |
| SEC | Returns the secant of an angle. | =SEC(number) |
| SECH | Returns the hyperbolic secant of a number. | =SECH(number) |
| SEQUENCE | Generates an array of sequential numbers. | =SEQUENCE(rows, [columns], [start], [step]) |
| SERIESSUM | Returns the sum of a power series based on x, n, m, and coefficients. | =SERIESSUM(x, n, m, coefficients) |
| SIGN | Returns the sign of a number: 1 (positive), 0 (zero), or -1 (negative). | =SIGN(number) |
| SIN | Returns the sine of an angle. | =SIN(number) |
| SINH | Returns the hyperbolic sine of a number. | =SINH(number) |
| SQRT | Returns the square root of a number. | =SQRT(number) |
| SQRTPI | Returns the square root of (number × π). | =SQRTPI(number) |
| SUBTOTAL | Returns a subtotal for a list or database using a specified function. | =SUBTOTAL(function_num, ref1, [ref2], …) |
| SUM | Adds up all numbers in a range or list. | =SUM(number1, [number2], …) |
| SUMIF | Adds values that meet a single condition. | =SUMIF(range, criteria, [sum_range]) |
| SUMIFS | Adds values that meet multiple conditions. | =SUMIFS(sum_range, criteria_range1, criteria1, …) |
| SUMPRODUCT | Multiplies corresponding elements in arrays and returns the sum. | =SUMPRODUCT(array1, [array2], …) |
| SUMSQ | Returns the sum of the squares of the numbers. | =SUMSQ(number1, [number2], …) |
| SUMX2MY2 | Returns the sum of the difference of squares of two arrays. | =SUMX2MY2(array_x, array_y) |
| SUMX2PY2 | Returns the sum of the sum of squares of two arrays. | =SUMX2PY2(array_x, array_y) |
| SUMXMY2 | Returns the sum of squares of differences of two arrays. | =SUMXMY2(array_x, array_y) |
| TAN | Returns the tangent of an angle. | =TAN(number) |
| TANH | Returns the hyperbolic tangent of a number. | =TANH(number) |
| TRUNC | Truncates a number to a whole number or specified decimal places. | =TRUNC(number, [num_digits]) |
Financial Functions in Excel
Financial functions are great for performing calculations related to finance, such as interest rates, loan payments, and investments. You can use PMT to calculate monthly payments for a loan or FV to calculate the future value of an investment.
Top Picks
- PMT: Calculates loan payments based on constant payments and a constant interest rate.
- FV: Determines the future value of an investment.
- NPV: Calculates net present value for a series of cash flows.
| Function | Description | Syntax |
| ACCRINT | Returns accrued interest for a security that pays periodic interest. | =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]) |
| ACCRINTM | Returns accrued interest for a security that pays interest at maturity. | =ACCRINTM(issue, settlement, rate, par, [basis]) |
| AMORDEGRC | Returns depreciation using a declining balance method (European accounting). | =AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis]) |
| AMORLINC | Returns depreciation using a straight-line method (European accounting). | =AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) |
| COUPDAYBS | Returns the number of days from the start of the coupon period to the settlement date. | =COUPDAYBS(settlement, maturity, frequency, [basis]) |
| COUPDAYS | Returns the number of days in the coupon period that contains the settlement date. | =COUPDAYS(settlement, maturity, frequency, [basis]) |
| COUPDAYSNC | Returns the number of days from the settlement date to the next coupon date. | =COUPDAYSNC(settlement, maturity, frequency, [basis]) |
| COUPNCD | Returns the next coupon date after the settlement date. | =COUPNCD(settlement, maturity, frequency, [basis]) |
| COUPNUM | Returns the number of coupons between the settlement and maturity dates. | =COUPNUM(settlement, maturity, frequency, [basis]) |
| COUPPCD | Returns the previous coupon date before the settlement date. | =COUPPCD(settlement, maturity, frequency, [basis]) |
| CUMIPMT | Returns cumulative interest paid between two periods. | =CUMIPMT(rate, nper, pv, start_period, end_period, type) |
| CUMPRINC | Returns cumulative principal paid between two periods. | =CUMPRINC(rate, nper, pv, start_period, end_period, type) |
| DB | Returns depreciation using fixed-declining balance method. | =DB(cost, salvage, life, period, [month]) |
| DDB | Returns depreciation using double-declining balance method. | =DDB(cost, salvage, life, period, [factor]) |
| DISC | Returns the discount rate for a security. | =DISC(settlement, maturity, pr, redemption, [basis]) |
| DOLLARDE | Converts a fractional dollar value to a decimal. | =DOLLARDE(fractional_dollar, fraction) |
| DOLLARFR | Converts a decimal dollar value to a fraction. | =DOLLARFR(decimal_dollar, fraction) |
| DURATION | Returns the Macaulay duration of a security. | =DURATION(settlement, maturity, coupon, yld, frequency, [basis]) |
| EFFECT | Returns the effective annual interest rate. | =EFFECT(nominal_rate, npery) |
| FV | Returns the future value of an investment. | =FV(rate, nper, pmt, [pv], [type]) |
| FVSCHEDULE | Returns the future value using a schedule of compound interest rates. | =FVSCHEDULE(principal, schedule) |
| INTRATE | Returns the interest rate for a fully invested security. | =INTRATE(settlement, maturity, investment, redemption, [basis]) |
| IPMT | Returns interest payment for a period of a loan. | =IPMT(rate, per, nper, pv, [fv], [type]) |
| IRR | Returns the internal rate of return for a series of cash flows. | =IRR(values, [guess]) |
| ISPMT | Returns interest paid during a specific period of a loan. | =ISPMT(rate, per, nper, pv) |
| MDURATION | Returns the modified duration of a security. | =MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) |
| MIRR | Returns the modified internal rate of return. | =MIRR(values, finance_rate, reinvest_rate) |
| NOMINAL | Returns the nominal annual interest rate. | =NOMINAL(effect_rate, npery) |
| NPER | Returns the number of periods for an investment. | =NPER(rate, pmt, pv, [fv], [type]) |
| NPV | Returns the net present value based on a discount rate and future payments. | =NPV(rate, value1, [value2], …) |
| ODDFPRICE | Returns the price of a security with an odd first period. | =ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis]) |
| ODDFYIELD | Returns the yield of a security with an odd first period. | =ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis]) |
| ODDLPRICE | Returns the price of a security with an odd last period. | =ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) |
| ODDLYIELD | Returns the yield of a security with an odd last period. | =ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) |
| PDURATION | Returns the number of periods to reach a target value at a given rate. | =PDURATION(rate, pv, fv) |
| PMT | Returns the payment for a loan based on constant payments and interest. | =PMT(rate, nper, pv, [fv], [type]) |
| PPMT | Returns the principal payment for a given period of a loan. | =PPMT(rate, per, nper, pv, [fv], [type]) |
| PRICE | Returns the price per $100 face value of a security with periodic interest. | =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) |
| PRICEDISC | Returns the price of a discounted security. | =PRICEDISC(settlement, maturity, discount, redemption, [basis]) |
| PRICEMAT | Returns the price of a security that pays interest at maturity. | =PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) |
| PV | Returns the present value of an investment. | =PV(rate, nper, pmt, [fv], [type]) |
| RATE | Returns the interest rate per period of an investment. | =RATE(nper, pmt, pv, [fv], [type], [guess]) |
| RECEIVED | Returns the amount to be received at maturity for a fully invested security. | =RECEIVED(settlement, maturity, investment, discount, [basis]) |
| RRI | Returns the interest rate required for growth over a number of periods. | =RRI(nper, pv, fv) |
| SLN | Returns straight-line depreciation for one period. | =SLN(cost, salvage, life) |
| SYD | Returns depreciation using the sum-of-years’ digits method. | =SYD(cost, salvage, life, per) |
| TBILLEQ | Returns bond-equivalent yield for a Treasury bill. | =TBILLEQ(settlement, maturity, discount) |
| TBILLPRICE | Returns the price per $100 face value of a Treasury bill. | =TBILLPRICE(settlement, maturity, discount) |
| TBILLYIELD | Returns the yield of a Treasury bill. | =TBILLYIELD(settlement, maturity, pr) |
| VDB | Returns depreciation using variable-declining balance method. | =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) |
| XIRR | Returns the internal rate of return for irregular cash flows. | =XIRR(values, dates, [guess]) |
| XNPV | Returns the net present value for irregular cash flows. | =XNPV(rate, values, dates) |
| YIELD | Returns the yield on a security that pays periodic interest. | =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) |
| YIELDDISC | Returns the annual yield for a discounted security. | =YIELDDISC(settlement, maturity, pr, redemption, [basis]) |
| YIELDMAT | Returns the annual yield of a security that pays interest at maturity. | =YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) |
Statistical Functions for Excel
Statistical functions are used to analyze datasets by calculating values like mean, median, and standard deviation. For example, you might use STDEV() to measure the spread of data or MEDIAN() to find the middle value in a dataset. These functions are essential for data analysis in industries such as marketing, healthcare, and finance.
Top Picks
- AVERAGE: Computes the average value in a set of data.
- STDEV: Calculates the standard deviation of a set of values.
- MEDIAN: Finds the middle value in a range of numbers.
| Function | Description | Syntax |
| AVEDEV | Returns the average of the absolute deviations from the mean. | =AVEDEV(number1, [number2], …) |
| AVERAGE | Returns the arithmetic mean of a set of numbers. | =AVERAGE(number1, [number2], …) |
| AVERAGEA | Returns the average of values, treating text as 0 and logicals as TRUE/FALSE. | =AVERAGEA(value1, [value2], …) |
| AVERAGEIF | Returns the average of cells that meet a single condition. | =AVERAGEIF(range, criteria, [average_range]) |
| AVERAGEIFS | Returns the average of cells that meet multiple conditions. | =AVERAGEIFS(average_range, criteria_range1, criteria1, …) |
| BETA.DIST | Returns the beta distribution. | =BETA.DIST(x, alpha, beta, cumulative, [A], [B]) |
| BETA.INV | Returns the inverse of the beta distribution. | =BETA.INV(probability, alpha, beta, [A], [B]) |
| BINOM.DIST | Returns the binomial distribution probability. | =BINOM.DIST(number_s, trials, probability_s, cumulative) |
| BINOM.DIST.RANGE | Returns the probability of a range of outcomes in a binomial distribution. | =BINOM.DIST.RANGE(trials, prob_s, number_s, [number_s2]) |
| BINOM.INV | Returns the smallest value for which the cumulative binomial is ≥ a criterion. | =BINOM.INV(trials, probability_s, alpha) |
| CHISQ.DIST | Returns the left-tailed chi-squared distribution. | =CHISQ.DIST(x, deg_freedom, cumulative) |
| CHISQ.DIST.RT | Returns the right-tailed chi-squared distribution. | =CHISQ.DIST.RT(x, deg_freedom) |
| CHISQ.INV | Returns the inverse of the left-tailed chi-squared distribution. | =CHISQ.INV(probability, deg_freedom) |
| CHISQ.INV.RT | Returns the inverse of the right-tailed chi-squared distribution. | =CHISQ.INV.RT(probability, deg_freedom) |
| CHISQ.TEST | Returns the chi-squared test for independence. | =CHISQ.TEST(actual_range, expected_range) |
| CONFIDENCE.NORM | Returns the margin of error for a normal distribution. | =CONFIDENCE.NORM(alpha, standard_dev, size) |
| CONFIDENCE.T | Returns the margin of error using the t-distribution. | =CONFIDENCE.T(alpha, standard_dev, size) |
| CORREL | Returns the correlation coefficient between two arrays. | =CORREL(array1, array2) |
| COUNT | Counts the number of numeric values. | =COUNT(value1, [value2], …) |
| COUNTA | Counts the number of non-empty values. | =COUNTA(value1, [value2], …) |
| COUNTBLANK | Counts the number of blank cells in a range. | =COUNTBLANK(range) |
| COUNTIF | Counts the number of cells that meet a condition. | =COUNTIF(range, criteria) |
| COUNTIFS | Counts the number of cells that meet multiple conditions. | =COUNTIFS(criteria_range1, criteria1, …) |
| COVARIANCE.P | Returns population covariance between two datasets. | =COVARIANCE.P(array1, array2) |
| COVARIANCE.S | Returns sample covariance between two datasets. | =COVARIANCE.S(array1, array2) |
| DEVSQ | Returns the sum of squares of deviations from the mean. | =DEVSQ(number1, [number2], …) |
| EXPON.DIST | Returns the exponential distribution. | =EXPON.DIST(x, lambda, cumulative) |
| F.DIST | Returns the left-tailed F distribution. | =F.DIST(x, deg_freedom1, deg_freedom2, cumulative) |
| F.DIST.RT | Returns the right-tailed F distribution. | =F.DIST.RT(x, deg_freedom1, deg_freedom2) |
| F.INV | Returns the inverse of the left-tailed F distribution. | =F.INV(probability, deg_freedom1, deg_freedom2) |
| F.INV.RT | Returns the inverse of the right-tailed F distribution. | =F.INV.RT(probability, deg_freedom1, deg_freedom2) |
| F.TEST | Returns the result of an F-test for two arrays. | =F.TEST(array1, array2) |
| FISHER | Returns the Fisher transformation of a correlation coefficient. | =FISHER(x) |
| FISHERINV | Returns the inverse of the Fisher transformation. | =FISHERINV(y) |
| FORECAST | Returns a future value based on linear regression (older version). | =FORECAST(x, known_ys, known_xs) |
| FORECAST.ETS | Returns a forecasted value using exponential smoothing. | =FORECAST.ETS(target_date, values, timeline, [seasonality], …) |
| FORECAST.ETS.CONFINT | Returns a confidence interval for a forecasted value. | =FORECAST.ETS.CONFINT(target_date, values, timeline, …) |
| FORECAST.ETS.SEASONALITY | Returns the length of the seasonal pattern detected in data. | =FORECAST.ETS.SEASONALITY(values, timeline, …) |
| FORECAST.ETS.STAT | Returns a specified statistic related to an exponential smoothing forecast. | =FORECAST.ETS.STAT(target_date, values, timeline, stat_type, …) |
| FORECAST.LINEAR | Returns a linear forecast for a future value. | =FORECAST.LINEAR(x, known_ys, known_xs) |
| FREQUENCY | Returns a frequency distribution as a vertical array. | =FREQUENCY(data_array, bins_array) |
| GAMMA | Returns the gamma function value. | =GAMMA(number) |
| GAMMA.DIST | Returns the gamma distribution. | =GAMMA.DIST(x, alpha, beta, cumulative) |
| GAMMA.INV | Returns the inverse of the gamma cumulative distribution. | =GAMMA.INV(probability, alpha, beta) |
| GAMMALN | Returns the natural logarithm of the gamma function. | =GAMMALN(number) |
| GAMMALN.PRECISE | Returns a more accurate natural logarithm of the gamma function. | =GAMMALN.PRECISE(number) |
| GAUSS | Returns the probability that a standard normal variable is less than a given value. | =GAUSS(z) |
| GEOMEAN | Returns the geometric mean of a dataset. | =GEOMEAN(number1, [number2], …) |
| GROWTH | Returns predicted exponential growth based on existing data. | =GROWTH(known_ys, known_xs, [new_xs], [const]) |
| HARMEAN | Returns the harmonic mean of a dataset. | =HARMEAN(number1, [number2], …) |
| HYPGEOM.DIST | Returns the hypergeometric distribution. | =HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) |
| INTERCEPT | Returns the y-intercept of the regression line. | =INTERCEPT(known_ys, known_xs) |
| KURT | Returns the kurtosis of a dataset. | =KURT(number1, [number2], …) |
| LARGE | Returns the k-th largest value in a dataset. | =LARGE(array, k) |
| LINEST | Returns regression statistics for a linear trend line (array formula). | =LINEST(known_ys, [known_xs], [const], [stats]) |
| LOGEST | Returns exponential curve-fitting statistics (array formula). | =LOGEST(known_ys, [known_xs], [const], [stats]) |
| LOGNORM.DIST | Returns the log-normal distribution. | =LOGNORM.DIST(x, mean, standard_dev, cumulative) |
| LOGNORM.INV | Returns the inverse of the log-normal cumulative distribution. | =LOGNORM.INV(probability, mean, standard_dev) |
| MAX | Returns the largest number in a dataset. | =MAX(number1, [number2], …) |
| MAXA | Returns the largest value including text (as 0) and logical values. | =MAXA(value1, [value2], …) |
| MAXIFS | Returns the maximum value that meets multiple criteria. | =MAXIFS(max_range, criteria_range1, criteria1, …) |
| MEDIAN | Returns the middle number in a dataset. | =MEDIAN(number1, [number2], …) |
| MIN | Returns the smallest number in a dataset. | =MIN(number1, [number2], …) |
| MINA | Returns the smallest value including text (as 0) and logical values. | =MINA(value1, [value2], …) |
| MINIFS | Returns the minimum value that meets multiple criteria. | =MINIFS(min_range, criteria_range1, criteria1, …) |
| MODE.MULT | Returns a vertical array of the most frequent values. | =MODE.MULT(number1, [number2], …) |
| MODE.SNGL | Returns the most frequent number in a dataset. | =MODE.SNGL(number1, [number2], …) |
| NEGBINOM.DIST | Returns the negative binomial distribution. | =NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) |
| NORM.DIST | Returns the normal distribution for a specified mean and standard deviation. | =NORM.DIST(x, mean, standard_dev, cumulative) |
| NORM.INV | Returns the inverse of the normal cumulative distribution. | =NORM.INV(probability, mean, standard_dev) |
| NORM.S.DIST | Returns the standard normal distribution (mean = 0, std dev = 1). | =NORM.S.DIST(z, cumulative) |
| NORM.S.INV | Returns the z-score for a given probability in the standard normal distribution. | =NORM.S.INV(probability) |
| PEARSON | Returns the Pearson correlation coefficient between two datasets. | =PEARSON(array1, array2) |
| PERCENTILE.EXC | Returns the k-th percentile excluding 0 and 1. | =PERCENTILE.EXC(array, k) |
| PERCENTILE.INC | Returns the k-th percentile including 0 and 1. | =PERCENTILE.INC(array, k) |
| PERCENTRANK.EXC | Returns the rank of a value as a percentage (excluding 0 and 1). | =PERCENTRANK.EXC(array, x, [significance]) |
| PERCENTRANK.INC | Returns the rank of a value as a percentage (including 0 and 1). | =PERCENTRANK.INC(array, x, [significance]) |
| PERMUT | Returns the number of permutations without repetitions. | =PERMUT(number, number_chosen) |
| PERMUTATIONA | Returns the number of permutations with repetitions. | =PERMUTATIONA(number, number_chosen) |
| PHI | Returns the value of the standard normal probability density function. | =PHI(x) |
| POISSON.DIST | Returns the Poisson distribution. | =POISSON.DIST(x, mean, cumulative) |
| PROB | Returns the probability that values fall between two limits. | =PROB(x_range, prob_range, lower_limit, [upper_limit]) |
| QUARTILE.EXC | Returns the quartile of a dataset excluding 0 and 1. | =QUARTILE.EXC(array, quart) |
| QUARTILE.INC | Returns the quartile of a dataset including 0 and 1. | =QUARTILE.INC(array, quart) |
| RANK.AVG | Returns the rank of a number, averaging ranks if there are ties. | =RANK.AVG(number, ref, [order]) |
| RANK.EQ | Returns the rank of a number, assigning the same rank to ties. | =RANK.EQ(number, ref, [order]) |
| RSQ | Returns the square of the Pearson correlation coefficient. | =RSQ(known_ys, known_xs) |
| SKEW | Returns the skewness of a dataset. | =SKEW(number1, [number2], …) |
| SKEW.P | Returns the population skewness of a dataset. | =SKEW.P(number1, [number2], …) |
| SLOPE | Returns the slope of the regression line. | =SLOPE(known_ys, known_xs) |
| SMALL | Returns the k-th smallest number in a dataset. | =SMALL(array, k) |
| STANDARDIZE | Returns a normalized value (z-score) based on mean and standard deviation. | =STANDARDIZE(x, mean, standard_dev) |
| STDEV.P | Calculates standard deviation using the entire population. | =STDEV.P(number1, [number2], …) |
| STDEV.S | Estimates standard deviation from a sample. | =STDEV.S(number1, [number2], …) |
| STDEVA | Estimates standard deviation from a sample, including text and logical values. | =STDEVA(value1, [value2], …) |
| STDEVPA | Calculates standard deviation from the population, including text and logicals. | =STDEVPA(value1, [value2], …) |
| STEYX | Returns the standard error of the predicted y-value. | =STEYX(known_ys, known_xs) |
| T.DIST | Returns the left-tailed Student’s t-distribution. | =T.DIST(x, deg_freedom, cumulative) |
| T.DIST.2T | Returns the two-tailed Student’s t-distribution. | =T.DIST.2T(x, deg_freedom) |
| T.DIST.RT | Returns the right-tailed Student’s t-distribution. | =T.DIST.RT(x, deg_freedom) |
| T.INV | Returns the t-value for a one-tailed distribution. | =T.INV(probability, deg_freedom) |
| T.INV.2T | Returns the t-value for a two-tailed distribution. | =T.INV.2T(probability, deg_freedom) |
| TREND | Returns values that follow a linear trend. | =TREND(known_ys, [known_xs], [new_xs], [const]) |
| TRIMMEAN | Returns the mean of the interior portion of a dataset (excludes outliers). | =TRIMMEAN(array, percent) |
| VAR.P | Calculates variance using the entire population. | =VAR.P(number1, [number2], …) |
| VAR.S | Estimates variance from a sample. | =VAR.S(number1, [number2], …) |
| VARA | Estimates sample variance including text and logical values. | =VARA(value1, [value2], …) |
| VARPA | Calculates population variance including text and logical values. | =VARPA(value1, [value2], …) |
| WEIBULL.DIST | Returns the Weibull distribution. | =WEIBULL.DIST(x, alpha, beta, cumulative) |
| Z.TEST | Returns the one-tailed P-value of a z-test. | =Z.TEST(array, x, [sigma]) |