Using AI for Excel Analysis: Formulas, Pivots and Charts

By the end you can turn "analyze this spreadsheet" into requests that produce a working formula, a defensible aggregation rule, and a chart that answers one question.

1. AI does not know your business rules

Hand an order table to any model and it returns a total at once. What it cannot know: whether cancelled orders count, whether refunds net at list or cash, whether test orders are excluded, and which month a cross-month shipment lands in.

Those are business decisions: state the rule in one sentence, then ask AI to apply it. AI decides how to write it; you decide what to compute. Invert that and everything downstream is unusable.

2. The three things it does well

• Language to formula: conditional sums, lookups, text splitting, distinct counts.
• Rule to pivot structure: which field in rows, month-over-month or year-over-year, what the denominator of a share is.
• Chart choice and takeaways: picking a chart type and compressing numbers into one repeatable judgment.

3. Formulas: describe the sheet, not the function

Failed formula requests are rarely model failures. The model cannot see your sheet. A usable prompt gives the range, each column's meaning, the traps, and the exact output.

Sheet is A1:H500, headers in row 1. A order id, B order date (text, 2026/10/05), C customer, D product, E quantity, F unit price, G status, H refund. One formula for I2: total order value (E times F) for October 2026 where status is completed and refund blank or zero. No helper columns, explain it piece by piece.

• Multi-condition lookup: name the two key columns, the return column, and what to show when nothing matches.
• Text splitting: give two real sample values. "A2 is 2026-10-East-001" beats "split on the dash".
• Distinct count: say "unique customers, not order ids", or you get a row count.

Test on a few rows and hand-check three. Syntax is usually right; ranges are often slightly off, and a missing absolute reference or a header pulled into the data shifts every number just enough to hide.

4. Pivots and period comparisons

Do not ask for "a pivot table". Give the row field, the column field, and the aggregation: Rows by region, columns by month, values summed. Add month-over-month, blank base as a dash.

Be precise about denominators. Year-over-year compares to the same month last year; month-over-month to the previous month. A model will not correct a mix-up. New regions have no base, so add "show missing base as blank, not zero".

5. Charts: decide the question first

• Trend: line, or columns under six time points.
• Composition: stacked, never a pie above five slices.
• Ranking: horizontal bars with long labels.
• Relationship: scatter, and skip the correlation claim below twenty points.

Then require the takeaway in a fixed shape: one conclusion, one number, one action. "East order value fell 12 percent month over month, driven by category A, so price-test A next month" belongs in a report. "East was highest" does not.

6. Three things to verify

1. Arithmetic. Paste rows, ask for a sum, and you have a draft: the model generates text, it does not calculate. It holds for dozens of rows and drops rows later. Recompute in the sheet.
2. References. Check absolute locks, whether headers entered the range, whether hidden rows were swept in.
3. Units. Yuan or ten-thousand, tax in or out, pieces or cases. Two units in one column produce a sum that is wrong and looks right.

Fix formats before analysis; messy dates and currency symbols are covered in cleaning data with AI.

7. Keep large tables out of the chat box

Past a few hundred rows, stop pasting: the model cannot read it all, long context pushes it to summarize, and uploaded data does not come back.

Have AI write the formula or script and run it locally. Pivots inside Excel; pandas above a few hundred thousand rows, with only the aggregate pasted back. If you must upload, anonymize first: codes for names, masked phones, scaled amounts.

For the full chain, see handling data in WorkBuddy and filter by data analysis in the tool directory. As of November 2026, limits still shift, so check official documentation.