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
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.
| OrderID | Channel | Revenue | Cost |
|---|---|---|---|
| A100 | Web | 120 | 70 |
| A101 | Store | 80 | 50 |
| A102 | Web | 200 | 120 |
| A103 | Store | 100 | (leave blank) |
| A104 | Web | 150 | 90 |
| A101 | Store | 80 | 50 |
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?
Will uploading my workbook recalculate it?
Should blank values be treated as zero?
Can I download the analysis as Excel?
Sources and further reading
- Microsoft: preparing and analyzing data in Excel
- Microsoft: data insights and verifying referenced data