Excelligence · Standards

STD-EXCEL-001

Analytical Workbook Standard

v0.2 Ratification Ready · 2026-03-16 · Council: 3 LOCK · 6 REVISE · 0 REJECT

An analytical workbook answers a business question through aggregation, trend analysis, or visualization of a structured dataset. This standard defines the architecture, naming conventions, formula patterns, and output rules that all DDL analytical workbooks must follow.

Excel becomes reliable when you treat it like a data system, not a spreadsheet.

Contents

Rule 0 — README Tab Rule 1 — Workbook Architecture Rule 2 — Fact Table Standards Rule 3 — Dimension Layer Rule 4 — Backend Data Tables Rule 5 — Statistical Trend Layer Rule 6 — Dashboard Standards Rule 7 — Naming Conventions Rule 8 — Formatting Standards Rule 9 — Scope Discipline

Rule 0

README Tab

Required for Level 2+

Every Level 2 and Level 3 workbook must include a README tab as the first (leftmost) tab. It contains: workbook name and version, purpose (one sentence — the business question), grain statement, data source, refresh frequency, owner, date created, and tab inventory.

The README tab is the first place a new user opens. If it is missing, the workbook is non-compliant regardless of how well every other rule is followed.

Rule 1

Workbook Architecture

All analytical workbooks follow a layered architecture. Data flows in one direction only — no reverse references.

SOURCE→ FACT→ DIM→ DATA_TABLES→ DASHBOARD

Layer	Purpose
`SOURCE`	Raw imported data. No transformations.
`FACT`	Cleaned, keyed, time-normalized fact table.
`DIM`	One tab per dimension. Spill-generated.
`DATA_TABLES`	All aggregations and matrices. No raw data.
`DASHBOARD`	Final presentation. No calculations.

Three Implementation Levels

Level	Architecture	When to Use
Level 1	`FACT → DASHBOARD`	Low rows (<2K), single question, single operator, exploratory. ALL conditions must be true.
Level 2	`SOURCE → FACT → DIM → DATA_TABLES → DASHBOARD`	DDL default. Multiple dimensions, repeated reporting, stakeholder consumption, or any shared artifact.
Level 3	`SOURCE → STAGING → FACT → DIM → METRICS → DATA_TABLES → DASHBOARD`	Multiple fact tables, reusable metric library, cross-domain reporting.

A DASHBOARD formula referencing tbl_Fact or SOURCE directly is a Rule 1 violation — findable in 30 seconds by auditing DASHBOARD formulas.

Rule 2

Fact Table Standards

2a. Grain Definition

Every fact table must have a documented row grain in cell B1:

GRAIN: One row = one [entity] per [time period].

Grain is determined before any formulas are written. Grain errors are structural errors, not formula errors.

2b. Surrogate Key

Every fact row receives a synthetic key. Format: <Entity>-0000

Step 1: Generate  — =“LOAN-“ & TEXT(ROW()-1, “0000“)
Step 2: Immediately Paste Special → Values Only
Step 3: New records use MAX(key column)+1

Never leave surrogate keys as live ROW()-based formulas. If rows are inserted, deleted, or sorted, keys shift silently — breaking every downstream reference.

2c. Time Normalization

Required time columns: Month (date, first of month), MnthTxt (“September 2025“), YrTxt (“2025“), MnthYrTxt (“Sep 2025“).

2d. Table Format

Fact datasets must be Excel Tables (Ctrl+T), not ranges. Naming: tbl_Fact<Entity>.

Rule 3

Dimension Layer

3a. Generation Pattern

Dimensions are generated from fact tables using dynamic array spills. Never manually maintained lists.

=LET(
  x,       tbl_Fact[Field],
  cleaned, TRIM(UPPER(x)),
  u,       SORT(UNIQUE(cleaned)),
  FILTER(u, u<>““)
)

Wrap in IFERROR for empty source protection: =IFERROR(LET(…), {“No Data“})

3b–3d. Naming

Each dimension on its own tab. Naming: dim<Entity><Attribute>. Named range uses spill operator: dimMonth = dimMonth!$B$3#

Rule 4

Backend Data Tables

4b. Single-Dimension Aggregation

=MAP(dimMonth, LAMBDA(m, COUNTIFS(tbl_FactLoans[Month], m)))

4c. Matrix Aggregation (MAKEARRAY)

=LET(
  rowDim, dimLoanType,
  colDim, dimMonth,
  MAKEARRAY(ROWS(rowDim), ROWS(colDim), LAMBDA(r, c,
    COUNTIFS(
      tbl_Fact[LoanType], INDEX(rowDim, r),
      tbl_Fact[Month],    INDEX(colDim, c)
    )
  ))
)

MAKEARRAY fallback: A manually constructed COUNTIFS grid is compliant but not preferred. Document the fallback with a comment on the DATA_TABLES sheet.

4d. Count and Amount Matrices

For entities with economic magnitude, build both count-based (volume) and amount-based (magnitude) matrices.

Rule 5

Statistical Trend Layer

Required when period count ≥ 6. Below 6 periods: MoM % change only.

Metric	Formula Pattern
MoM % Change	`=IFERROR((Current / Prior) - 1, ““)`
Rolling Average	`=IFERROR(AVERAGE(range), ““)`
Deviation %	`=IFERROR((Value / RollingAvg) - 1, ““)`
Threshold Flag	`=IF(ABS(Dev) >= threshold, “Outside“, “Within“)`

Frequency	Window	Adjustment
Daily	7	14 if high volatility
Weekly	4	Align with fiscal month
Monthly	3	6 for seasonal/noisy data
Quarterly	4	Maximum 8

Rule 6

Dashboard Standards

Dashboard tabs contain only chart objects, formatted display tables, labels, and KPI cards referencing DATA_TABLES ranges. No business logic, aggregation formulas, COUNTIFS, or SUMIFS on DASHBOARD.

Three Analytical Questions (Minimum)

#	Question	Example
1	Total trend	How is the overall number moving?
2	Category mix	What is the composition?
3	Magnitude	What is the dollar or volume impact?

Rule 7

Naming Conventions

Object	Convention	Example
Fact table	`tbl_Fact<Entity>`	tbl_FactLoans
Dimension spill	`dim<Entity><Attr>`	dimLoanType
Matrix table	`mtx_<R>_<C>_<M>`	mtx_LoanType_Month_Count
Surrogate key	`<Entity>-0000`	LOAN-0001
Parameter cell	`sel_<Name>`	sel_Threshold

Rule 8

Formatting Standards

README tab first (leftmost). Headers at B2. Column A and Row 1 reserved as padding. Minimum 3 blank rows between tables on DATA_TABLES. Zebra striping via conditional format: =ISEVEN(ROW()).

Rule 9

Scope Discipline

V1 rule: Build only what answers the stakeholder question. All future enhancements go into a Surface Area List tab — the explicit backlog. Items on it are not forgotten — they are deferred.