Excel Functions: The 30 Functions You Need to Know

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel is a great way to organize and keep track of your data. 

But Excel is more powerful than that because of these so-called “functions”.

There are more than 100 functions in Excel. But there’s no need to learn them all, especially at once.

That’s why in this article, we’ve included 30 of the most common functions in Excel that you should know about. If you would like to know more about a function, simply follow the links we added for each of them.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 😊

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

What is an Excel function?

By definition, a function is a predefined formula in Excel which does calculations in the order specified by its parameters.

A function has three (3) basic parts:

  • Equals sign (=) – signals the start of a function
  • Function name – a unique identifier which tells Excel which set of formulas you would like to use
  • Parameters or arguments – individual cells or ranges enclosed within parentheses; not all functions need arguments

Now that you know the basics of an Excel function, you’re now ready for the 30 functions you need to know.

Kasper Langmann, Co-founder of Spreadsheeto

For convenience, we have categorized them into 5 themes:

Math, Statistics, and Financial Functions

1. SUM Function

The ‘SUM’ function is one of the first functions a new user learns.

Why? Because learning how to add numbers in Excel is one of the most fundamental skills you need to learn.

Syntax:

=SUM(number1, [number2], …)

Parameters:

  • ‘number1’, ‘number2’, – represents the addends or the values to be added

As you know, addition is an integral part of almost any calculation and task in Excel.

2. SUMIF and SUMIFS Functions

The ‘SUMIF’ and ‘SUMIFS’ functions are variations of the ‘SUM’ function with the addition of the logical operator ‘IF’.

As their name implies, they add the values in a specified range only when the criteria are met.

Kasper Langmann, Co-founder of Spreadsheeto

The differences between the two are in the number of criteria you can specify.

  • ‘SUMIF’: 1 criteria
  • ‘SUMIFS’: Multiple criteria

Syntaxes:

=SUMIF(range, criteria, [sum_range])

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

Parameters:

  • ‘range’ – the range of cells to be evaluated by the criteria
  • ‘criteria’ – a condition that must be met in the ‘range’ parameter
  • ‘sum_range’ – optional; if omitted, the ‘sum_range’ will default to the same cells specified in the ‘range’ parameter
  • ‘criteria_range2’ – optional; a range of cells to be evaluated by ‘criteria2’
  • ‘criteria2’ – a condition that must be met in the ‘criteria_range2’ parameter

These functions are useful when dealing with large data sets and manual calculations are inefficient and impractical.

3. AVERAGE Function

The ‘AVERAGE’ function is one of the basic tasks being done in Excel.

This function calculates the arithmetic mean of a set of numbers or the sum of the values divided by the number of values.

Syntax:

=AVERAGE(number1, [number2]…)

Parameter:

  • ‘number1’, ‘number2’ – the values or cell references you want to average

People use the averages every day, from school grades to statistics. It’s not surprising why the ‘average’ function is one of the most important functions in Excel you need to learn.

4. AVERAGEIF AND AVERAGEIFS Functions

The ‘AVERAGEIF’ and ‘AVERAGEIFS’ functions are like the ‘AVERAGE’ function, but with a more capable punch.

These functions solve the problem of “what if I need to choose a specific subset of numbers to average?”.

Kasper Langmann, Co-founder of Spreadsheeto

The ‘AVERAGEIF’ function gives you the average of the values in a specified range that meets the criteria. If you need to specify multiple criteria, ‘AVERAGEIFS’ is what you use.

Syntaxes:

=AVERAGEIF(range, criteria, [average_range])

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

‘AVERAGEIF’ Parameters:

  • ‘range’ – a range of cells to be evaluated by the criteria
  • ‘criteria’ – condition to be met which determines which cells to average
  • ‘average_range’ – optional; if omitted, the ‘range’ becomes the ‘average_range’

‘AVERAGEIFS’ Parameters:

  • ‘average_range’ – a range of cells to average
  • ‘criteria_range1’, ‘criteria_range2’,.. – the ranges to be evaluated by the criteria
  • ‘criteria1, criteria2’,.. – the conditions to be met by its associated range

These functions shine when you need to get averages from specifics sets in a range.

5. COUNT Function

