Formulas

15 Excel Formulas That Save Hours of Manual Work (With Examples)

By Sagnik Bhattacharya15 Mar 202611 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for 15 Advanced Excel Formulas Every Professional Should Know, with formula blocks, dynamic arrays, and advanced spreadsheet logic.

Once you've mastered SUM, VLOOKUP, and IF, these 15 advanced formulas will take your Excel skills to the professional level. Each one solves a real business problem that basic formulas can't handle — and for every formula below you'll find the syntax, a real-world scenario where it beats the alternatives, and the common mistake that trips up beginners.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

Who this is for: analysts, operations staff, finance teams, and anyone who opens Excel daily and wants to stop doing by hand what a formula can do in one cell. You'll need Excel 365 or Excel 2021 for the newer dynamic array functions (FILTER, SORT, UNIQUE, LAMBDA, LET, CHOOSECOLS, VSTACK); the rest work in any modern Excel version.

How to read this tutorial: each section follows the same shape — what the formula does, the syntax, a real scenario, and the pitfall. Jump to the Troubleshooting section if a formula is misbehaving, or the FAQ for the most common beginner questions.

1. SUMPRODUCT

Sum values based on multiple criteria without needing an array formula:

=SUMPRODUCT((A2:A100="Sales")*(B2:B100="North")*(C2:C100))

This sums column C only where column A is "Sales" AND column B is "North".

Real-world scenario: you have a sales register with Region, Product, and Revenue columns and your boss wants the total revenue for "Sales" team in the "North" region. SUMIFS does this too, but SUMPRODUCT shines when the criteria involve arithmetic — e.g. summing revenue where a calculated margin exceeds 20%, which SUMIFS cannot do directly.

Beginner pitfall: the ranges must be the same size. Mixing A2:A100 with B2:B50 returns #VALUE!. Also, boolean conditions like (A2:A100="Sales") return TRUE/FALSE arrays — multiplying them coerces to 1/0, which is why the pattern works. For an alternative approach to multi-criteria counting, pivot tables handle this interactively.

2. INDEX-MATCH (Two-Way Lookup)

=INDEX(C2:F10, MATCH("Product A",A2:A10,0), MATCH("Q2",C1:F1,0))

Looks up a value based on both a row and column header — like a grid lookup.

Real-world scenario: a quarterly revenue matrix where product names run down column A and quarters run across row 1. INDEX-MATCH lets you pull any cell in the grid by naming the product and the quarter, without hard-coding cell references. Unlike VLOOKUP, you can insert a new column anywhere without breaking the formula.

Beginner pitfall: the third argument to MATCH must be 0 for exact match. Leaving it blank defaults to 1 (approximate match) which requires the lookup range to be sorted and silently returns wrong answers when it isn't. For a deeper comparison with the newer alternative, see INDEX-MATCH — the superior alternative to VLOOKUP.

3. TEXTJOIN

Combine multiple cell values with a delimiter, skipping blanks:

=TEXTJOIN(", ", TRUE, A2:A20)

Real-world scenario: you have a column of employee names with some gaps, and you need a single comma-separated list for an email BCC field. CONCAT will string them together but leaves awkward extra commas where blanks are; TEXTJOIN's second argument (TRUE) skips blanks so the output stays clean.

Beginner pitfall: TEXTJOIN truncates if the output exceeds 32,767 characters — a genuine problem when joining a 50,000-row column. If you hit that limit, break the range into chunks and TEXTJOIN the results.

4. UNIQUE (Dynamic Array)

=UNIQUE(A2:A100)

Returns a list of unique values that automatically spills into adjacent cells.

Real-world scenario: you receive a weekly orders export with thousands of rows and need the list of distinct customer IDs for a report. Before dynamic arrays this required "Remove Duplicates" which destroys your source data; UNIQUE produces the list as a live formula that refreshes when the source changes.

