What are the most useful Excel formulas to learn first?

Start with SUM, AVERAGE, IF, SUMIFS, COUNTIFS, XLOOKUP, INDEX/MATCH, IFERROR, TEXTSPLIT, FILTER, and LET. That set covers most reporting, lookup, cleanup, and model-review work.

What is the difference between an Excel formula and a function?

A formula is any calculation that starts with an equals sign. A function is a built-in formula component such as SUM, IF, or XLOOKUP that you place inside a formula.

Should I learn VLOOKUP or XLOOKUP?

Learn XLOOKUP first if your version supports it. Microsoft says XLOOKUP works in any direction and returns exact matches by default, which makes it safer than VLOOKUP for most modern workbooks.

Why does my Excel formula return #N/A or #SPILL!?

#N/A usually means a lookup did not find a match. #SPILL! means a dynamic array formula needs more empty cells to return its full result.

Can AI write Excel formulas for me?

Yes, AI can draft and explain formulas well, especially for lookups and text cleanup. You should still test the result on a few rows before you trust it in a live workbook.

Does XLOOKUP work in older Excel versions?

No. Microsoft's support page says XLOOKUP is not available in Excel 2016 or Excel 2019, though those versions can open workbooks created in newer Excel releases.

Excel Formulas: 12 Essential Functions for Analysts

Name: Deckary
Rating: 4.8 (50 reviews)
Author: Deckary

Excel formulas guide for analysts covering SUMIFS, XLOOKUP, INDEX/MATCH, IFERROR, TEXTSPLIT, FILTER, and the fastest way to build safer models.

Bob · Former McKinsey and Deloitte consultant with 6 years of experienceMay 9, 20269 min read

Excel formulas are still the fastest way to turn a raw workbook into something decision-ready. Most ranking pages for "excel formulas" try to cover everything, when most analysts only need a smaller set they can trust under deadline.

For this guide, we reviewed the top 5 Google results for "excel formulas" on May 9, 2026 and checked 10 current Microsoft support pages covering formula basics, SUMIFS, XLOOKUP, INDEX, MATCH, IFERROR, TEXTSPLIT, FILTER, dynamic arrays, and LET. The gap in those pages is not coverage. It is prioritization.

This guide focuses on those formulas: totals, criteria logic, lookups, text cleanup, spill formulas, and safer model patterns. If you want the broader AI workflow around formula creation and workbook edits, read AI for Excel, How to Use Copilot in Excel, and ChatGPT for Excel.

Formula or pattern	Use it for	Why it matters
`SUM` / `AVERAGE`	Basic totals and averages	Still the fastest first pass for any reporting tab
`IF`	Simple decision rules	Good for traffic lights, flags, and status labels
`SUMIFS` / `COUNTIFS`	Criteria-based analysis	Better than filtering manually every time
`XLOOKUP`	Modern lookups	Exact match by default and can look left or right
`INDEX` + `MATCH`	Older-version or position-based lookups	Useful when `XLOOKUP` is unavailable
`IFERROR`	Error handling	Keeps dashboards readable while you debug
`TEXTSPLIT`	Breaking apart messy text	Faster than repeating Text to Columns
`FILTER`	Dynamic extracts	Builds live output ranges without copy-paste
`LET`	Cleaner long formulas	Makes nested logic easier to read and faster to recalculate

Excel formulas quick reference infographic

What Excel Formulas Are#

Excel formulas are expressions that start with an equals sign and calculate a result from values, references, operators, and functions. Microsoft's Overview of formulas in Excel says every formula starts with = and can include functions, references, operators, and constants.

That distinction matters because people often mix up formulas and functions:

Term	Meaning	Example
Formula	The full calculation in a cell	`=SUMIFS(D:D,B:B,G2,C:C,H2)`
Function	A built-in formula component	`SUMIFS`, `XLOOKUP`, `IFERROR`
Reference	A cell or range the formula reads	`B2`, `D:D`, `Sales[Region]`

If you only remember one rule, keep business assumptions in cells, not hardcoded inside formulas, whenever the value may change.

Excel Formulas Analysts Should Learn First#

Priority	Formula	Example	Best use case
1	`SUM`	`=SUM(F2:F13)`	Total revenue, spend, units
1	`AVERAGE`	`=AVERAGE(F2:F13)`	Average price, margin, cycle time
1	`IF`	`=IF(G2>=0,"On track","At risk")`	Simple logic and labels
2	`SUMIFS`	`=SUMIFS(F:F,B:B,H2,C:C,I2)`	Sum by region, product, owner
2	`COUNTIFS`	`=COUNTIFS(B:B,H2,C:C,I2)`	Count records by multiple criteria
2	`XLOOKUP`	`=XLOOKUP(A2,IDs,Owners,"Not found")`	Return a value from another table
3	`INDEX` + `MATCH`	`=INDEX(C:C,MATCH(A2,A:A,0))`	Backward-compatible lookups
3	`IFERROR`	`=IFERROR(A2/B2,"")`	Clean up expected formula errors
3	`TEXTSPLIT`	`=TEXTSPLIT(A2,",")`	Split raw exports into usable columns
3	`FILTER`	`=FILTER(A2:F200,F2:F200="Open","")`	Create dynamic output tabs
4	`LET`	`=LET(x,A2/B2,IFERROR(x,0))`	Make long formulas readable

Microsoft's functions by category page highlights SUM, IF, SUMIFS, XLOOKUP, COUNTIFS, and LET among its featured functions. That lines up with what shows up most often in analyst work: totals, criteria logic, lookups, and cleaner formulas.

Excel Formulas for Lookups, Criteria, and Reconciliation#

