Excel Formulas: The Complete Guide for Beginners to Power Users
Every spreadsheet that does anything useful runs on formulas. They are the difference between a grid of typed-in numbers and a model that recalculates itself the instant your data changes. This guide is the map: how formulas are built, how references behave when you copy them, which handful of functions in each family actually matter, and how to debug things when Excel throws #REF! at you. Wherever a topic deserves its own deep dive, we link to the full tutorial.
The Anatomy of a Formula
Every formula in Excel starts with an equals sign. That single character tells Excel “calculate this” instead of “store this text.” Strip a formula down and you’ll find at most five kinds of parts:
| Part | Example | What it does |
|---|---|---|
| Equals sign | = | Marks the cell as a formula |
| Cell references | A2, B2:B50 | Pull values from other cells |
| Operators | + - * / ^ & = < > | Combine or compare values |
| Functions | SUM(), IF() | Prebuilt calculations |
| Constants | 42, "Paid", 7% | Literal values typed directly in |
So =SUM(B2:B50)*1.08 reads as: add up B2 through B50, then multiply the result by 1.08. Excel follows standard order of operations — parentheses first, then exponents (^), then multiplication and division, then addition and subtraction. =2+3*4 returns 14, not 20. When in doubt, add parentheses; they cost nothing and make your intent explicit.
Two operators trip up beginners. The ampersand (&) joins text — =A2&" "&B2 produces John Smith — and comparison operators (=, <>, >=) return TRUE or FALSE, which is the raw material every logical function feeds on.
A function is just a named calculation with a defined set of inputs (arguments) inside parentheses. Excel has over 500 of them. You will use maybe 25 regularly, and this guide covers the ones that earn their keep. If you’re writing your very first one, start with our walkthrough on how to enter a formula in Excel and the basics of creating formulas.
Cell References: Relative, Absolute, and Mixed
This is the single most important concept in Excel. Misunderstand it and every copied formula breaks; master it and you can fill a thousand-row model in one keystroke.
Relative references (the default)
A reference like A2 is relative. It doesn’t really mean “cell A2” — it means “the cell two columns left of me,” or whatever the offset happens to be. Copy =A2*B2 from C2 down to C3 and it becomes =A3*B3 automatically. This is exactly what you want 90% of the time.
Absolute references
Sometimes you need a reference to stay put — a tax rate in F1, an exchange rate, a lookup table. Dollar signs lock it: $F$1 is an absolute reference that never shifts no matter where you copy the formula. So in D2 you’d write:
=C2*$F$1Copy that down 500 rows and C2 walks down with each row while $F$1 stays nailed to the rate cell. The full mechanics are covered in our guide to absolute cell references, and the related how-to on locking a cell in a formula shows the common patterns.
Mixed references
You can lock just the column ($F1) or just the row (F$1). Mixed references are what make a single formula fillable in two directions — the classic example is a multiplication table where =$A2*B$1 works in every cell of the grid.
The shortcut to memorize: select the reference in the formula bar and press F4 to cycle through F1 → $F$1 → F$1 → $F1. On a Mac it’s Cmd+T or Fn+F4 — see using the F4 key on Mac if yours isn’t cooperating.
One more reference type worth knowing: prefix with a sheet name (Sheet2!B4) to reach across worksheets, and use a 3D reference like =SUM(Jan:Dec!B4) to total the same cell across many sheets at once.
Entering and Copying Formulas
Workflow matters more than people admit. The fast pattern:
- Write one formula in the first row of your data and press Enter (or Ctrl+Enter to commit without moving the selection).
- Double-click the fill handle — the small square at the cell’s bottom-right corner. Excel copies the formula down exactly as far as the adjacent column has data. No dragging, no overshooting.
- Audit one or two copies. Select a copied cell and check the formula bar: did the relative parts shift the way you intended, and did the absolute parts hold?
Dragging the fill handle works too, as does copying the cell (Ctrl+C) and pasting over a selected range. Our guides on copying a formula down a column and copying and pasting formulas cover the variations, including filling huge ranges via the Name Box. The fill handle also powers Autofill for series like dates and numbered lists.
Two paste tricks every power user leans on:
- Paste Values (Ctrl+Alt+V, then V) replaces formulas with their current results — essential before deleting source data. Full steps in paste values without formulas.
- Ctrl+` (the backtick, above Tab) toggles the whole sheet between showing results and showing formulas — see display formulas in Excel. Invaluable when auditing someone else’s workbook.
For more keyboard leverage, our roundup of the best Excel keyboard shortcuts is worth ten minutes of your time.
Math Functions
The workhorses. If you learn three, learn these.
SUM adds a range: =SUM(B2:B50). The fastest way to write it is Alt+= (AutoSum), which guesses the range for you. Details and edge cases in the SUM function guide.
ROUND controls precision: =ROUND(A2, 2) rounds to two decimals; =ROUND(A2, -3) rounds to the nearest thousand. This matters because formatting a cell to show two decimals does not change the underlying value — totals built on displayed-but-unrounded numbers drift by pennies. ROUNDUP and ROUNDDOWN force the direction. See the ROUND function reference, and fixing rounding errors when the pennies have already crept in.
SUMPRODUCT multiplies paired ranges and sums the results in one step: =SUMPRODUCT(B2:B50, C2:C50) gives you total revenue from quantity and price columns without a helper column. It’s also the Swiss Army knife behind many conditional-counting tricks. Full treatment in the SUMPRODUCT guide.
Basic arithmetic needs no function at all — =A2-B2, =A2*B2, =A2/B2 — though our pages on subtraction formulas and multiplying cells cover the patterns beginners ask about, like subtracting a whole column.
Logical Functions
Logical functions let a formula make decisions.
IF is the gateway: =IF(B2>=70, "Pass", "Fail") tests a condition and returns one value when it’s true, another when it’s false. Nest them for multiple outcomes, or use IFS in modern Excel to keep things flat. Start with the IF function guide.
AND / OR combine conditions inside IF: =IF(AND(B2>=70, C2="Yes"), "Pass", "Fail") requires both tests to pass; OR requires only one. Remember these return TRUE/FALSE on their own, which is handy for flag columns you can filter on.
IFERROR catches failures gracefully: =IFERROR(VLOOKUP(...), "Not found") swaps any error for a value you choose. Wrap lookups and division with it — but only after you’ve confirmed the formula works, or you’ll silence real bugs. The IFERROR reference covers when to use it versus the more surgical IFNA.
Lookup Functions
Lookups pull related data from another table — the skill that separates people who use Excel from people who retype things.
XLOOKUP is the modern answer and should be your default in Excel 2021/365:
=XLOOKUP(E2, A2:A500, C2:C500, "Not found")Find E2 in column A, return the matching value from column C, show “Not found” if it’s missing. It looks left or right, defaults to exact match, and doesn’t break when you insert columns. Full syntax in the XLOOKUP guide.
VLOOKUP is what you’ll inherit in older workbooks: =VLOOKUP(E2, A2:C500, 3, FALSE) finds E2 in the first column of the table and returns the value from column 3. Always pass FALSE (exact match) unless you specifically want approximate banding, as in tax brackets. The classic gotchas — it can’t look left, and the column number breaks when columns move — are covered in the VLOOKUP reference.
INDEX + MATCH is the pre-XLOOKUP power combo: =INDEX(C2:C500, MATCH(E2, A2:A500, 0)). MATCH finds the row position, INDEX retrieves the value at that position. It’s still the most flexible option for two-way lookups (MATCH on both row and column). Our walkthrough on using INDEX MATCH builds it step by step, and if you’re deciding between the three approaches, read XLOOKUP vs VLOOKUP vs INDEX MATCH.
For horizontal tables there’s HLOOKUP, though XLOOKUP handles both orientations.
Text Functions
Data arrives messy. Text functions clean it without manual retyping.
TEXTJOIN / CONCAT combine pieces: =TEXTJOIN(", ", TRUE, A2:D2) joins a range with a delimiter and skips blanks — far better than chaining & for more than two pieces. See TEXTJOIN and the older CONCATENATE approach you’ll still meet in legacy files.
LEFT / MID / RIGHT extract by position: =LEFT(A2, 3) grabs the first three characters (think area codes, SKU prefixes); MID pulls from the middle; RIGHT from the end. Pair them with LEN when the length varies. Start with the LEFT function guide.
TRIM / SUBSTITUTE fix invisible problems: =TRIM(A2) strips leading, trailing, and doubled spaces — the #1 reason lookups mysteriously fail on data pasted from other systems. SUBSTITUTE replaces specific text, like stripping dashes from phone numbers: =SUBSTITUTE(A2, "-", ""). The TRIM reference explains the non-breaking-space case TRIM alone can’t fix.
Honorable mention: for one-off splits and joins, Flash Fill (Ctrl+E) often beats a formula entirely — type one example and Excel infers the pattern. Our text functions overview catalogs the rest of the family.
Date and Time Functions
Excel stores dates as serial numbers (June 11, 2026 is 46,184), which is why date math is just arithmetic: =B2-A2 returns the days between two dates.
TODAY / NOW: =TODAY() returns the current date and recalculates every time the workbook opens — perfect for age, aging buckets, and days-until-deadline columns. =NOW() adds the time. Both take no arguments. See TODAY and NOW.
EOMONTH returns the last day of a month relative to a date: =EOMONTH(A2, 0) is month-end of A2’s month; =EOMONTH(A2, 0)+1 is a clean trick for “first day of next month.” Indispensable for billing and accrual schedules — details in the EOMONTH guide.
NETWORKDAYS counts working days between dates, excluding weekends and an optional holiday list: =NETWORKDAYS(A2, B2, $H$2:$H$12). Its sibling WORKDAY goes the other direction — “what date is 10 business days from now?” Full reference at NETWORKDAYS.
For year/month/day spans in human terms (“3 years, 2 months”), the undocumented DATEDIF is still the tool. Keep our date functions cheat sheet bookmarked — it’s the page we send people to most.
Statistical Functions
AVERAGE / MEDIAN: =AVERAGE(B2:B50) is the mean; MEDIAN resists outliers — for skewed data like salaries or house prices, report both. AVERAGE ignores blanks but counts zeros, a distinction that bites people; the AVERAGE reference spells it out.
COUNT family: COUNT counts numbers, COUNTA counts non-empty cells, COUNTBLANK counts empties. Knowing which one you need is half the battle in data-quality checks.
COUNTIF / SUMIFS: conditional aggregation is where statistics meets real work. =COUNTIF(A2:A500, "West") counts matching rows; =SUMIFS(D2:D500, A2:A500, "West", B2:B500, ">100") sums with multiple criteria (note SUMIFS puts the sum range first). Both accept wildcards ("*smith*") and comparison strings (">="&F1). Start with COUNTIF and SUMIFS; AVERAGEIF rounds out the set.
MAX and MIN need no explanation, but their conditional cousins MAXIFS/MINIFS (Excel 2019+) are underused gems.
Formula Errors and How to Debug Them
Errors are information, not failure. Each one tells you exactly what class of problem to look for:
| Error | Meaning | First thing to check |
|---|---|---|
#REF! | A referenced cell was deleted | Undo the deletion; see fixing #REF! errors |
#N/A | Lookup found no match | Stray spaces or text-vs-number mismatch — what #N/A means |
#VALUE! | Wrong data type in an argument | Text where a number belongs — what #VALUE! means |
#DIV/0! | Division by zero or blank | Guard with =IF(B2=0, "", A2/B2) |
#NAME? | Excel doesn’t recognize a name | Misspelled function or unquoted text |
#SPILL! | A dynamic array can’t expand | Clear the blocking cells — fix spill errors |
##### | Column too narrow (or negative time) | Double-click the column border |
A debugging workflow that works on anything:
- Read the error type and narrow the suspect list using the table above.
- Select the formula and press F9 on a highlighted piece in the formula bar to evaluate just that fragment in place (press Esc, not Enter, to back out). This is the fastest way to find which argument is rotten.
- Use Formulas → Evaluate Formula to step through the calculation one operation at a time.
- Trace Precedents (Ctrl+[) to jump to the cells a formula depends on.
Two non-error failure modes deserve mention. A circular reference — a formula that depends on its own result — produces a status-bar warning and a silent zero; our guide to finding circular references shows how to hunt them down. And if formulas show as text or simply don’t update, calculation is probably set to Manual (press F9 to recalculate, or check Formulas → Calculation Options) or the cell was formatted as Text before you typed the formula.
Named Ranges
A named range replaces a cryptic reference with a word. Define TaxRate for cell F1 (select the cell, type the name in the Name Box left of the formula bar, press Enter) and your formula becomes:
=C2*TaxRateThat formula documents itself, and names are absolute by default — no dollar signs needed when copying. Names shine in three places: constants used all over a workbook, lookup tables (=XLOOKUP(E2, ProductIDs, Prices)), and data-validation lists. Manage them in Formulas → Name Manager, which is also where you fix names pointing at deleted ranges. Setup options and scoping rules are in our guide to named ranges in Excel.
One caution from experience: prefer Excel Tables (Ctrl+T) over named ranges for data that grows. A table’s structured references (Sales[Amount]) expand automatically when rows are added; a static named range does not.
Array Formulas and Dynamic Arrays
In Excel 365 and 2021, formulas can return many values at once. Write =SORT(A2:A100) in one cell and the results spill into the cells below automatically — a blue border marks the spill range, and you refer to the whole thing as D2#. This replaced the old Ctrl+Shift+Enter array formulas, which you’ll still see in legacy workbooks wrapped in {curly braces}. Background in our primer on arrays in Excel and how spilling works.
The dynamic array functions worth knowing first:
- FILTER —
=FILTER(A2:D500, C2:C500="West", "None")returns every matching row, live. It’s a queryable report in one formula. See the FILTER function guide. - UNIQUE —
=UNIQUE(B2:B500)returns the distinct values, perfect for building summary tables that update themselves: pair it with COUNTIF or SUMIFS keyed to the spill range. Walkthrough at using UNIQUE. - SORT / SORTBY / SEQUENCE — ordering and number-series generation without touching the source data.
The mental shift: instead of one formula per row, you write one formula per question. A FILTER + SORT + UNIQUE stack can replace a manually maintained report entirely. The only new failure mode is #SPILL! when something blocks the output range — covered in the error table above.
Where to Go Next
Formulas reward deliberate practice more than reading. Pick a real spreadsheet you maintain by hand and automate one piece of it: a SUMIFS summary, an XLOOKUP join, a FILTER-based report. Then audit it with Ctrl+` and Evaluate Formula until you can explain every cell. The reference guides linked throughout this page go deeper on each function — and when a formula misbehaves at 4:55 on a Friday, the error table above will usually get you home on time.
FAQ
What is the difference between a formula and a function?
A formula is anything you type after = — it can be as simple as =A1+A2. A function is a prebuilt, named calculation like SUM or XLOOKUP that you use inside a formula. Every function call is part of a formula, but plenty of formulas (plain arithmetic, & concatenation) contain no functions at all.
Why does my formula show as text instead of calculating?
Three usual suspects: the cell was formatted as Text before you entered the formula (change the format to General, then press F2 and Enter to re-commit), Show Formulas mode is on (toggle it with Ctrl+), or there's a leading space or apostrophe before the =`. If formulas calculate but don’t update, check Formulas → Calculation Options — someone set the workbook to Manual.
Should I learn VLOOKUP or XLOOKUP?
Learn XLOOKUP as your default if you’re on Excel 2021 or Microsoft 365 — it’s safer (exact match by default), more capable (looks in any direction), and easier to read. Learn to read VLOOKUP regardless, because millions of existing workbooks use it and you will inherit them.
How do I stop a cell reference from changing when I copy a formula?
Add dollar signs to make it absolute: $F$1 stays fixed in both directions, while F$1 locks only the row and $F1 only the column. The F4 key cycles through all four states while you’re editing. Full details in our guide to absolute cell references.
What is the most important Excel formula to learn first?
SUM for mechanics, then IF, then SUMIFS, then XLOOKUP — in that order. SUM teaches ranges and AutoSum, IF teaches conditions, SUMIFS handles most real-world reporting questions (“total sales for the West region over $100”), and XLOOKUP connects tables together. Those four cover the bulk of everyday spreadsheet work.