What are Excel functions? An overview of the best functions in Excel

Contents

This article contains an overview of the most important Excel functions.The functions listed below can be used on their own or in combination with each other. We’ve provided links to the most popular ones, so you can read more about what they are used for and see examples of how to use them.

Excel with Microsoft 365 Business and IONOS!

The Office you know, only better

Up to 50 GB Exchange email account
Outlook Web App and collaboration tools
Expert support & setup service

Note

Read our article on Excel keyboard shortcuts and find out how to work more efficiently and up your Excel proficiency.

What are Excel functions and which are the best ones to use?

Functions are built-in expressions in Excel that apply a predefined algorithm to arguments (specific values) and return a result. In addition to mathematical calculations, functions can perform logical comparisons, unit conversions and more. Excel functions can be assigned to various categories, and most calculations can be automated partially or completely using Excel functions. Functions are useful tools that are an essential part of Excel, and they can boost your efficiency when working with the spreadsheet program. Below, we’ve put together a list of the 10 most important Excel functions:

SUM(): The SUM function adds together values.
LOOKUP(): The LOOKUP function searches for a value in a row or column. If the value is found, the function returns the value at the same position in another row or column.
HLOOKUP(): The Excel HLOOKUP function searches for a value in a matrix and returns the value at the same position in a column specified by the user.
IF(): The IF function checks values against a condition. If the condition is met, it outputs one of two results. If the value doesn’t fulfil the condition, it outputs a different result.
DATE(): The Excel DATE function combines three values in order to create a date.
DAYS(): This function returns the number of days between two calendar dates.
CHOOSE(): This function returns a value at a specific position in a list.
MATCH(): The MATCH function looks for a value within a cell range and returns the relative position of the value.
FIND(): The Excel FIND function searches for a string within another string. If found, its location within the first string is returned.
INDEX(): The INDEX function retrieves a value from a specific index that can be located in a row, column or matrix.

Note

Excel functions are often confused with Excel formulas, however, there is a difference between the two. Formulas are calculations that contain functions, operators, references and/or constants. While functions can be used in formulas, a formula does not have to contain a function in order to be complete.

An example of a formula is =SUM(A1:A5)*5. In this example, the SUM function is one part of the formula and contains the reference A1:A5. The result of the calculation from the function is then multiplied by the constant 5 using the multiplication operator *.

Database functions

Function	Syntax
Excel DCOUNT function	`=DCOUNT(database, field, criteria)`
Excel DCOUNTA function	`=DCOUNTA(database, field, criteria)`
Excel DGET function	`=DGET(database, field, criteria)`
Excel DMAX function	`=DMAX(database, field, criteria)`
Excel DMIN function	`=DMIN(database, field, criteria)`
Excel DAVERAGE function	`=DAVERAGE(database, field, criteria)`
Excel DPRODUCT function	`=DPRODUCT(database, field, criteria)`
Excel DSUM function	`=DSUM(database, field, criteria)`

Excel functions for date and time

Function	Syntax
Excel WORKDAY function	`=WORKDAY(start_date, days, [holidays])`
Excel WORKDAY.INTL function	`=WORKDAY.INTL(start_date, days, [weekend], [holidays])`
Excel YEARFRAC function	`=YEARFRAC(start_date, end_date, [basis])`
Excel DATEDIF function	`=DATEDIF(start_date, end_date, unit)`
Excel DATE function	`=DATE(year, month, day)`
Excel DATEVALUE function	`=DATEVALUE(date_text)`
Excel EDATE function	`=EDATE(start_date, months)`
Excel TODAY function	`=TODAY()`
Excel ISOWEEKNUM function	`=ISOWEEKNUM(date)`
Excel YEAR function	`=YEAR(serial_number)`
Excel NOW function	`=NOW()`
Excel WEEKNUM function	`=WEEKNUM(serial_number, [return_type])`
Excel MINUTE function	`=MINUTE(serial_number)`
Excel MONTH function	`=MONTH(serial_number)`
Excel EOMONTH function	`=EOMONTH(start_date, months)`
Excel NETWORKDAYS function	`=NETWORKDAYS(start_date, end_date, [holidays])`
Excel NETWORKDAYS.INTL function	`=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])`
Excel SECOND function	`=SECOND(serial_number)`
Excel HOUR function	`=HOUR(serial_number)`
Excel DAY function	`=DAY(serial_number)`
Excel DAYS function	`=DAYS(end_date, start_date)`
Excel DAYS360 function	`=DAYS360(start_date, end_date, [method])`
Excel WEEKDAY function	`=WEEKDAY(serial_number, [return_type])`
Excel TIME function	`=TIME(hour, minute, second)`
Excel TIMEVALUE function	`=TIMEVALUE(time_text)`

