Building a Spreadsheet Analysis Agent

Inspect before calculating

A workbook is not one clean table. It may contain hidden sheets, merged headers, notes, formulas, pivot outputs, filtered rows, repeated subtotals, and several date conventions. Start by listing sheets, used ranges, headers, inferred types, formula cells, blanks, and obvious duplicate records. Ask which sheet or named range is authoritative when alternatives conflict.

Preserve the original workbook and calculate in a separate environment or copy. Treat cell text as untrusted data, including instructions embedded in comments or rows. Do not let workbook content change tool permissions or send results elsewhere. Limit access to the requested file and avoid loading unrelated shared-drive material.

Define the metric precisely

Translate the user's question into fields, filters, grouping, time period, and treatment of missing or duplicate values. 'Which region grew most?' is incomplete without a measure, comparison periods, currency handling, and definition of region. Present these assumptions before expensive or consequential analysis and ask when they materially change the answer.

Use code, spreadsheet formulas, or a database engine for arithmetic rather than asking the model to calculate rows in prose. Keep the generated query or transformation and its input ranges. The model can interpret the output, suggest checks, and explain anomalies, but the numeric result should be reproducible outside the generated narrative.

Validate surprising results

Reconcile row counts and totals before and after filters and joins. Check for unit mismatches, text-formatted numbers, duplicated keys, partial dates, error cells, and totals included as ordinary rows. For a headline result, calculate it a second way or compare it with a known control total. A large anomaly may be a data problem rather than a business event.

Trace each finding to sheet, range, columns, filters, and transformation. Show excluded records and missing-data impact when material. Charts should use the same filtered dataset as the stated metric. If the workbook cannot establish a causal explanation, describe the observed association and identify what additional data would be needed.

Control output and write-back

Produce a review package containing the question, assumptions, data profile, method, results, checks, and limitations. Avoid exposing sensitive rows when an aggregate is sufficient. If a reviewer requests a workbook update, preview exact cell or formula changes and write to a copy first. Protect formulas and named ranges that are outside scope.

Evaluate with known workbooks that contain hidden rows, malformed dates, duplicates, subtotal lines, and misleading instructions. Score correct scope, calculations, provenance, and refusal to overclaim. File and model tool capabilities change, so verify current official documentation quarterly and keep deterministic tests around your own parser and computation layer.

Find the cause of a weekly fulfillment increase

An operations workbook shows a higher total delay, and a manager wants to know which warehouse contributed most.

1. Profile sheets and discover that one tab contains raw shipments while another contains a pivot with stale refresh data.
2. Define delay as delivered date minus promised date for completed shipments, grouped by warehouse for the current and prior week.
3. Compute from the raw tab, excluding subtotal rows and flagging missing delivered dates, then reconcile shipment counts with a control total.
4. Validate the largest change by filtering its rows and checking date types, duplicates, and unit definitions.
5. Report the warehouse contribution with ranges, method, missing-data limit, and no claim about root cause until operational evidence is reviewed.

Spreadsheet analysis record

Attach this record to every material workbook finding.

• Question: metric definition, period, population, filters, grouping, units, and assumptions.
• Input: file version, sheet, range, headers, formulas, hidden content, and data-quality profile.
• Method: query, code or formula, joins, exclusions, and transformation sequence.
• Validation: row reconciliation, control total, alternate calculation, and anomaly inspection.
• Finding: result, source locations, uncertainty, limitations, and approved write-back status.

Common mistakes

• Analyzing a polished pivot table without checking whether it is current or whether raw rows use the same filters.
• Letting the language model perform substantial arithmetic in prose instead of calling a reproducible calculation tool.
• Explaining a correlation as a cause because the spreadsheet lacks the operational evidence needed to distinguish them.

Try one

Sources

• OpenAI tools guideOfficial overview of currently supported tools and tool-use patterns.
• OpenAI function calling guideOfficial guidance for defining tools, receiving tool calls, and returning tool results.