Excel formulas for analyst work are usually lookup formulas first, not fancy math formulas. You spend more time matching IDs, summing by criteria, and checking whether two lists agree than you do building statistical models from scratch.

SUMIFS is the default formula for multi-criteria totals. Microsoft's SUMIFS function page says it adds values that meet multiple criteria and supports up to 127 range-and-criteria pairs. That is why it belongs in every reporting workbook.

XLOOKUP is the default lookup formula if your Excel version supports it. Microsoft's XLOOKUP support page says it can look in one column and return from another regardless of direction, and exact match is the default. That alone fixes two of the biggest VLOOKUP problems.

INDEX and MATCH still matter for older workbooks. Microsoft's MATCH page says MATCH returns the relative position of a value, and Microsoft's INDEX page says INDEX returns a value from a row and column intersection. Together, they still solve a lot of inherited-model work.

Task	Best formula now	Use older fallback when needed
Sum sales for one region and one product	`SUMIFS`	`SUMPRODUCT` if you inherit older logic
Return owner by employee ID	`XLOOKUP`	`INDEX` + `MATCH`
Return column position only	`MATCH`	`MATCH`
Pull a full filtered list	`FILTER`	Advanced Filter or helper columns

If your workbook still uses VLOOKUP, upgrade it when you are already touching the model or when the current lookup is brittle enough to cause review comments.

Continue reading: PowerPoint Master Slide · Best Fonts for PowerPoint · AI for Excel

Build and audit Excel workbooks with AI

Describe the model, cleanup, audit, or output sheet you need. Deckary's AI Excel agent works through the task inside your workbook.

Try AI Excel Agent See How It Works

Excel Formulas for Cleanup and Dynamic Arrays#

Excel formulas have improved a lot since dynamic arrays arrived. Two functions matter most for messy business data: TEXTSPLIT and FILTER.

Microsoft's TEXTSPLIT page says the function splits text by column and row delimiters and works like Text to Columns, but in formula form. That makes it much better for repeatable cleanup because the result stays live when the source text changes.

Microsoft's FILTER page says FILTER returns an array based on criteria you define, and the result spills automatically. Microsoft's dynamic array guide explains that spill behavior means Excel fills neighboring cells for you from a single formula.

That leads to a simple rule:

If the job is...	Use this
Split one messy text field into columns	`TEXTSPLIT`
Extract only open items, one region, or one owner	`FILTER`
Sort or de-duplicate a live result	`SORT`, `UNIQUE`, or both on top of `FILTER`
Clean a one-off export once	Native Text to Columns is fine

Microsoft's support pages show TEXTSPLIT and LET in newer Excel and Microsoft 365 editions, not legacy perpetual releases. If you work across mixed client environments, use newer functions only when you know the receiving team can open them.

Excel Formulas for Safer Models and Cleaner Logic#

Excel formulas break most often in lookups, division steps, and copied logic that no one wants to read later. IFERROR and LET help, but only if you use them carefully.

Microsoft's IFERROR page says it can catch #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. That is useful for client-facing tabs, but it is not a substitute for finding the underlying issue. Wrap the last mile of a formula, not every broken assumption upstream.

LET is the most underused modern Excel function. Microsoft's LET documentation says it lets you assign names inside a formula and supports up to 126 name-and-value pairs. More important than the number is the benefit Microsoft calls out: Excel can calculate a repeated expression once instead of several times.

Use LET when:

You repeat the same lookup or division several times
The formula is long enough that another analyst will struggle to review it
You want to name intermediate logic such as price, volume, or variance

Avoid one common mistake with IFERROR: hiding genuine data issues. If a lookup should always find a match, returning a blank can hide the fact that your join key changed.

When to Ask AI to Build Excel Formulas#

AI is useful for Excel formulas when the logic is clear but the syntax is annoying. It is much less useful when the workbook itself is messy and the formula request is vague.

Microsoft and LinkedIn's 2024 Work Trend Index surveyed 31,000 knowledge workers across 31 markets and found that 75% already use AI at work. In spreadsheet work, the best use is still first-pass drafting and explanation.

Use AI for:

Drafting XLOOKUP, SUMIFS, or nested IF formulas from plain-English logic
Explaining what a long inherited formula is doing
Converting a cleanup rule into a TEXTSPLIT, FILTER, or LET pattern

Test the output on a few known rows, then inspect edge cases. If the task is workbook-wide rather than one-cell-wide, tools like Deckary are a better fit because they can read the workbook, write formulas or output sheets, and verify the result before destructive edits. For more on that workflow, see Best AI for Excel, AI Tools for Excel, and Microsoft Copilot for Excel.

Common Excel Formula Mistakes#

Mistake	Why it causes trouble	Better approach
Hardcoding assumptions inside formulas	Reviewers cannot see what changed	Put the assumption in a cell and reference it
Using `VLOOKUP` everywhere by habit	Left-to-right limit and brittle column indexes	Use `XLOOKUP` or `INDEX` + `MATCH`
Wrapping every formula in `IFERROR`	Real data issues disappear	Catch only expected errors
Forgetting spill range space	`#SPILL!` blocks dynamic arrays	Leave output room around `FILTER` and `SORT`
Building unreadable nested logic	Hard to audit and hand off	Use helper columns or `LET`

Summary#

Learn SUMIFS, COUNTIFS, XLOOKUP, INDEX + MATCH, IFERROR, TEXTSPLIT, FILTER, and LET.
Use XLOOKUP by default when your Excel version supports it.
Use dynamic array formulas for live outputs, but leave room for spill ranges.
Use AI to draft and explain formulas, then verify the result in the workbook.
Keep formulas readable enough that the next analyst can audit them in minutes, not hours.