Excel SCAN function: running totals in one formula
Excel SCAN function replaces sliding SUM anchors with one dynamic-array running total. See the syntax, category resets, and error traps inside.
Formulas
Excel DATEDIF: calculate age and tenure the right way
Excel’s DATEDIF returns years, months, or days between two dates. Learn every unit code, the MD bug, and the tenure formula HR reports actually want.
Excel AGGREGATE function: ignore errors and hidden rows
Excel’s AGGREGATE function skips errors and hidden rows in one formula — no IFERROR wrapper, no SUBTOTAL swap. See when to reach for which option.
Excel MAXIFS and MINIFS: conditional max and min values
Excel MAXIFS and MINIFS return the highest or lowest value that matches your criteria. See the syntax, common traps, and the row-lookup pattern.
Excel SEQUENCE function: dynamic number and date lists
Excel SEQUENCE function generates number and date series in one formula — no dragging, no re-fills when your data grows. See how it works in practice.
Excel PMT function: build a real loan payment calculator
Excel PMT function done right: sign convention, matching rate to period, IPMT/PPMT amortization, extra principal, plus a working calculator.
Google Sheets locale and date format: fix stuck cells
Change Google Sheets date format by locale, then fix the cells that stay stuck as text — with DATEVALUE, custom formats, and one QUERY trick.
Excel TEXTJOIN: ignore empty cells the right way
Excel TEXTJOIN’s ignore_empty argument skips true blanks, not whitespace. Learn the fix, the FILTER combo, and the 32,767-character trap. Read on.
CHOOSECOLS and CHOOSEROWS in Excel: extract clean subsets
CHOOSECOLS and CHOOSEROWS extract, reorder, and mirror columns or rows from any Excel array in one spill formula. Here is how to use them cleanly.
Excel WORKDAY and NETWORKDAYS: skip weekends and holidays
Excel WORKDAY and NETWORKDAYS turn calendar math into working-day math. Feed them a holiday Table and every due date lands on a real business day. Read on.
Excel BYROW and BYCOL function: skip helper columns
Excel BYROW and BYCOL turn a range into one summary per row or column, so the helper column and fill-down disappear. Here’s how to use them well.
Excel SORT and SORTBY: dynamic sorting for real data
Excel SORT and SORTBY spill sorted results as formulas, so lists stay current when data changes. See when to pick each and how to sort custom orders.