Excel has over 450 functions, and you’ll use maybe a dozen of them for 90% of everything you do. This is that dozen — the core formulas that handle adding, deciding, looking up, cleaning text, and working with dates, grouped in the order you’ll actually grow into them. Learn these and most spreadsheet tasks become a matter of picking the right tool you already know, rather than searching for one you don’t. Each section shows the formula and what it’s genuinely for.
Add and count: SUM, AVERAGE, COUNT
The four that come first because everything else builds on them. They summarize a range in a single cell.
=SUM(B2:B20) 'add a range
=AVERAGE(B2:B20) 'mean of a range
=COUNT(B2:B20) 'how many cells hold numbers
=COUNTA(A2:A20) 'how many cells aren't emptySUM and AVERAGE are obvious; the pair worth distinguishing is COUNT versus COUNTA. COUNT tallies only numeric cells, while COUNTA counts anything non-empty, including text — so COUNT a column of prices, but COUNTA a column of names. Getting that distinction right saves the classic mistake of a count that silently ignores your text entries. These four, plus the AutoSum button (Alt+=), cover the bulk of everyday summarizing.
Lock cells with $: absolute vs relative
This isn’t a function, but it’s the concept that makes every formula above reusable — and the one beginners stumble on most. When you copy a formula, its cell references shift automatically. Sometimes you want that; sometimes you need one to stay put.
=B2*$F$1 'B2 moves as you copy down; $F$1 stays locked
A plain reference like B2 is relative — copy the formula down a row and it becomes B3, which is usually what you want. A reference with dollar signs like $F$1 is absolute — it stays pinned no matter where you copy it, perfect for a single tax rate or conversion factor that every row should reference. Press F4 while editing a reference to cycle through the lock options. Nearly every “my formula works in the first row but breaks below it” problem traces back to a reference that should have been locked and wasn’t.
Make decisions: IF
IF is where formulas start to think. It checks a condition and returns one thing if true, another if false.
=IF(B2>100, "Over budget", "OK")
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")The basic IF takes a test, a value-if-true, and a value-if-false. When you need more than two outcomes, reach for IFS rather than nesting a dozen IFs inside each other — IFS reads each condition in turn and returns the first that’s true, with a final TRUE catching everything else. Nested IFs work but become unreadable fast; IFS keeps a multi-way decision legible. IF is also the building block you’ll wrap around other formulas constantly, so it’s worth being fluent in.
Math with conditions: SUMIF and COUNTIF
The “IF” family applies math only to rows that meet a condition — total just the Food expenses, count just the open tickets.
=SUMIF(A2:A, "Food", C2:C) 'total amounts where category is Food
=COUNTIF(A2:A, "Open") 'count rows marked Open
=AVERAGEIF(A2:A, "North", C2:C) 'average for the North regionThese are the workhorses of any report, because real questions are almost always conditional — not “what’s the total?” but “what’s the total for this category?” For multiple conditions at once (Food and North and this month), the plural versions SUMIFS and COUNTIFS stack criteria, which the SUMIFS vs COUNTIFS guide covers in depth. Master the conditional aggregates and you can answer most reporting questions without ever building a pivot table.
Look things up: VLOOKUP and XLOOKUP
Lookups connect two tables by a shared key — pull a price from a product list, a name from an ID. This is the family that turns separate sheets into a connected model.
=VLOOKUP(E2, A2:C100, 3, FALSE) 'classic, searches left-to-right
=XLOOKUP(E2, A2:A100, C2:C100) 'modern, any direction, exact by defaultVLOOKUP is everywhere in existing spreadsheets, so you must be able to read it — always end it with FALSE for an exact match. But for new work, XLOOKUP is easier and safer: it looks in any direction, returns exact matches by default, and doesn’t break when you insert a column. Learn VLOOKUP to maintain old files, lean on XLOOKUP for everything you build going forward.
Work with text: TEXTJOIN, TRIM, LEFT
Spreadsheets are full of messy text, and a few functions clean and combine it without retyping.
=TEXTJOIN(" ", TRUE, A2, B2) 'combine first and last name with a space
=TRIM(A2) 'strip stray leading/trailing spaces
=LEFT(A2, 3) 'first 3 characters (RIGHT, MID do the rest)TEXTJOIN combines cells with a separator and can skip blanks — far cleaner than chaining & signs. TRIM is the quiet hero of data cleanup: imported data is riddled with invisible extra spaces that break lookups and comparisons, and TRIM removes them in one pass. LEFT, RIGHT, and MID pull pieces out of a cell — the first three letters of a code, the area code from a phone number. Together they handle most of the text wrangling a clean report needs.
Work with dates: TODAY, EOMONTH, DATEDIF
Dates are just numbers under the hood, which is why you can do math on them. These three cover most date needs.
=TODAY() 'today's date, updates daily
=EOMONTH(A2, 0) 'last day of A2's month
=DATEDIF(A2, B2, "d") 'days between two datesTODAY() keeps anything time-sensitive current on its own — pair it with subtraction (=A2-TODAY()) for “days until due.” EOMONTH finds month-ends, essential for billing and reporting periods, and shifts by any number of months with its second argument. DATEDIF measures the gap between dates in days, months, or years. Date math trips people up only until they realize a date is a number they can add to and subtract from like any other.
Clean the output: ROUND and IFERROR
The finishing touches that make a formula’s result presentable and robust.
=ROUND(A2, 2) 'round to 2 decimal places
=IFERROR(VLOOKUP(...), "N/A") 'show "N/A" instead of an errorROUND controls precision so a currency figure doesn’t show twelve decimal places — and note it changes the actual value, unlike number formatting, which only changes the display. IFERROR catches errors like #N/A or #DIV/0! and replaces them with something readable, which keeps a report clean. Use IFERROR deliberately, though — add it only once a formula is proven correct, or it’ll hide a genuine mistake as cheerfully as it hides an expected blank.
From these, almost everything
SUM, IF, SUMIF, VLOOKUP and XLOOKUP, the text cleaners, the date functions, and the safety nets — that’s the toolkit behind the overwhelming majority of real spreadsheet work. None is hard on its own; the skill is recognizing which one a problem calls for, and that comes from knowing the dozen exist. Microsoft’s functions-by-category reference lists the rest for when you outgrow these. Start with the math basics, add a tier as you need it, and within a few weeks the formula bar stops being intimidating and starts being the fastest way to answer almost any question your data holds.