The ExcelMaster.ai Blog

INDIRECT turns a text string like "A1" or "Sheet2!B3" into a live cell reference. Learn the mental model, the #1 trap (text doesn't auto-update, so renaming a sheet breaks every INDIRECT with #REF!), why it's volatile and invisible to Excel's dependency tracer, the closed-workbook #REF! gotcha, its one killer use — pulling from a sheet named in a cell — and when a Table, 3D reference, or CHOOSE is the better tool.

OFFSET returns a reference a set number of rows and columns from an anchor, optionally resized to a whole block. Learn the mental model, why it returns a reference (not a value) so it can feed SUM, the volatility cost, the classic dynamic-named-range trick and why a Table or spill range now beats it, the #REF! off-the-edge trap, and the key judgment call: use non-volatile INDEX to index into a range, and keep OFFSET only for genuinely moving windows.

ADDRESS builds the text of a cell address — =ADDRESS(1,1) returns the string "$A$1", not what's in A1. Learn the mental model (it's the opposite of INDIRECT's input side), the #1 misunderstanding that it returns text not a value, the abs_num argument for $ locking, why it is NOT volatile unlike INDIRECT and OFFSET, the real jobs — reporting where a value is via MATCH and building references for INDIRECT — and an honest take on how niche it is in modern Excel.

SUMPRODUCT multiplies arrays element by element and adds the results — one cell, one dot product. Learn the dimension-mismatch #VALUE! trap, why you multiply boolean arrays for AND and add them for OR (never AND()/OR()), when the double-unary -- is needed, and the weighted-total and OR-across-columns jobs that SUMIFS still can't touch.

