Mastering Excel formulas has become an essential skill for every individual working in tech or the tech industry. The reason is clear: most white-collar jobs require working with data that often includes using Microsoft Excel. There are a lot of formulas, and it can be complicated to learn them all at once. You just have to learn what each formula does in isolation, then start to implement them in real-world cases.
This guide will help you do that. It includes a comprehensive list of different formulas to run on the same 10-row order-tracking sheet. By the end, you'll have effectively built one working tracker instead of skimming fifty flashcards. I'll show the dataset once, then reuse the same cell references all the way through. If you want to follow along, open a blank sheet and copy the table below first.
This is a simple order tracker for a small sales team. Row 1 is headers; data runs from row 2 to row 11.
| A: Order ID | B: Region | C: Rep | D: Units | E: Unit Price | F: Order Date | G: Status | H: Total |
| 1001 | North | Priya Sharma | 12 | 450 | 05-Jan-2026 | Delivered | 5400 |
| 1002 | South | Aman Verma | 8 | 620 | 07-Jan-2026 | Pending | 4960 |
| 1003 | East | Priya Sharma | 15 | 300 | 10-Jan-2026 | Delivered | 4500 |
| 1004 | West | Neha Gupta | 5 | 900 | 12-Jan-2026 | Cancelled | 4500 |
| 1005 | North | Aman Verma | 20 | 450 | 15-Jan-2026 | Delivered | 9000 |
| 1006 | South | Neha Gupta | 10 | 620 | 18-Jan-2026 | Pending | 6200 |
| 1007 | East | Priya Sharma | 7 | 300 | 20-Jan-2026 | Delivered | 2100 |
| 1008 | West | Aman Verma | 18 | 900 | 22-Jan-2026 | Delivered | 16200 |
| 1009 | North | Neha Gupta | 9 | 450 | 25-Jan-2026 | Pending | 4050 |
| 1010 | South | Priya Sharma | 14 | 620 | 28-Jan-2026 | Delivered | 8680 |
Column H (Total) is just =D2*E2 filled down - keep that in the sheet, because a few formulas later reference it directly.
Key takeaway: these are the formulas you'll type without thinking about it within a week - they're the arithmetic layer everything else sits on top of.
|
Returns 65,590. The one habit worth building early: select a full column range (H2:H11) rather than typing individual cells, so new rows you add later get picked up automatically if you convert the range to a Table.
|
Gives 11.8. Worth knowing: AVERAGE ignores blank cells but not zeros - a zero-unit order will drag this down in a way a blank row won't.
|
Returns 10. If you instead need to count cells with text in them (say, counting how many rows have any Status entered at all), use COUNTA - COUNT only looks at numbers.
|
2,100 and 16,200. Useful as a quick sanity check before you present numbers - if MAX looks suspiciously high, you'll usually find a decimal or unit-price typo behind it.
|
6,559. Rounding at the display stage rather than rounding the raw data keeps your totals accurate even though the report looks tidy.
|
600. This is the formula people reach for in variance reports where "over" and "under" both need to show as a positive gap.
|
Order 1008 (16,200) returns "High Value"; everything else returns "Standard."
|
Checks that an order is both delivered and worth at least 5,000. On its own AND just returns TRUE/FALSE - it earns its keep once you wrap it in IF (see the Combining Formulas section below).
|
Flags anything that isn't a completed sale yet - handy for a "needs follow-up" column.
|
Returns TRUE for every status except Cancelled. It reads more naturally than an OR of every other status when you only care about excluding one thing.
|
Order 1008 (16,200) → Platinum, order 1005 (9,000) → Gold, everything else → Standard. The trailing TRUE,"Standard" is doing the job an ELSE would do in other languages - don't skip it or non-matching rows return #N/A.
|
Best for a short, known list of values (like your four regions) - once you're past six or seven cases, a lookup table is easier to maintain than a long SWITCH.
|
5,400 + 9,000 + 4,050 = 18,450.
|
Returns 6.
|
6,150.
|
5,400 + 9,000 = 14,400. Note the argument order flips versus SUMIF - the sum range comes first here, not last. This trips people up constantly when switching between the two.
|
Returns 1 (order 1002).
Key takeaway: if you're on Microsoft 365, learn XLOOKUP first and treat VLOOKUP/INDEX-MATCH as "what to use if a client's file is on an older Excel version."
|
Returns "Aman Verma." Unlike VLOOKUP, the lookup column doesn't have to be the leftmost one, and a missing match returns a clean value you set yourself instead of an ugly #N/A - add a fourth argument like ,"Order not found" and you get that for free.
|
Column 3 counting from A means column C (Rep). This is the formula's biggest weakness in practice - insert a new column between A and C and the "3" now points somewhere else, silently. XLOOKUP doesn't have this problem because you reference the return column directly.
|
Returns "Aman Verma" (row 5 of the range, which is order 1005).
|
Returns 5 - that's the position INDEX used above. This is why INDEX and MATCH are almost always taught together: MATCH finds the row, INDEX returns the value at that row.
|
Same result as MATCH here, but XMATCH also supports searching from the last item backwards and approximate-match modes MATCH doesn't handle as cleanly.
|
Lands on order 1006. Performance note: OFFSET recalculates every single time anything on the sheet changes, even in cells that have nothing to do with it. On a small tracker like this it's invisible; on a sheet with thousands of rows and several OFFSET formulas, it can noticeably slow things down. INDEX doesn't have this problem and can usually do the same job.
|
"Priya Sharma - North."
|
"Priya Sharma, Aman Verma, Priya Sharma."
|
"Priya."
|
"harma."
|
Starting at character 7 for 5 characters: "Sharm."
|
12 characters for "Priya Sharma."
|
Won't visibly change "Priya Sharma" here, but run it on a column pasted from a CRM export and you'll often see LEN drop by a character or two - that's trailing whitespace you didn't know was there.
|
|
Both update on their own every time the sheet recalculates - don't use them if you need a date to stay fixed once it's entered (use Ctrl+; to paste a static date instead).
|
|
DATEDIF is genuinely undocumented in Excel's own function list, but it's stable and everyone uses it anyway - swap "D" for "M" or "Y" to get months or years instead of days.
|
2026 and 1. This is how you'd build a "group by month" summary without touching a Pivot Table.
|
31-Jan-2026. Change the 0 to 1 and you get the last day of the following month instead - useful for calculating a payment due date.
|
|
Use IFNA instead of IFERROR when you specifically want to catch "not found" but still see a real error if something else goes wrong (like a #DIV/0! hiding somewhere it shouldn't).
|
|
|
This one catches a surprisingly common problem: numbers imported from another system (or typed with a stray space) that Excel treats as text. SUM and AVERAGE will silently skip those cells rather than erroring, so a wrong total is often the first sign something's wrong.
|
5,050.
|
|
In current Excel, prefer STDEV.S for a sample (which this is - 10 orders out of presumably many more over time) or STDEV.P if this really is your entire population of orders. Plain STDEV still works but is the older, less explicit name for STDEV.S.
|
5,400 ranks 6th out of 10.
|
Note: FILTER, SORT, UNIQUE and LET need Excel 365 or Excel 2021+. On older versions these will show a #NAME? error instead.
|
Add or change an order's status and this list updates on its own - it's the modern replacement for a manually maintained "Delivered only" tab.
|
|
Returns North, South, East, West once each - useful as the source list for a dropdown or a summary table.
|
On a small sheet the performance gain is invisible; on a heavy model with the same range referenced five times in one formula, LET can meaningfully speed things up and makes the formula readable besides.
Single formulas rarely do the whole job on their own - most real spreadsheets stack two or three together. Using the same tracker:
|
|
|
Formulas aren't only for calculation cells - they can drive highlighting rules too. To flag every Pending order automatically:
=$G2="Pending" and pick a fill color.Because the reference is $G2 (column locked, row relative), the rule checks column G on whatever row each cell belongs to, so it correctly highlights entire rows rather than just column G itself.
Microsoft has been shipping formulas faster than most "Excel formulas" guides get updated. Here are the ones actually worth knowing in 2026, still using our tracker.
|
|
|
|
|
|
Returns "1005." This one alone can replace several nested MID/FIND formulas people used to build for exactly this kind of extraction.
|
|
|
| Mistake | What Happens | Example | Fix |
| Wrong cell range | Result silently excludes rows | =SUM(H2:H10) when data runs to H11 | Convert the range to a Table so it expands automatically |
| Missing parentheses | Wrong order of operations | =D2*E2+100 vs. =D2*(E2+100) | Always bracket the part you want calculated first |
| Dividing by zero | #DIV/0! error | =H2/D2 when Units is 0 | =IFERROR(H2/D2,"N/A") |
| Numbers stored as text | SUM/AVERAGE quietly skips the cell | "12" typed with a leading space | Check with ISNUMBER, then re-enter or use VALUE() |
| Relative vs. absolute reference | Formula shifts unexpectedly when copied | B2 instead of $B$2 in a rule meant to stay fixed | Lock with $ wherever the reference must not move |
| Lookup value not found | #N/A | VLOOKUP(9999,...) when 9999 doesn't exist | Wrap in IFNA or IFERROR |
| Hidden trailing spaces | Lookups and matches fail silently | "Delivered " vs. "Delivered" | TRIM the source column |
Learning Resources:
SUM, IF, VLOOKUP or XLOOKUP, COUNTIF, and IFERROR cover the majority of everyday spreadsheet work. Everything else in this guide builds on those five.
If you're on Microsoft 365 or Excel 2021+, learn XLOOKUP first - it's more flexible and less fragile when columns get inserted or removed. Learn VLOOKUP as well if you regularly work with files from people on older Excel versions.
#N/A almost always means a lookup didn't find a match (check for typos or trailing spaces). #DIV/0! means you divided by an empty or zero cell. #VALUE! usually means a formula expected a number but found text. Wrap any formula prone to these in IFERROR or IFNA once you understand why it's happening - don't reach for IFERROR before you've diagnosed the actual cause.
Select your range, go to Data → Remove Duplicates, choose which columns must match, and confirm. Keep a copy of the original data before doing this - it can't be undone once you close the file.
No. They require Microsoft 365 or Excel 2021 and later. On Excel 2019 or older, they'll return a #NAME? error.
Somewhere north of 500 built-in functions as of the current Microsoft 365 release, and Microsoft keeps adding more. Realistically, the roughly 50 covered in this guide will handle almost all day-to-day spreadsheet work.
