How to Create Spreadsheets With Super Intelligence | AI Formulas, Models, Scenarios and Spreadsheet QA

Spreadsheet Architecture: Raw Data, Logic, Outputs and Controls

A maintainable workbook usually separates at least four logical layers. Raw data preserves imported or entered facts. Logic transforms those facts. Outputs present the results. Controls hold assumptions, validation and error checks.

These layers can live in one simple sheet for a small task or several sheets for a larger model. The principle is conceptual separation, not maximum sheet count.

When SI helps design the workbook, ask it to name the role of every major range or sheet. Unowned regions become sources of hidden logic.

Raw Data Should Remain Recoverable

Do not overwrite raw imported data with cleaned values unless the workflow intentionally creates a new canonical dataset and preserves provenance.

A common pattern is Raw → Clean → Model. Raw remains untouched, Clean applies documented transformations and Model performs calculations.

This architecture allows the user to reproduce how a result emerged and correct a cleaning rule without losing the original record.

Data Cleaning Rules

Cleaning includes standardising dates, trimming spaces, resolving categories, handling missing values and validating ranges.

Every cleaning rule should answer why the change is justified. Do not convert an unusual but valid value into a missing value merely because it looks different.

SI can propose formulas or transformations, but the user should inspect samples before applying a rule across the dataset.

Duplicate Handling

Duplicate rows can represent accidental repetition or legitimate repeated events. Decide what a duplicate means before removing it.

Use a unique identifier when possible. If no ID exists, define the combination of fields that determines sameness.

A spreadsheet should preserve a count or log of removed duplicates when the change affects important totals.

Category Normalisation

Names such as Sec 1, Secondary One and S1 may refer to the same category. Normalisation improves aggregation.

Maintain a mapping table rather than embedding many text replacements directly inside complex formulas.

Mapping tables are easier to audit and update when terminology changes.

Date Normalisation

Dates can arrive as text, serial values or locale-specific formats. Convert them carefully into one canonical representation.

Ambiguous dates such as 03/04/2026 should not be guessed silently. Use source locale or ask for clarification.

For time-sensitive analysis, keep timezone where it matters.

Number Parsing

Currency symbols, commas, spaces and percentage signs can cause numbers to be stored as text.

Clean values into numeric fields while preserving unit separately where needed.

Test for negative numbers, decimals and large values before applying bulk conversion.

Formula Design Principles

A good formula should be understandable enough that another user can explain the business rule. Extremely compact formulas can become harder to audit than several clear helper columns.

Prefer clarity over cleverness when the workbook will be maintained by others.

SI can refactor a formula into helper steps if the current expression is too opaque.

Relative and Absolute References

Relative references change when copied; absolute references remain fixed. Mixed references lock only row or column.

Generated formulas often fail because the intended anchoring was not specified.

Test a formula after copying it across several rows and columns to confirm the reference behaviour.

Structured References

Table-based structured references can make formulas more readable by using column names rather than coordinates.

They also expand automatically as the table grows in many spreadsheet systems.

The user should still understand which table and field the formula references.

Lookup Logic

Lookups connect one table to another. Define the key and what should happen when no match or multiple matches exist.

A missing lookup should not automatically become zero. It may be Unknown, error or a separate exception state.

SI can write lookup formulas, but the key uniqueness and source table need validation.

Aggregation

Sum, average, count and other aggregations require a well-defined population. Filter conditions should be visible.

Ask what rows are included and excluded. A total can be arithmetically correct but conceptually wrong.

For averages, consider whether weighting is required.

Conditional Logic

IF-style logic can encode business rules, but long nested condition chains become difficult to maintain.

Where possible, move categories and thresholds into tables or helper columns.

Document what each branch represents and add a test case for every branch.

Error Handling

Do not hide all errors with blanket error suppression. An error may be the signal that a required source or match is missing.

Use error handling intentionally: return a meaningful state such as Missing Source, Invalid Date or Division Undefined when appropriate.

A visible error can be safer than a plausible-looking fallback.

Formula Auditing

Inspect precedent cells, dependent cells and formula consistency across ranges.

Use spot checks with known results. Create reconciliation totals where the model should balance.

AI can identify unusual formulas in a column, but final acceptance should include direct inspection of critical cells.

Reconciliation Checks

A reconciliation check compares two independently derived totals that should agree.

Examples include total debits versus credits, row totals versus summary total or source record count versus imported count.

A failed reconciliation should block important outputs rather than remain a hidden cell.

