How to Use AI for Excel

Start with the workbook's purpose and grain

Before asking for a formula, write down what one row represents, the reporting period, the currency or unit, and the decision the workbook supports. A column called Amount might be an invoice total, a line-item value, or a running balance. AI cannot infer that safely from a header. Ask it to repeat the grain and list ambiguities before it proposes a calculation.

Work on a copy and preserve the raw sheet. Give the assistant a small, anonymised sample plus the table name and exact headers. If the source is sensitive, use synthetic values with the same shape. The goal is to establish a data contract before the assistant touches formulas, not to make it guess from a polished screenshot.

Use the right AI surface for the job

Copilot in Excel is useful when the live workbook, table and Microsoft 365 permissions are the source of truth. ChatGPT, Claude and Gemini are useful for explaining a formula, drafting a cleaning plan or working from an uploaded or pasted sample. The product surface matters: an assistant that cannot see a hidden sheet, named range, external connection or workbook calculation setting cannot safely claim to have audited it.

Ask what the tool actually inspected. If it cannot access a file, request a formula or checklist rather than a conclusion. Keep the official Excel documentation and your organisation's data policy as the authority for features, permissions and retention. A tool's ability to read a workbook is not permission to upload every field in it.

Build formulas from examples and invariants

Describe the result in plain language, then give three to five rows with expected answers. Add invariants such as 'a cancelled order must contribute zero' or 'the total of the output must equal the approved control total'. Ask for a formula, the cell or table column where it belongs, a short explanation and failure cases. If the assistant offers several formulas, compare them against the examples rather than choosing the shortest one.

Test on a spare sheet. Check blanks, duplicates, text stored as numbers, dates stored in different formats, errors and rows added after the formula was written. Structured references may be safer than fixed ranges, but only if the data is actually an Excel Table. Ask the assistant to state that assumption explicitly.

Clean data without destroying the source

Cleaning is a transformation, not a cosmetic step. Ask for a rule table with the original example, transformed example, reason, affected rows and whether the operation is reversible. Keep raw values intact and write cleaned output to a new sheet or file. Common issues include leading spaces, inconsistent labels, mixed date formats, duplicate keys, numbers stored as text and blanks that mean different things.

Do not let an AI silently replace 'unknown' with zero or merge labels that look similar but have different business meanings. Compare row counts, distinct keys, null counts and control totals before and after cleaning. If the numbers change, require an explanation for every material change. A clean-looking dataset that no longer preserves meaning is worse than a visibly messy one.

Ask for analysis that another person can reproduce

For a PivotTable, chart or summary, name the table, row grain, measure, filters, comparison period and expected total. Ask for the exact fields and aggregation, not just a sentence saying that sales increased. Keep a calculation ledger with source range, filter, formula, units, rounding and reviewer. For a chart, inspect the axis, date grain, zero baseline, missing periods and whether the chosen visual implies causation.

Separate observed values, calculations, assumptions and recommendations. An AI-generated trend is not a forecast, and a correlation is not an explanation. If you need a scenario, label it as a scenario and list the variables that changed. Recompute important figures in Excel or another controlled tool before they enter a report or decision.

Use a staged prompt workflow

A dependable session has five passes: inventory, quality audit, calculation, explanation and adversarial review. In the inventory pass, ask for sheets, columns, formulas, date coverage and unreadable areas. In the audit pass, ask for duplicates, blanks, suspicious totals and inconsistent types. In the calculation pass, require the working and a control total. In the explanation pass, separate findings from interpretations. In the adversarial pass, ask what could be wrong and what evidence would change the conclusion.

Save the approved prompt and output with the workbook version. If multiple workbooks are joined, require a join-key report, row counts and duplicate-key check after every join. Never allow a fuzzy name match to become a confirmed relationship without a human review list.

Protect confidential workbook content

Payroll, customer, health, pricing and contract sheets often contain more information than the task needs. Minimise columns, pseudonymise identifiers, remove credentials and exclude free-text notes unless essential. Use an approved account and confirm the organisation's retention, training and sharing rules. Enterprise controls reduce risk but do not make every prompt appropriate.

Check hidden sheets, comments, hyperlinks and named ranges before uploading. Do not share a workbook link with broader permissions than the task requires. Keep the original in its controlled location and store the AI output where the authorised reviewer can find it. If you cannot explain who can access the prompt, stop and use a redacted sample.

Finish with a workbook and report review

Review the file and the narrative separately. In the workbook, inspect formulas, filters, hidden rows, named ranges, chart sources, dates, units, totals and conditional formatting. In the report, trace every important number to the calculation ledger and every external claim to a current primary source. Open the export in another viewer and copy the text or formulas where appropriate.

Ask the AI for an omission and contradiction checklist, then perform the approval yourself. For finance, employment, health, legal or safety decisions, add qualified review. Record the workbook version, source files, transformations, unresolved questions and reviewer. The final question is not 'does this look polished?' but 'can another person reproduce and challenge this result?'