Dynamic array functions

Function	Syntax
Excel UNIQUE function	`=UNIQUE(array, [by_col], [exactly_once])`
Excel FILTER function	`=FILTER(array, include, [if_empty])`
Excel SEQUENCE function	`=SEQUENCE(rows, [columns], [start], [step])`
Excel SORT function	`=SORT(array, [sort_index], [sort_order], [by_col])`
Excel SORTBY function	`=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)`
Excel RANDARRAY function	`=RANDARRAY([rows], [columns], [min], [max], [whole_number])`

Financial functions

Function	Syntax
Excel PV function	`=PV(rate, nper, pmt, [fv], [type])`
Excel PPMT function	`=PPMT(rate, per, nper, pv, [fv], [type])`
Excel CUMPRINC function	`=CUMPRINC(rate, nper, pv, start_period, end_period, type)`
Excel CUMIPMT function	`=CUMIPMT(rate, nper, pv, start_period, end_period, type)`
Excel PMT function	`=PMT(rate, nper, pv, [fv], [type])`
Excel RATE function	`=RATE(nper, pmt, pv, [fv], [type], [guess])`
Excel IPMT function	`=IPMT(rate, per, nper, pv, [fv], [type])`
Excel FV function	`=FV(rate, nper, pmt, [pv], [type])`
Excel NPER function	`=NPER(rate, pmt, pv, [fv], [type])`

Tip

When using Excel tables to process sensitive financial information, data protection and security are essential. With HiDrive cloud storage from IONOS, you can store your Excel workbooks on secure servers. Not only can you rest assured that your data is safe, you’ll also be able to access your files from anywhere!

Functions for information

Function	Syntax
Excel SHEETS function	`=SHEETS(reference)`
Excel SHEET function	`=SHEET(value)`
Excel ERROR.TYPE function	`=ERROR.TYPE(error_val)`
Excel INFO function	`=INFO(type_text)`
Excel ISREF function	`=ISREF(value)`
Excel ISERR function	`=ISERR(value)`
Excel ISERROR function	`=ISERROR(value)`
Excel ISFORMULA function	`=ISFORMULA(reference)`
Excel ISEVEN function	`=ISEVEN(number)`
Excel ISNONTEXT function	`=ISNONTEXT(value)`
Excel ISBLANK function	`=ISBLANK(value)`
Excel ISLOGICAL function	`=ISLOGICAL(value)`
Excel ISNA function	`=ISNA(value)`
Excel ISTEXT function	`=ISTEXT(value)`
Excel ISODD function	`=ISODD(number)`
Excel ISNUMBER function	`=ISNUMBER(value)`
Excel N function	`=N(value)`
Excel NA function	`=NA()`
Excel TYPE function	`=TYPE(value)`
Excel CELL function	`=CELL(info_type, [reference])`

Construction function

Function	Syntax
Excel CONVERT function	`=CONVERT(number, from_unit, to_unit)`

Logical functions