Basically, the ‘COUNT’ function returns the number of cells that contain numbers.

It may seem rather rudimentary. But in actuality, this function is used in a lot of computations and scenarios.

Syntax:

=COUNT(value1, [value2], …)

Parameter:

  • ‘value1’, ‘value2’ – the items, cell reference, or ranges you want to count numbers

This function is used in many things like counting how many items there are in a list, counting specific cases, and others.

6. COUNTIF and COUNTIFS Functions

But what if you only need to count a specific subset of cells?

Here’s where the ‘COUNTIF’ and ‘COUNTIFS’ functions enter.

The ‘COUNTIF’ function lets you count cells in a range with only a single condition wherein ‘COUNTIFS’ let you count cells with multiple conditions.

Kasper Langmann, Co-founder of Spreadsheeto

Syntaxes:

=COUNTIF(range, criteria)

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

‘COUNTIF’ Parameters:

  • ‘range’ – a range of cells you want to count
  • ‘criteria’ – condition to be evaluated on the range of cells to be counted

‘COUNTIFS’ Parameters

  • ‘criteria_range1’ – a range of cells to evaluate against ‘criteria1’
  • ‘criteria1’ – condition to bet met for ‘criteria_range1’
  • ‘criteria_range2’ – a range of cells to evaluate against the associated criteria
  • ‘criteria2’ – condition to be met for the associated range

These functions are useful in tasks like project management, sales inventory, order fulfillment, and others.

7. SUMPRODUCT Function

The ‘SUMPRODUCT’ function is a powerful and useful function in Excel.

This function returns the sum of the product of two or more arrays.

Syntax:

=SUMPRODUCT(array1, [array2], [array3], …)

Parameter:

  • ‘array1’, ‘array2’, ‘array3’ – the arrays that contain the values to be multiplied and the products added

This is an important Excel function since this is used to calculate weighted averages as well as simplify a lot of tasks like sales inventory.

Kasper Langmann, Co-founder of Spreadsheeto

8. RANDBETWEEN Function

Ever had the need to come up with random values between a specified minimum and maximum values?

The ‘RANDBETWEEN’ function returns a random number within a range you specify.

Syntax:

=RANDBETWEEN(bottom, top)

Parameters:

  • ‘bottom’ – minimum integer to return
  • ‘top’ – maximum integer to return

You don’t know when you’ll need to generate random numbers in Excel. With this function, there’s no need for you to get random numbers from websites or other tools.

9. INT Function

The ‘INT’ function is a rather simple Excel function with a single purpose:

Rounding a value down to the nearest integer.

Syntax:

=INT(number)

Parameter:

  • ‘number’ – represents the value or cell reference with the value you want to round down

Mostly, the ‘INT’ function is used to get the integer portion of a number.

Kasper Langmann, Co-founder of Spreadsheeto

One of the most common uses of this function is getting the age using the date of birth along with ‘TODAY’ and ‘YEARFRAC’ functions.

10. PMT Function

The ‘PMT’ function is one of Excel’s most useful financial functions.

In simple terms, this function, which stands for “Payment”, calculates the period loan payment based on the following:

  • Constant interest rate
  • Number of payments
  • Loan amount

Syntax:

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

Parameters:

  • ‘rate’ – interest rate of the loan either in percentage or decimal number form
  • ‘nper’ – number of payments or installments
  • ‘pv’ – principal or present value; the total loan amount
  • ‘fv’ – optional; represents the balance you like to remain after making the last payment; if omitted, the default value is zero (0)
  • ‘type’ – annuity type or when payments are due (beginning or end of period)

This function may look a bit advanced but it’s actually easy to learn and use.

With combinations of different functions, you’ll be able to create an automated loan payment calculator in Excel.

Text Functions

12. CONCATENATE Function

To concatenate is to join or link things together.

That’s what the ‘CONCATENATE’ function does — combine the contents of different cells into a single cell.

This function is a simpler version of Excel’s ‘TEXTJOIN’ function.

Syntax:

=CONCATENATE(text1, [text2], …)

Parameter:

  • ‘text1’, ‘text2’ – the text value, number, or cell reference to join (up to 255 items with a total of 8,192 characters)

Learning how to use this function is can save you a great deal of time. There’s no need to re-type every single word you need on the sheet.

