Excel custom number formats for currency and dates
Excel custom number formats let you display currency, dates, and negatives cleanly without changing the underlying value. Here’s the code you need.
Excel
Excel PIVOTBY function: dynamic pivot in one formula
Excel PIVOTBY builds a live pivot from one formula — grouped rows, columns, aggregated values, no Refresh needed. Read on for the argument tour.
Excel TAKE and DROP: slice dynamic arrays cleanly
Excel TAKE and DROP slice any dynamic array by rows or columns from either end. Build live top-N views, strip headers, and chain them for middle slices. Read on.
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.
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.
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.