**Excel Functions: The** **30 Functions**** You Need to Know**

**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 functionsin Excel that you should know about. If you would like to know more about a function, simply follow thelinkswe added for each of them.

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.*

**Table of Content**

**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.

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.

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?”.

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 conditionwherein ‘COUNTIFS’ let youcount cells with multiple conditions.

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 averagesas well assimplify a lot of tasks like sales inventory.

**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.

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**

**11. FIND and SEARCH Functions**

The ‘FIND’ and ‘SEARCH’ functions, as you might have guessed, are actually similar.

Both return a

number that represents the starting position of the string you are looking for in another string.

But they’re not the same. Here are two (2) notable differences they have against each other:

- You can use wildcards (asterisk, question mark, and tilde) with ‘SEARCH’ but not with ‘FIND’.
- ‘FIND’ is
**case sensitive**while ‘SEARCH’ is not.

Syntaxes:

**=FIND(find_text, within_text, [start_num])**

**=SEARCH(find_text, within_text, [start_num])**

Parameters:

**‘find_text’**– text/string you’re looking for**‘within_text’**– the text or location you want to look in**‘start_num’**– optional; represents the position you want to start looking; if omitted, the search starts from the beginning

The secret to knowing when to use either lies in your *analytical *and *creative thinking*.

**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’sno need to re-type every single wordyou need on the sheet.

**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.

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 numberis what Excel uses fordate and time calculations.

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**

The ‘HOUR’, ‘MINUTE’, and ‘SECOND’ functions **retrieve parts of time data**.

To elaborate, these functions take

time as an inputandconvert it into individual hours, minutes, and seconds.

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 conditionandget a response of either TRUE or FALSE. When you havemultiple conditionsyou like to test, ‘IFS’ is what you use.

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 functionsin Excel if you want to make your taskseasierandmore convenient.

**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 formulaused.

**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.

**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 inand it will give you theircolumn numbers.

‘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 rowsin a given range.

**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 valuein a column andreturns a valuein the same row when it finds the column with the specified value.

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 rowand when that data is found, you canfetch a valueby going down the column of that row.

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 lookupbut theactual position of the lookupin the ‘lookup_array’.In most cases, ‘MATCH’ is used in tandem with the ‘INDEX’ function.

**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.