Practical guides

How to analyze Excel with AI

Give the AI a clean table and a precise question. Make it state its rules for missing values and duplicates, then reconcile the answer with calculations you can inspect.

On this page
  1. Start with a specific analysis question
  2. 1. Prepare the workbook and preserve the original
  3. 2. Specify the data and analysis rules
  4. 3. Work through a small, checkable example
  5. 4. Verify totals and formulas in Excel
  6. 5. Download a useful result and check it again
  7. Common questions
  8. Sources

Start with a specific analysis question

In Chat AI App, choose + → Files and attach your Excel workbook. Wait for processing and check any coverage message. State the worksheet, columns and business rule to use, then request a small summary table with its calculations. Compare the results with Excel before relying on them. To save a generated table as a workbook, use Download → Excel (.xlsx) on the response.

“What are our sales?” leaves too much unstated. “Total September revenue by channel, counting each order once and listing orders with missing costs” defines a testable task. Decide what one row represents before doing arithmetic: an order, an order line, a customer or a daily total can require different rules.

1. Prepare the workbook and preserve the original

Save a working copy with clear worksheet names. Put column names in one header row, keep dates and amounts consistent, and separate explanatory notes from the data table. Microsoft’s Analyze Data guidance recommends tabular data and warns about structures such as merged cells. Those preparation principles are useful for an uploaded workbook too; this guide does not describe Microsoft’s built-in feature.

Recalculate and save the workbook in Excel before uploading if it depends on formulas or external sources. Chat AI App’s attachment reader extracts displayed cell values; uploading a file does not run its macros, refresh its connections or open a live Excel session. If the task is to explain a formula, paste the actual formula and relevant cell values as well.

Current spreadsheet upload limits are 50 MB per file for full Pro and 30 MB for free or trial access. Extracted content has separate limits. Large workbooks may have shortened material or missing sheets, so follow the actual attachment information. For a focused task, a relevant worksheet or smaller export is often easier to verify than an entire workbook.

2. Specify the data and analysis rules

Name the sheet and the columns. Define the period, currency and exclusions. Say whether a blank means zero, unknown or not applicable. Specify how duplicate identifiers should be handled; repeated IDs can be legitimate line items, so do not automatically delete them without understanding the table.

Request an inventory before a complex analysis: recognized sheet names, column headings, row counts and any limitations. Compare this with the workbook yourself. A plausible narrative based on the wrong worksheet is still wrong. Microsoft’s data-insights documentation similarly emphasizes checking the data behind an answer.

3. Work through a small, checkable example

This original sample contains six data rows. It is fictional teaching data, not a customer workbook or a claimed AI execution. Create a sheet called Sales with these headers in row 1 and records in rows 2–7. Amounts are in USD; each unique order is meant to occur once.

OrderIDChannelRevenueCost
A100Web12070
A101Store8050
A102Web200120
A103Store100(leave blank)
A104Web15090
A101Store8050

Analyze the Sales worksheet. Each unique OrderID is one order. The repeated A101 row is an accidental exact duplicate: retain one copy. Revenue and Cost are USD. A blank cost means unknown, not zero. Report input rows, unique orders, revenue by channel and total revenue. Calculate gross profit and gross margin only for orders with known costs; state the revenue covered and list excluded orders. Show the arithmetic, separate findings from assumptions, and do not invent missing costs.

The expected results below follow directly from the sample. They give you a reference against which to judge an answer:

  • Rows: six input records, five unique orders, one duplicate removed.
  • Revenue: Web = 120 + 200 + 150 = 470; Store = 80 + 100 = 180; total = 650.
  • Known-cost coverage: four orders with revenue of 550 and costs of 330.
  • Gross profit for those four orders: 550 − 330 = 220. Gross margin = 220 ÷ 550 = 40%.
  • Unresolved item: A103 has revenue of 100 and unknown cost. Full-dataset profit cannot be established from the sample.

Two common errors are easy to spot here. Counting the duplicate gives revenue of 730. Treating the missing cost as zero produces an unjustified full-dataset profit of 320. A useful analysis exposes the missing information instead of making the total look complete.

4. Verify totals and formulas in Excel

For a calculation you need to reuse, follow our Excel formula guide: specify the ranges and rules, then check a small known result before filling down.

For this small example, copy the five unique orders to a separate sheet called Clean, keeping the same columns and rows 2–6. =SUM(C2:C6) should give revenue of 650. This check is deliberately simple enough to inspect without trusting another AI answer.

In E2, enter =IF(D2="","",C2-D2) and fill down to E6. =SUM(E2:E6) gives known-cost profit of 220. Known-cost revenue is =SUMIF(D2:D6,"<>",C2:C6), or 550. Divide the profit by that covered revenue, not by all revenue, for the 40% margin. These formulas use English function names and comma separators; your Excel locale may differ.

For a larger workbook, reconcile three layers: source row count, included or excluded records, and the grouped totals. Check several individual rows, including a blank, a refund or negative amount, and a date at the boundary of the period. A matching grand total can conceal errors that cancel each other out.

5. Download a useful result and check it again

Ask for a compact summary table plus separate data-quality notes, then select Excel (.xlsx) from the response’s Download menu. Ask explicitly for formulas when you need calculations that update, and inspect the downloaded formula cells in Excel. A number displayed in chat is not proof that the exported cell contains a working formula.

Distinguish a new analysis workbook from a revision of the uploaded original. If you need edits to the original, state the exact sheet, range and changes, and compare the resulting file against your saved copy. Do not assume an exported summary retains your original macros, connections, charts or complete workbook layout.

For a decision brief, carry forward both the result and its limitations: “Revenue is 650; profit is only established for four of five orders.” Our PowerPoint guide shows how to turn checked findings into a presentation without losing those qualifications.

Common questions

Can AI analyze more than one worksheet?
Name the worksheets and explain how they relate. Verify the recognized sheets and coverage first; a large workbook may be partly represented. Check join keys and duplicates before combining data.
Will uploading my workbook recalculate it?
No. Save a recalculated copy in Excel first. The attachment reader extracts displayed values; uploading is not a live Excel session or a promise to run macros and refresh external data.
Should blank values be treated as zero?
Only when your data definition says so. An unknown cost and a zero cost have different implications. State the rule and report how missing values affect coverage.
Can I download the analysis as Excel?
Use Download → Excel (.xlsx) on the completed response. Verify the saved cells and any requested formulas, and keep the original workbook when the output is a separate summary.

Sources and further reading