Let’s be honest.
You’ve opened Excel a thousand times. You know SUM. You know AVERAGE. Maybe you’ve even braved a VLOOKUP once — and then spent 20 minutes debugging why it returned #N/A.
Here’s the problem with most “Excel formulas” articles: they list 50 functions you’ll never touch. Who actually needs ACCRINTM? No one.
So I cut through the noise. These are the 10 formulas I’ve watched actual professionals use every single week. Not textbook examples. Real work.
One more thing before we dive: I’m not going to pretend you have the latest Excel. I’ll note which formulas work in older versions. Because let’s face it, half of us are still on Excel 2019 at work.
The 10 Formulas (No Fluff)
1. XLOOKUP — The King of Lookups
What it does: Finds a value in one column and returns a matching value from another column.
Real example: You have a list of employee IDs. You need their department names from a separate table.
text
=XLOOKUP(A2, Employees!A:A, Employees!B:B)
Why it’s better than VLOOKUP:
- No counting columns (VLOOKUP needs a column index number — annoying).
- Works left or right (VLOOKUP only looks right).
- No #N/A freakouts by default.
Expert Tip: Add a fourth argument to handle missing values:
=XLOOKUP(A2, Employees!A:A, Employees!B:B, "Not found")
Common mistake: Using XLOOKUP in an old Excel file shared with coworkers who have Excel 2016. It will break for them. For compatibility, use INDEX/MATCH (see formula #10).
2. FILTER — Extract What You Need Instantly
What it does: Returns all rows that meet a condition.
Real example: You have a sales table. You want only rows where Region = “West”.
text
=FILTER(A2:D100, B2:B100="West", "No results")
This single formula replaces what used to take filters, copy-paste, or array formulas. It’s a game-changer.
When NOT to use it: Your coworker has Excel 2019 or earlier. It won’t work. Use a pivot table instead.
3. IFERROR — Stop Ugly Error Messages
What it does: Hides errors and shows something you choose instead.
Real example: You’re dividing by zero in a report your boss will see. #DIV/0! looks unprofessional.
text
=IFERROR(A2/B2, "Check data")
Now it shows “Check data” instead of an error.
Personal opinion: IFERROR is overused. Don’t hide errors you actually need to fix. Use it only for presentation layers, not data validation.
4. TEXTJOIN — Combine Text Without Headaches
What it does: Joins multiple cells with a delimiter you choose, skipping blanks.
Real example: You have first names in column A, last names in B. You want “John Smith” format.
text
=TEXTJOIN(" ", TRUE, A2, B2)
The TRUE part skips empty cells. CONCATENATE doesn’t do that. You’ll never go back.
5. UNIQUE — List Distinct Values Instantly
What it does: Returns a list of unique values from a range.
Real example: You have 500 rows of sales data with repeat customer names. You just need the list of unique customers.
text
=UNIQUE(A2:A500)
It spills the results down automatically. No more “Remove Duplicates” button and losing your other data.
Where it fails: Old Excel versions again. Sorry.
6. SUMIFS — Conditional Summing (The Real Deal)
What it does: Adds numbers that meet multiple conditions.
Real example: Sum all sales over $500 from the West region in January.
text
=SUMIFS(SalesAmount, RegionColumn, "West", SalesAmount, ">500", MonthColumn, "January")
Yes, SUMIFS (with an S) is almost always better than SUMIF (without the S). The order of arguments is different, so don’t mix them up.
Expert Tip: Use SUMIFS even for one condition. Then when you need to add another condition later, you don’t have to rewrite everything.
Common mistake: Putting the sum range last. Wrong. It’s first in SUMIFS.
7. COUNTIFS — The Pivot Table Alternative for Quick Counts
What it does: Counts cells that meet conditions.
Real example: How many unread emails in a status column? How many projects marked “High Priority”?
text
=COUNTIFS(StatusColumn, "Unread", PriorityColumn, "High")
I use COUNTIFS constantly for sanity checks before building pivot tables.
8. SORT — Because Manual Sorting Is a Waste
What it does: Sorts a range by one or more columns dynamically.
Real example: Your data updates daily. You always want it sorted by date, newest first.
text
=SORT(A2:D100, 1, -1)
The -1 means descending (newest first). The best part? When your source data changes, the sort updates automatically. No more re-sorting manually.
9. LET — Name Your Variables (Game Changer)
What it does: Assigns names to calculations inside a formula.
Real example: You’re calculating a commission that uses the same value three times.
Without LET (messy):
text
=(B2*0.1)+(B2*0.1*0.2)+(B2*0.1*0.05)
With LET (clean):
text
=LET(sales, B2, commission, sales*0.1, commission + (commission*0.2) + (commission*0.05))
It’s easier to read. Easier to debug. And it calculates faster because Excel only evaluates sales*0.1 once.
Honest truth: LET took me a year to start using. Now I can’t stop. Just try it once.
10. INDEX/MATCH — The Old Reliable
What it does: Same as XLOOKUP but works in any Excel version ever made.
Real example: You need to find a price from a table where the lookup column is to the right of the result column. VLOOKUP fails here.
text
=INDEX(PriceColumn, MATCH(ProductID, IDColumn, 0))
When to use this instead of XLOOKUP:
- You share files with people on old Excel.
- You work in a locked-down corporate environment that hasn’t updated.
- You’re applying for jobs that test legacy Excel skills.
Is it harder to learn? Yes. Is it worth it? Also yes.
Your Quick Reference Cheat Sheet
| Formula | Best For | Excel Version |
|---|---|---|
| XLOOKUP | Looking up values | 2021+ / 365 |
| FILTER | Extracting matching rows | 2021+ / 365 |
| IFERROR | Hiding ugly errors | 2007+ |
| TEXTJOIN | Combining text | 2019+ / 365 |
| UNIQUE | Distinct values | 2021+ / 365 |
| SUMIFS | Conditional sums | 2007+ |
| COUNTIFS | Conditional counts | 2007+ |
| SORT | Auto-updating sorting | 2021+ / 365 |
| LET | Clean complex formulas | 2021+ / 365 |
| INDEX/MATCH | Lookups (any version) | All versions |
3 Common Mistakes That Will Ruin Your Day
Mistake #1: Forgetting absolute references ($A$1)
You write a formula. You drag it down. Everything shifts. Disaster. Lock your ranges with F4.
Mistake #2: Mixing up SUMIF and SUMIFS argument order
SUMIF: =SUMIF(range, criteria, sum_range)
SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1)
They look similar. They are not. I’ve made this mistake at 5 PM on a Friday. It’s humbling.
Mistake #3: Using entire column references (A:A)
It’s convenient. But it slows down your workbook dramatically. Use A2:A1000 instead. Your computer will thank you.
FAQ
What’s the fastest way to learn these formulas?
Open Excel right now. Type each one with fake data. Break them on purpose. See what happens. That’s how you actually learn.
Do I need to memorize all of them?
No. Remember what each formula does. Look up the syntax when you need it. Even experts use Google.
Which Excel version do I need for these?
Eight of them work in Excel 2019 or newer. INDEX/MATCH works in anything. If you’re on Excel 2016 at work, skip XLOOKUP and FILTER for now.
What’s one formula on this list I should absolutely learn first?
XLOOKUP. Then IFERROR. Then SUMIFS. That covers 80% of what people actually need.
Why didn’t you include VLOOKUP?
Because XLOOKUP is better in every way. If you’re stuck on an old version, use INDEX/MATCH instead. VLOOKUP is the past.
Internal Links
- Dig deeper: XLOOKUP vs VLOOKUP: Which One Should You Actually Use? (supporting article 1)
- Avoid these: 7 Excel Formula Mistakes That Cost You Hours (supporting article 2)
- Practice here: The 15-Minute Excel Productivity Workout (supporting article 3)
External Resources (Suggested)
- ExcelJet – best formula reference site, period.
- Microsoft’s dynamic array formulas documentation – for when you’re ready to go deeper.
Independent technology writer focused on artificial intelligence, emerging technologies, and digital innovation. Covers AI applications in sports, productivity, and online business.
