Essential functions

Add up a range of numbers — the first function anyone learns and still the most-used.

The arithmetic mean of a range — total ÷ count, with blanks left out.

Answer 'how many?' — numbers only, anything at all, or the empty ones.

The smallest or largest number in a range — and a neat way to cap or floor a value.

The 2nd, 3rd … nth smallest or largest — the top-N list MIN/MAX can't do.

Sum amounts that match several conditions — the workhorse of P&L roll-ups.

Count rows that meet multiple conditions.

Average the values that match several conditions — the mean sibling of SUMIFS.

The smallest or largest value that meets your conditions.

Totals that carry on past an #N/A instead of letting one error poison the report.

Customer concentration, top-N lists and ABC analysis — and ties are where it trips.

Average deal size gets dragged around by one whale. The median describes the business.

Puts a number on how lumpy revenue is — the difference between a bad month and a signal.

Multiply arrays element-by-element, then sum — weighted averages and conditional math.

Aggregate only the visible (filtered) rows, ignoring other subtotals.

Round a number to a set number of digits — control the pennies before they compound.

Three small math tools that punch above their weight — magnitude, whole part, remainder.

Round to a multiple — price points, pack sizes, round thousands — not to decimal places.

The current date (or date-and-time) that updates itself every time the sheet recalcs.

Build a real date from three numbers, or pull the year, month, or day back out.

The last day of a month N months out — clean period-end dates.

The same day, N months out — clean anniversary, renewal, and due dates.

Format a number or date as text — for labels and headers.

Count working days between two dates — the unit aging, SLAs and close calendars are actually measured in.

Land a due date on a day someone can actually pay — terms counted in working days, not calendar ones.

The fraction of a year between two dates — and the basis argument that quietly changes every accrual.

Whole months and years between two dates — the function Excel hides and never autocompletes.

Two day counts that disagree on purpose — and DSO changes depending on which you use.

Every export arrives with dates as text. This is how they become dates again.

Cash timing is a day-of-week question — a Friday payment run clears differently to a Tuesday one.

Return the rows that meet a condition as a live, spilling result.

Spill a distinct list — perfect for dropdowns and clean lists.

Spill a range into sorted order — live, with no manual re-sort.

Pile ranges on top of each other into one tall list — combine tabs or blocks before you filter or total.

Set ranges side by side into one wider block — glue separate columns into a single table.

The period headers every model starts with, as one formula that stretches.

A pivot table as a formula — so it refreshes without anyone remembering to refresh it.

Top ten customers, or everything except the header row, without hardcoding a range.

Pull specific columns out of an export, in the order your template wants them.

Run a calculation down every row at once — no helper column.

SCAN is a running balance in one formula — which is the cash-forecast shape exactly.

Name a value or calculation once, then reuse it — faster formulas that read like plain English.

Build your own reusable function — no VBA — then name it and call it like any built-in.

NPV and IRR for cash flows on real, irregular dates.

The level payment on a loan, per period.

Build a full loan payoff schedule — principal vs. interest, month by month.

Spread an asset's cost over its life — build an asset register and schedule.

Fixed-declining-balance depreciation — accelerated, at a single rate Excel works out for you.

Variable-declining balance — accelerated depreciation that switches to straight-line so nothing is stranded.

Value an evenly-spaced stream of cash and get its return — with the off-by-one that catches nearly everyone.

Split a loan payment into the interest half that hits the P&L and the principal half that pays the balance down.

The two ends of time value: what a stream is worth today, and what a balance grows into.

Back the interest rate out of a deal you've already been quoted — the honest way to compare financing.

How many payments until this is paid off — the runway question, in loan form.

Total the interest and principal across a range of periods — how interest expense actually gets budgeted.

Convert a quoted rate into the one you actually pay, and find out what an early-payment discount really costs.

IRR's honest cousin — the return when you say what you'd really earn on the cash coming back.