Control Totals

Control totals preserve simple counts or sums before and after transformation.

If raw data contains 1,000 records and cleaned data contains 994, the workbook should explain six removals.

This makes cleaning operations recoverable and auditable.

Scenario Architecture

Scenarios should change a controlled set of assumptions while keeping the model logic stable.

A scenario table can contain Base, Low and High values for growth, cost or demand. The output sheet reads the selected scenario.

Do not duplicate entire workbooks for each scenario unless the model is too simple for a shared architecture.

Sensitivity Tables

Sensitivity analysis shows how an output changes when one or two assumptions vary.

It is useful for decisions with uncertain inputs. A result that flips under small changes is fragile.

Label sensitivity outputs clearly as conditional analysis rather than forecasts.

Goal Seeking

Sometimes the question is inverse: what input value is required to achieve a target output?

Goal-seeking tools can help find the threshold, but the model still needs to be monotonic or otherwise well behaved enough for the method.

Verify the resulting assumption and whether it is feasible in the real system.

Break-Even Analysis

Break-even models compare fixed cost, variable cost, price or revenue to find the threshold where outcome changes sign.

State the assumptions clearly and test whether they remain valid across the range.

SI can build the formula quickly, but the business meaning of each input still requires human validation.

Forecasting

Forecasts depend on historical data, model assumptions and future uncertainty. Avoid presenting projected values as actuals.

Use baseline, method, horizon and uncertainty. Compare simple methods before using complex ones.

For changing environments, old patterns may not remain predictive.

Budget Variance

A budget sheet can compare Actual, Budget and Variance. Define whether favourable variance is positive or negative consistently.

Add notes for material variances rather than relying on numbers alone.

If one category changes definition mid-year, preserve comparability or label the break.

Cash Runway

Cash runway calculations should define starting cash, burn rate, incoming cash and timing.

Average burn can hide seasonality or large future payments.

Use scenario analysis and date-based cash flows for decisions where timing matters.

Student Performance Models

Education spreadsheets can track topic, question type, error category, independent or assisted performance and date.

Avoid reducing the learner to one average. Use the model to identify patterns and next practice, not to label ability permanently.

Sensitive student data should follow school and privacy requirements.

Assessment Analysis

An assessment sheet can calculate item difficulty, topic accuracy and recurring error types.

Check whether the test itself is comparable across groups before making conclusions.

Generated interpretations should remain bounded by the available data.

Attendance Models

Attendance rate requires a defined denominator such as registrations, scheduled sessions or expected participants.

Different denominators answer different questions. Store the metric definition beside the calculation.

Do not use room capacity simply because it is available.

Research Data Workbooks

Research sheets should include codebook, raw data, cleaning log, derived variables and analysis outputs.

Keep changes reproducible. Avoid manual edits that cannot be traced.

SI can help write formulas or scripts, but method documentation belongs with the workbook.

Survey Data

Survey responses can include missing values, multi-select fields and inconsistent free text.

Define how incomplete responses are handled before analysis.

Open-text coding can use SI assistance, but category definitions and quality checks should be explicit.

Project Management Sheets

Project trackers can model tasks, owners, dependencies, milestones, status and blockers.

Do not treat percentage-complete as precise when the task definition does not support that precision.

Use dates and status states that reflect actual project governance.

Inventory Sheets

Inventory models need item IDs, quantities, locations, reorder points and transaction records.

A current stock number should reconcile with transactions or source system where appropriate.

Negative inventory or impossible quantities should trigger checks.

Sales and Pipeline Sheets

Sales models often mix observed pipeline with forecasts. Separate actual, committed, probable and speculative states.

Conversion assumptions should be visible and reviewed over time.

Do not allow optimistic weighting to become hidden inside formulas.

Comparison Models

Option-comparison workbooks should use common criteria and keep unknowns visible.

Weights represent human priorities. Document who set them and test sensitivity.

The spreadsheet should support the decision rather than produce an automatic verdict disguised as mathematics.

Dashboard Architecture

A dashboard should answer recurring monitoring questions at a glance.

Separate KPI definition, current value, trend, target and warning state. Avoid too many metrics.

Link every visual to a validated underlying range or table.

KPI Definitions

Each KPI needs name, purpose, formula, source, frequency and owner.

Do not allow teams to use the same metric name for different definitions.

A KPI dictionary reduces organisational drift.

Thresholds and Alerts

