10 Excel Functions Every User Should Know
10 Excel functions every user should know will handle most of what you do in a spreadsheet: adding things up, counting, making decisions, looking up values, and cleaning up text.
Excel has hundreds of functions, and nobody needs all of them. Whether you run budgets, track hours, or build reports, a small core set does most of the heavy lifting. Learn these well and you will spend less time on manual work and more time on the numbers that matter.
Each function below includes the syntax, a real example, a tip to avoid the most common mistake, and a short video. There is also a free formula builder that writes any of them for you.
The 10 Excel Functions at a Glance
| # | Function | What It Does | Example |
|---|---|---|---|
| 1 | SUM | Adds numbers | =SUM(A2:A10) |
| 2 | AVERAGE | Finds the mean | =AVERAGE(B2:B10) |
| 3 | COUNT and COUNTA | Counts numbers or non-empty cells | =COUNTA(C2:C10) |
| 4 | IF | Returns one result or another based on a test | =IF(D2>100,"High","Low") |
| 5 | VLOOKUP and XLOOKUP | Finds a value in a table | =XLOOKUP(E2,A:A,B:B) |
| 6 | INDEX and MATCH | Flexible lookup in any direction | =INDEX(B:B,MATCH(F2,A:A,0)) |
| 7 | CONCATENATE and TEXTJOIN | Combines text | =TEXTJOIN(" ",TRUE,G2:H2) |
| 8 | LEFT, RIGHT, and MID | Pulls part of a text string | =LEFT(I2,5) |
| 9 | TODAY and NOW | Inserts the current date or date and time | =TODAY() |
| 10 | SUMIF and COUNTIF | Adds or counts with a condition | =SUMIF(J:J,">50",K:K) |
The 10 Excel Functions and Formulas You Need to Know
1. SUM
SUM is the most used function in Excel. It adds up a range of cells, individual cells, or a mix of both.
Syntax: =SUM(number1, [number2], ...)
Example: =SUM(A1:A10)
This adds every value from A1 through A10. You can also add separate ranges, such as =SUM(A1:A10, C1:C10).
Tip: Press Alt and the equals sign together to insert a SUM for the cells above automatically. If a total looks too low, check for numbers stored as text, which SUM ignores.
2. AVERAGE
AVERAGE calculates the mean of a group of numbers, a quick way to find the typical value in a range.
Syntax: =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(B1:B10)
This returns the average of the values in B1 through B10.
Tip: Blank cells are skipped, but cells containing 0 are included. If zeros mean "no data yet," they will pull your average down. Leave those cells empty instead.
3. COUNT and COUNTA
COUNT counts cells that contain numbers. COUNTA counts every cell that is not empty, including text.
Syntax: =COUNT(value1, [value2], ...) and =COUNTA(value1, [value2], ...)
Example: =COUNT(C1:C10) and =COUNTA(C1:C10)
If C1:C10 holds seven numbers, two names, and one blank, COUNT returns 7 and COUNTA returns 9.
Tip: Use COUNTA to count how many rows of data you have, such as employees or orders. Use COUNTBLANK when you want to find how many cells are still empty.
Watch the COUNT and COUNTA video
4. IF
IF makes a logical test and returns one value if it is true and another if it is false. It is the foundation of decision-making in a spreadsheet.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: =IF(D1>100, "Above 100", "100 or Below")
If D1 is greater than 100, the cell shows "Above 100." Otherwise it shows "100 or Below."
Tip: Put text results in double quotes, but not numbers or cell references. For several conditions, the IFS function is easier to read than stacking IF inside IF.
5. VLOOKUP (and XLOOKUP)
VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: =VLOOKUP(E1, A1:B10, 2, FALSE)
This looks for the value in E1 in the first column of A1:B10 and returns the matching value from the second column. FALSE means an exact match.
Tip: Always use FALSE unless you specifically want an approximate match on sorted data. In current versions of Excel, XLOOKUP is easier: =XLOOKUP(E1, A1:A10, B1:B10, "Not found") looks in any direction and has a built-in "not found" message. See the Microsoft XLOOKUP guide.
Watch the VLOOKUP (and XLOOKUP) video
6. INDEX and MATCH
INDEX and MATCH work together as a flexible lookup. MATCH finds the position of a value, and INDEX returns what is in that position.
Syntax: =INDEX(array, row_num, [column_num]) and =MATCH(lookup_value, lookup_array, [match_type])
Example: =INDEX(B1:B10, MATCH(F1, A1:A10, 0))
MATCH finds where F1 sits in A1:A10, and INDEX returns the value in the same position from B1:B10.
Tip: Unlike VLOOKUP, INDEX and MATCH can return a value from a column to the left of the one you search, and they do not break when someone inserts a column. Use 0 in MATCH for an exact match.
Watch the INDEX and MATCH video
7. CONCATENATE (or TEXTJOIN)
CONCATENATE combines text from several cells into one. TEXTJOIN does the same but lets you set a separator once and skip blank cells.
Syntax: =CONCATENATE(text1, [text2], ...) and =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example: =CONCATENATE(G1, " ", H1) and =TEXTJOIN(" ", TRUE, G1:H1)
Both combine G1 and H1 with a space between them, such as a first and last name.
Tip: The ampersand works too and is quick to type: =G1&" "&H1. In newer versions, CONCAT replaces CONCATENATE and accepts whole ranges.
Watch the CONCATENATE (or TEXTJOIN) video
8. LEFT, RIGHT, and MID
These functions pull part of a text string: characters from the start, from the end, or from the middle.
Syntax: =LEFT(text, [num_chars]), =RIGHT(text, [num_chars]), and =MID(text, start_num, num_chars)
Example: =LEFT(I1, 5), =RIGHT(I1, 3), and =MID(I1, 2, 4)
LEFT returns the first 5 characters of I1, RIGHT returns the last 3, and MID returns 4 characters starting at the second character.
Tip: Combine them with FIND when the length changes from row to row. For example, =LEFT(A2, FIND(" ", A2) - 1) returns everything before the first space, such as a first name.
Watch the LEFT, RIGHT, and MID video
9. TODAY and NOW
TODAY and NOW insert the current date, or the current date and time.
Syntax: =TODAY() and =NOW()
Example: =TODAY()-A2
Subtracting a start date in A2 from TODAY returns the number of days since that date, such as days an invoice is overdue.
Tip: Both functions update every time the workbook recalculates. To stamp a date that never changes, press Ctrl and semicolon instead of using a formula.
10. SUMIF and COUNTIF
SUMIF and COUNTIF add or count cells that meet a condition.
Syntax: =SUMIF(range, criteria, [sum_range]) and =COUNTIF(range, criteria)
Example: =SUMIF(J1:J10, ">50", K1:K10) and =COUNTIF(J1:J10, ">50")
SUMIF adds the values in K1:K10 where the matching cell in J1:J10 is greater than 50. COUNTIF counts how many cells in J1:J10 are greater than 50.
Tip: Put comparison criteria in quotes. For more than one condition, use SUMIFS and COUNTIFS, which accept several range and criteria pairs.
Watch the SUMIF and COUNTIF video
Here is the COUNTIF video as well.
Free Excel Formula Builder
Free Excel Formula Builder
Pick a function, fill in your cell ranges and values, and the builder writes the formula and explains what it does in plain English. Copy it straight into Excel or Google Sheets. Nothing you enter is saved or sent anywhere.
Your Formula
Common Mistakes With These Functions
| Mistake | What Happens | Fix |
|---|---|---|
| Numbers stored as text | SUM and AVERAGE ignore them, lookups fail | Convert them with the warning icon or Text to Columns |
| VLOOKUP without FALSE | Returns the wrong row on unsorted data | Add FALSE, or switch to XLOOKUP |
| Extra spaces in text | Lookups and COUNTIF miss matches | Clean the data with TRIM |
| Ranges that do not move correctly | Copied formulas point at the wrong cells | Lock ranges with dollar signs, such as $A$2:$A$100 |
| Criteria without quotes | SUMIF or COUNTIF returns an error or zero | Write criteria like ">50" in quotes |
When a formula does return an error, our guide to handling and blanking out Excel errors explains what each one means and how to fix it.
Do These Work in Google Sheets?
Yes. Every function on this list works in Google Sheets with the same name and nearly identical syntax, including XLOOKUP and TEXTJOIN. The main differences are in keyboard shortcuts and a few newer Excel-only functions, not in these ten.
What to Learn Next
- SUMIFS, COUNTIFS, and AVERAGEIFS for multiple conditions
- IFERROR and IFNA for cleaner results when something is missing
- FILTER, SORT, and UNIQUE for dynamic lists in current versions of Excel
- PivotTables to summarize thousands of rows without formulas
Microsoft keeps a complete list of Excel functions if you want to explore further, and our printable Excel formulas cheat sheet keeps the essentials in one place.
When Spreadsheets Are Not Enough
These functions will take you a long way, but when a whole team is updating the same workbook for hours, schedules, and projects, spreadsheets start to break. Updoot is business management software that keeps time tracking, scheduling, projects, invoicing, and HR in one system, and every tool exports to Excel or Google Sheets when you want to run these formulas on your own data. Plans start at $5 per user per month.
Final Thoughts
Master these ten functions and you will handle most everyday spreadsheet work with confidence: totals, averages, counts, decisions, lookups, text cleanup, dates, and conditional math. Practice each one on your own data, use the formula builder when you need a quick start, and add the next level of functions once these feel natural.
Opens in Google Drive. View and download for free.
Frequently Asked Questions About Excel Functions
What are the most important Excel functions to learn first?
Start with SUM, AVERAGE, COUNT and COUNTA, IF, a lookup function such as VLOOKUP or XLOOKUP, INDEX and MATCH, TEXTJOIN, LEFT, RIGHT and MID, TODAY and NOW, and SUMIF and COUNTIF. Together they cover most everyday spreadsheet work.
What is the difference between COUNT and COUNTA?
COUNT counts only cells that contain numbers. COUNTA counts every cell that is not empty, including text.
Should I use VLOOKUP or XLOOKUP?
If your version of Excel has XLOOKUP, it is usually the better choice. It can look in any direction, defaults to an exact match, and has a built-in option for what to show when nothing is found.
Why is my SUM formula returning the wrong total?
The most common cause is numbers stored as text, which SUM ignores. Convert them to real numbers and the total will update.
How do I add up values that meet more than one condition?
Use SUMIFS, which accepts several range and criteria pairs, for example adding sales for one region and one month at the same time.
Do these Excel functions work in Google Sheets?
Yes. All ten work in Google Sheets with the same names and nearly identical syntax.