Consolidates all global workbook assumptions, toggles, thresholds, and user-selectable variables into a single labeled input grid on a dedicated tab — so every downstream formula reads from one governed source.
Every workbook has assumptions. Tax rates. Date ranges. Tier thresholds. Filter values. The question isn't whether those assumptions exist — it's where they live. The Control Panel Pattern answers that question: they all live on one tab, labeled, validated, and protected. Every formula in the workbook reads from that one source. Change one cell, every formula updates.
A Config tab. One structured table. Three columns: Parameter, Value, Notes. The Value column is the only unlocked column.
| Parameter | Value | Notes | Status |
|---|---|---|---|
| Report_Date | 2026-03-29 | Override for Run_Date. Never TODAY(). | ✓ edit |
| Tax_Rate | 8.75% | State + Local combined rate. | ✓ edit |
| Tier1_Threshold | 500 | Commission floor. Units sold. | ✓ edit |
| Region_Filter | All | All / North / South / East / West | ✓ edit |
| Show_Inactive | FALSE | Toggle. TRUE to include inactive records. | ✓ edit |
The Value column is unlocked. Everything else is protected. Users interact with this tab. Formulas read from it. Nothing else.
Add a new tab. Name it Config. Place it first in the tab order — before Calculations, before Output. First tab = first thing an auditor sees. That positioning is a governance signal: this workbook knows where its inputs live.
Three columns: Parameter (what it is), Value (what it currently is), Notes (why it exists and where it's used). Format as a structured Excel Table (Ctrl+T). Name it tbl_Config.
Select each Value cell and give it a named range that matches the Parameter column. Tax_Rate in col A → select col B cell → Name Box → type Tax_Rate → Enter. Now any formula in the workbook can reference =Revenue * Tax_Rate directly.
For dropdown parameters like Region_Filter or LOD_Filter: select the Value cell → Data → Data Validation → List → enter the valid options. For boolean toggles: List → TRUE,FALSE. This prevents invalid entries from flowing into downstream formulas silently.
Select the entire Value column → Format Cells → Protection → uncheck Locked. Then protect the sheet (Review → Protect Sheet). Now users can only edit the Value column. Parameter names can't be accidentally overwritten. Notes can't drift. The structure is enforced by Excel, not by trust.
-- Option 1: Named range (cleanest) =Revenue * Tax_Rate -- Tax_Rate is a named range pointing to Config!B4 -- Formula reads like a policy document -- Option 2: XLOOKUP against tbl_Config =Revenue * XLOOKUP("Tax_Rate", tbl_Config[Parameter], tbl_Config[Value]) -- More explicit. Useful when named ranges aren't set up. -- Works well in Power Query too. -- Option 3: LET + Config references (best of both) =LET( rate, Tax_Rate, threshold, Tier1_Threshold, base, Revenue, IF(base > threshold, base * rate, base * 0.08) ) -- Named ranges pulled into LET variables -- Formula is fully self-documenting
Every DDL workbook with more than 3 user-configurable inputs must have a Control Panel tab. Inputs scattered across formula cells are a governance finding. The Control Panel is the human-facing layer of the workbook — users interact with it, formulas read from it, auditors trust it.
When a workbook has multiple user-configurable inputs that drive calculations across multiple tabs, build a Control Panel tab so all inputs are visible, labeled, and validated in one place.
Control Panel must be protected with only the Value column unlocked. Parameter names must be stable — renaming breaks downstream formulas. Dropdown validation on toggle cells prevents invalid entries. Undocumented parameters accumulate over time — every row needs a Notes column entry.
Zero computational overhead. The Control Panel is a lookup source, not a calculation engine. XLOOKUP against a 20-row parameter table is negligible.
Every DDL workbook with more than 3 user-configurable inputs must have a Control Panel tab. Inputs scattered across formula cells are a governance finding. The Control Panel is the human-facing layer of the Driver Table Architecture (ARC-0010) — same principle, different audience. Users interact with the Control Panel; formulas interact with the Driver Table.
architecture · configuration · user-input
Also known as: parameter block · assumptions tab · config tab
-- Config tab layout for a commission model +---------------------+--------+---------------------------+ | Parameter | Value | Notes | +---------------------+--------+---------------------------+ | Run_Date | 3/31/26| Current run. Never TODAY()| | Tier1_Threshold | 500 | Units. Commission floor. | | Tier1_Rate | 12% | Above threshold rate. | | Base_Rate | 8% | Below threshold rate. | | Include_Inactive | FALSE | Toggle for inactive reps. | +---------------------+--------+---------------------------+ -- Formula in Calculations tab: =LET( units, tbl_Sales[Units_Sold], rate, IF(units > Tier1_Threshold, Tier1_Rate, Base_Rate), units * rate )
-- Config tab | Region_Filter | North | Dropdown: All/N/S/E/W | | Date_From | 1/1/26 | Start of period | | Date_To | 3/31/26 | End of period | -- Dashboard formula reads Config: =SUMIFS( tbl_Sales[Revenue], tbl_Sales[Region], IF(Region_Filter="All", tbl_Sales[Region], Region_Filter), tbl_Sales[Date], ">="&Date_From, tbl_Sales[Date], "<="&Date_To ) -- Change Region_Filter on Config → dashboard updates -- Change dates → dashboard updates -- No formula edits. Ever.