AI Spreadsheet Analysis: Formulas, Data and Sanity Checks

#01What question can the workbook actually answer?

Write the business question in measurable terms. "Which channel is best?" is underspecified. "What was attributed media spend per accepted request in this defined period, using the agreed request-quality rule?" names a metric, scope and limitation.

State the grain of each table: one row per campaign, day, session, request or account. Two files with matching column names may still represent different units. Joining them without understanding the grain can duplicate rows and inflate totals while producing a plausible chart.

Define each metric before calculation. A request is not necessarily a qualified opportunity, and platform conversions may use different attribution rules from CRM records. AI cannot resolve a business definition by choosing the most convenient column name.

Check whether the data supports the requested conclusion. A descriptive channel table can compare recorded outcomes. It cannot by itself establish incremental revenue or explain why a metric changed. Keep those limitations in the final narrative.

#02How should you prepare the data?

Use clear headers, consistent types and a documented date range. Separate currency, dates and categorical labels. Remove repeated header rows, summary rows and accidental blank sections that could be counted as data. Preserve the original export before cleaning.

Create a data dictionary with definitions, units, source system, expected values and known exclusions. Mark whether missing means unknown, not applicable or zero. Replacing all blanks with zero can create a false analytical result.

Reconcile basic totals before AI analysis. Count rows, check unique identifiers where appropriate and compare source totals with the cleaned table. If a join is required, record row counts before and after and inspect unmatched records rather than hiding them.

Minimise personal information. Aggregated marketing analysis often does not need names, emails or raw customer notes. Use the approved commercial account and organisational policies, and do not assume that a familiar spreadsheet interface makes every connected processing step appropriate.

#03What current Excel modes matter?

Microsoft's checked Copilot in Excel FAQ describes different modes, including editing, planning and chat experiences, with availability depending on licence, application, platform and tenant conditions. Specify the intended mode and confirm actual access before designing the workflow.

Direct workbook editing can change formulas, tables and other content, and those changes can be visible to coauthors. Use a controlled copy or appropriate review process, inspect the edits and retain a recovery route through the available undo or version mechanisms.

The FAQ states that editing requires Automatic calculation. Check workbook settings rather than assuming the environment is ready. A formula inserted into a workbook with unexpected settings can produce confusion even if the formula itself is reasonable.

Microsoft's separate direct-answer article describes Python-based analysis over structured tables or table-like ranges. That experience does not modify the workbook by default. Inserted tables or image visualisations can be static and not refreshable. Do not mistake a one-off answer for a maintained reporting model.

#04How do you specify an analytical request?

Name the tables, fields, date range, filters and required outputs. Ask the assistant to describe its interpretation before performing a consequential analysis. A useful first response identifies grain, missing values, possible duplicates and assumptions rather than rushing to an insight.

Require the method and intermediate results. For a rate, ask for numerator and denominator. For a join, ask for matched and unmatched counts. For a comparison, ask for the exact period and exclusions. A polished paragraph without the working is difficult to review.

Use explicit uncertainty rules. If a required field is missing or definitions conflict, the assistant should stop or return an exception. It should not infer a currency, guess a missing spend value or treat an unavailable attribution field as zero.

#05Which independent checks catch common failures?

Recompute at least the consequential totals and a sample of row-level calculations outside the generated answer. In a simple sheet, ordinary formulas such as =SUM(B2:B4) can provide a transparent control total. For more complex work, have a competent analyst review the code or logic.

Check weighted rates. Averaging channel percentages can differ from dividing total outcomes by total eligible sessions. Neither is automatically the right question; choose the definition deliberately and label it. The overall conversion rate normally needs the combined numerator and denominator for the included population.

Inspect zero and missing denominators. A zero denominator should not silently become a valid zero rate. A missing cost should not create an apparent free channel. Separate unavailable values from actual zeros and explain how they affect comparisons.

Review joins for multiplication. If a campaign appears several times in each source, a many-to-many join can inflate both spend and outcomes. Compare totals and row counts before trusting any downstream calculation or visual.

#06How do you turn exploration into a maintained report?

Keep the approved calculation method, not just the narrative answer. If a direct-answer chart is static, label its data date and do not place it in a recurring report as though it automatically refreshes. Confirm the behaviour of any advanced analysis or workbook-based implementation separately.

Document the source exports, cleaning steps, metric definitions and exceptions. Another analyst should be able to reproduce the result without replaying an entire chat. Store the final approved table with the report revision and retain the original according to policy.

Use a regression fixture for repeated analysis. A small synthetic table with known totals can reveal whether a changed prompt, model or workflow now handles blanks, rates or joins differently. The fixture tests mechanics without exposing customer data.

