An Excel function is a ready-made formula with a name, such as SUM, IF or XLOOKUP. You give it some values, and it does the calculation for you. Excel has more than 450 of them. Below you’ll learn how functions work, then find a guide to 93 of the most useful ones.
Formula vs function
To add three months of sales, you could write a formula with plus signs:
=C3+D3+E3
The SUM function does the same with a range, and it stays short however many cells you add:
=SUM(C3:E3)
Both return 1,350. With 12 months, the plus-sign version needs 12 references, but SUM still only needs one range: =SUM(C3:N3). That’s why most formulas use functions.
How a function is written
Every function follows the same pattern: the name, then brackets holding its arguments, the inputs, separated by commas.
| Part | In the IF example |
|---|---|
| Function name | IF |
| Arguments | logical_test, value_if_true and value_if_false |
| Square brackets | Mark an optional argument. You can leave it out. |
| Commas | Separate the arguments. Some regional settings use semicolons instead. |
Arguments can be numbers, text in double quotes, cell references, ranges, or other functions.
A range or several ranges
Functions like SUM and AVERAGE take a whole range. This averages the January column:
=AVERAGE(C3:C8)
The result is 571.7. To use separate ranges, list them with commas: =AVERAGE(C3:C8,E3:E8).
Functions inside functions
A function can be an argument of another function. This averages each product’s three months, then rounds the result:
=ROUND(AVERAGE(C4:E4),0)
Excel works from the inside out. AVERAGE returns 826.67 for Pen, and ROUND turns it into 827. Nesting is how simple functions combine into powerful formulas.
Functions that return many results
In Excel 365, some functions return a whole list. The results spill into the cells below and beside the formula:
=FILTER(B3:C8,C3:C8>600)
One formula in G3 returns both products with January sales over 600: Pen and Marker. The blue border shows the spill range. These are called dynamic array functions. Read more on the FILTER page.
Three ways to enter a function
- Type it. Type
=and the first letters, such as=VL. Excel lists matching functions. Press Tab to pick one, and a tooltip shows its arguments as you type. - Insert Function. Click the fx button beside the formula bar, or press Shift + F3. Search for what you want to do, and Excel suggests a function with a box for each argument.
- The Formulas tab. Browse functions by category in the Function Library group.
Excel function guides
Each guide explains the syntax, has worked examples with screenshots, lists common errors and includes a free practice file.
Math (14)
| ABS | Absolute value of a number |
| AGGREGATE | Totals that ignore errors and hidden rows |
| INT | Round down to the nearest integer |
| MOD | Remainder after division |
| MROUND | Round to the nearest multiple |
| RANDBETWEEN | Random integer between two numbers |
| ROUND | Round a number to N digits |
| ROUNDDOWN | Always round toward zero |
| ROUNDUP | Always round away from zero |
| SUBTOTAL | Totals that ignore hidden/filtered rows |
| SUM | Add numbers, ranges and cells |
| SUMIF | Sum cells that meet one condition |
| SUMIFS | Sum cells that meet multiple conditions |
| SUMPRODUCT | Multiply arrays and sum the results |
Statistical (16)
| AVERAGE | Arithmetic mean of numbers |
| AVERAGEIF | Average with one condition |
| AVERAGEIFS | Average with multiple conditions |
| COUNT | Count cells containing numbers |
| COUNTA | Count non-empty cells |
| COUNTBLANK | Count empty cells |
| COUNTIF | Count cells that meet one condition |
| COUNTIFS | Count cells that meet multiple conditions |
| LARGE | Nth largest value |
| MAX | Largest value |
| MAXIFS | Largest value with conditions |
| MEDIAN | Middle value of a list |
| MIN | Smallest value |
| MINIFS | Smallest value with conditions |
| RANK.EQ | Rank a number in a list |
| SMALL | Nth smallest value |
Logical (8)
| AND | TRUE only if all conditions are TRUE |
| IF | Return one value if TRUE, another if FALSE |
| IFERROR | Replace errors with a value |
| IFNA | Replace #N/A only |
| IFS | Test multiple conditions without nesting |
| NOT | Reverse TRUE/FALSE |
| OR | TRUE if any condition is TRUE |
| SWITCH | Match a value against a list of cases |
Lookup and reference (9)
| CHOOSE | Pick a value from a list by number |
| HLOOKUP | Horizontal lookup in the first row |
| INDEX | Value at a given row and column |
| INDIRECT | Reference from a text string |
| MATCH | Position of a value in a list |
| OFFSET | Reference shifted from a starting cell |
| VLOOKUP | Vertical lookup in the first column |
| XLOOKUP | Modern lookup in any direction |
| XMATCH | Modern MATCH with more options |
Dynamic arrays (12)
| CHOOSECOLS | Return selected columns |
| FILTER | Return rows that meet conditions |
| GROUPBY | Group and summarize with a formula |
| HSTACK | Combine ranges side by side |
| LAMBDA | Create custom reusable functions |
| LET | Name values inside a formula |
| PIVOTBY | Pivot table with a formula |
| SEQUENCE | Generate a list of numbers |
| SORT | Sort a range by column |
| SORTBY | Sort by another range |
| UNIQUE | Extract unique values |
| VSTACK | Stack ranges vertically |
Text (19)
| CONCAT | Join text without a delimiter |
| FIND | Position of text (case-sensitive) |
| LEFT | Characters from the start of text |
| LEN | Count characters in text |
| LOWER | Convert text to lowercase |
| MID | Characters from the middle of text |
| PROPER | Capitalise each word |
| REPLACE | Replace text by position |
| RIGHT | Characters from the end of text |
| SEARCH | Position of text (not case-sensitive) |
| SUBSTITUTE | Replace specific text |
| TEXT | Format a number or date as text |
| TEXTAFTER | Text after a delimiter |
| TEXTBEFORE | Text before a delimiter |
| TEXTJOIN | Join text with a delimiter |
| TEXTSPLIT | Split text into cells |
| TRIM | Remove extra spaces |
| UPPER | Convert text to capitals |
| VALUE | Convert text to a number |
Date and time (12)
| DATE | Build a date from year, month, day |
| DATEDIF | Difference between dates in years/months/days |
| DAY | Day of the month |
| EDATE | Add months to a date |
| EOMONTH | Last day of a month |
| MONTH | Month number of a date |
| NETWORKDAYS | Working days between two dates |
| NOW | Current date and time |
| TODAY | Current date |
| WEEKDAY | Day of the week as a number |
| WORKDAY | Date after N working days |
| YEAR | Year of a date |
Financial (2)
Other (1)
| HYPERLINK | Create a clickable link |
Excel’s function categories
Excel groups its 450+ functions into these categories, which you’ll see on the Formulas tab and in the Insert Function dialog:
| Category | For | Examples |
|---|---|---|
| Math and trigonometry | Totals, rounding and maths | SUM, ROUND, SUMPRODUCT |
| Statistical | Averages, counts, highest and lowest | AVERAGE, COUNTIF, MEDIAN |
| Logical | Tests and decisions | IF, AND, IFERROR |
| Lookup and reference | Finding values in tables | XLOOKUP, VLOOKUP, INDEX |
| Text | Cleaning and splitting text | LEFT, TRIM, TEXTSPLIT |
| Date and time | Dates, ages and working days | TODAY, DATEDIF, NETWORKDAYS |
| Financial | Loans, investments and returns | PMT, FV |
| Information | Checking what a cell contains | ISBLANK, ISNUMBER, ISERROR |
| Engineering | Unit conversion, number systems | CONVERT, DEC2BIN |
| Database | Totals for a criteria table | DSUM, DCOUNT |
| Cube | Data from Power Pivot and OLAP | CUBEVALUE, CUBEMEMBER |
| Web | Working with web addresses | ENCODEURL, WEBSERVICE |
| Compatibility | Older functions kept for old files | RANK, CONCATENATE |
Dynamic array functions such as FILTER, SORT and UNIQUE are listed under Lookup and reference in Excel. Functions from add-ins and your own VBA functions appear under User Defined.
Browse every function: Complete list in alphabetical order · Complete list by category
Frequently asked questions
How many functions does Excel have?
More than 450 in Microsoft 365, and Microsoft adds new ones regularly. Most people use a few dozen of them for nearly everything.
Which Excel functions should I learn first?
Start with SUM, AVERAGE, IF, COUNTIF, SUMIF, XLOOKUP (or VLOOKUP), IFERROR, TEXT, TODAY and ROUND. They cover most everyday work.
Why does my function show #NAME?
The name is misspelled, text is missing its quotes, or the function is newer than your Excel version. For example, XLOOKUP and FILTER need Excel 2021 or Microsoft 365.
Do I type commas or semicolons between arguments?
It depends on your Windows regional settings. Where the decimal separator is a comma, Excel uses semicolons: =IF(A1>5;"Yes";"No").