Concept
1. Tables for every data block
Ctrl + T on every raw dataset, then rename it (Table Design → Table Name):
=SUMIFS(tblSales[Revenue], tblSales[Region], inp_Region)
is clearer than =SUMIFS(RAW_Sales!$L$2:$L$50001, RAW_Sales!$E$2:$E$50001, CALC!$C$3) — and it includes new rows automatically.
2. Named cells and ranges
Select a cell → type a name in the Name Box (left of the formula bar) → Enter. Or Formulas → Define Name / Create from Selection (uses labels next to the cells).
Good uses:
- inputs:
inp_FY,inp_Region,inp_Month - constants:
k_GSTRate,k_TargetGrowth - KPI outputs:
kpi_Revenue,kpi_YoY(DASH cards reference these)
Manage all names: Formulas → Name Manager (Ctrl + F3) — filter "Names with errors" to find broken ones.
3. Dynamic named ranges for spills
A spilled list in CALC!F5 can be named for charts and validation:
Name: lst_Regions Refers to: =CALC!$F$5#
Charts can't use # directly in the series box, but they accept a name: series values ='Dashboard.xlsx'!lst_Regions. (The older way, OFFSET(...,COUNTA(...)), works too but is volatile — Module 8.)
4. A naming convention
| Object | Prefix | Example |
|---|---|---|
| Table | tbl |
tblSales, tblTargets |
| Input cell | inp_ |
inp_Region |
| Constant | k_ |
k_RedThreshold |
| List (for dropdowns) | lst_ |
lst_Regions |
| KPI output | kpi_ |
kpi_Revenue |
| Chart range | ch_ |
ch_TrendCur, ch_TrendLY |
| Power Query | stg_, dim_, fact_, chk_ |
fact_Sales |
| Sheet | layer prefix | DASH, CALC, RAW_Sales |
| Pivot | pvt_ |
pvt_RegionMonth (PivotTable Analyze → PivotTable Name) |
Rules: no spaces, start with a letter, don't use names that look like cells (FY26, Q4), keep them short but meaningful.
5. Scope
Names are Workbook scope by default — usable anywhere. Use Sheet scope only when two sheets need the same name for different cells (rare in dashboards).
6. Labels and units next to inputs
Every input cell gets a label on its left and a note on allowed values (data validation list). Colour input cells consistently (e.g. light yellow) so builders know "this can be changed".
Common mistakes
Unnamed Tables (Table1, Table2…). Hard-coded ranges that miss new rows. Names with errors left in Name Manager after deleting sheets. Mixing naming styles.