Thresholds should reflect action. A red status is useful only if someone knows what should happen next.

Document whether a threshold is evidence-based, policy-based or a temporary management rule.

Avoid arbitrary traffic-light colouring that creates urgency without meaning.

Chart Integrity in Spreadsheets

Charts should preserve correct axis, unit and source. Check whether filters exclude data unexpectedly.

When a workbook is filtered, confirm whether the chart updates as intended.

Do not rely on 3D effects or decoration that make values harder to compare.

Sparklines and Small Visuals

Compact visuals can show trend inside tables, but they should not replace exact values when precision matters.

Use consistent scale where comparisons are intended.

Label the time period or context.

Pivot Tables

Pivot tables help aggregate large tables quickly. The result depends on field placement, aggregation type and source refresh.

Check whether values are Sum, Count, Average or another function.

Refresh after source changes and verify that new categories are included.

Pivot Charts

Pivot charts can support interactive exploration but can confuse users when filters change invisibly.

Make active filters visible in dashboards or instructions.

A screenshot of one pivot state should not be treated as a permanent result.

Filters and Slicers

Interactive filters make one workbook serve several questions.

Default states should be clear. A user should know whether the dashboard shows all data or a subset.

Avoid hiding excluded data in ways that could mislead interpretation.

Spreadsheet Documentation

Add a README or Notes sheet for complex workbooks. Include purpose, data sources, editable inputs, calculation sheets, known limitations and update steps.

This is the workbook equivalent of a document handoff package.

SI can draft the instructions, but they should match the actual file.

Workbook Naming and Versioning

Use stable file naming with date or version when multiple releases exist.

Do not keep several files called final.xlsx in shared locations.

Where collaborative platforms provide version history, use one canonical workbook rather than copying repeatedly.

Spreadsheet Collaboration

Assign ownership for data imports, assumptions, formulas and outputs in team models.

Simultaneous editing can be useful, but important changes should be reviewed when they affect core logic.

A change log is valuable for recurring financial or operational models.

Permissions

Some users should edit assumptions; others may need read-only access. Protect sensitive sheets or files according to policy.

Spreadsheet protection is not equivalent to strong security, so use proper platform permissions as well.

Do not store sensitive information simply because the workbook is convenient.

A Spreadsheet Review Checklist

• Decision or question is explicit.
• Raw data is recoverable.
• Cleaning rules are documented.
• Units and types are consistent.
• Metric definitions are explicit.
• Formulas match the business rule.
• Missing values are handled deliberately.
• Error checks and reconciliations exist.
• Assumptions are visible.
• Scenarios are labelled.
• Charts use validated data.
• Outputs remain traceable.
• Permissions and handoff are clear.

A Spreadsheet Testing Protocol

Create known-answer cases for critical formulas. Add one missing-value case, one boundary value and one deliberately invalid input.

Check that the workbook either returns the correct result or produces an understandable error state.

Repeat the test after major formula or architecture changes.

The Spreadsheet Receiver Test

Give the workbook to another authorised user. Can they identify editable inputs, understand outputs and avoid overwriting logic?

Ask them to change one assumption and explain which outputs respond.

If the file needs a long verbal walkthrough, improve labels, notes or architecture.

The Spreadsheet Maintenance Rule

Review source freshness, assumptions, formulas and dashboard relevance at intervals appropriate to the model.

Retire obsolete sheets and duplicate metrics.

A workbook should become easier to trust as it matures, not accumulate invisible logic.

The Spreadsheet Audit Trail

For high-value models, preserve version history, source dates and major assumption changes.

A user should be able to explain why today’s output differs from last month’s.

The audit trail turns the workbook into accountable infrastructure rather than a disposable calculation.

The Spreadsheet Exit Test

Before relying on the workbook for a decision, reproduce one critical output independently, check the most sensitive assumption and inspect one error condition.

Then confirm that the decision owner understands what the model includes and excludes.

A spreadsheet is ready when its logic is visible enough to challenge.

Spreadsheet Architecture: Workbook Before Formula

A strong SI-created spreadsheet begins with workbook architecture rather than isolated formulas. Decide which sheets represent Inputs, Reference Data, Calculations, Outputs, Dashboard, Scenarios and Documentation. Not every workbook needs all of these, but the roles should be explicit.

This architecture makes the model easier to inspect. Inputs can be changed without touching formulas. Calculations can be audited without sorting through presentation elements. Outputs can be designed for the receiver without hiding the logic that produced them.

