The 12 most important Excel functions to learn first are SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF, SUMIF, IF, ROUND, PROPER, VLOOKUP and CONCAT. Together they cover adding, averaging, counting, conditional totals, decisions, tidy-up and lookups, which is most of what people do in a spreadsheet at work.
Each one below has the syntax, a small worked example, a common mistake and a tip. After the 12, you will find the newer Microsoft 365 functions (XLOOKUP, FILTER, UNIQUE and friends) that now do some of these jobs better.
Quick Reference: The 12 Functions at a Glance
| Function | What it does | Example formula | Works in |
|---|---|---|---|
| SUM | Adds numbers | =SUM(B2:B6) |
All versions |
| AVERAGE | Mean of numbers | =AVERAGE(B2:B6) |
All versions |
| COUNT | Counts cells with numbers | =COUNT(B2:B6) |
All versions |
| COUNTA | Counts cells that are not empty | =COUNTA(B2:B6) |
All versions |
| COUNTBLANK | Counts empty cells | =COUNTBLANK(B2:B6) |
All versions |
| COUNTIF | Counts cells that meet one condition | =COUNTIF(A2:A6,"East") |
All versions |
| SUMIF | Adds cells that meet one condition | =SUMIF(A2:A6,"East",B2:B6) |
All versions |
| IF | Returns one result if true, another if false | =IF(B2>=1000,"Hit","Miss") |
All versions |
| ROUND | Rounds a number to set digits | =ROUND(1234.5678,2) |
All versions |
| PROPER | Capitalises Each Word | =PROPER(A2) |
All versions |
| VLOOKUP | Finds a value in the first column, returns another column | =VLOOKUP(102,A2:C4,3,FALSE) |
All versions |
| CONCAT | Joins text together | =CONCAT(A2," ",B2) |
Excel 2019 and later, Microsoft 365 |
Most of the examples use this small sales list. Region is in column A and sales are in column B.
| A (Region) | B (Sales) | |
|---|---|---|
| 2 | East | 1200 |
| 3 | West | 800 |
| 4 | East | 1500 |
| 5 | North | 950 |
| 6 | West | 650 |
1. SUM: Add Up Numbers
SUM adds the numbers you give it. It is the first function almost everyone learns, and still the one you will use most.
Syntax: =SUM(number1, [number2], ...)
Example: =SUM(B2:B6) adds 1200 + 800 + 1500 + 950 + 650 and returns 5100.
Common mistake: Numbers stored as text. Microsoft notes that SUM ignores text values, so a column of “numbers” that are really text (often left-aligned, or with a green corner) quietly gives a total that is too low. Convert them to real numbers first.
Pro tip: Select the cell under your numbers and press Alt + = (or click AutoSum on the Home tab). Excel writes the SUM for you. For more ways to total data, see how to summarise data in Excel.
2. AVERAGE: Find the Mean
AVERAGE adds the numbers and divides by how many there are. Use it for average sales, salary, ticket count or call time.
Syntax: =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(B2:B6) returns 5100 / 5 = 1020.
Common mistake: Mixing up blanks and zeros. AVERAGE ignores empty cells and text, but it counts a zero. If B6 were empty, the average of the other four would be 1112.5. If B6 held 0, it would drop to 890 for the same rows (4450 / 5).
Pro tip: Use AVERAGE together with COUNT to check how many values went into the answer.
3. COUNT: Count the Numbers
COUNT tells you how many cells contain numbers. Dates count too, because Excel stores dates as numbers.
Syntax: =COUNT(value1, [value2], ...)
Example: Suppose B2:B6 holds 1200, 800, the text “n/a”, 950 and one empty cell. =COUNT(B2:B6) returns 3.
Common mistake: Using COUNT on a column of names. Text inside a range is not counted, so you get 0. Use COUNTA for text.
Pro tip: Click the drop-down arrow beside AutoSum on the Home tab to insert COUNT in one click.
4. COUNTA: Count Everything That Is Not Empty
COUNTA counts cells that have anything in them: numbers, text, dates, logical values and even errors. It is the one to use for headcount or “how many rows have data”.
Syntax: =COUNTA(value1, [value2], ...)
Example: On the same range (1200, 800, “n/a”, 950, empty), =COUNTA(B2:B6) returns 4.
Common mistake: Cells that look empty but are not. Microsoft notes that COUNTA counts empty text ("") returned by a formula, so a column full of such formulas still shows a count. A stray space does the same.
Pro tip: To count names in a list, point COUNTA at the whole column of names, then subtract 1 for the header. More on this in multiple ways to count in Excel.
5. COUNTBLANK: Find the Gaps
COUNTBLANK counts empty cells in a range. It is a quick check for missing data before you send a report.
Syntax: =COUNTBLANK(range)
Example: On the range B2:B6 above, =COUNTBLANK(B2:B6) returns 1.
Common mistake: Expecting zeros to count. Per Microsoft, cells containing 0 are not counted as blank, but cells with a formula returning "" are.
Pro tip: Watch the COUNTBLANK walkthrough with use cases on our YouTube channel.
6. COUNTIF: Count Cells That Meet a Condition
COUNTIF counts the cells in a range that match one condition. Use it to answer “how many orders came from East?” or “how many reps beat 1000?”.
Syntax: =COUNTIF(range, criteria)
Examples:
=COUNTIF(A2:A6,"East")returns 2.=COUNTIF(B2:B6,">1000")returns 2 (1200 and 1500).
Common mistake: Leaving out the quotes around an operator, or typing a cell reference inside the quotes. To compare with a value in D1, join them: =COUNTIF(B2:B6,">"&D1).
Pro tip: COUNTIF is not case sensitive, and supports wildcards: "E*" matches anything starting with E. For two or more conditions, switch to COUNTIFS. See also how to count cells with a certain value.
7. SUMIF: Add Only What Matches
SUMIF adds the cells that meet a condition. It is the quickest way to get a total per region, product or month.
Syntax: =SUMIF(range, criteria, [sum_range])
Example: =SUMIF(A2:A6,"East",B2:B6) adds 1200 + 1500 and returns 2700.
Common mistake: A sum_range that is a different size from range. Microsoft says they should be the same size and shape, otherwise results can mislead and performance suffers.
Pro tip: Put the region name in a cell, such as D2, and use =SUMIF(A2:A6,D2,B2:B6). Change D2 and the total follows. Need two conditions? Use SUMIFS.
8. IF: Make Excel Decide
IF tests a condition and returns one result when it is true and another when it is false. It is the base of almost every “pass or fail”, “bonus or no bonus” sheet.
Syntax: =IF(logical_test, value_if_true, [value_if_false])
Example: =IF(B2>=1000,"Hit","Miss") returns Hit for 1200.
Common mistake: Forgetting the quotes around text results. Without them, Excel reads Hit as a name and shows #NAME?.
Pro tip: Nested IFs get hard to read after two or three levels. In Microsoft 365, Excel 2019 and later, IFS (see below) is tidier. Our IF and IFS video shows both on pass, fail and grades.
9. ROUND: Control the Decimals
ROUND changes the number itself, not just how it looks. Use it for prices, tax and anything that must add up exactly.
Syntax: =ROUND(number, num_digits)
Examples:
=ROUND(1234.5678,2)returns 1234.57.=ROUND(1234.5678,0)returns 1235.=ROUND(1234.5678,-2)returns 1200 (a negative number rounds to the left of the decimal point).
Common mistake: Using number formatting (fewer decimals on the ribbon) when you need real rounding. Formatting only changes the display, so totals can look “off by one cent”.
Pro tip: Wrap ROUND around a whole calculation, such as =ROUND(B2*1.09,2), so every result is a true two-decimal figure.
10. PROPER: Fix Names and Titles
PROPER capitalises the first letter of each word and lowercases the rest. It rescues name lists typed in ALL CAPS or all lowercase.
Syntax: =PROPER(text)
Example: =PROPER("aNNA lEE-tan") returns Anna Lee-Tan.
Common mistake: Trusting it on every name. PROPER capitalises any letter that follows a non-letter, so “andy’s” becomes “Andy’S” and “mcdonald” becomes “Mcdonald”. Check names like these by hand.
Pro tip: Build the clean names in a helper column, then copy and Paste Special as Values to replace the originals. For other text clean-up, see extracting text from variable-length strings.
11. VLOOKUP: Look Up a Value in a Table
VLOOKUP searches the first column of a table for a value and returns something from the same row. Think price lists, staff IDs and product codes.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: With the table below in A2:C4, =VLOOKUP(102,A2:C4,3,FALSE) returns 1.20.
| A (ID) | B (Item) | C (Price) |
|---|---|---|
| 101 | Notebook | 3.50 |
| 102 | Pen | 1.20 |
| 103 | Stapler | 8.90 |
Common mistake: Leaving out the last argument. If you omit it, VLOOKUP defaults to an approximate match (TRUE), which can return the wrong row on unsorted data. Type FALSE for an exact match.
Pro tip: VLOOKUP can only look to the right, because the lookup value must sit in the first column of the table. Microsoft now points people to XLOOKUP, covered below. Learn VLOOKUP anyway, since you will meet it in older workbooks. Watch: VLOOKUP with exact match.
Want the full comparison? Read Excel lookup functions to save time.
12. CONCAT: Join Text Together
CONCAT joins text from several cells. Use it to build full names, addresses or IDs. The old CONCATENATE function still works, but Microsoft says CONCAT replaces it and CONCATENATE stays for compatibility with earlier versions.
Syntax: =CONCAT(text1, [text2], ...)
Example: If A2 holds Anna and B2 holds Lee, =CONCAT(A2," ",B2) returns Anna Lee.
Common mistake: Forgetting the space. =CONCAT(A2,B2) gives AnnaLee. Add " " between the cells.
Pro tip: The & operator does the same job and works in every version: =A2&" "&B2. If you see #NAME?, your version may be too old for CONCAT, so use &. To join a whole list with a separator, use TEXTJOIN (below). Also see combining text columns into one.
Modern Microsoft 365 Functions Worth Learning Next
Excel has changed a lot since many “top functions” lists were written. These newer functions replace awkward older workarounds. Check your version first, because some are not in older Excel.
| Function | What it does | Example | Version (per Microsoft) |
|---|---|---|---|
| XLOOKUP | Looks up in any direction; exact match by default | =XLOOKUP(102,A2:A4,C2:C4,"Not found") |
Microsoft 365, Excel 2021 and later. Microsoft’s page states it is not available in Excel 2016 and 2019 |
| FILTER | Returns only the rows that meet a condition | =FILTER(A2:B6,A2:A6="East","No sales") |
Microsoft 365, Excel 2021 and later |
| UNIQUE | Returns a list without duplicates | =UNIQUE(A2:A6) |
Microsoft 365, Excel 2021 and later |
| SORT | Sorts a range with a formula | =SORT(UNIQUE(A2:A6)) |
Microsoft 365, Excel 2021 and later |
| LET | Names a calculation so you can reuse it | =LET(avg,AVERAGE(B2:B6),IF(B2>avg,"Above","Below")) |
Microsoft 365, Excel 2021 and later |
| TEXTJOIN | Joins a list with a separator, skipping blanks | =TEXTJOIN(", ",TRUE,A2:A6) |
Excel 2019 and later |
| IFS | Several IF tests without nesting | =IFS(B2>=1500,"A",B2>=1000,"B",TRUE,"C") |
Excel 2019 and later |
XLOOKUP vs VLOOKUP
XLOOKUP is the upgrade. The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It returns an exact match by default, can look left, and lets you set what to show when nothing is found. On the price table above, =XLOOKUP(102,A2:A4,C2:C4,"Not found") returns 1.20.
FILTER, UNIQUE and SORT
These return many results at once, which “spill” into the cells below. =FILTER(A2:B6,A2:A6="East","No sales") lists only the East rows. Without the third argument, FILTER returns a #CALC! error when nothing matches. Keep the cells below the formula empty, or the spill is blocked.
Combine them: =SORT(UNIQUE(A2:A6)) gives an alphabetical list of regions with no repeats. Here that is East, North, West.
LET, TEXTJOIN and IFS
=LET(name1, value1, calculation) stores a result under a name, so long formulas become shorter and easier to read. =TEXTJOIN(", ",TRUE,A2:A6) returns “East, West, East, North, West”. =IFS(...) replaces a chain of nested IFs, but returns #N/A if no test is TRUE, so end with TRUE,"default".
Which should you learn first? If you have Microsoft 365, start with XLOOKUP and FILTER. If you share files with people on older Excel, stay with VLOOKUP and SUMIF for those files, because newer functions show #NAME? in versions that lack them.
How to Practise These Functions
Reading is not enough. Spend 10 minutes a day on one function:
- Copy the sales list above into a blank sheet.
- Type each formula yourself rather than pasting it, and check the answer against the one shown here.
- Break it on purpose: add text to a number column, leave out FALSE in VLOOKUP, add a blank. See what changes.
- Apply it to a real file from your own work the same week.
Then add one more skill from the list. Dropdown lists make data entry cleaner and pair well with COUNTIF and VLOOKUP, so learn how to make a dropdown list in Excel next. When these feel easy, follow our guide to learning advanced Excel fast, or browse 201 things to learn in Microsoft Excel.
Want to learn Excel step by step with video?
Our Excel for Beginners course takes you from the basics to confident formulas, with lessons you can replay at your own pace. See the Excel for Beginners course.
Frequently Asked Questions
What are the most important Excel functions?
The ones almost every user needs are SUM, AVERAGE, COUNT, COUNTA, COUNTIF, SUMIF, IF, ROUND and a lookup function. This article adds COUNTBLANK, PROPER and CONCAT for everyday data clean-up. No official ranking exists, so the “most important” list depends on your job.
What is a function in Excel?
A function is a built-in calculation you call by name, such as SUM or PI(). A formula is whatever you type after the equals sign, and it can contain functions, cell references, operators and constants.
What are the types of functions in Excel?
Microsoft groups worksheet functions by what they do. The categories include Math and trigonometry, Statistical, Logical, Text, Date and time, Lookup and reference, Financial, Information, Database, Engineering, Cube and Web. SUM sits in Math and trigonometry, IF in Logical, and VLOOKUP in Lookup and reference.
What is the difference between a formula and a function in Excel?
=B2*1.09 is a formula with no function. =SUM(B2:B6) is a formula that uses the SUM function. Every function call sits inside a formula.
Is VLOOKUP still worth learning?
Yes. VLOOKUP works in every version of Excel and appears in countless existing workbooks. If you use Microsoft 365 or Excel 2021 and later, learn XLOOKUP as well, since Microsoft recommends it as an improved version of VLOOKUP.
What is the difference between COUNT, COUNTA and COUNTIF?
COUNT counts cells with numbers. COUNTA counts any cell that is not empty. COUNTIF counts only the cells that meet a condition you set.
What are advanced Excel skills?
Typically they include lookups with XLOOKUP or INDEX and MATCH, dynamic array functions (FILTER, UNIQUE, SORT), PivotTables, Power Query and dashboards. Master the 12 functions above first. Then follow our guide to learning advanced Excel fast.
Which Excel versions support these functions?
Eleven of the 12 work in every version of Excel. CONCAT needs Excel 2019 or later (or Microsoft 365); in older versions use the & operator. XLOOKUP, FILTER, UNIQUE, SORT and LET need Microsoft 365 or Excel 2021 and later. TEXTJOIN and IFS need Excel 2019 and later.
Subscribe to the ExcelChamp YouTube channel for more Excel tips and tutorials.