Beginner pitfall: make sure to clean your data first — trailing spaces and inconsistent casing ("Alice" vs "alice ") will produce false "unique" entries. Wrap the input in TRIM and LOWER if the source is messy.

5. SORT

=SORT(A2:C100, 3, -1)

Dynamically sorts data by column 3 in descending order.

Real-world scenario: a live leaderboard that always shows the top sellers sorted by revenue. Because SORT is a formula, refreshing the source data (or adding new rows) re-sorts automatically — no manual "Sort A-Z" click required, and no risk of someone sorting the source table and breaking the row order for another analyst.

Beginner pitfall: the second argument is the column index inside your range, not the worksheet column letter. If your range is B2:E100, column C is index 2, not 3.

6. FILTER

=FILTER(A2:D100, C2:C100>50000, "No results")

Returns rows where column C exceeds 50,000. Replaces complex Advanced Filter setups.

Real-world scenario: a dashboard cell that lists only the deals above your sales threshold, updating as the deal pipeline changes. The third argument ("No results") is the empty-state string — without it you get a jarring #CALC! error when nothing matches.

Beginner pitfall: FILTER spills its output. If a cell immediately below the formula contains anything (even a space), you'll see #SPILL! and nothing will render. Clear the spill range or move the formula. For the dedicated fix see how to fix #SPILL! errors in Excel.

7. LET

Define variables within formulas to avoid repeating calculations. LET is especially useful when building financial models with complex, multi-step calculations:

=LET(total, SUM(B2:B100), avg, AVERAGE(B2:B100), total/avg)

Real-world scenario: a formula that uses FILTER(...) three times with the same arguments. Without LET, Excel recomputes FILTER three times, which on a 100,000-row range is measurably slow. Assigning the result to a LET variable once and reusing it cuts recalculation time dramatically.

Beginner pitfall: LET variable names can't contain spaces and can't clash with existing cell references (so A1 as a variable name will silently break). Stick to short descriptive lowercase names like total, filtered, rate.

8. LAMBDA

Create custom, reusable functions:

=LAMBDA(price, tax, price * (1 + tax))(100, 0.18)

Name it via Formulas → Name Manager to use it like a built-in function.

Real-world scenario: every spreadsheet in your team repeats the same gross-to-net tax calculation. Save it once as a named LAMBDA called NET_FROM_GROSS and every team member can type =NET_FROM_GROSS(A2, 0.18) instead of copy-pasting the logic. Unlike VBA UDFs, LAMBDA functions travel with the workbook and don't trigger the macro-security warning.

Beginner pitfall: the inline-call pattern =LAMBDA(...)(args) is only useful for testing. For reuse, you must save the LAMBDA via Name Manager — otherwise it only exists in the one cell where you typed it.

9. XLOOKUP with Multiple Returns

=XLOOKUP("Alice", A2:A100, B2:D100)

Returns multiple columns at once — spills across cells automatically.

Real-world scenario: an employee lookup where, given an employee name, you need to return their department, manager, and start date in one go. Traditional VLOOKUP requires three separate formulas pointing at three different column indexes; XLOOKUP with a multi-column return_array grabs all three in one pass.

Beginner pitfall: the return_array must have the same number of rows as the lookup_array. Mixing A2:A100 (99 rows) with B2:D50 (49 rows) returns #VALUE!.

10. INDIRECT

Create dynamic references from text strings:

=SUM(INDIRECT("Sheet_"&A1&"!B:B"))

Sums column B from a sheet whose name is in cell A1.

Real-world scenario: monthly report workbooks with one sheet per month (Sheet_Jan, Sheet_Feb, …). A dashboard cell can sum revenue from whichever month's name is typed into A1, without maintaining twelve separate formulas.

Beginner pitfall: INDIRECT is volatile — it recalculates on every change anywhere in the workbook. On a sheet with thousands of INDIRECT calls, this cripples performance. Prefer named ranges or CHOOSE for small fixed sets. Also: if the referenced sheet name contains a space, wrap it in single quotes: "'"&A1&"'!B:B".