SI can propose the layout, but the user should decide which separation reduces real error and handoff cost. A three-row personal tracker does not need six sheets; a financial or operational model often does.

The Input Sheet as a Contract

The input layer defines which values humans are allowed to change. Every editable field should have a label, unit, description and—where useful—a validation rule or permitted range.

For example, a budget model can separate Monthly Revenue, Fixed Costs, Variable Cost Rate and Starting Cash. The inputs should not be mixed with hidden formulas. A user should be able to see which assumptions drive the model.

When SI generates a workbook specification, ask it to identify input ownership as well: who provides the value, how often it changes and which source is authoritative.

Reference Data

Reference data includes lookup tables, category maps, rates, calendars, exchange rates, score bands or other repeated values used by formulas. Keep these values in one controlled location rather than hard-coding them into several formulas.

A named reference table reduces inconsistency. If a tax rate, tuition fee or grading band changes, the model can update one source instead of hunting through many cells.

Time-sensitive reference data should include a date or version marker. SI can help import or clean the values, but freshness still needs verification.

Calculation Layer

The calculation layer should expose the logic of the model. Complex formulas are easier to audit when broken into meaningful intermediate calculations rather than compressed into one giant expression.

For example, Revenue, Variable Cost, Gross Margin, Fixed Cost and Operating Result can remain separate calculations before a dashboard summarises them. This makes it possible to locate the first wrong step.

Ask SI to explain every non-trivial formula in plain language. If neither the user nor a future reviewer can explain the logic, the model is too opaque.

Output Layer

Outputs should answer the decision or user task. A spreadsheet can contain hundreds of calculations and still fail because the receiver cannot see the one metric that matters.

Define the output before polishing the workbook. A parent tracker might need Current Score, Error Pattern and Next Practice. A manager might need Cash Runway, Variance and Risk Flag. A project owner might need Status, Deadline and Blocker.

The output layer should not become a second calculation engine. Keep transformations upstream where they can be audited.

Dashboard Layer

A dashboard is useful when the receiver needs recurring visibility across several measures. It is not automatically better than a small output table.

Use a dashboard when trend, comparison, exception or threshold status matters. Keep the number of visuals limited enough that the user can identify what changed and what action follows.

SI can suggest chart types and layouts, but the user should verify axes, aggregation, labels and whether the visual supports the underlying decision.

Documentation Sheet

Recurring workbooks benefit from a short Documentation or Read Me sheet. Include purpose, owner, update frequency, input sources, important formulas, scenario definitions and known limitations.

This sheet protects the workbook from becoming a personal black box. A future user can understand what should be edited and what should remain stable.

For important models, add a change log and version marker. Easy AI-assisted editing increases the need for clear provenance.

Spreadsheet Data Model

Think of a spreadsheet as a small data model. Each row should represent a consistent kind of record, and each column should represent one attribute. Mixing totals, notes and records in the same table makes formulas fragile.

A student tracker might use one row per assessment and columns for Date, Subject, Topic, Score, Maximum Score, Error Type and Notes. A project tracker might use one row per task with Owner, Status and Deadline.

SI can propose the schema, but the semantic meaning of each row and column should be decided before formula design.

Tidy Data Principles

Tidy data means one variable per column, one observation per row and one kind of observational unit per table. These principles make filtering, pivots, charts and automated analysis more reliable.

Avoid layouts designed only for visual appearance when the workbook also needs analysis. Merged cells, repeated headers and blank spacer rows can make formulas and imports more difficult.

Presentation can be added after the underlying table is structurally sound.

Data Validation

Data validation reduces avoidable input error. Use dropdowns for controlled categories, limits for percentages and date rules where appropriate.

Validation should match the real domain. A score cannot exceed maximum marks. A project status should come from a controlled set. A date field should not accept free-form comments.

SI can suggest rules, but test legitimate edge cases so validation does not reject valid data.

Missing Data

Blank, zero, unknown and not applicable are different states. A blank cell should not automatically be interpreted as zero if absence changes meaning.

Define missing-value policy explicitly. Use blank for not entered, a controlled code for not applicable or a separate status field when the distinction matters.

When SI analyses the workbook, require it to preserve the difference. A generated average should not silently treat missing scores as zeros.

Duplicate Records

Duplicate rows can distort totals, averages and counts. Add unique identifiers where repeated records are possible.

A payment table may use invoice ID. A student assessment table may use student + assessment ID. A project log may use task ID.

