Essential Excel Formulas Every Beginner Should Know
Excel formulas transform raw data into useful information — from simple sums to conditional calculations…
Excel formulas transform raw data into useful information — from simple sums to conditional calculations and automated lookups. This guide covers the 25 most essential formulas organized by category, each with syntax, a practical example, and when to use it. If you are completely new to Excel, start with our Excel tutorial for beginners first.
Key Takeaways
- Every Excel formula starts with
=followed by a function name and arguments in parentheses (e.g.,=SUM(A1:A10)). - The 5 most-used formulas are SUM (add numbers), IF (conditional logic), VLOOKUP/XLOOKUP (data lookup), COUNTIF (count by criteria), and CONCAT (join text).
- Use F4 while editing a formula to toggle between relative (A1), absolute ($A$1), and mixed references ($A1, A$1) — this controls how formulas behave when copied.
Math and Statistics Formulas
These foundational formulas perform calculations on numbers — from basic addition to statistical analysis.
| Formula | Syntax | What It Does | Example | Result |
|---|---|---|---|---|
| SUM | =SUM(range) |
Adds all numbers in a range | =SUM(B2:B10) |
Total of B2 through B10 |
| AVERAGE | =AVERAGE(range) |
Calculates the mean | =AVERAGE(C2:C50) |
Average of C2 through C50 |
| COUNT | =COUNT(range) |
Counts cells with numbers | =COUNT(A2:A100) |
Number of numeric cells |
| COUNTA | =COUNTA(range) |
Counts non-empty cells | =COUNTA(A2:A100) |
Number of filled cells |
| MIN | =MIN(range) |
Returns the smallest value | =MIN(D2:D50) |
Lowest value in range |
| MAX | =MAX(range) |
Returns the largest value | =MAX(D2:D50) |
Highest value in range |
| ROUND | =ROUND(number, digits) |
Rounds to specified decimals | =ROUND(3.14159, 2) |
3.14 |
Tip: Use =SUM() instead of =A1+A2+A3. SUM handles ranges, ignores text and blanks, and is easier to maintain when rows are added or deleted.
Conditional Formulas
Conditional formulas return different results based on whether specified criteria are met.
| Formula | Syntax | What It Does | Example |
|---|---|---|---|
| IF | =IF(test, true, false) |
Returns one value if true, another if false | =IF(B2>100, "High", "Low") |
| IFS | =IFS(test1, val1, test2, val2, ...) |
Multiple conditions without nesting | =IFS(B2>90, "A", B2>80, "B", TRUE, "C") |
| COUNTIF | =COUNTIF(range, criteria) |
Counts cells matching criteria | =COUNTIF(C2:C100, "North") |
| SUMIF | =SUMIF(range, criteria, sum_range) |
Sums values where criteria match | =SUMIF(C2:C100, "North", D2:D100) |
| AVERAGEIF | =AVERAGEIF(range, criteria, avg_range) |
Averages values where criteria match | =AVERAGEIF(C2:C100, ">50", D2:D100) |
| COUNTIFS | =COUNTIFS(range1, criteria1, range2, criteria2) |
Counts with multiple criteria | =COUNTIFS(B:B, "North", C:C, ">100") |
| SUMIFS | =SUMIFS(sum_range, range1, criteria1, ...) |
Sums with multiple criteria | =SUMIFS(D:D, B:B, "North", C:C, "2026") |
Nested IF vs IFS
Instead of deeply nested IF statements:
=IF(B2>90, "A", IF(B2>80, "B", IF(B2>70, "C", "F")))
Use IFS (Microsoft 365 / Excel 2019+):
=IFS(B2>90, "A", B2>80, "B", B2>70, "C", TRUE, "F")
IFS is easier to read, write, and maintain — especially with 4+ conditions.
Lookup Formulas
Lookup formulas find and retrieve data from tables — essential for connecting data across sheets and ranges.
| Formula | Syntax | Best For |
|---|---|---|
| XLOOKUP | =XLOOKUP(value, lookup, return, [not_found]) |
Modern lookup (M365/2021+) |
| VLOOKUP | =VLOOKUP(value, table, col_num, FALSE) |
Legacy vertical lookup |
| INDEX/MATCH | =INDEX(return, MATCH(value, lookup, 0)) |
Flexible lookup (all versions) |
| HLOOKUP | =HLOOKUP(value, table, row_num, FALSE) |
Horizontal table lookup |
XLOOKUP Example
=XLOOKUP(A2, Products[ID], Products[Price], "Not found")
Searches for A2 in the ID column, returns the matching Price. Returns “Not found” if no match exists. For a complete comparison of lookup methods, see our Excel lookup table tutorial.
Text Formulas
Text formulas manipulate, clean, and combine text strings — essential for data cleaning and formatting.
| Formula | Syntax | What It Does | Example | Result |
|---|---|---|---|---|
| CONCAT | =CONCAT(text1, text2, ...) |
Joins text strings | =CONCAT(A2, " ", B2) |
“John Smith” |
| LEFT | =LEFT(text, chars) |
Extracts from the left | =LEFT("Hello", 3) |
“Hel” |
| RIGHT | =RIGHT(text, chars) |
Extracts from the right | =RIGHT("Hello", 2) |
“lo” |
| MID | =MID(text, start, chars) |
Extracts from the middle | =MID("Hello", 2, 3) |
“ell” |
| LEN | =LEN(text) |
Returns text length | =LEN("Hello") |
5 |
| TRIM | =TRIM(text) |
Removes extra spaces | =TRIM(" Hello ") |
“Hello” |
| UPPER | =UPPER(text) |
Converts to uppercase | =UPPER("hello") |
“HELLO” |
| LOWER | =LOWER(text) |
Converts to lowercase | =LOWER("HELLO") |
“hello” |
| PROPER | =PROPER(text) |
Capitalizes first letters | =PROPER("john smith") |
“John Smith” |
Most important: TRIM removes hidden extra spaces that cause VLOOKUP and COUNTIF failures. If a lookup formula returns #N/A unexpectedly, wrap the lookup value with TRIM first. For splitting text into columns, see our text to columns tutorial.
Date and Time Formulas
Date formulas calculate with dates — durations, deadlines, working days, and date components.
| Formula | Syntax | What It Does | Example | Result |
|---|---|---|---|---|
| TODAY | =TODAY() |
Returns current date | =TODAY() |
2026-04-06 |
| NOW | =NOW() |
Returns current date and time | =NOW() |
2026-04-06 14:30 |
| DATE | =DATE(year, month, day) |
Creates a date from parts | =DATE(2026, 12, 31) |
2026-12-31 |
| YEAR | =YEAR(date) |
Extracts year from date | =YEAR(A2) |
2026 |
| MONTH | =MONTH(date) |
Extracts month from date | =MONTH(A2) |
4 |
| DAY | =DAY(date) |
Extracts day from date | =DAY(A2) |
6 |
| NETWORKDAYS | =NETWORKDAYS(start, end) |
Working days between dates | =NETWORKDAYS(A2, B2) |
Excludes weekends |
| EDATE | =EDATE(date, months) |
Date plus/minus months | =EDATE(A2, 3) |
3 months later |
Practical use: Calculate project deadlines with =NETWORKDAYS(StartDate, EndDate) to count only business days, automatically excluding weekends.
Logical and Error Handling Formulas
Logical formulas combine conditions and handle errors gracefully.
| Formula | Syntax | What It Does | Example |
|---|---|---|---|
| AND | =AND(condition1, condition2) |
TRUE if all conditions met | =AND(B2>50, C2="Active") |
| OR | =OR(condition1, condition2) |
TRUE if any condition met | =OR(B2>100, C2="VIP") |
| NOT | =NOT(condition) |
Reverses TRUE/FALSE | =NOT(B2="Closed") |
| IFERROR | =IFERROR(formula, error_value) |
Returns alternate if error | =IFERROR(A2/B2, 0) |
| ISBLANK | =ISBLANK(cell) |
TRUE if cell is empty | =ISBLANK(A2) |
IFERROR Is Essential
Wrap any formula that might produce an error:
=IFERROR(VLOOKUP(A2, Table, 3, FALSE), "Not found")
Without IFERROR, a missing lookup value shows #N/A — with it, you get a clean “Not found” message. XLOOKUP has this built-in as the fourth argument.
How Do Cell References Work?
Understanding absolute, relative, and mixed references is critical for formulas that need to be copied across cells correctly.
| Reference Type | Syntax | Behavior When Copied | Use Case |
|---|---|---|---|
| Relative | A1 |
Both row and column adjust | Most formulas |
| Absolute | $A$1 |
Nothing adjusts — locks to cell | Tax rate, fixed constants |
| Mixed (column) | $A1 |
Column stays, row adjusts | Lookup ranges |
| Mixed (row) | A$1 |
Row stays, column adjusts | Header references |
Press F4 while editing a formula to cycle through all four reference types: A1 → $A$1 → A$1 → $A1 → back to A1.
For more advanced formulas including pivot tables and macros, see our intermediate Excel tutorial and Excel macros tutorial. If you need Excel with all formula capabilities, Microsoft Office 2024 Professional Plus ($199.99) includes the full Excel desktop application.
Frequently Asked Questions
What are the 5 most important Excel formulas to learn first?
Start with SUM (add numbers), IF (conditional logic), VLOOKUP or XLOOKUP (data lookup), COUNTIF (count by criteria), and CONCAT (join text). These five formulas cover 80% of common Excel tasks. Once comfortable, add SUMIF, IFERROR, and INDEX/MATCH to your toolkit.
Why does my formula show #VALUE! error?
The #VALUE! error means a formula received the wrong type of data — for example, trying to multiply text by a number. Common causes: cells that look like numbers but are formatted as text (re-enter or use VALUE() to convert), extra spaces in cells (use TRIM()), or referencing an entire column in a function that expects a single value.
What is the difference between a formula and a function?
A formula is any calculation in Excel starting with = (e.g., =A1+B1 ). A function is a predefined operation with a name (e.g., =SUM(A1:A10) ). All functions are formulas, but not all formulas use functions. You can combine multiple functions in one formula: =IF(SUM(A1:A10)>100, "Over budget", "OK") .
Should I learn VLOOKUP or XLOOKUP?
Learn XLOOKUP if you use Microsoft 365 or Excel 2021+ — it is simpler and more powerful. XLOOKUP can look in any direction, has built-in error handling, and does not break when columns are inserted. If you need backward compatibility with Excel 2019 or earlier, learn INDEX/MATCH instead. See our lookup table tutorial for a detailed comparison.
by Editorial Team
Updated on April 5, 2026
by Editorial Team
Updated on April 5, 2026
ON THIS PAGE