11. IFERROR + IFNA

=IFERROR(VLOOKUP(A2,Data!A:C,3,0), "Not found")

Handle errors gracefully. Use IFNA when you only want to catch #N/A errors.

Real-world scenario: a customer-facing report where raw #N/A cells look unprofessional. Wrapping the lookup in IFERROR replaces every error with a friendly "Not found". Use IFNA rather than IFERROR when you want to preserve signalling errors like #DIV/0! or #VALUE! (so genuine formula bugs still surface instead of silently reading "Not found").

Beginner pitfall: IFERROR hides all errors, including real ones. If you wrap a formula in IFERROR while building it, you won't notice you're dividing by zero or referencing the wrong sheet. Add IFERROR last, after you've validated the formula works. To prevent errors at the source, set up data validation rules on your input cells.

12. COUNTIFS (Multiple Criteria)

=COUNTIFS(A2:A100,"Sales", B2:B100,">50000", C2:C100,"<>")

Count cells matching multiple conditions across multiple columns.

Real-world scenario: "how many deals did the Sales team close above £50k that have a non-empty close-date column?" That single question becomes one COUNTIFS call. The "<>" operator means "not empty" and is the most commonly forgotten piece of COUNTIFS syntax.

Beginner pitfall: comparison operators must be inside quotes (">50000", not >50000). To compare against a cell value, concatenate: ">"&D1.

13. SEQUENCE

=SEQUENCE(10, 1, 1, 1)

Generates a sequence of numbers. Combine with other functions for dynamic lists, date ranges, and numbering.

Real-world scenario: building a calendar. Pair SEQUENCE with DATE to generate all working days in a month: =SEQUENCE(30, 1, DATE(2026,4,1), 1) returns April's dates as a spilled column. Combine with WEEKDAY filtering for business-day-only lists.

Beginner pitfall: SEQUENCE spills a range. Formulas that expect a single value (like passing SEQUENCE to a cell referenced elsewhere) need @ to "grab the first" or a wrapper like INDEX.

14. CHOOSECOLS / CHOOSEROWS

=CHOOSECOLS(A1:F100, 1, 3, 5)

Select specific columns from a range — great for rearranging data without manual copying.

Real-world scenario: an import from another system gives you 12 columns but the report template only needs columns 1, 4, and 9, in that order. CHOOSECOLS returns a reshaped spill with just those columns, and because it's a formula, re-running the import doesn't break the report.

Beginner pitfall: negative indices count from the right (-1 = last column). Mixing positive and negative indices in a single call works but is a common source of confusion — prefer consistent counting direction.

15. VSTACK / HSTACK

=VSTACK(Sheet1!A1:C10, Sheet2!A1:C10)

Vertically combine ranges from multiple sheets into one dynamic array.

Real-world scenario: consolidating monthly transaction sheets into one quarterly view. Previously this meant copy-paste-append-copy-paste; VSTACK does it as a single spilled formula that updates when any of the source sheets change. Perfect feed into a PivotTable on the consolidated spill.

Beginner pitfall: the source ranges must have matching column counts for VSTACK (and matching row counts for HSTACK). Mismatched ranges pad the shorter one with #N/A, which can be desirable or surprising depending on your intent.

Learning Strategy

Don't memorise all 15 at once. Pick the 3-4 that solve problems you face regularly, practice them in real spreadsheets, and gradually add more to your toolkit. A workable starter set for most business roles: XLOOKUP (replaces VLOOKUP), FILTER (replaces copy-paste reports), UNIQUE (replaces Remove Duplicates), and IFERROR (cleans up client-facing sheets). Add SUMPRODUCT and COUNTIFS when multi-criteria aggregation shows up, and reach for LET and LAMBDA once you catch yourself typing the same sub-formula three times in one cell.