Kasper Langmann, Co-founder of Spreadsheeto

13. LEN Function

The ‘LEN’ function is actually one of the most useful ‘little’ function in Excel.

All this function does is return the length (number of characters) of a string in a cell.

Syntax:

=LEN(text)

Parameter:

  • ‘text’ – the string you want to count the number of characters

This function, especially when used with the ‘LEFT’, ‘RIGHT’, ‘MID’ and ‘TRIM’ functions, raises your data parsing skills and make a more efficient solution.

14. TEXT Function

The ‘TEXT’ function is one of Excel’s specialized functions you can use.

What this function does it simple: convert a number to text in a specified format.

Kasper Langmann, Co-founder of Spreadsheeto

Syntax:

=TEXT(value, format_text)

Parameters:

  • ‘value’ – number or cell reference with the numerical value you want to convert into text
  • ‘format_text’ – the format you want to output the text

This function is commonly used in dealing with dates. In addition, this is also a great function to use to prepare data for pivot tables.

Date and Time Functions

15. TODAY Function

If you need to enter today’s date, all you have to do is use the ‘TODAY’ function.

As one of Excel’s time functions, this function returns the serial number of the current date.

A date serial number is what Excel uses for date and time calculations.

Kasper Langmann, Co-founder of Spreadsheeto

Syntax:

=TODAY()

Because this function gets you the current date, there’s no need to provide any parameters.

The ‘TODAY’ function is one of the volatile functions (those that need to recalculate again and again). Once you use this function, it automatically updates every time you open the workbook into the current date.

16. NOW Function

You can think of the ‘NOW’ function as adding the ‘TODAY’ function with the current time.

It returns the serial number representing the current date and time.

Syntax:

=NOW()

Like the previous function, this function also doesn’t take any parameters.

When using this function, take caution as this is also one of the volatile functions.

17. DATE Function

Compared with the above functions, the ‘DATE’ function doesn’t update every time you open your workbook.

What this function returns is the serial number of a date you specify.

Syntax:

=DATE(year, month, day)

Parameters:

  • ‘year’ – the year of the date
  • ‘month’ – the month of the date
  • ‘day’ – the day of the date

What you need to remember is that some of Excel’s functions don’t accept dates as inputs or text values. If you need to enter a date, it’s best to use the ‘DATE’ function.

18. HOUR, MINUTE, and SECOND Functions

To elaborate, these functions take time as an input and convert it into individual hours, minutes, and seconds.

Kasper Langmann, Co-founder of Spreadsheeto

Syntaxes:

=HOUR(serial_number)

=MINUTE(serial_number)

=SECOND(serial_number)

Parameter:

  • ‘serial_number’ – the time value or cell reference which has the value for which the hour, minute, or second that needs to be determined.

These functions are useful when converting time to integers. However, you can use them to convert time values to decimals. You need to use either the arithmetic method or the ‘CONVERT’ function.

Logical Functions

19. IF and IFS Functions

The ‘IF’ and ‘IFS’ functions are two of the most popular functions in Excel.

So what are they and what do they do?

For a start, they are called logical operators.

You use ‘IF’ if you want to evaluate a condition and get a response of either TRUE or FALSE. When you have multiple conditions you like to test, ‘IFS’ is what you use.

Kasper Langmann, Co-founder of Spreadsheeto

Syntaxes:

=IF(logical_test, [value_if_true], [value_if_false])

=IFS(Condition1, Value1, [Condition2, Value2],…[Condition127, Value127])

‘IF’ Parameters:

  • ‘logical_test’ – condition you want to test; can be a cell reference, the output of a formula, or manually entered
  • ‘value_if_true’ – optional; the value returned if the condition is TRUE
  • ‘value_if_false’ – optional; the value returned if the condition is FALSE

‘IFS’ Parameters

  • ‘Condition1’ – first condition to check
  • ‘Value1’ – value to return if the first condition is TRUE
  • ‘Condition2’….’Condition127’ – optional; additional conditions to check; up to 127 conditions
  • ‘Value2’….’Value127’ – optional; value(s) to return if the associated conditions are true also up to 127 values