SI can help identify likely duplicates, but deletion should follow a rule. Similar-looking records may represent legitimate repeated events.

Date and Time Design

Dates should be stored as real date values rather than inconsistent text. Time zones matter when the workbook combines events or teams across regions.

Separate Event Date, Start Time and Timezone when downstream tools need precision. Use unambiguous display formats for international audiences.

Derived fields such as Month, Week or Quarter should come from the canonical date rather than being entered manually.

Units and Currency

A numeric model becomes dangerous when units are implicit. Label dollars, percentages, kilograms, hours, marks and rates explicitly.

For multi-currency models, separate Amount and Currency or convert through a controlled rate table with date. Do not mix converted and unconverted values in one total.

SI can write formulas, but unit consistency should be part of the verification checklist.

Percentages and Rates

Every rate needs a denominator. Store or expose the numerator and denominator where the rate matters operationally.

Attendance rate may be Attended ÷ Registered, while Capacity Utilisation may be Attended ÷ Room Capacity. Those answer different questions.

A spreadsheet that shows 75% without the denominator definition invites semantic error even when the formula is syntactically correct.

Named Ranges and Named Inputs

Named ranges can make formulas easier to understand: Revenue_Growth instead of $B$7. They are useful when a workbook has stable assumptions reused across sheets.

Use names selectively. Too many poorly named ranges create a different kind of complexity.

SI can propose names, but a human should ensure they remain concise, unique and aligned with the business or learning language used elsewhere.

Formula Design

Write formulas from plain-language rules. First state the relationship, then translate it into spreadsheet syntax.

Example: Operating Profit = Revenue – Variable Costs – Fixed Costs. Only after the semantic rule is accepted should the exact formula be generated.

This prevents a syntactically valid formula from implementing the wrong concept.

Formula Auditing

Audit formulas using representative rows, boundary values and manual calculations. Check references before copying formulas down long ranges.

Use a small number of cells for hand-calculated benchmarks. If the spreadsheet disagrees, investigate before scaling the formula.

SI can explain formula logic and propose tests, but the workbook itself should supply reproducible evidence.

Lookup Design

Lookups connect records to reference data. Define whether the lookup should be exact, approximate or return multiple matches.

An exact student ID lookup is different from assigning a score band based on thresholds. The formula and error handling should reflect the business rule.

Always define what happens when no match exists. Returning a plausible nearby value can be worse than an explicit Not Found.

Scenario Modelling

Scenario models should change a controlled set of assumptions while holding the rest of the model stable. Name scenarios clearly: Base, Conservative, High-Growth or other domain-specific labels.

Each scenario should document which inputs differ. Avoid copying entire workbooks for every scenario because versions drift.

SI can help define ranges and scenario narratives, but the assumptions should remain visible and editable.

Sensitivity Tables

Sensitivity analysis asks how an output changes when one or two assumptions vary. It is valuable when decisions depend heavily on uncertain inputs.

A cash-runway model can vary Revenue Growth and Cost Growth. A study plan can vary available hours and retention rate. A project model can vary task duration and staff capacity.

The analysis reveals which assumption deserves the strongest evidence or monitoring.

Break-Even Analysis

Break-even models identify the value at which outcome changes sign or crosses a threshold. Examples include revenue needed to cover cost or score required to meet a target.

State which variables are fixed and which vary. A break-even number is only meaningful under those assumptions.

SI can derive formulas, but test them with simple values before relying on the result.

Forecasting

Forecasting spreadsheets extend historical or assumed relationships into the future. Label forecasts clearly and distinguish them from actuals.

Keep Actual, Forecast and Scenario values separate. Record forecast horizon and major assumptions.

A model should not create false precision. Use ranges where evidence does not justify a single point estimate.

Variance Analysis

Variance compares actual with budget, target or prior period. Define whether positive variance is favourable or merely numerically positive.

A cost variance of +$1,000 may be unfavourable while a revenue variance of +$1,000 may be favourable. Labels should reflect interpretation.

SI can generate commentary from variance tables, but the formula definitions should remain inspectable.

Pivot Tables

Pivot tables are useful for summarising repeated records by category, time or owner without manually writing many formulas.

Before building a pivot, ensure the source table is tidy and categories are consistent. The pivot cannot correct ambiguous underlying data.

Use pivots for exploration and recurring reporting, then verify that filters and aggregation match the question.

Conditional Formatting