SUBTOTAL is a filter-aware total: it skips rows hidden by a filter, so the number at the bottom of your list updates as you filter. Learn the function_num table, the 1–11 vs 101–111 trap (why hiding rows manually doesn't change your total), why SUBTOTAL ignores other SUBTOTALs so grand totals never double-count, and why it can't skip errors — the job AGGREGATE was built for.

AGGREGATE is SUBTOTAL with two upgrades: 19 functions instead of 11, and an options argument that lets it ignore error values, hidden rows, and nested totals. Learn the function_num + options grid, its most underused trick — summing a column that contains #N/A without any IFERROR cleanup — and the array-form functions (LARGE, SMALL, PERCENTILE) that need an extra k argument.

LEFT, RIGHT and MID slice a string by position — count from the left, from the right, or from a fixed spot. Learn why MID is 1-based, why the result is always text (and breaks SUM), and the real reason hardcoded positions are the most fragile line in your sheet.

FIND and SEARCH return the position of one string inside another — the anchor you feed to MID and LEFT. Learn the two real differences (FIND is case-sensitive with no wildcards; SEARCH is case-insensitive with wildcards), why both throw #VALUE! when there's no match, and the ISNUMBER(SEARCH()) contains-test trick.

SUBSTITUTE and REPLACE both swap text, but they think differently: SUBSTITUTE matches by content (replace every 'x', or just the Nth one), while REPLACE works by position (overwrite characters 5 through 9). Learn which to use, why SUBSTITUTE is case-sensitive, and the LEN-minus-LEN counting trick.

ROUND changes the stored value to a number of decimal places; cell formatting only changes how it looks. Learn why that gap makes invoice totals off by a penny, the num_digits trick (positive, zero, negative), how ROUNDUP and ROUNDDOWN differ from ROUND, and why they round by distance from zero — not up and down.

MROUND, CEILING and FLOOR round to the nearest multiple of a number — 5 cents, 15 minutes, a full carton — not to a count of decimal places. Learn why the second argument is a multiple not digits, why old CEILING throws #NUM! on negatives, why CEILING.MATH and FLOOR.MATH are the safe modern choices, and when to pick each.

INT and TRUNC both drop the decimals, but INT rounds toward minus-infinity while TRUNC just chops toward zero — so they split on negative numbers. MOD returns the remainder, and in Excel the remainder takes the sign of the divisor, not the number. Learn the off-by-one trap, splitting dates from times, and the zebra-stripe pattern.

IF asks one yes/no question and returns one of two answers. Learn why a missing third argument shows FALSE, why nested IF pyramids fall apart past two levels (use IFS or a lookup instead), how to combine conditions with AND/OR instead of nesting, and why IF's text compare ignores case.

IFS checks a list of conditions top to bottom and returns the first match; SWITCH matches one value against a list of cases. Learn the two traps that cause most bugs — IFS has no built-in else (a missed default returns #N/A) and first-match-wins means order matters — plus when to use IFS vs SWITCH vs a lookup table.

IFERROR replaces a formula's error with a fallback value, but it catches every error type — so it silently hides real bugs like #REF! and #NAME? too. Learn why IFNA is usually the right tool for lookups, why wrapping everything in IFERROR is an anti-pattern, and how XLOOKUP's if_not_found beats both.

SUMIFS sums only the rows that pass every test. Learn why SUMIF and SUMIFS reverse their arguments (the #1 wrong-total bug), how to compare against a cell with ">"&A1, how AND is built in but OR is not, and how to spill a whole summary table with UNIQUE.

COUNTIFS counts the rows that pass every test, and a wrong criteria gives you a plausible-but-wrong number with no error. Learn why you must write ">"&A1 not ">A1", how AND is built in but OR is not, how to count between two dates, and why distinct counts need UNIQUE, not COUNTIFS.

AVERAGEIFS averages only the rows that pass every test — but when nothing matches it returns #DIV/0! (not 0 like SUMIFS), and it counts zeros while skipping blanks. Learn the empty-set guard, the zero-vs-blank trap, the reversed argument order, and why it can't do a weighted average.

LET lets you declare named variables inside a single formula. Learn the names-in-pairs-result-last rule, why LET computes each value only once (so it is faster, not just tidier), the naming traps, and when a 200-character nested formula should become a LET.

LAMBDA turns a formula into a named, reusable function with no VBA. Learn why a bare LAMBDA returns #CALC!, the trailing-paren trick to call it, how to name it in Name Manager so the whole workbook can use it, and how a LAMBDA can call itself for recursion.

MAP, REDUCE, SCAN, BYROW and BYCOL run a LAMBDA across an array — For Each without a macro. Learn the BYROW single-value rule that fixes #CALC!, the REDUCE accumulator pattern, what shape each helper returns, and when a plain SUMIFS still wins.

VSTACK stacks ranges on top of each other and HSTACK sets them side by side, merging several tables into one array that updates itself. Learn to combine sheets without copy-paste, why ragged ranges pad with #N/A, and how DROP and SORT finish the job.

SEQUENCE conjures an array of numbers out of thin air — it is the engine behind dynamic-array row IDs, date series and grids, not a replacement for the fill handle. Learn the argument order (rows, cols, start, step), how to build a live calendar, why it throws #SPILL!, and when it beats dragging or ROW().

TAKE keeps the first or last N rows or columns of an array; DROP removes them. Together they slice a dynamic array from either edge. Learn the positive/negative trick for top vs bottom, how they pair with SORT for a live Top 10, and how DROP strips headers off a VSTACK.

TEXTJOIN is a glue gun with a delimiter that skips empty cells for you. Learn the ignore_empty argument everyone misses, the 32,767-character limit that throws #VALUE!, when it beats CONCAT and &, and how to join a spilled array back into one cell.

TEXTSPLIT is the formula version of Text to Columns, except the result is a live array that updates with the source. Learn to split into columns and rows at once, pass multiple delimiters as an array constant, fix #SPILL!, and when it replaces LEFT/MID/FIND.

CONCAT replaces CONCATENATE and can take an entire range at once — but it has no delimiter and skips nothing. Learn when CONCAT wins, why CONCATENATE is a legacy leftover, how it reads a 2-D range row by row, the number-format trap, and when to jump straight to TEXTJOIN.

FILTER is a query, not a lookup — it returns every row that matches and spills them into a range. Learn the AND/OR trick, why empty results throw

UNIQUE returns the distinct values from a range and keeps them up to date — unlike the destructive Remove Duplicates button. Learn the exactly_once trap, how to count distinct, and why UNIQUE almost always belongs inside SORT or a dropdown.

The SORT function returns a sorted copy that re-sorts itself when the source changes — unlike the destructive ribbon Sort button. Learn the column-index rule, multi-key sorts, when to reach for SORTBY, and the live SORT(UNIQUE(FILTER())) pattern.