I built a 2,257-formula workbook with zero VBA; here's what I learned about formula-only architecture

just finished a project that kind of got out of hand lol. started as a simple restaurant P&L tracker and ended up being 7 sheets, 2,257 formulas, conditional formatting everywhere, cross-sheet references, data validation dropdowns. no macros. no VBA. no power query.

why formula-only? because the people using this are restaurant managers, not excel nerds. the file needs to open and just work in excel, google sheets, and libreoffice without enabling anything or trusting some macro they don't understand.

some stuff i learned the hard way:

SUMPRODUCT is the real MVP. when you can't use SUMIFS across sheets or you need multi-condition logic without helper columns, SUMPRODUCT handles it. i probably have 200+ SUMPRODUCT formulas in this thing. once you understand that (condition1)*(condition2)*values is just boolean multiplication it clicks and you start using it for everything

named ranges will save your life. i ignored these for years and just used cell references like a caveman. when 50+ formulas reference the same range and you need to change it, one named range beats 50 find-and-replaces. also makes formulas actually readable when you come back 3 months later

conditional formatting order matters and nobody tells you this. excel evaluates rules top-down and stops at the first match. had a situation where my "red alert" rule was below my "green good" rule. red never fired. spent 2 hours debugging something that should've taken 10 seconds

IFERROR everything that faces the user. empty input cells create #DIV/0! cascades that look terrifying to non-excel people. one blank cell in a revenue row and suddenly the entire dashboard is a wall of errors. wrap every division in IFERROR and show clean zeros or dashes instead

color-code your inputs religiously. blue = type here. black = don't touch, it's a formula. sounds obvious but the moment you stop being consistent about it someone types over a formula and breaks the whole sheet. data validation + sheet protection on formula cells is the belt and suspenders approach

the biggest challenge was keeping it cross-platform compatible. some stuff that works fine in excel breaks in google sheets (looking at you, TEXTJOIN with arrays). had to test in all three platforms and rewrite a handful of formulas to use the lowest common denominator functions

anyway curious if anyone else builds complex workbooks without VBA. what patterns have worked for you? what's the most formulas you've crammed into a single file?