u/bored_af_98

Something I started doing for every sheet I hand to someone else (family budget, trackers, team logs), after watching people open a blank template, stare at it, and close it: pre-load 3-4 demo rows so every formula, chart and conditional format is already alive at first open.

The rules that make it work:

1. Prefix demo entries unmistakably: "DEMO - Credit card", "DEMO - Groceries". Nobody mistakes them for real data, and deleting them is obviously safe.

2. Every formula must survive their deletion. That means wrapping ranking/aggregation columns in guards like =IF($B7="","",...) so empty rows don't rank as zeros, and using IFERROR on averages that would divide by zero once demo rows go.

3. Optional but nice: a one-cell banner that nags until the demo data is gone:

   =IF(COUNTIF(B:B,"DEMO*")>0,"Replace the DEMO rows with your data - everything updates by itself","")

Why bother: a blank grid asks the user to trust that formulas will work once they type. Working demo rows PROVE it, show the expected data shape (dates as dates, amounts as numbers), and double as free documentation. The person you shared it with edits a demo row, sees the dashboard move, and gets it - no instructions needed.

Works the same in Excel and Google Sheets since it's a design habit, not a feature.

Common setup: transactions with dates in A, amounts in E, and you want a summary of each month. The instinct is a helper column with =MONTH(A2) and a SUMIF on it. Works, but there's a cleaner way that also survives multi-year data.

Put the month number (1-12) in G2 and the year in a cell, say $H$1:

=SUMIFS($E:$E, $A:$A, ">="&DATE($H$1,G2,1), $A:$A, "<"&DATE($H$1,G2+1,1))

Drag it down twelve rows and you have the whole year.

The quiet star is DATE(): when G2+1 hits 13, DATE(year,13,1) doesn't error - it returns January 1st of the NEXT year. So the December row needs no special-casing, and the same formula works across year boundaries.

Why brackets beat MONTH() helpers:

1. No helper column to maintain (or forget to fill down).

2. MONTH(A2)=1 matches January of EVERY year in your data - the brackets pin both month and year.

3. SUMIFS with ranges stays fast; array tricks like SUMPRODUCT(MONTH(...)) slow down on long logs and choke on full-column references.

Same idea works for weekly brackets (">="&start, "<"&start+7) or any custom period - the pattern is always ">= period start" and "< next period start". Half-open ranges also mean timestamps like Jan 31 23:59 can't fall through the cracks the way "<="&EOMONTH() versions sometimes do.

Snowball = pay smallest balance first (quick wins). Avalanche = highest interest rate first (mathematically cheaper). People argue about which is better; the nicer answer is: build the sheet so switching is one dropdown.

Say debts are in a table: name (B), balance (C), rate (D). Put a data-validation dropdown in C2 with the two options, then in the "attack order" column:

=IF($C$2="Snowball", RANK(C7,$C$7:$C$14,1), RANK(D7,$D$7:$D$14,0))

