Excel formulas everyone should actually know

The shortlist

Most everyday spreadsheet work runs on a small set of formulas. Get comfortable with these and you can handle the large majority of tasks people call "advanced".

• SUM, AVERAGE, COUNT for the basic math.
• IF for decisions, plus IFS when there are several.
• XLOOKUP (or VLOOKUP on older versions) to pull a value from another table.
• SUMIF and COUNTIF to total or count only the rows that match a condition.
• TEXT, LEFT, RIGHT, TRIM to clean and format messy data.
• IFERROR to keep your sheet readable when something goes wrong.

Everything past this list is either a niche tool or a job better done by a pivot table. A sensible order to learn them:

1. 1Start with SUM and IF so basic math and decisions feel automatic.
2. 2Add XLOOKUP next, because connecting two tables is where most real value shows up.
3. 3Then SUMIF, COUNTIF and the cleanup functions for conditional totals and tidy data.
4. 4Learn IFERROR last to keep everything above readable when something breaks.

The everyday core

These four cover the math and the decisions in almost every sheet.

• SUM adds a range: =SUM(B2:B20). Obvious, but people still add cells by hand and introduce errors doing it.
• AVERAGE and COUNT do exactly what they say. COUNT counts numbers, COUNTA counts non-empty cells (handy for lists of text).
• IF returns one thing when a condition is true and another when it is false: =IF(C2>1000,"Review","OK"). This is the workhorse of any sheet that makes a decision.
• IFS handles several conditions without nesting a pile of IFs inside each other. Easier to read, easier to fix later.

If any of this is new, start at the beginning rather than copying formulas you do not understand. Our Excel introduction certificate builds these from zero, and Microsoft’s own functions reference is the reliable place to check exact syntax.

The lookup that changes everything

XLOOKUP, and why it retired VLOOKUP

The single most useful formula to learn is a lookup: given a value in one place, go find the matching value somewhere else. Order IDs to customer names, product codes to prices, email addresses to regions. This is the formula that turns two disconnected tables into one.

XLOOKUP is the modern version: =XLOOKUP(A2, Products[Code], Products[Price]) reads as "find A2 in the code column, return the matching price". It is clearer and less breakable than the old VLOOKUP, which counted columns and shattered the moment someone inserted one. If you are on a recent version of Excel, learn XLOOKUP and skip VLOOKUP’s quirks. Microsoft’s XLOOKUP guide walks through every argument.

If you learn exactly one new Excel formula this month, make it XLOOKUP. It is the difference between copying data between sheets by hand and letting the sheet connect itself.

Conditional math and cleaning up messes

Two more groups earn their place in your muscle memory: the "only count what matches" formulas, and the ones that tidy human-entered data.

• SUMIF totals only the rows that meet a condition: =SUMIF(Region, "West", Sales) adds the sales where the region is West. SUMIFS handles several conditions at once.
• COUNTIF counts rows that match: how many orders over $500, how many entries say "pending". Same idea, counting instead of adding.
• TRIM strips the stray spaces that break lookups and comparisons. Nine times out of ten, a lookup that "should work but doesn’t" is a trailing space.
• TEXT formats a value as text you control: dates, currency, leading zeros. LEFT, RIGHT and MID pull pieces out of a cell (an area code, a product prefix).
• IFERROR wraps a formula so an error shows something readable instead of a wall of #N/A: =IFERROR(XLOOKUP(...), "Not found").

Once these feel automatic, the honest next step is analysis rather than more formulas. Our Excel certificate takes you through them properly, and the Excel data analysis certificate picks up where formulas start to strain.

When a formula is the wrong tool

Reach for a formula too soon and you build something fragile. When you find yourself summarizing the same data a dozen different ways, stop writing SUMIFs and use a pivot table. It answers "total sales by region by month" in seconds, updates when the data changes, and does not break when someone adds a row. Formulas are for calculations. Pivot tables are for summaries. Trying to force one to do the other’s job is where spreadsheets turn into spaghetti.

And you do not need a full course to solve one specific problem. If you just need today’s XLOOKUP to behave, a help article or a quick search will get you there faster. The course is for building the whole skill so you stop searching for the same answers every week. When Excel has become a real part of your job, that is when the Business & Productivity school is worth the time, not before.

Common questions