Conditional formatting can highlight thresholds, overdue items or outliers. It should direct attention, not substitute for the underlying rule.

Document the threshold behind the colour. Red should mean something operational, such as Deadline Passed or Error Rate Above Limit.

Avoid decorative colour rules that make the workbook visually busy but analytically weaker.

Chart Selection

Choose charts according to relationship: line for time trend, bar for categorical comparison, scatter for relationship between two numeric variables and stacked forms only when composition is clear.

Pie charts and decorative graphics can make precise comparison difficult. Use them only when they genuinely serve the receiver.

SI can suggest visuals, but the final chart should be checked for scale, units, labels, denominator and missing categories.

Chart QA

A chart can be technically linked to the right cells and still mislead. Check zero baselines where relevant, truncated axes, category ordering and aggregation.

Ensure the chart title states what is measured rather than making an unsupported conclusion.

For public or educational use, keep the source and time period available near the visual.

Spreadsheet Error Checks

Add explicit checks for totals, balances, missing values, impossible dates and formula completeness. A model should be able to signal that the workbook is not ready.

Examples include Total Assets – Total Liabilities – Equity = 0, Sum of category shares = 100%, or Count of required fields missing = 0.

Error checks turn hidden assumptions into visible QA.

Control Totals

Control totals compare independent calculations. If two routes to the same total disagree, something is wrong.

For example, sum line items and compare with source total. Count imported records and compare with system export count.

Independent control totals are especially useful in financial, attendance and operational workbooks.

Circular References

Some iterative models deliberately use circular calculations, but accidental circular references usually signal unclear model structure.

Trace the dependency chain. If the calculation genuinely requires iteration, document the method and convergence assumptions.

Do not let SI “fix” a circular reference by deleting a formula without understanding the model.

Volatile Functions and Performance

Large workbooks can become slow when many volatile formulas or full-column calculations recalculate repeatedly.

Use efficient ranges and simpler formulas where possible. Separate heavyweight analysis from presentation.

Performance is part of usability when a workbook becomes an operational tool.

Imports and External Data

When spreadsheets import data, record the source, refresh method, date and transformation steps. External connections can fail or change schema.

Keep raw imported data separate from cleaned data and calculations where practical.

SI can help write transformation formulas or scripts, but the data lineage should remain visible.

Data Cleaning

Common cleaning steps include trimming whitespace, standardising categories, parsing dates, handling missing values and removing confirmed duplicates.

Document cleaning rules rather than manually editing cells inconsistently.

A reproducible cleaning layer makes later analysis easier to audit and rerun.

Spreadsheet Handoffs

A handoff-ready workbook tells the next user what to edit, what not to edit, where data comes from and how outputs are checked.

Use labels, documentation and protection rather than relying on verbal explanation.

Test the handoff with another authorised person. If they break formulas immediately, the workbook architecture or instructions need improvement.

Spreadsheet Permissions

Shared workbooks should align access with roles. Some users may need view access, others input access, and only a few formula or structure access.

Permissions reduce accidental changes but should not replace backups or version history.

For sensitive data, minimise what enters the workbook and follow applicable organisational controls.

Version History

Important operational workbooks need identifiable versions or change history. Record major formula, assumption or schema changes.

If a model changes the definition of a metric, older outputs may no longer be comparable. Note the change rather than silently overwriting history.

Easy SI-assisted modification makes version discipline more important, not less.

Spreadsheet Governance

Recurring spreadsheets need an owner, source rules, update schedule, quality checks and retirement plan.

A workbook can become shadow infrastructure inside an organisation. Governance keeps it from depending on one person’s memory.

When the workbook outgrows spreadsheet architecture, migrate to a database, application or code pipeline rather than adding endless sheets.

When a Spreadsheet Is the Wrong Tool

Spreadsheets are excellent for visible models, moderate datasets and flexible analysis. They become weak when many users need concurrent transactions, strict permissions, large-scale data or complex workflows.

Ask whether the workbook is modelling, reporting, storing operational records or running a business process. Those jobs may need different tools.

SI can help compare options, but tool choice should follow scale and control requirements.

A Full Worked Example: Tuition Progress Workbook

Inputs: Student ID, assessment date, topic, score, maximum score, error type and notes. Calculations: percentage, rolling performance, error frequency and topic status.

Outputs: current strongest and weakest topics, repeated error category, latest independent score and next review date. Dashboard: only the measures the tutor and parent actually need.

QA: source marks checked, denominator uses Maximum Score, missing assessments remain blank rather than zero, and error categories come from a controlled list.

