• Online sales

An overview of Excel formulas and functions

Excel can seem intimidating. However, knowing the right Excel formulas and functions will save you lots of time in the office or even at home if you’re a keen organizer. In the following, we present an overview of the most important Excel formulas that you will come into contact with regularly when using Microsoft Excel.

In addition, we’ve listed ways in which you can use these Excel functions in practice. Our list of useful articles will help you use Excel more effectively and efficiently. The listed formulas and functions can be used in Excel either as standalone applications or in combination with each other. If you want to find out more about a function, we’ve provided links to the most popular ones, to help you get started. Each function is listed in its category, alongside its syntax, provided by Microsoft’s Support page.

Tip

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

Database functions

  • 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)

Date and time Excel functions 

=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])
=DATEDIF(start_date,end_date,unit)
=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()
=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 formulas

  • 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

  • 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])

Information functions

  • 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 ISEVENFunction
=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])

Logical functions

  • Excel ERSTERWERT 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()
=OR()
=AND()
=NOT()
  • Excel TRUE Function
=TRUE()
=IF(logical_test, value_if_true, [value_if_false])
=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

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)
=ROUNDUP(number, num_digits)
=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)
=MOD(number, divisor)
  • Excel ROMAN Function
=ROMAN(number, [form])
=ROUND(number, num_digits)
  • Excel SIN Function
=SIN(number)
=SUM(number1,[number2],...)
=SUMPRODUCT(array1, [array2], [array3], ...)
  • Excel TAN Function
=TAN(number)
=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()
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

Statistical functions 

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 STDEVPFunction
=STDEVP(number1,[number2],...)
=SUMIF(range, criteria, [sum_range])
  • Excel SUMIFS Function
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • Excel PERMUT Function
=PERMUT(number, number_chosen)
=COUNTIF(range, criteria)
  • Excel COUNTIFS Function
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Text functions

  • Excel CODE Function
=CODE(Text)
  • Excel DOLLAR Function
=DOLLAR(number, [decimals])
  • Excel REPLACE Function
=REPLACE(old_text, start_num, num_chars, new_text)
=FIND(find_text, within_text, [start_num])
  • Excel TRIM Function
=TRIM(text)
  • Excel UPPER Function
=UPPER(text)
  • Excel PROPERFunction
=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)
=SEARCH(find_text,within_text,start_num)
  • Excel MID Function
=MID(text, start_num, num_chars)
=TEXT(Value you want to format, "Format code you want to apply")
  • Excel CONCAT Function
=CONCAT(text1, [text2],…)
  • Excel TEXTVERKETTEN Function
=TEXTVERKETTEN(Trennzeichen; Leer_ignorieren; Text1; [Text2]; [Text3]; ...)
=TEXTJOIN(delimiter, ignore_empty, 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

  • 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])
=INDEX(array, row_num, [column_num])
=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])
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) ***CODE***
=MATCH(lookup_value, lookup_array, [match_type])

=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)

“More” functions

The following function is categorized under “more functions” by Microsoft:

  • Excel Convert Function
=CONVERT(number,from_unit,to_unit)

Uses for Excel

Now you’ve had a chance to see the many Excel functions, you may be curious as to what their uses are. We’ve got a list of common ways Excel is used in everyday life. Each article will help you get started, whether you’re in need of a way to represent data, or could do with some more guidance on how to use Excel properly. Enjoy!

  • Online sales

Related articles

What are Excel formulas? An explanation and overview

What are Excel formulas? An explanation and overview

Excel can be used to create a variety of tables for task planning, diagrams and presentations. With the help of various Excel formulas, you can perform calculations automatically and in a short amount of time. Excel offers everything you need, from simple basic calculations to complex formulas. Find out how to create Excel formulas here.

What are Excel formulas? An explanation and overview
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.