For vendor-specific alternatives, see the existing ChatGPT analysis of marketing exports and Claude spreadsheet lesson. This lesson's distinct focus is workbook control and independent analytical verification.

#07What should a formula audit record?

For each consequential result, record the business definition, input columns, filter conditions, formula or code and independent check. A formula that is syntactically valid can still answer the wrong question if it includes rejected requests or uses the wrong date field.

Use a small control table with deliberately obvious answers. Include a zero denominator, a missing value and a duplicate key, then specify the expected treatment before running the AI-assisted method. This is not a substitute for checking production data; it tests whether the method handles known mechanics correctly.

When an answer changes after a prompt or model revision, compare intermediate tables before discussing the narrative. Find whether the difference arose from source selection, cleaning, joining, filtering or arithmetic. That investigation is more useful than repeatedly asking the assistant whether it is sure.

Keep the approved formula in a maintained location with an owner. A successful one-off chat should not become an undocumented dependency for every future reporting cycle.

#08Worked example: Ledgerline

Ledgerline compares attributed media spend and accepted requests in a synthetic campaign table. All values and calculated results below are illustrative. They describe the example only and do not establish channel effectiveness or incremental impact.

The approved definitions produce total spend of EUR 2,600, 22 accepted requests and 1,300 sessions. Spend per accepted request is approximately EUR 118.18. The combined request rate is approximately 1.69%, calculated as 22 divided by 1,300, not as an unweighted average of channel rates.

Search and Partner each have an illustrative rate of 2%, while Social has 1%. That observation does not prove that moving budget will reproduce those rates or that one channel caused more incremental demand. The small table lacks the design needed for that conclusion.

Walkthrough

1. The analyst confirms that each row represents one channel in the same period and that spend uses one currency.
2. The request-quality rule is documented before any comparison, and summary rows are excluded from the input.
3. Copilot is asked to state assumptions, return component totals and explain the rate calculation in the intended non-editing workflow.
4. The analyst independently sums the columns and recomputes spend per request and the weighted rate.
5. Any returned chart is checked for whether it is static or refreshable, with the data date recorded.
6. The report retains the approved table, method and limitations rather than only the generated recommendation.

#09Common mistakes

• Asking for insights before defining metrics. Ambiguous columns create ambiguous answers.
• Treating missing as zero. Unknown values can distort both cost and rate comparisons.
• Averaging rates without checking weights. Match the calculation to the intended population.
• Ignoring join grain. Duplicated rows can inflate apparently correct arithmetic.
• Assuming a static answer is a live report. Confirm refresh behaviour and data date.
• Accepting a causal recommendation from descriptive data. Keep inference within the evidence.

#10Try it yourself

Build a small synthetic marketing table with known totals, one blank value and one duplicated identifier. Write the grain, metric definitions and expected treatment of each edge case before using an AI tool.

Ask for a first-pass data inspection and a specific calculation with intermediate totals. Independently recompute the answer, inspect any edits and confirm whether the output is static or maintained. Record the discrepancy if the tool makes an incorrect assumption.

Deliver the source fixture, approved calculation, exception notes and a reproducible method. The goal is not to demonstrate that AI always gets arithmetic wrong or right; it is to make the workflow capable of detecting consequential errors.

#11Quick quiz

1. Is a direct-answer chart necessarily a refreshable workbook report?

Answer: No. The checked direct-answer experience can return static tables or image visualisations.

2. What should be checked before joining two exports?

Answer: Their grain, keys, expected relationship and row counts, including unmatched records.

3. How is the illustrative combined request rate calculated?

Answer: Total accepted requests divided by total included sessions: 22 divided by 1,300, approximately 1.69%.

#12What should you check before moving on?

Confirm the definitions, calculations, mode, recovery route and refresh behaviour are understood. Continue with analytics narratives to keep the written explanation as trustworthy as the reconciled table.

Previous: AI Presentations: Evidence, Story and Reviewable Slides · Next: AI Analytics Narratives: Explain Reports Without Overclaiming

Or return to the AI Tools for Marketers track.

Related: Evaluate AI Marketing Tools With a Task-Based Scorecard · AI Analytics Narratives: Explain Reports Without Overclaiming · AI Marketing ROI: Measure Accepted Work and Run the Review

#13Common questions

Can Copilot direct answers become static outputs? Yes. The checked Excel experience can return static tables or image visualisations that are not refreshable.

What should be checked before joining exports? Table grain, keys, expected relationships, row counts and unmatched records.

Should missing values always become zero? No. Define whether they mean unknown, not applicable or an actual zero.