A Full Worked Example: Small-Business Cash Model

Inputs: opening cash, monthly revenue, fixed cost, variable cost rate and one-off expenses. Calculations: operating result, ending cash and runway.

Scenarios vary revenue growth and cost growth. Sensitivity identifies which assumption changes runway most.

QA includes balance checks, unit consistency, negative-cash warning and explicit separation of actuals from forecasts.

A Full Worked Example: Project Delivery Model

Rows represent tasks. Fields include Owner, Start, Duration, Dependency, Status and Risk. Calculations derive expected finish and overdue flags.

The model should not schedule dependent tasks before prerequisites finish. Shared resources should be checked for over-allocation.

A dashboard can show critical blockers and milestone status without hiding the underlying task table.

A Full Worked Example: Research Dataset

Raw data remains separate from cleaned data. A data dictionary defines each variable, units, missing codes and source.

Cleaning rules are reproducible. Analysis outputs identify sample size, exclusions and calculated metrics.

Charts and conclusions link back to validated calculation ranges rather than manually copied numbers.

A Spreadsheet QA Checklist

• Inputs are separated from formulas.
• Each table has one clear row meaning.
• Data types and units are explicit.
• Missing values are defined.
• Important categories use validation.
• Formulas match plain-language rules.
• Denominators and units are checked.
• Reference data is centralised.
• Scenarios change named assumptions.
• Error checks and control totals exist.
• Charts match the data and question.
• Outputs answer the receiver’s decision.
• Documentation explains ownership and update process.
• Permissions and version history fit the consequence.
• Another user can operate the workbook without hidden context.

A Spreadsheet Regression Set

Keep a small set of inputs whose expected outputs are known. Include normal case, blank value, zero, negative number where valid, boundary threshold, missing lookup and one historical formula failure.

After major formula or structure changes, rerun the regression set. This protects against a repair in one area silently breaking another.

A spreadsheet becomes trustworthy through repeatable checks rather than appearance.

The Final Spreadsheet Portability Test

Open the workbook without the original SI conversation. Another authorised user should be able to identify assumptions, inputs, formulas, outputs, source data and validation rules.

Then change one assumption and trace how the output changes. If the path cannot be explained, the model is too opaque.

The strongest SI-created spreadsheet therefore remains useful after the assistant is gone: understandable, testable and maintainable by humans.

Spreadsheet Automation With Super Intelligence

SI can accelerate spreadsheet automation by drafting formulas, scripts, cleaning rules and recurring report logic. Automation should follow a model whose inputs, outputs and error states are already understood.

Do not automate a workbook merely because one task repeats. First determine whether the repetition is stable, whether the source data is consistent and whether exceptions require human judgment.

Scripts, Macros and Custom Functions

Scripts and macros can automate imports, formatting, calculations and exports. Treat generated code as software: review it, test it and restrict permissions.

Keep source code or script logic accessible. A workbook that depends on hidden automation becomes difficult to audit and recover.

External Data Connections

Connected spreadsheets can pull data from databases, APIs or other files. Record where the data comes from, how often it refreshes and what happens when the connection fails.

A dashboard should not continue presenting stale data as current without a visible freshness indicator.

Refresh State

Show when data was last refreshed where freshness affects decisions. For automated imports, distinguish successful refresh from attempted refresh.

If the source is unavailable, the workbook should expose the failure rather than quietly reusing an old value as if it were live.

Spreadsheet Security

Spreadsheets often contain sensitive data, formulas and business assumptions. Use appropriate file and platform permissions rather than relying only on sheet protection.

Review links, hidden sheets, comments and embedded data before external sharing.

Spreadsheet Privacy

Collect the minimum personal information required. Use anonymised identifiers for student, customer or employee analysis where identity is unnecessary.

Generated summaries and charts can still reveal sensitive patterns, so privacy review should include outputs as well as raw data.

Hidden Sheets and Hidden Logic

Hidden sheets can support configuration or calculations, but they should not become a place where critical assumptions disappear from review.

Document their purpose and make them accessible to authorised maintainers.

Circularity and Iterative Models

Some financial and operational models intentionally contain circular relationships. Use iterative calculation only when the model requires it and convergence behaviour is understood.

Do not accept circular references introduced accidentally by generated formulas.

Array and Dynamic Formulas

Modern spreadsheet functions can return arrays and replace many copied formulas. This can simplify models but changes how downstream references behave.

