AI for Spreadsheet Analysis: Ask Better Questions About Your Data

Protect the original and define the decision

Never begin analysis in the only copy of a workbook. Preserve the original export as read-only and create a dated working copy. Record where the data came from, who owns it, when it was exported, which period it covers, and the decision it may inform. A decision such as “reorder this product” needs different evidence from an exploratory question such as “which items have unusual monthly movement.” Separate exploration from approval.

• Use a clear filename or cover sheet containing source, period, refresh time, owner, and version.
• Record active filters, hidden rows and columns, protected ranges, external links, macros, queries, and manual adjustments.
• Keep an unmodified control copy so generated edits, formulas, sorts, and fills can be compared or reversed.
• Name the person authorised to interpret the result and the person authorised to act on it.

Create a data dictionary and metric contract

Column names are not definitions. For each field, document its meaning, type, unit, currency, source, valid values, missing-data treatment, and example. For every metric, write the numerator, denominator, inclusions, exclusions, date basis, aggregation, and rounding rule. If “revenue,” “active customer,” “return,” or “late delivery” means different things to different teams, resolve that before asking AI to calculate it.

• Date basis: order date, invoice date, payment date, shipment date, or service completion date.
• Money: gross or net, tax inclusive or exclusive, booked or collected, currency and conversion date.
• Counts: rows, distinct orders, customers, units, tickets, or days.
• Missing values: unknown, not applicable, zero, not yet entered, or excluded by policy.
• Metric contract example: “Return rate = distinct returned order IDs divided by distinct delivered order IDs for the same delivery month.”

Minimise sensitive data before AI use

Decide whether AI is needed before sharing the workbook. Use an organisation-approved account and product configuration, and supply only the columns and rows required for the task. Replacing a name with an ID may not make a dataset anonymous when other fields still identify the person. Keep restricted information out and follow applicable policy, contracts, and Indian data-protection requirements with qualified advice.

• Remove names, phone numbers, email and postal addresses, account identifiers, identity documents, exact locations, free-text notes, and payment credentials when not required.
• Avoid complete payroll, health, performance, complaint, fraud, and customer-history files in general-purpose AI workflows.
• Use aggregated or synthetic test data for prompt development whenever it can answer the workflow question.
• Review sharing links, connected drives, add-ons, extensions, version history, exports, and offboarding—not only the AI chat setting.

Clean the table and establish control totals

AI analysis works from the structure it receives. Use one header row with unique, non-blank labels; one record per row; one type per column; and a stable key where the source provides one. Remove decorative merged cells from the data range. Convert real dates to date values, make units explicit, and preserve raw fields before deriving cleaned fields. Then calculate control totals that must remain stable through the analysis.

• Profile row count, distinct keys, duplicates, blank rates, minimum and maximum dates, and unexpected category values.
• Check for numbers stored as text, invisible spaces, inconsistent spellings, mixed date formats, negative values, and decimal-versus-percentage confusion.
• Record source totals such as total invoice value, units, distinct orders, opening and closing balance, or category subtotals.
• Reconcile the cleaned table to the source; document every removed, merged, corrected, or imputed record.

Ask bounded questions and require visible methods

Select the exact table, tab, or range and ask one question with a defined metric and period. State whether the task is descriptive, comparative, diagnostic, or a forecast. Require the response to identify the rows or range, formula or method, filters, assumptions, and uncertainty. Ask for a formula or PivotTable plan before allowing workbook edits. A good response is inspectable, not merely persuasive.

• Descriptive: “Using OrdersTable, calculate distinct delivered orders and net collected revenue by invoice month.”
• Comparative: “Compare April–June with January–March using the same customer population and list the exact formula.”
• Diagnostic: “List rows contributing to the variance; do not infer a cause not represented in the sheet.”
• Forecast: “Separate historical calculation, assumptions, method, error range, and scenarios; do not present the forecast as a commitment.”
• Categorisation: provide a label rubric and require an “uncertain” category rather than forcing every row into a class.

Validate formulas, tables, and analysis steps

