Key takeaway
Spreadsheet headers are not a system schema. Before turning a shared Excel ledger into software, remove color-coding, merged cells, and hidden remark rules, then specify seven attributes per column: business meaning, data type, source and formula timing, stage validation, uniqueness, edit locks, and owner.

When small and mid-sized companies plan to replace a shared Excel ledger with an internal business system, kickoff meetings often begin with a tempting shortcut: "Here is our 40-column spreadsheet—just build a web version of it." Almost immediately after launch, three snags appear. The delivery date column holds both 2026-11-18 and "TBD". One customer appears under four different spellings. And formulas copied from the spreadsheet break because nobody decided whether a catalog price update should recalculate historical quotes or leave issued quotes untouched.
The failure happens because teams mistake spreadsheet column headers for a system field dictionary. Spreadsheets work when human eyes read cell colors, comments, and surrounding rows. Business systems lack that intuition; they depend on strict data types, stage validation, unique keys, and state-driven locks. Before converting high-frequency spreadsheets into software—if you are still choosing which manual ledger to tackle first, digitising repetitive work first offers a simple prioritization check—strip out hidden spreadsheet signals and define seven rules for every column.
Why Spreadsheet Headers Are Not a System Field Dictionary
In Excel, row 4 of a "Delivery Date" column can hold a date while row 5 holds "Next Tuesday?", and the file saves fine because spreadsheets treat cells individually. Databases and web forms enforce structural consistency across an entire column. As SQLite's datatype documentation explains when contrasting standard SQL databases (and SQLite's optional STRICT tables) with flexible typing, most SQL engines bind data types to the column container and require dates or times to follow an agreed explicit format such as ISO 8601 strings rather than mixing dates with free-text notes.
Web forms follow the same discipline. According to MDN's <input> element specification, every form field maps to a specific input type (date, number, text, email, etc.) along with attributes like required, min, max, pattern, and readonly. Skipping these rules forces developers to fall back on unrestricted text fields—effectively rebuilding spreadsheet chaos online, a pattern highlighted in Five Pitfalls That Derail Small-Business Digitalisation.
(Scope note: this guide covers field specification during requirements scoping. Cleaning years of historical typos and blanks right before data import is a separate migration-prep activity.)
Strip Four Hidden Spreadsheet Signals Before Listing Fields
- Merged cells
- Parent and detail records
- Color and note conventions
- Explicit states and fields
Before passing a working file to developers, inspect 20 typical rows and surface four informal signals that software cannot infer:
- Cell fills and font colors: Yellow rows for "waiting on deposit" or red numbers for "director-approved discount" cannot be filtered, summed, or automated. Translate every color convention into an explicit field such as
Quote StatusorSpecial Discount Approved. - Merged cells across rows or columns: One customer name and quote ID merged vertically over five equipment rows does not describe a single flat table. It signals a parent-child relationship: a Quote Header linked to Quote Line Items. Keeping them flat breaks product-level reporting.
- Operational rules buried in a "Notes" column or cell comments: Remarks like
"Incl. 13% VAT, freight covered, split billing, 7-day extension approved"trap searchable business terms inside free text. Extract frequent conditions into structured fields (tax_scheme,freight_terms) and leave Notes strictly for rare exceptions. - Multiple values or status words in one cell: Putting two phone numbers in one cell, or
"TBD"inside a date column, breaks sorting and reminders. Split multiple contacts into child entries and move"TBD"into an explicit status dropdown.
Seven Rules to Define Column by Column
Once hidden cues are cleaned up, define seven concise rules for each remaining column in plain business language:
- Business Meaning and Scope: Clarify exact definitions—e.g., does "Quote Amount" include tax and freight, and in which currency? Eliminate any two-way interpretation.
- Data Type and Format: Specify integer, decimal (decimal places and rounding), date vs. datetime (
YYYY-MM-DD), closed enum vs. admin-managed dictionary, boolean toggle, or short/long text with character limits. - Data Source and Formula Timing: Assign one of four sources: Manual entry, System auto-generated (quote ID, creator, timestamp), Master record lookup (picked from Customer or SKU catalogs rather than re-typed), or Formula / Computed. For formulas, define when recalculation happens and when values freeze—for example, quote totals recalculate dynamically in
Draft, then snapshot permanently uponIssuedso future catalog price changes never alter sent quotes. - Requiredness and Stage Validation: Separate fields optional in
Draftfrom fields mandatory atSubmitorIssue, plus bounds and cross-field checks (e.g.,valid_until >= quote_date). - Uniqueness and Deduplication: Flag system-wide unique keys (Quote No.) and enforce master deduplication—require selecting customers from the Customer Master, checked by tax ID or registration number rather than free-text names.
- Editability and Lock Timing: Define per-field mutability across record stages: Quote No. stays read-only, while tax schemes and totals lock once
IssuedorWon, changeable only through formal revisions. (Cross-role visibility rules belong in your role permission matrix; here, settle each field's lifecycle lock.) - Field Business Owner: Name the role accountable for defining rules, maintaining dropdown options, and resolving duplicate master records.
Hypothetical Example: Field Dictionary for an Equipment Trading Quote Ledger
Consider a hypothetical scenario: a trading and equipment supplier is upgrading a 40-column shared Excel quote sheet into a web system. With merged product rows split into a separate Quote Line Items child table, here is how eight representative fields on the Quote Header look in a field dictionary:
Field Name | Business Meaning & Scope | Type & Format | Source & Formula Timing | Requiredness & Validation | Uniqueness & Dedup | Editability & Lock | Business Owner |
|---|---|---|---|---|---|---|---|
Quote No. | Unique ID per outgoing quote | Text ( | System auto-generated monthly | Auto-filled on create | Globally unique key | Read-only permanently | Sales Operations |
Customer | Legal buyer account | Master reference (no free text) | Picked from Customer Master | Optional in Draft; required on Submit | Deduped in master by Tax ID | Editable pre-Issue; locked once Issued | Sales Admin |
Quote Status | Replaces yellow/red row fills | Enum ( | Defaults to | Always populated | None | Changes via workflow actions only | Sales Manager |
Quote Date | Official pricing date | Date ( | Defaults to today; editable | Optional in Draft; required on Submit | None | Locked once Issued | Sales Admin |
Valid Until | Replaces | Date ( | Defaults to | Required on Submit; | None | Locked once Issued; drives expiry | Sales Manager |
Tax Scheme | Replaces | Enum ( | Defaults from Customer profile; overridable | Required before Submit | None | Locked once Issued | Finance |
Quote Total (Incl. Tax) | Final order total in billing currency | Decimal (2 decimal places) | | Must be | None | System-calculated; no manual override | Finance & Sales Ops |
Special Terms Note | Rare non-standard terms only | Multi-line text (max 500 chars) | Manual entry | Optional | None | Editable pre-Issue; append-only later | Account Rep |
With these eight rows aligned, business owners know sent quotes will stay frozen when prices shift, and engineers immediately see the three-table design (Customers, Quote Headers, Quote Line Items).
Blank Field Inventory Checklist Before Building
Before scoping a system build, pick one core spreadsheet and complete this blank checklist:
[Spreadsheet-to-System Field Inventory Checklist]
Part 1: Pre-Inventory Signal Cleanup (Check 4 Items)
- [ ] Merged cells present? Separate columns into Parent Header vs. Child Line Items:
- [ ] Color fills or font colors used? Map each color meaning to explicit Status/Flag fields:
- [ ] High-frequency rules hidden in Notes (tax, freight, split billing)? List fields to extract:
- [ ] Formulas present? Confirm whether catalog updates recalculate drafts only or snapshot historical records:
Part 2: Seven-Rule Field Dictionary
- Module / Form Name: (e.g., Quote Header / Quote Line Items)
| Field Name | Business Meaning & Scope | Type & Format (Int/Dec/Date/Enum/Text/File) | Source (Manual/Auto/Master/Formula + Timing) | Requiredness & Validation (Draft vs. Submit) | Uniqueness & Dedup | Editability & Lock Condition | Business Owner |
|---|---|---|---|---|---|---|---|
| Field 1: | | | | | | | |
| Field 2: | | | | | | | |
| Field 3: | | | | | | | |
| Field 4: | | | | | | | |Clearing up the field dictionary for one core ledger prevents most early rework. When you are ready to move from shared spreadsheets to a tailored workflow system, pair this inventory with our Business System Project Checklist or explore ChengXuYuan's Business System Development Services to scope your first release around a real working ledger.