The ‘IFS’ function is like a new variation of the ‘IF’ function to simplify nested ‘IF’ formulas.

Consider these functions as the top logical functions you should learn. Most advanced tasks become simpler and easier to do with the help of ‘IF’ and ‘IFS’ functions.

20. AND and OR Functions

The ‘AND and OR’ functions are the kind of functions that resolve logical statements and display specific values based on the results.

Both functions are related and are used to check multiple conditions.

Syntaxes:

=AND(logical1, [logical2],…)

=OR(logical1, [logical2],…)

Parameters:

  • ‘logical1’ – first condition to evaluate for TRUE or FALSE
  • ‘logical2’ – optional; second condition to evaluate

Usually, these functions are used with the ‘IF’ function to test a condition and specify a value to return depending on the results.

Along with ‘IF’, the ‘AND’ and ‘OR’ functions are must-learn logical functions in Excel if you want to make your tasks easier and more convenient.

Kasper Langmann, Co-founder of Spreadsheeto

21. IFERROR Function

The ‘IFERROR’ function can literally save your tasks from erroneous results due to errors.

To simplify, this function allows you to specify a value to return when the value you specified encountered an error.

Syntax:

=IFERROR(value, value_if_error)

Parameters:

  • ‘value’ – represents the argument to be checked for error;
  • ‘value_if_error’ – value to return when there’s an error

This function can evaluate the following errors:

  • #N/A
  • #REF!
  • #DIV/0!
  • #VALUE!
  • #NUM!
  • #NAME?
  • #NULL!

Of course, if there’s no error, the result from formula or argument inside the ‘value’ is the return value.

When used to check a formula, it may make the formula look a bit complicated. But in reality, it makes troubleshooting more efficient and effective.

Using this formula no way degrades the argument or formula used.

Kasper Langmann, Co-founder of Spreadsheeto

22. TRUE and FALSE Functions

Formally, the ‘TRUE’ and ‘FALSE’ functions are called Boolean logic.

Boolean logic is derived from the binary logic of 1 and 0 where TRUE is 1 (one) and FALSE is 0 (zero).

As their name implies, the functions return the logical values of TRUE and FALSE.

Syntaxes:

=TRUE()

=FALSE()

Since these functions return the values TRUE and FALSE, there’s really no need for parameters.

In truth, there’s no need to use these functions in normal circumstances.

Their value lies in their binary equivalent, one (1) and zero (0).

Because of that, these functions are usually integrated into larger formulas to make calculations.

23. NOT Function

The ‘NOT’ function is like a trick function.

Basically, this function returns the opposite logical value TRUE is FALSE and FALSE is TRUE.

Syntax:

=NOT(logical)

The parameter ‘logical’ is the value or expression evaluated by the function.

You have to be careful with this function and only use it when you want to reverse the value of a logical argument.

Kasper Langmann, Co-founder of Spreadsheeto

Lookup and Reference Functions

24. COLUMN and COLUMNS Functions

Although both functions have something to do with columns, they don’t really have the power to do anything to the columns.

The ‘COLUMN’ function is what you use to get the column number in Excel.

Basically, you tell the function the cells you’re interested in and it will give you their column numbers.

Kasper Langmann, Co-founder of Spreadsheeto

‘COLUMN’ Syntax:

=COLUMN([reference])

Parameter:

  • ‘reference’ – optional; refers to a cell or range of cells; if omitted, the function returns the column number of the cell where the formula with this function is located

On the other hand, the ‘COLUMNS’ function doesn’t return the column number. Instead, it returns the total number of columns in a specified range.

‘COLUMNS’ Syntax:

=COLUMNS(array)

Parameter:

  • ‘array’ – array, array formula, reference to a range of cells

These functions are useful when you need to use column numbers or the number of columns in your calculations.

At first, these functions may not feel that useful. But keep an eye out in times when you need to look for data in a range of columns, these functions would prove more useful than you think.

25. ROW and ROWS Functions

The ‘ROW’ and ‘ROWS’ functions are the row-equivalent of the previous functions.

The ‘ROW’ function returns the row number of a specific cell or range (top-most row in the range).

‘ROW’ Syntax:

=ROW([reference])

Parameter:

  • ‘reference’ – optional; refers to a cell or range of cells; if omitted, the function returns the row number of the cell where the formula with this function is located