For important numerical work, prefer native spreadsheet formulas, PivotTables, or queries that another person can inspect and recalculate. Read every generated formula. Check absolute and relative references, included boundary rows, blanks, errors, zero denominators, negative values, and mixed units. Manually calculate a small sample and compare an independent method. Reconcile the final total to the control totals before accepting an explanation.

• Use native SUM, COUNTIFS, SUMIFS, XLOOKUP, PivotTables, or equivalent functions where they make the calculation reproducible.
• Check whether a formula changes correctly when filled down and whether newly added rows enter the source range.
• Test empty data, one record, duplicate keys, month boundaries, zero values, refunds, cancellations, and extreme values.
• Inspect the AI product’s analysis steps or cited source cells when available, but still verify them independently.
• Paste final generated labels as values only after review when nondeterministic recalculation would be unacceptable.

Audit every chart before using it

A polished chart can visualise the wrong population. Check the source range or helper tab, aggregation, filters, category order, date grain, missing values, axes, units, labels, and whether the chart updates when the original data changes. Google currently notes that some Gemini-generated charts are placed with underlying data on a new tab and do not respond to changes in the original dataset; verify refresh behaviour in the product and workflow you use.

• Match chart totals to the validated table or PivotTable totals.
• Label currency, percentage, count, and time units explicitly.
• Avoid truncated axes or mixed scales that exaggerate differences.
• Show missing periods and distinguish zero from unavailable data.
• Record the filters and refresh date on exported charts or reports.

Keep decisions and the audit trail human-owned

AI may help explore patterns, but the accountable owner must interpret business context, check alternative explanations, and decide what evidence is sufficient. Do not use an AI-generated workbook result as professional financial, tax, legal, employment, credit, medical, safety, or compliance advice. Store the approved calculation, source version, data dictionary, formulas, assumptions, reviewer, corrections, and decision separately from the transient AI conversation.

• Describe what the data can and cannot show; correlation or timing does not prove a cause.
• Check whether excluded, missing, delayed, or biased data could change the conclusion.
• Require an authorised source system before changing a price, payment, payroll, stock order, customer status, or employee action.
• Re-run controls after each refresh and compare revisions before replacing a previously approved report.

1. 1Save the untouched source and create a dated analysis copy; record the file owner, source system, export time, period, and intended decision.
2. 2Classify the data, remove fields the analysis does not need, and confirm that the selected account, plan, workspace, and sharing method are approved.
3. 3Convert the working range into a clean table with one header row, one record per row, consistent types, stable IDs, explicit units, and no merged data cells.
4. 4Write a data dictionary and metric contract describing formulas, inclusions, exclusions, date basis, currency, missing values, and expected control totals.
5. 5Run pre-analysis checks for row count, duplicates, blanks, invalid dates, unexpected categories, outliers, filter state, and agreement with the source system.
6. 6Ask one bounded analysis question at a time and require the output to name the range, steps, formula or method, filters, assumptions, and uncertainty.
7. 7Validate each important result independently, inspect formulas and chart ranges, test edge cases, and reconcile subtotals and totals before interpretation.
8. 8Have the responsible business owner review the evidence and limitations before a decision, then store the approved result separately from generated commentary.

• Uploading customer lists, payroll, bank exports, identity details, employee records, or confidential operations data without an approved purpose and environment.
• Treating blanks as zero, text dates as dates, formatted percentages as raw decimals, or mixed currencies as directly comparable values.
• Asking for “insights” without defining the population, metric, date range, unit, filters, exclusions, and intended decision.
• Accepting a formula because it returns a plausible number without inspecting references, boundary rows, error handling, and refresh behaviour.
• Trusting a chart without checking its source range, aggregation, filters, missing values, sorting, axes, labels, and link to changing source data.
• Allowing generated categories or sentiment labels to become objective facts without a rubric, sample review, and error measurement.
• Making a financial, tax, legal, employment, safety, credit, pricing, or inventory commitment from an unverified AI calculation or forecast.
• Overwriting the only source file or failing to record which version, prompt, formula, and correction produced the final number.

Recommended tools for this workflow

Frequently asked questions