Level 3Spreadsheet ArchitectureReading, 5 min
Raw, Calc and Output: sheets that don't break
Most broken spreadsheets aren't broken by bad formulas. They're broken by layout: data typed over, an assumption buried in a formula on row 340, a summary that someone pasted values into. Good sheets separate jobs into tabs, and data flows one way:
| Tab | Holds | Rule |
|---|---|---|
| Raw | exports and imports, untouched | never edit by hand; paste over or import |
| Inputs | assumptions: targets, rates, scenario switch | the only place people type numbers |
| Calc | the working: lookups, cleanup, helper columns | one formula per column, no typing |
| Output | dashboards and report tables | reads from Calc, never from Raw |
Add a README tab at the front: what the file is for, who owns it, where the data comes from, when it was last checked. It takes five minutes and saves the next person an hour.
Named ranges give a range a name. Select Inputs!B3, open Data › Named ranges, and call it GrowthRate. Now formulas read =Revenue*(1+GrowthRate) instead of =B2*(1+Inputs!$B$3). Names are absolute by default, so they never drift when you copy a formula. Use them for the handful of inputs people need to understand, not for every range.
Protected ranges stop accidents. Data › Protect sheets and ranges lets you lock a tab or a range in two ways:
- Show a warning: anyone can still edit, but they're asked to confirm. Good for Calc tabs in a trusted team.
- Restrict who can edit: only the people you list can change it. Good for Raw data and the formulas a report depends on.
A common setup: protect the whole file except the Inputs tab's blue cells, so people can play with assumptions without breaking the engine.
Two more habits:
- Check rows. Put a cell on Output that compares totals across tabs:
=IF(ROUND(SUM(Raw!E2:E)-SUM(Calc!E2:E), 2)=0, "OK", "Totals don't tie"). When it says OK, nothing fell through. - Whole-column formulas in Calc. One ARRAYFORMULA, MAP or QUERY at the top of each column means new rows in Raw flow through by themselves.
In the practice, the Calc tab converts currencies with rates from Inputs, and Output only reads Calc. Switch tabs with the bar under the grid.