If you want an AI shortcut while you learn, ask Claude or Copilot to draft the formula in plain English first, then edit — how to use Claude AI to write Excel formulas instantly walks through the exact prompt pattern.

Troubleshooting: 5 errors beginners hit with advanced formulas

#SPILL! when using FILTER, SORT, UNIQUE, SEQUENCE, or XLOOKUP with multi-return. A cell inside the spill range is non-empty — often a stray space from an old paste. Fix: select the cells below the formula, press Delete, and the spill will render. If you need the formula to land inside a table, convert the table to a range first (tables don't accept spills).
#VALUE! from SUMPRODUCT or XLOOKUP. The ranges don't match size. Example: SUMPRODUCT((A2:A100="X")*(B2:B99)) — 99 rows vs 98 rows. Fix: double-check every range in the formula ends on the same row.
INDIRECT returns #REF!. The referenced sheet doesn't exist, has been renamed, or contains a space. Fix: add single quotes around the sheet name inside the INDIRECT text: "'"&A1&"'!B:B". Also confirm the sheet tab still exists.
LAMBDA works in the test cell but not after naming. The Name Manager saved it but you're calling it with the wrong number of arguments. Fix: re-open Name Manager, copy the LAMBDA body, count the parameters, and match your call. Excel doesn't show a parameter list for named LAMBDAs the way it does for built-ins.
Workbook feels sluggish after adding SUMPRODUCT / INDIRECT formulas. These recalculate on every workbook change. On 100k+ rows, switch SUMPRODUCT to SUMIFS / COUNTIFS where the criteria allow it, and replace INDIRECT with static named ranges. See auditing slow Excel formulas for a performance-tracing workflow.

FAQ

What are the most useful Excel formulas to learn?

The most impactful Excel formulas are XLOOKUP for data retrieval, SUMIFS and COUNTIFS for conditional totalling and counting, INDEX-MATCH for flexible lookups, IF (including nested and IFS) for decision logic, TEXT for formatting, and the dynamic array family — FILTER, SORT, UNIQUE, SEQUENCE. Together these cover the vast majority of real-world business spreadsheet work.

What Excel formulas save the most time?

XLOOKUP saves the most time for anyone doing data lookups. SUMIFS and COUNTIFS eliminate manual filtering and counting. FILTER and SORT automate report generation that used to require pivot tables. TEXT and CONCAT save hours of manual formatting. Learning these well can cut repetitive spreadsheet work by 50% or more.

Which Excel version do I need for these formulas?

XLOOKUP, FILTER, SORT, UNIQUE, and SEQUENCE need Excel 365 or Excel 2021. LET and LAMBDA need Excel 365. CHOOSECOLS, CHOOSEROWS, VSTACK, HSTACK, and TEXTSPLIT need Excel 365. The older functions (SUMPRODUCT, INDEX-MATCH, IFERROR, COUNTIFS, INDIRECT, TEXTJOIN) work in Excel 2019 and earlier. If you're stuck on an older version, INDEX-MATCH is your XLOOKUP substitute and {=IFERROR(INDEX(...))} array formulas replicate most FILTER use cases.

Should I use SUMPRODUCT or FILTER for multi-criteria work?

Use FILTER if you have Excel 365 or 2021 and the criteria are straightforward equality / comparison checks — it's more readable and handles spilled output natively. Reach for SUMPRODUCT when criteria involve arithmetic (e.g. "revenue where margin > 20%") or when you need to work in an older Excel version without dynamic arrays.

Is it worth learning LAMBDA for business use?

Yes, once you catch yourself writing the same 40-character sub-formula in multiple cells across multiple sheets. LAMBDA lets you save it once as a named function and reuse it like a built-in. It's especially useful for finance, tax, and commission calculations where business logic repeats across many workbooks — and unlike VBA, LAMBDA functions travel with the workbook without triggering macro warnings.