Excel
Excel-specific features: LET, LAMBDA, Power Query, and dynamic arrays.
excel
Filter Rows That Match a Condition
FILTER returns every row where a condition is true — a live, self-updating extract of the deals, orders, or invoices that match.
=FILTER(A2:C10,C2:C10>1000,"No matches")excel
Write Readable Formulas by Naming Values with LET
LET names the moving parts of a formula — define SUM(B2:B10) once as total, then reuse it — so long formulas get readable and faster.
=LET(total,SUM(B2:B10),count,COUNTA(B2:B10),total/count)excel
Generate Number Sequences with One Formula
SEQUENCE spills a list of numbers from a single formula — row numbers, invoice numbers, or a date series with any start and step.
=SEQUENCE(10)excel
Sum with Conditions and Math Using SUMPRODUCT
Multiply TRUE/FALSE arrays and SUMPRODUCT adds only the matching rows — OR logic and row-by-row calculations that SUMIFS can't do.
=SUMPRODUCT((A2:A10="Sales")*(B2:B10))excel
Replace Nested IFs with a Clean SWITCH
SWITCH checks one value against a list of exact matches and returns the first hit — flatter and far easier to edit than nested IFs.
=SWITCH(B2,"L","Lead","Q","Qualified","W","Won","Unknown")