Function	Syntax
Excel SWITCH function	`=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)`
Excel FALSE function	`=FALSE()`
Excel OR function, Excel AND function & Excel NOT function	`=OR()` `=AND()` `=NOT()`
Excel TRUE function	`=TRUE()`
Excel IF function	`=IF(logical_test, value_if_true, [value_if_false])`
Excel IFERROR function	`=IFERROR(value, value_if_error)`
Excel IFNA function	`=IFNA(value, value_if_na)`
Excel IFS function	`=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)`
Excel XR function	`=XOR(logical1, [logical2],…)`

Math and trigonometry functions

Function	Syntax
Excel ROUNDDOWN function	`=ROUNDDOWN(number, num_digits)`
Excel ABS function	`=ABS(number)`
Excel AGGREGATE function	`=AGGREGATE(function_num, options, ref1, [ref2], …)`
Excel ARABIC function	`=ARABIC(Text)`
Excel ROUNDUP function	`=ROUNDUP(number, num_digits)`
Excel RADIANS function	`=RADIANS(angle)`
Excel COS function	`=COS(number)`
Excel EXP function	`=EXP(number)`
Excel FACT function	`=FACT(number)`
Excel INT function	`=INT(number)`
Excel EVEN function	`=EVEN (number)`
Excel GCD function	`=GCD(number1, [number2], ...)`
Excel DEGREES function	`=DEGREES(angle)`
Excel LCM function	`=LCM(number1, [number2], ...)`
Excel TRUNC function	`=TRUNC(number, [num_digits])`
Excel LOG function	`=LOG(number, [base])`
Excel LOG10 function	`=LOG10(number)`
Excel CEILING function	`=CEILING(number, significance)`
Excel PI function	`=PI()`
Excel POWER function	`=POWER(number, power)`
Excel MOD function	`=MOD(number, divisor)`
Excel ROMAN function	`=ROMAN(number, [form])`
Excel ROUND function	`=ROUND(number, num_digits)`
Excel SIN function	`=SIN(number)`
Excel SUM function	`=SUM(number1, [number2],...)`
Excel SUMPRODUCT function	`=SUMPRODUCT(array1, [array2], [array3], ...)`
Excel TAN function	`=TAN(number)`
Excel SUBTOTAL function	`=SUBTOTAL(function_num, ref1, [ref2],...)`
Excel ODD function	`=ODD(number)`
Excel FLOOR function	`=FLOOR(number, significance)`
Excel SIGN function	`=SIGN(number)`
Excel MROUND function	`=MROUND(number, multiple)`
Excel SQRT function	`=SQRT(number)`
Excel RANDBETWEEN function	`=RANDBETWEEN(bottom, top)`
Excel RAND function	`=RAND()`

PIVOT function

Function	Syntax
Excel GETPIVOTDATA function	`(=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)`

Statistical functions

Function	Syntax
Excel COUNT function	`=COUNT(value1, [value2], ...)`
Excel COUNTA function	`=COUNTA(value1, [value2], ...)`
Excel COUNTBLANK function	`=COUNTBLANK(range)`
Excel FREQUENCY function	`=FREQUENCY(data_array, bins_array)`
Excel LARGE function	`=LARGE(array, k)`
Excel SMALL function	`=SMALL(array, k)`
Excel COMBIN function	`=COMBIN(number, number_chosen)`
Excel MAX function	`=MAX(number1, [number2], ...)`
Excel MAXA function	`=MAXA(value1,[value2],...)`
Excel MAXIFS function	`=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`
Excel MEDIAN function	`=MEDIAN(number1, [number2], ...)`
Excel MIN function	`=MIN(number1, [number2], ...)`
Excel MINA function	`=MINA(number1, [number2], ...)`
Excel MINIFS function	`=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`
Excel AVERAGE function	`=AVERAGE(number1, [number2], ...)`
Excel AVERAGEA function	`=AVERAGEA(value1, [value2], ...)`
Excel AVERAGEIF function	`=AVERAGEIF(range, criteria, [average_range])`
Excel AVERAGEIFS function	`=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`
Excel MODE function	`=MODE(number1,[number2],...)`
Excel QUARTILE function	`=QUARTILE(array,quart)`
Excel RANK function	`=RANK(number,ref,[order])`
Excel STDEV function	`=STDEV(number1,[number2],...)`
Excel STDEV.P function	`=STDEV.P(number1,[number2],...)`
Excel STDEV.S function	`=STDEV.S(number1,[number2],...)`
Excel STDEVP function	`=STDEVP(number1,[number2],...)`
Excel SUMIF function	`=SUMIF(range, criteria, [sum_range])`
Excel SUMIFS function	`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`
Excel PERMUT function	`=PERMUT(number, number_chosen)`
Excel COUNTIF function	`=COUNTIF(range, criteria)`
Excel COUNTIFS function	`=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)`

