Excel Functions: The 30 Functions You Need to Know

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

Excel is a nifty mode to organize and keep track of your information.

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

There are more 100 functions in Excel. But there's no demand to learn them all, particularly at in one case.

That'southward why in this article, we've included 30 of the almost common functions in Excel that you should know nearly. If you would like to know more than about a office, just follow the links we added for each of them.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No trouble, you tin can yet 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) bones parts:

  • Equals sign (=) – signals the outset of a role
  • Part name – a unique identifier which tells Excel which set of formulas you would similar to use
  • Parameters or arguments – private cells or ranges enclosed within parentheses; not all functions demand arguments

Now that you lot know the basics of an Excel office, you're at present prepare for the thirty functions y'all demand to know.

Kasper Langmann, Co-founder of Spreadsheeto

For convenience, we have categorized them into 5 themes:

Math, Statistics, and Fiscal Functions

1. SUM Function

The 'SUM' role is 1 of the first functions a new user learns.

Why? Because learning how to add numbers in Excel is one of the most primal skills y'all demand to larn .

Syntax:

=SUM(number1, [number2], …)

Parameters:

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

Every bit you know, add-on is an integral role of nearly any adding and task in Excel.

two. SUMIF and SUMIFS Functions

Every bit their name implies, they add the values in a specified range merely when the criteria are met .

Kasper Langmann, Co-founder of Spreadsheeto

The differences betwixt the two are in the number of criteria you tin specify.

  • 'SUMIF': one 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 past 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 aforementioned cells specified in the 'range' parameter
  • 'criteria_range2' – optional; a range of cells to exist evaluated past '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 'Boilerplate' role is i of the basic tasks being done in Excel.

This function calculates the arithmetics mean of a gear up 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 employ the averages every day, from schoolhouse grades to statistics . Information technology's not surprising why the 'average' role is one of the most important functions in Excel you demand to learn.

4. AVERAGEIF AND AVERAGEIFS Functions

These functions solve the trouble of "what if I demand to cull a specific subset of numbers to average?" .

Kasper Langmann, Co-founder of Spreadsheeto

The 'AVERAGEIF' office 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 past 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 incorporate 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 role is used in many things similar counting how many items there are in a listing , counting specific cases , and others.

vi. COUNTIF and COUNTIFS Functions

The 'COUNTIF' function lets you count cells in a range with only a single condition wherein 'COUNTIFS' permit you count cells with multiple weather .

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 exist 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 , gild fulfillment , and others.

7. SUMPRODUCT Function

The 'SUMPRODUCT' role is a powerful and useful part 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 incorporate the values to be multiplied and the products added

This is an of import Excel function since this is used to summate weighted averages as well every bit simplify a lot of tasks like sales inventory .

Kasper Langmann, Co-founder of Spreadsheeto

8. RANDBETWEEN Function

Always had the need to come upwards with random values between a specified minimum and maximum values?

The 'RANDBETWEEN' role returns a random number within a range you specify.

Syntax:

=RANDBETWEEN(bottom, elevation)

Parameters:

  • 'bottom' – minimum integer to return
  • 'top' – maximum integer to return

You don't know when y'all'll need to generate random numbers in Excel. With this function, at that place's no demand for you to go random numbers from websites or other tools .

9. INT Office

The 'INT' function is a rather simple Excel function with a unmarried purpose:

Rounding a value down to the nearest integer.

Syntax:

=INT(number)

Parameter:

  • 'number' – represents the value or cell reference with the value y'all desire to round down

More often than not, 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 part is getting the age using the appointment of nascency forth with 'TODAY' and 'YEARFRAC' functions.

10. PMT Role

The 'PMT' function is 1 of Excel's near useful financial functions .

In simple terms, this function, which stands for "Payment", calculates the flow loan payment based on the following:

  • Constant involvement rate
  • Number of payments
  • Loan amount

Syntax:

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

Parameters:

  • 'rate' – involvement rate of the loan either in per centum or decimal number form
  • 'nper' – number of payments or installments
  • 'pv' – primary or present value; the total loan amount
  • 'fv' – optional; represents the residue you similar to remain after making the last payment; if omitted, the default value is zero (0)
  • 'blazon' – annuity blazon or when payments are due (commencement or terminate of catamenia)