A repeatable example: from raw rows to a decision

Imagine a table with order date, region, product, units, revenue, cost and return status. The decision is whether to investigate a fall in contribution margin. First ask the AI to restate the row grain and identify whether revenue and cost are line-item or order-level values. Then request a quality table covering missing dates, duplicate order keys, text numbers, negative values, cancelled orders and inconsistent region labels. Do not calculate the margin until those checks are resolved or explicitly bounded.

Next ask for a new-sheet plan: a clean table that preserves raw rows, a formula for contribution margin, a PivotTable by region and month, and one control total for revenue and cost. Give the expected total if you have one. Test the formula on five known rows, then compare the PivotTable totals with the source. If a region has a large change, ask for the row-level bridge and source records behind it. Choose the chart only after checking the time grain and whether missing months create a misleading line.

Finish with a brief under four headings: verified findings, plausible explanations, evidence still needed and recommended next checks. It should not say that returns caused the fall unless the calculation supports that conclusion. Keep the raw data, transformation rules, formula cells, PivotTable, chart specification and approved narrative together so a colleague can reproduce and challenge the result.

Decide when a formula, PivotTable or script is appropriate

Use a formula when the rule is row-level, stable and easy for a colleague to inspect. Use a PivotTable when the question is a grouped summary with filters and a known measure. Use Power Query or a script when the transformation is repeated, larger than a comfortable manual range, or needs a logged sequence of steps. Ask AI to compare these options rather than assuming the most elaborate answer is best.

For every option, ask what happens when a new row arrives, a field is blank, a label changes, a date crosses a time zone, a duplicate appears or the workbook is opened by someone with different regional settings. Formula separators, decimal symbols, date parsing and locale can change behaviour. Ask for a small test fixture with expected results and keep it next to the workbook. Do not install an add-in, run generated code or connect a live data source without checking permissions and the organisation's change process.

A robust spreadsheet is understandable by its next owner. Prefer named tables, clear input and output sheets, documented assumptions, visible control totals and a short README over a clever formula nobody can maintain. AI can draft that documentation, but the person who owns the workbook must decide whether the method is appropriate and approve the final file.

Treat charts and forecasts as communication choices

A chart is not a neutral decoration. Before asking AI to create one, name the comparison, audience and action it should support. A line chart can show a time trend only when periods are comparable; a bar chart can compare categories but can exaggerate small differences if the axis is truncated; a stacked chart can hide the movement of a small category. Ask the assistant to state what a reader could wrongly conclude from the visual and how to prevent that.

For forecasts, show historical data and scenario inputs separately. Include the date of the last observed value, the assumptions that drive the line, the range of plausible outcomes and the point at which the scenario should be updated. Never let an AI-generated line be labelled actual, expected or approved without the owner of the model signing off. Add a source note and units to the chart itself. Inspect labels, legends, missing periods, filters and rounding in the rendered workbook, not only in the prompt response.

When presenting a result, keep the chart beside a small evidence table and one sentence describing the limitation. This gives an executive reader the quick view while preserving a route back to the cells. If the question is consequential, provide the workbook version and reviewer with the deck or email. Good visualisation shortens the path to a decision; it does not remove the need to understand the data.

Make the final handoff maintainable

Before sharing a workbook, ask AI to produce a short handoff note: purpose, data owner, refresh date, input sheets, output sheets, formulas or queries that matter, control totals, known limitations and the next reviewer. Check the note against the actual file. Remove hidden test tabs and stale comments, rename the file with a clear version, and protect formulas or sheets according to the team's normal process.

A future user should know which cells they may edit, what must remain unchanged, where the source data came from and how to tell whether a refresh failed. If the workbook is part of a recurring report, write down the refresh sequence and what to do when a column disappears or a total no longer ties. Store the approved file and source documentation together, and do not overwrite the historical version when the numbers affect a decision. This is the difference between a one-off AI answer and a spreadsheet workflow that remains reviewable after the original chat is gone.

Use an exception queue instead of silent repair

When AI finds a blank, duplicate, impossible date, negative quantity or label that does not match the data dictionary, put it in an exception queue. Include row or key, original value, suspected issue, proposed treatment, evidence needed and owner. Do not silently repair the row because the model can imagine a likely answer. A visible exception is a useful handoff; a hidden change is an audit problem.

Review exceptions in business order. A duplicate transaction key may need the source system owner, while a spelling variation may be safe to map after the data owner confirms it. Record whether a rule was applied, rejected or deferred, and rerun the control totals after each approved batch. Ask AI to summarise the queue and prioritise by materiality, but let the responsible person decide what is correct. This approach turns uncertainty into work that can be assigned and closed instead of allowing it to disappear inside a cleaned export.