Text functions

Function	Syntax
Excel CODE function	`=CODE(Text)`
Excel DOLLAR function	`=DOLLAR(number, [decimals])`
Excel REPLACE function	`=REPLACE(old_text, start_num, num_chars, new_text)`
Excel FIND function	`=FIND(find_text, within_text, [start_num])`
Excel TRIM function	`=TRIM(text)`
Excel UPPER function	`=UPPER(text)`
Excel PROPER function	`=PROPER(text)`
Excel EXACT function	`=EXACT(text1, text2)`
Excel LOWER function	`=LOWER(text)`
Excel LEN function	`=LEN(text)`
Excel LEFT function	`=LEFT(text, [num_chars])`
Excel RIGHT function	`=RIGHT(text,[num_chars])`
Excel CLEAN function	`=CLEAN(text)`
Excel SEARCH function	`=SEARCH(find_text, within_text, start_num)`
Excel MID function	`=MID(text, start_num, num_chars)`
Excel TEXT function	`=TEXT(Value you want to format, "Format code you want to apply")`
Excel CONCATENATE function	`=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)`
Excel CONCAT function	`=CONCAT(text1, [text2],…)`
Excel SUBSTITUTE function	`=SUBSTITUTE(text, old_text, new_text, [instance_num])`
Excel VALUE function	`=VALUE(text)`
Excel REPT function	`=REPT(text, number_times)`
Excel CHAR function	`=CHAR(number)`

Lookup functions

Function	Syntax
Excel ADDRESS function	`=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])`
Excel OFFSET function	`=OFFSET(reference, rows, cols, [height], [width])`
Excel AREAS function	`=AREAS(reference)`
Excel FORMULATEXT function	`=FORMULATEXT(reference)`
Excel HYPERLINK function	`=HYPERLINK(link_location, [friendly_name])`
Excel INDEX function	`=INDEX(array, row_num, [column_num])`
Excel INDIRECT function	`=INDIRECT(ref_text, [a1])`
Excel MMULT function	`=MMULT(array1, array2)`
Excel TRANSPOSE function	`=TRANSPOSE(array)`
Excel COLUMN function	`=COLUMN([reference])`
Excel COLUMNS function	`=COLUMNS([reference])`
Excel VLOOKUP function	`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`
Excel MATCH function	`=MATCH(lookup_value, lookup_array, [match_type])`
Excel LOOKUP function	`=LOOKUP(lookup_value, lookup_vector, [result_vector])`
Excel CHOOSE function	`=CHOOSE(index_num, value1, [value2], ...)`
Excel HLOOKUP function	`=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`
Excel ROW function	`=ROW([reference])`
Excel ROWS function	`=ROWS(array)`

Where can I find more articles on Excel?

Now that you’ve had a chance to explore Excel functions, you may be curious as to how they work with other aspects of Excel. We’ve compiled a list of articles that look at different features of Excel and how they can be used in everyday life. Each article will help you get started with a new Excel topic. Whether you’re in need of a way to represent data or could do with some more guidance on how to use Excel properly, the list below can help you on your way. Enjoy!

Microsoft 365 Business