DSUM and DCOUNT answer 'total (or count) the rows that match' — but you write the conditions as a small table on the sheet, not inside the formula. Learn the three arguments, why the database must include its header row, how a criteria range encodes AND and OR, why DCOUNT ignores text (use DCOUNTA), and when a visible criteria block beats SUMIFS.
DGET pulls a single value from a table using a criteria range — and its defining trait is that it errors on purpose: #NUM! when more than one row matches, #VALUE! when none does. That's not a bug, it's a built-in uniqueness check that VLOOKUP silently skips. Learn the three arguments, multi-condition lookups with no helper column, and when DGET beats XLOOKUP (and when it doesn't).
The twelve D-functions — DSUM, DCOUNT, DGET, DAVERAGE, DMAX, DMIN and more — all share one signature and one skill: the criteria range. Learn how a criteria block encodes AND and OR, why a range on one field needs its header twice, how formula criteria work (and the header rule that makes or breaks them), the starts-with over-match trap, and the decision tree for D-functions vs SUMIFS vs FILTER.
GROUPBY(row_fields, values, function) groups rows and aggregates them in a single spilling formula — the modern replacement for the UNIQUE + SUMIFS pattern and, for many reports, for the pivot table itself. The catch that trips everyone: you pass the function by name (SUM, not SUM()), because it's a lambda Excel applies per group. Learn the three required arguments, why it recalculates live where a pivot table goes stale, how to add totals and sort by the aggregated value, and the version it needs.
PIVOTBY(row_fields, col_fields, values, function) is GROUPBY with a second dimension — it cross-tabulates data into a rows-by-columns grid, a pivot table expressed as a single spilling formula that recalculates live with no Refresh. Learn the four required arguments, why row_fields and col_fields are not interchangeable, how to add totals on both axes, and the real trade-off against a classic PivotTable (live and referenceable, but not drag-and-drop interactive). Needs Excel 365.
The optional arguments GROUPBY and PIVOTBY share are where they stop replacing pivot tables and start beating them: PERCENTOF for percent-of-total in one word, multiple value columns for several metrics at once, a LAMBDA in the function slot for a weighted average pivot tables can't do, and filter_array to aggregate only the rows you want. Learn the eta-reduced function grammar, the percent-of-total trap, and the errors (#CALC!, #FIELD!, #SPILL!) that tell you which argument is wrong.
ROW([reference]) returns a row number and COLUMN([reference]) returns a column number — the position of a cell, never its contents. With no argument, each returns the coordinate of the cell holding the formula. Learn the mental model that separates 'where a cell is' from 'what's in it', how ROW()-anchor builds self-healing serial numbers, why MOD(ROW(),n) drives striping and grouping, and the letter-vs-number trap in COLUMN.
ROWS(array) returns how many rows a range has and COLUMNS(array) returns how many columns — a size, not a position. ROWS(A1:A10) is 10; COLUMNS(A1:C1) is 3. Learn why the plural forms differ from ROW and COLUMN, the killer use of a self-adjusting VLOOKUP column index with COLUMNS(), counting the rows a FILTER returns, and the whole-column trap where ROWS(A:A) is 1,048,576.
HYPERLINK(link_location, [friendly_name]) is a formula that builds a clickable jump — to a cell, sheet, file, web page, or email — whose target other formulas can compute. It is not the same as the Insert > Link menu, which creates a static link. Learn the #-prefix for jumping inside a workbook, dynamic targets with ADDRESS and MATCH, why the link navigates on click rather than fetching a value, and why you should always pass friendly_name.
RAND() returns a random decimal from 0 up to 1; RANDBETWEEN(bottom, top) returns a random integer in a range, both ends included. Learn the mental model that both are volatile — they re-roll on every edit — why that's the #1 gotcha, how to freeze them with Paste Special, why RANDBETWEEN repeats values, and the =a+(b-a)*RAND() formula for random decimals.
RANDARRAY([rows],[cols],[min],[max],[whole_number]) spills a block of random numbers from one formula — RAND and RANDBETWEEN merged and upgraded. Learn why the whole_number flag defaults to FALSE (so you get decimals unless you ask for integers), why it's still volatile, when it throws #SPILL!, and the SORTBY(list, RANDARRAY(...)) shuffle trick.
The one trick behind every random pick in Excel: attach a random number to each row, then sort by it. Learn the modern one-liner TAKE(SORTBY(data, RANDARRAY(...)), n), the classic RAND helper-column method, the critical with-replacement vs without-replacement distinction that ruins samples, and why you must freeze the random column before you sort.
POWER(x,n) and the ^ operator raise a number to a power; SQRT(x) is the square-root shortcut for ^(1/2). Learn the mental model that roots are just fractional exponents, why =-3^2 returns 9 and =27^1/3 returns 9 (both precedence traps), why SQRT of a negative is #NUM!, and how one POWER formula gives you CAGR.
ABS(x) returns a number's magnitude (distance from zero); SIGN(x) returns only its direction as -1, 0, or +1. Learn the mental model that every number is magnitude times direction, why a tolerance check needs ABS(A-B) not A-B, the identity x = SIGN(x)*ABS(x), and why ABS is a tool for when direction truly does not matter — not a band-aid over a sign you did not expect.
EXP(x) raises e to a power; LN, LOG10, and LOG are its inverses. Learn the mental model that a logarithm is exponentiation run backwards (solve for the exponent), why LOG(x) with no base is base 10 and NOT the natural log, why LN(0) and LOG of a negative are #NUM!, and the two formulas business users actually need: geometric mean = EXP(AVERAGE(LN)) and years-to-target = LN(target)/LN(rate).
FORMULATEXT(reference) returns the formula in a cell as text; ISFORMULA(reference) returns TRUE when a cell holds a formula. Learn the mental model — these functions audit a sheet, they do not calculate — the #N/A you get on a non-formula cell, the conditional-formatting trick that highlights every formula, and when to reach for them instead of Show Formulas.
CELL(info_type, [reference]) returns metadata about a cell — its address, row, column, number format, content type, or the workbook's filename — instead of its value. Learn the mental model, why info_type is a keyword string, the one job it's still uniquely good at (getting the file path and sheet name), and the recalculation trap that makes it look wrong.
TYPE(value) returns a code for what kind of value a cell holds — 1 number, 2 text, 4 logical, 16 error, 64 array — and N(value) coerces a value to a number. Learn the mental model, how TYPE pins down numbers-stored-as-text, why N('anything') returns 0 instead of erroring, and the classic N() trick for embedding a comment inside a formula.
SWITCH matches one expression against a list of exact values and returns the first hit — =SWITCH(expr, val1, res1, val2, res2, default). Learn the mental model, why it only does exact equality (and why that is the line between SWITCH and IFS), the default-argument trap that returns #N/A, the SWITCH(TRUE()) trick for ranges, and when to use it instead of nested IF.
CHOOSE returns the Nth item from a list by position — =CHOOSE(index_num, value1, value2, …). Learn the mental model, why it picks by position not by match (the line between CHOOSE and SWITCH), the #VALUE! you get when the index is out of range, and its underused superpower — returning whole ranges to switch scenarios or reorder columns for a left lookup.
MAXIFS and MINIFS return the largest or smallest value that meets one or more conditions — =MAXIFS(max_range, criteria_range1, criteria1, …). Learn the mental model, the argument order that trips up SUMIF users, why they replace the old {=MAX(IF())} array formula, and the silent 0 trap they return when no rows match.
COUNT counts only numbers, COUNTA counts any non-empty cell, and COUNTBLANK counts empties — three functions for three different 'how many' questions. Learn why =COUNT on a column of text IDs returns 0, why COUNTA still counts a formula that returns an empty string, and why COUNTA plus COUNTBLANK does not always equal the total.
COUNTIF counts cells that meet a single condition — =COUNTIF(range, criteria) — where the criteria is a small test written as text. Learn why operators must go in quotes, how to use a cell value as the threshold with &, wildcards for text matching, the 15-digit trap on long numbers, and when to upgrade to COUNTIFS.
Excel has no COUNTUNIQUE function, so counting distinct values has two answers: the modern =COUNTA(UNIQUE(range)) in 365/2021, and the classic =SUMPRODUCT(1/COUNTIF(range,range)) for older versions. Learn why blanks add 1 to your count, how to exclude them with FILTER, why the classic formula throws #DIV/0!, and the difference between distinct values and values that appear once.
TRIM and CLEAN both remove invisible junk from imported text, but they target different things: TRIM strips extra spaces (ASCII 32), CLEAN strips non-printing control characters (0-31). Neither removes the non-breaking space CHAR(160) that web and PDF pastes leave behind — the #1 reason 'TRIM doesn't work.' Learn the mental model and the one fix that catches everything.
UPPER, LOWER and PROPER re-case text, but case in Excel is a display and export concern, not a matching one — the = comparison and VLOOKUP are already case-insensitive. The real trap is PROPER: it capitalizes the first letter of every word, mangling McDonald into Mcdonald and iPhone into Iphone. Learn when case actually matters and why PROPER is a starting point, not a finisher.
LEN counts the characters in a cell, but its real power is diagnostic: because it counts every character including trailing spaces and non-printing ones, LEN is how you prove a cell that looks like 'Apple' is actually 'Apple ' with 6 characters. Learn LEN for validation, the LEN-minus-LEN counting trick, dynamic extraction, and why LEN ignores number formatting.
VLOOKUP looks down the leftmost column of a table and counts N columns to the right — that one-directional design is the source of every bug it has. Learn why the 4th argument (range_lookup) must almost always be FALSE, why VLOOKUP can't look to the left, why a hard-coded column number silently breaks when you insert a column, and how to fix the #N/A that means 'not found'.
INDEX and MATCH split a lookup into two jobs: MATCH finds the position ('which row?'), INDEX returns the value at that position ('what's there?'). Decoupling search from retrieval is what gives INDEX/MATCH everything VLOOKUP lacks — it looks left as easily as right, survives inserted columns, and does true two-dimensional lookups. Learn the pattern, the match_type trap, and when it still beats XLOOKUP.
HLOOKUP is VLOOKUP rotated 90 degrees — it searches the first ROW and reads down. LOOKUP is VLOOKUP's ancestor, and its fatal flaw is that it has no exact-match option: it always approximates and demands sorted data. Learn when a horizontal layout makes HLOOKUP the right call, why the classic LOOKUP(2,1/…) trick still shows up in old sheets, and why both mostly give way to XLOOKUP.
AND and OR don't make decisions — they collapse many TRUE/FALSE tests into one verdict that feeds an IF, a rule, or a filter. Learn why AND belongs inside IF's first argument (not wrapped around nested IFs), why a bare =AND(...) just prints the word TRUE, why Excel evaluates every argument (no short-circuit, so it can still throw #DIV/0!), and when to drop them for +/* array masks.
NOT flips a single TRUE/FALSE verdict; XOR is the exclusive-or that AND and OR can't express. Learn why NOT takes exactly one argument (so you must write NOT(AND(...)) for compounds), when NOT is just <> in disguise, how De Morgan's law is NOT's real superpower, and the XOR surprise nobody warns you about — with 3+ inputs it returns TRUE for an ODD count of TRUEs, not 'exactly one'.
The IS family inspects a cell and returns a TRUE/FALSE verdict — they classify, they never transform. Learn why ISBLANK is stricter than 'looks empty' (a formula's empty string is not blank), why ISERROR hides real bugs while ISNA and IFNA are usually safer, why IF(ISERROR(x),…,x) computes x twice, and ISNUMBER's killer app: turning SEARCH into a clean partial-match test.
The TEXT function turns a number or date into a formatted text string using a format code — =TEXT(1234.5,"$#,##0.00") gives "$1,234.50". Learn the mental model (baking a format into an actual string vs a cell's display-only number format), the #1 trap (the output is text, so SUM ignores it), the format-code crash course for numbers and dates, leading zeros and locale, and when a cell format, CONCAT, or ROUND is the better tool.
VALUE parses a text string that looks numeric — "1,234.50" or "$9.00" — into a real number you can sum, sort, and chart. It's the inverse of TEXT. Learn the mental model, the #1 real-world cause (numbers stored as text after an import or paste), why VALUE follows your system locale and how NUMBERVALUE fixes a European "1.234,56", the faster no-formula fixes (*1, double-unary, Text to Columns), and why modern Excel coerces some text but SUM never does.
A date in Excel is really a serial number wearing a date format; a date that arrives as text is an impostor that won't sort, subtract, or feed DATEDIF. DATEVALUE parses a text date into its serial number and TIMEVALUE parses a text time into a fraction of a day. Learn the mental model, the symptoms of text dates, the regional-ambiguity trap with 03/04/2026, how to combine date + time, when DATEVALUE can't parse a string, and the DATE(LEFT,MID,RIGHT) and Text to Columns alternatives.
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.