Test spill ranges, empty results and new rows before relying on the pattern operationally.

Lookup Robustness

Lookups can fail when keys contain spaces, inconsistent case, changed IDs or duplicates. Create key-quality checks before trusting results.

When a lookup returns multiple matches, decide whether the data is invalid or the model should aggregate rather than silently selecting one.

Date Arithmetic

Dates are stored numerically in many spreadsheet systems, which makes calculations powerful but can hide timezone, locale and business-day assumptions.

Define whether elapsed time includes weekends, holidays or partial days. Use the rule the real process requires.

Financial Precision

Currency models need clear rounding rules. Display rounding and calculation rounding are different.

For tax, pricing or contractual calculations, follow the applicable domain rules rather than an arbitrary number of decimals.

Scenario Documentation

Every scenario should record which assumptions changed and why. A High case should not quietly alter ten values without explanation.

Keep scenario logic centralised so new assumptions can be reviewed easily.

Sensitivity and Decision Thresholds

Use sensitivity analysis to find the input value at which the preferred decision changes. This threshold is often more useful than one base-case forecast.

Ask whether the real-world input is likely to cross that threshold and what evidence would update the estimate.

Spreadsheet Model Risk

Model risk comes from wrong formulas, bad inputs, inappropriate assumptions, misunderstood outputs and users changing logic accidentally.

Control model risk through separation, validation, review, protected cells, documentation and independent checks on high-consequence outputs.

Independent Calculation Checks

For critical outputs, reproduce the result through a second method: manual calculation, code, calculator or independent formula.

Independence matters. Two cells using the same flawed assumption do not constitute verification.

Spreadsheet Peer Review

A reviewer should inspect model purpose, inputs, formulas, assumptions, errors and outputs rather than only formatting.

Ask the reviewer to trace at least one critical output backward to raw data and assumptions.

Spreadsheet Change Logs

Recurring models benefit from a change log for material formula, source, metric or assumption changes.

This makes differences between versions explainable and supports later audits.

Spreadsheet Regression Tests

Preserve known-answer cases for formulas and historical failure cases for cleaning or lookup logic.

After a major change, rerun the test set before relying on the model for live decisions.

Workbook Portability

Functions, macros and charts may behave differently across spreadsheet platforms or versions. Test the workbook in the environment where recipients will use it.

If portability matters, avoid unnecessary platform-specific features or document the dependency explicitly.

Workbook Export

Exports to PDF or CSV remove some workbook behaviour. Check whether formulas, filters, comments and sheet context survive in a form the receiver can understand.

A CSV contains values, not the full model. Do not treat it as a substitute for formula provenance.

Workbook Handoff Package

For important workbooks, hand over the file with README, source list, assumptions, update procedure, permissions and test cases.

The receiver should be able to refresh data, change approved assumptions and detect broken states without the original creator present.

Workbook Retirement

Retire models that are superseded by a new source system, metric definition or process. Archive when history matters and remove obsolete copies from current workflows.

A stale workbook can be more dangerous than no workbook because its familiar format creates false trust.

A Full Build Example: Scenario Budget Model

Sheets: README, Inputs, Actuals, Assumptions, Model, Scenarios, Dashboard and Checks. Inputs contain source data; assumptions hold editable drivers; model contains formulas; checks reconcile totals.

Scenarios change only named assumptions. Dashboard shows cash runway, variance and one sensitivity threshold. The decision owner can see exactly what changes the conclusion.

A Full Build Example: Learning Analytics Workbook

Raw assessment data remains unchanged. Cleaning standardises topic labels. Model computes independent accuracy by topic and error category. Dashboard shows unstable skills rather than one overall grade.

Student identity is minimised where possible. Teachers verify interpretation against actual work before changing instruction.

A Full Build Example: Research Tracker

Source sheet records paper, date, population and method. Claim sheet records findings and limitations. Synthesis sheet compares claims under common definitions.

The workbook supports evidence mapping without treating a generated summary as the canonical source.

The Final Spreadsheet Governance Gate

Before relying on a workbook operationally, confirm canonical data sources, metric definitions, editable assumptions, formula ownership, test cases, permissions and review trigger.

Then hand the workbook to another authorised user and ask them to trace one critical output to raw source and one assumption. If they cannot, the model remains too opaque.

The strongest SI-assisted spreadsheet is not the most automated. It is the one whose logic, evidence, failure states and ownership remain visible after the generation session ends.