Like its column-equivalent, the ‘ROWS’ function returns the total number of rows in a given range or array.

‘ROWS’ Syntax:

=ROWS(array)

Parameter:

  • ‘array’ – array, array formula, reference to a range of cells

Both functions, when combined with other functions, can do lots of useful things. For one, you can use them to randomly select a row or rows in a given range.

Kasper Langmann, Co-founder of Spreadsheeto

26. VLOOKUP Function

The ‘VLOOKUP’ function is one of the most well-known functions in Excel.

Most jobs that require Excel skills have interview questions related to this function.

Questions such as:

  • What does a ‘VLOOKUP’ do?
  • What’s the syntax of ‘VLOOKUP’?
  • Are there any disadvantages to this function?

Simply, this “vertical lookup” function looks for a specified value in a column and returns a value in the same row when it finds the column with the specified value.

Kasper Langmann, Co-founder of Spreadsheeto

Syntax:

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

Parameters:

  • ‘lookup_value’ – what you’re looking for
  • ‘table_array’ – where to look
  • ‘col_index_num’ – what you want to know
  • ‘range_lookup’ – optional; setting for returning an exact match or approximate match; if omitted, the default is to return the approximate match

Up to this day, the power of this function, along with its ease of use is unmatched in Excel. This is certainly one of the functions you should master.

27. HLOOKUP Function

The ‘HLOOKUP’ function is the horizontal equivalent of the ‘VLOOKUP’ function.

This is as powerful as the vertical lookup function. However, ‘HLOOKUP’ is less often used as most data is arranged vertically.

This function works by letting you look for a data in a row and when that data is found, you can fetch a value by going down the column of that row.

Kasper Langmann, Co-founder of Spreadsheeto

Syntax:

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

Parameters:

  • ‘lookup_value’ – what you’re looking for
  • ‘table_array’ – where to look
  • ‘row_index_num’ – what you want to know
  • ‘range_lookup’ – optional; setting for returning an exact match or approximate match; if omitted, the default is to return the approximate match

The ‘HLOOKUP’ function, especially when paired with other lookup and reference functions, is one of the most useful advanced functions in Excel.

28. INDEX Function

The ‘INDEX’ function is a handy tool when looking for specific data in large sets.

Basically speaking, this function returns a value from a table based on a specified row and column number.

Syntax:

=INDEX (array, row_num, [col_num])

=INDEX (array, row_num, [col_num], [area_num])

The ‘INDEX’ function has 2 syntaxes. The first one is used in most cases.

Parameters:

  • ‘array’ – a range of cells or array
  • ‘row_number’ – row number of the value to get
  • ‘col_number’ – optional; column number of the value to get
  • ‘area_num’ – optional; if the array has lots of arguments, the value in this parameter is the basis of reference selection from the ranges

This function is one of the lookup functions you should know.

29. MATCH Function

The ‘MATCH’ function is designed so you won’t have to count manually when you need to get the position of a value in range.

This function returns the number of the position of the lookup value.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Parameters:

  • ‘lookup_value’ – the value you’re looking for a match
  • ‘lookup_array’ – the range of cells you want to search in
  • ‘match_type’ – optional; how Excel looks for the matching value (exact match, largest value, smallest value)

You should take note that this function does not return the value of the lookup but the actual position of the lookup in the ‘lookup_array’.

In most cases, ‘MATCH’ is used in tandem with the ‘INDEX’ function.

Kasper Langmann, Co-founder of Spreadsheeto

30. INDIRECT Function

The ‘INDIRECT’ function is a fascinating function in Excel.

In essence, this function returns the reference specified by the text string.

Syntax:

=INDIRECT(ref_text, [a1])

Parameters:

  • ‘ref_text’ – text string with the reference to a cell or range
  • ‘a1’ – logical value

This function is one of the volatile functions

Every time you open the workbook, Excel recalculates the function which might slow down your workbook.

Wrapping things up…

When you’re still trying to learn the basics of Excel, there’s no need for you to know all the functions.

The functions we listed above are enough to get you going and provide you with advanced uses for your tasks.

Kasper Langmann, Co-founder of Spreadsheeto