This role may look a bit avant-garde but it'south actually easy to learn and use.

With combinations of different functions, you lot'll be able to create an automated loan payment figurer in Excel.

Text Functions

12. CONCATENATE Part

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 prison cell reference to join (upwards to 255 items with a full of eight,192 characters)

Learning how to apply this office is can save you a cracking bargain of fourth dimension . At that place'south no need to re-type every single give-and-take you lot need on the sail.

Kasper Langmann, Co-founder of Spreadsheeto

xiii. LEN Part

The 'LEN' function is actually one of the about useful 'little' function in Excel.

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

Syntax:

=LEN(text)

Parameter:

  • 'text' – the cord you want to count the number of characters

This function, especially when used with the 'LEFT', 'Correct', 'MID' and 'TRIM' functions, raises your information parsing skills and make a more efficient solution .

14. TEXT Role

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 yous desire to convert into text
  • 'format_text' – the format you lot want to output the text

This role is commonly used in dealing with dates . In addition, this is too a great function to utilize to set data for pivot tables .

Date and Fourth dimension Functions

15. TODAY Role

If you need to enter today'due south date , all y'all have to do is utilise the 'TODAY' office.

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

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

Kasper Langmann, Co-founder of Spreadsheeto

Syntax:

=TODAY()

Because this function gets y'all the current appointment, there's no need to provide whatsoever parameters .

The 'TODAY' function is one of the volatile functions (those that demand to recalculate again and again ). In one case y'all use this function, it automatically updates every time you open the workbook into the current engagement.

sixteen. Now Function

You can recall 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 part, accept caution every bit this is also 1 of the volatile functions .

17. DATE Function

Compared with the above functions, the 'Engagement' function doesn't update every time you open your workbook.

What this part returns is the series number of a date you lot specify .

Syntax:

=Engagement(year, month, day)

Parameters:

  • 'yr' – the year of the date
  • 'month' – the month of the appointment
  • 'day' – the day of the date

What you demand to call back is that some of Excel'southward functions don't accept dates as inputs or text values . If you need to enter a date, information technology'southward best to employ the 'Date' function.

eighteen. Hr, MINUTE, and SECOND Functions

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

Kasper Langmann, Co-founder of Spreadsheeto

Syntaxes:

=Hr(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 2d that needs to be determined.

These functions are useful when converting time to integers . Nonetheless, you can use them to convert time values to decimals . You lot need to use either the arithmetic method or the 'Catechumen' part .

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 exercise they practise?

For a start, they are called logical operators .

Yous use 'IF' if yous desire to evaluate a condition and go a response of either TRUE or FALSE . When you take multiple weather yous like to test, 'IFS' is what y'all 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 exist 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' – get-go condition to cheque
  • 'Value1' – value to return if the offset condition is TRUE
  • 'Condition2' …. 'Condition127' – optional; additional weather condition to bank check; up to 127 weather condition
  • 'Value2' …. 'Value127' – optional; value(southward) to render if the associated conditions are true likewise upward to 127 values

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

Consider these functions every bit 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' – start condition to evaluate for TRUE or Faux
  • 'logical2' – optional; 2d condition to evaluate

Unremarkably, these functions are used with the 'IF' function to test a condition and specify a value to render 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 than convenient .

Kasper Langmann, Co-founder of Spreadsheeto

21. IFERROR Function

The 'IFERROR' function tin can literally save your tasks from erroneous results due to errors.

To simplify, this part allows you to specify a value to render when the value y'all 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 render when in that location's an error

This office can evaluate the post-obit errors:

  • #Northward/A
  • #REF!
  • #DIV/0!
  • #VALUE!
  • #NUM!
  • #Proper name?
  • #Aught!

Of course, if there'due south no fault, the result from formula or argument inside the 'value' is the return value .

When used to cheque a formula, it may make the formula look a bit complicated. Merely in reality, information technology makes troubleshooting more efficient and constructive .

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

Kasper Langmann, Co-founder of Spreadsheeto

22. TRUE and Fake Functions

Formally, the 'Truthful' and 'Fake' functions are called Boolean logic .

Boolean logic is derived from the binary logic of i and 0 where True is ane (i) and FALSE is 0 (cypher) .

As their name implies, the functions render the logical values of TRUE and Imitation .

Syntaxes:

=TRUE()

=FALSE()

Since these functions render the values TRUE and FALSE, there'southward really no need for parameters .

In truth, there's no need to employ these functions in normal circumstances .

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

Because of that, these functions are ordinarily integrated into larger formulas to brand calculations .

23. Non Part

The 'NOT' part is like a fox role .

Basically, this function returns the opposite logical value — Truthful is Fake and FALSE is True .

Syntax:

=Not(logical)

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

You take to be conscientious with this part and only apply it when you want to reverse the value of a logical statement .

Kasper Langmann, Co-founder of Spreadsheeto

Lookup and Reference Functions

24. COLUMN and COLUMNS Functions

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

The 'COLUMN' role is what you apply to get the column number in Excel.

Basically, you tell the part the cells you're interested in and it will give yous their column numbers .

Kasper Langmann, Co-founder of Spreadsheeto

'Cavalcade' Syntax:

=Column([reference])

Parameter:

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

On the other hand, the 'COLUMNS' role doesn't return the cavalcade number . Instead, it returns the full number of columns in a specified range.

'COLUMNS' Syntax:

=COLUMNS(assortment)

Parameter:

  • 'array' – array, assortment formula, reference to a range of cells

These functions are useful when y'all demand to use column numbers or the number of columns in your calculations .

At first, these functions may not experience that useful. But keep an eye out in times when you need to look for data in a range of columns , these functions would show more than useful than y'all call up .

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 jail 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 role 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 assortment .

'ROWS' Syntax:

=ROWS(array)

Parameter:

  • 'array' – assortment, array formula, reference to a range of cells

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

Kasper Langmann, Co-founder of Spreadsheeto

26. VLOOKUP Role

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

Most jobs that crave Excel skills have interview questions related to this part.

Questions such as:

  • What does a 'VLOOKUP' do?
  • What's the syntax of 'VLOOKUP'?
  • Are there any disadvantages to this part?

Simply, this "vertical lookup" office 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 y'all're looking for
  • 'table_array' – where to look
  • 'col_index_num' – what you want to know
  • 'range_lookup' – optional; setting for returning an exact friction match or approximate match; if omitted, the default is to return the estimate lucifer

Upwardly to this day, the ability of this role, along with its ease of utilise 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 about data is bundled vertically.

This function works by letting y'all look for a data in a row and when that information is found, y'all tin fetch a value by going down the cavalcade 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 yous want to know
  • 'range_lookup' – optional; setting for returning an exact match or estimate match; if omitted, the default is to return the approximate friction match

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

28. INDEX Office

The 'INDEX' part 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 cavalcade number .

Syntax:

=Alphabetize (array, row_num, [col_num])

=Alphabetize (assortment, 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 assortment
  • 'row_number' – row number of the value to get
  • 'col_number' – optional; column number of the value to get
  • 'area_num' – optional; if the assortment has lots of arguments, the value in this parameter is the basis of reference pick from the ranges

This function is ane of the lookup functions yous should know.

29. MATCH Part

The 'Match' function is designed and so y'all won't have to count manually when you need to become 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 y'all're looking for a match
  • 'lookup_array' – the range of cells y'all desire to search in
  • 'match_type' – optional; how Excel looks for the matching value (exact match, largest value, smallest value)

30. INDIRECT Function

The 'INDIRECT' function is a fascinating function in Excel.

In essence, this function returns the reference specified past 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 role is one of the volatile functions .

Every time yous open the workbook, Excel recalculates the part which might slow down your workbook .

Wrapping things up…

When you're still trying to learn the basics of Excel, at that place's no need for you to know all the functions .

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

Kasper Langmann, Co-founder of Spreadsheeto