One of the best things about Excel is that it can do the math for you, find answers from your data, and even make decisions using just a few formulas and functions.
If you’ve ever typed something like =A1+A2 in Excel, you’ve already used a formula.
If you’ve used something like =SUM(A1:A5), that’s a function — a built-in shortcut for doing specific tasks.
In this post, we’ll give you a quick overview of essential Excel formulas and functions, organized into three skill levels:
Beginner Functions – Start Simple
These are the functions you’ll use most often for basic calculations and everyday tasks.
- SUM – Adds up numbers
Example:=SUM(A1:A5) - AVERAGE – Finds the average value
Example:=AVERAGE(B1:B10) - MIN / MAX – Finds the smallest or largest number
Example:=MIN(C1:C10),=MAX(C1:C10) - COUNT / COUNTA – Counts cells with numbers or any values
Example:=COUNT(D1:D10),=COUNTA(D1:D10) - IF (with AND, OR) – Checks a condition and gives a result
Example:=IF(A1>50, "Pass", "Fail") - TODAY / NOW – Shows current date or date + time
Example:=TODAY(),=NOW()
Intermediate Functions – Get a Bit Smarter
These are helpful when your work gets a little more complex, like searching or working with text and dates.
- VLOOKUP / HLOOKUP – Search for a value in a table
Example:=VLOOKUP("Item", A2:B10, 2, FALSE) - IFERROR/IFNA – Hides errors and shows a custom message
Example:=IFERROR(A1/B1, "Error!") - TEXT – Format numbers and dates as text
Example:=TEXT(TODAY(), "dd-mm-yyyy") - CONCAT / TEXTJOIN – Combine text from different cells
Example:=CONCAT(A1, B1)or=TEXTJOIN(" ", TRUE, A1:A3) - DATEDIF – Find the difference between two dates
Example:=DATEDIF(A1, B1, "Y")(years) - LEFT / RIGHT / MID – Extract specific text from a cell
Example:=LEFT(A1, 5),=RIGHT(A1, 3)
Advanced Functions – Power User Tools
These are powerful functions used in dashboards, reports, or large data sets.
- INDEX + MATCH – Better alternative to VLOOKUP
Example:=INDEX(B2:B10, MATCH("Apple", A2:A10, 0)) - XLOOKUP – Newer and more flexible lookup function
Example:=XLOOKUP("John", A2:A10, B2:B10) - IFS – Checks multiple conditions
Example:=IFS(A1>90, "A", A1>80, "B", A1>70, "C") - SUMIFS / COUNTIFS / AVERAGEIFS – Add/count/average with multiple conditions
Example:=SUMIFS(B2:B10, A2:A10, "East") - ARRAYFORMULA / SEQUENCE / UNIQUE – Work with dynamic or large sets of data
Example:=UNIQUE(A2:A100) - LET / LAMBDA – Create your own variables or custom functions (Excel 365 only)
Other Functions – OFFSET, TRIM, SUBSTITUTE, PROPER, UPPER, LOWER, FIND, SEARCH, VALUE, INT, INDIRECT, TEXTSPLIT, TEXTBEFORE, TEXTAFTER and many more.
Summary
| Level | Key Focus | Sample Functions |
|---|---|---|
| Beginner | Basic math and logic | SUM, AVERAGE, IF, COUNT, TODAY |
| Intermediate | Text, date, and error handling | VLOOKUP, TEXT, DATEDIF, CONCAT, IFERROR |
| Advanced | Data analysis and automation | XLOOKUP, SUMIFS, INDEX-MATCH, IFS, UNIQUE, LET |
What’s Next?
Now that you know what these formulas and functions do, we’ll explore each one in detail in upcoming posts, with real examples, step-by-step guides, and practice files.
Start with SUM Function — they’re your best Excel friends!