Third argument is the whole trick: 1 = ascending (smallest balance ranks #1), 0 = descending (highest rate ranks #1). Flip the dropdown and the whole payoff order re-ranks instantly.

Two details that bite:

1. Ties. Two debts at 22% get the same rank. Classic fix: add a tiny row-based tiebreaker inside RANK, e.g. RANK(D7+ROW()/10^6, ...) - or just accept the tie, order between equals doesn't change the math.

2. Blank rows. Wrap it: =IF(C7="","",IF(...)). Otherwise empty rows rank as zeros and pollute the order.

Bonus: SUMIFS against the rank column gives you "extra payment goes to rank 1" logic without any VBA:

=IF(rank_cell=1, base_payment + extra, base_payment)

Works identically in Excel and Google Sheets, no add-ins.

Learned this one from a comment two days ago and it has already deleted a habit I'd had for years, so passing it on.

The problem: you write =SUM(A2:A1000) because you don't know how far your data goes. Too small and you miss rows; too big and you're evaluating 900 empty cells and any formula referencing them has to handle blanks. Then someone pastes row 1001 and your total is quietly wrong.

The fix (Excel 365, fairly recent): put a dot in the reference.

=SUM(A2:.A)

The dot means "trim". A2:.A reads from A2 down to the last non-empty cell in column A and stops there. Add rows, it extends. Delete rows, it shrinks. No table required, no OFFSET/COUNTA gymnastics, no volatile functions.

Three variants:

• A2:.A - trim the end (the one you'll use 95% of the time)
• A2.:A100 - trim the start
• A2.:.A100 - trim both

Where it actually changed something for me: I had a ranking formula wrapped in FILTER purely to drop the empty tail of a range I'd guessed at:

=SORT(FILTER(A2:B1000, B2:B1000>0), 2, -1)

With a trimmed ref the FILTER isn't doing that job any more:

=SORT(HSTACK(A2:.A, B2:.B), 2, -1)

I'd keep FILTER if you have genuinely blank cells in the MIDDLE of your data - trim only handles the tail, so a gap on row 40 still needs filtering. But if your FILTER exists only to compensate for a range you picked out of thin air, this replaces it.

Caveat: needs a current Excel 365 build. Not in Google Sheets, where the equivalent is just leaving the row number off (A2:A), which has done the same job there forever.

EDIT: corrected the trim-the-start syntax - it's A2.:A100, not .A2:A100 (the dot goes after the reference you're trimming from). Thanks u/OldJames47 for catching it.

Excel 365 (Current Channel), Windows 11. Posting this because the failure mode is silent - no error, no spill, just a wrong-looking number - and searching for it is hard when you don't know the term yet.

Setup: a Data table (a real ListObject) with Item, Metric, Price. On a separate sheet I had a working dynamic array:

=SORT(FILTER(CHOOSECOLS(HSTACK(Data[Item], Data[Metric]*10/Data[Price]),1,2), Data[Price]>0), 2, -1)

Spills fine, ranks correctly. Then I wanted the same computed value visible next to each row, so I added a column INSIDE the table and typed what looks like the same reference:

=Data[Metric]*10/Data[Price]

And got a plausible single number per row instead of an error. That's the trap: inside a table, an unqualified column reference gets the implicit intersection applied, so Excel silently rewrites it as =@Data[Metric]*10/@Data[Price] - the value on THAT row. Which is what you usually want, and is why nothing screams. But if you were expecting the column (say you're wrapping it in SUM or feeding it to another array function), you get a quiet wrong answer that looks like a plausible number.

What finally made it visible: select the cell and look at the formula bar, not the cell. The @ is there in the bar even though I never typed it. Or use =ROWS(Data[Price]) in a helper cell - inside the table it returns 1, outside it returns the row count.

Two fixes depending on intent:

• Want the whole column inside a table: force it with =INDEX(Data[Price],0) or reference the range through a name defined outside the table
• Want per-row (the usual case): leave it, but write the @ explicitly so future-you knows it was deliberate

Related thing I learned the same day, from a comment on another sub: trimmed refs (C2:.C) solve the "how far down does my range go" problem outside tables without the arbitrary C2:C200 - the FILTER(...>0) clause I'd been writing was mostly compensating for a badly chosen range, not for real empty cells. Genuinely useful if you'd missed it like I had.

Anyone else have a silent-failure favourite in this family? The ones that error loudly I can handle; it's the plausible-wrong-number ones that eat afternoons.

Pattern I keep reusing and rarely see written down, so here it is.

Problem: you have a table of items with a cost and some metric, and you want a "best value" ranking. Most people sort manually, then the sort goes stale the moment a price changes. Then they re-sort. Forever.

The fix is to never sort the source data at all. Keep the raw table untouched and build the ranking as a formula that reads it.

Google Sheets:

=SORT(FILTER({Data!A2:A, Data!B2:B*10/Data!C2:C}, Data!C2:C>0), 2, FALSE)

That's the whole thing. FILTER drops rows with no price (avoids div/0 and empty rows), the {} builds a virtual two-column array (name + computed value), SORT orders by column 2 descending. Edit any price in Data and the ranking below reshuffles instantly, no re-sorting, no macro.

Excel 365 equivalent:

=SORT(FILTER(CHOOSECOLS(HSTACK(Data!A2:A200, Data!B2:B200*10/Data!C2:C200),1,2), Data!C2:C200>0), 2, -1)

On older Excel without dynamic arrays you're stuck with a helper column + LARGE/INDEX/MATCH, which is why I keep the raw metric in its own column anyway.

Two things that saved me pain:

1. Compute the ratio inside the array, not as a stored column. If it's stored, it's another thing to drag down when rows get added.

2. Wrap in IFERROR only at the outer level. Wrapping each term hides the div/0 that tells you a price cell is empty.

The cross-sheet variant of the same idea: SUMIF against a plan sheet to total quantities per item, then the same SORT/FILTER on top. That's how a weekly plan can generate a shopping list that re-totals itself when you change a cell.

Anyone using a cleaner way to do the ranking half? I've seen QUERY used for this and it reads nicer, but it chokes on mixed types in a way SORT/FILTER doesn't.