Copilot in Excel: formula columns, tables, and charts

Most people's first ten minutes with Copilot in Excel go one of two ways: it produces a perfect XLOOKUP column and they're converted, or it says it can't help with the selection and they close the pane forever. The difference is almost always data shape and how the prompt is phrased. This guide covers the patterns that work — for formulas, for charts and PivotTables, and for the newer COPILOT() function — and the ones that quietly don't.

Tables: no longer required, still the whole game

Early Copilot in Excel refused to work on anything that wasn't a formatted Excel table. That restriction has relaxed — Copilot will now work on a plain range with a clear header row — but the honest advice hasn't changed: press Ctrl+T first. A table gives Copilot unambiguous column names, a fixed boundary, and structured references, which is exactly what formula generation needs. On a raw range, Copilot has to guess where the data ends and whether row 1 is a header. It guesses well; it doesn't guess perfectly.

The corollary: what Copilot can't fix is a sheet that isn't tabular. Merged header cells, subtotal rows in the middle of data, two tables side by side, a "notes" column that's really free text — these aren't Copilot limitations, they're data problems, and no prompt rescues them.

Formula column patterns that work

Copilot's strongest single feature is adding a formula column. You describe the calculation; it inserts a new column with a formula (using structured references if you're in a table) and a plain-English explanation. Patterns that succeed consistently:

Lookups across tables. "Add a column that pulls the Region from the Customers table by matching Customer ID." You get an XLOOKUP. If you'd written INDEX/MATCH by hand, Copilot's version is usually cleaner.
Conditional categories. "Add a column called Tier: Gold if Revenue is over 100000, Silver over 25000, otherwise Bronze." IFS, nested correctly, with the thresholds you gave it.
Date arithmetic. "Days between Order Date and Ship Date, blank if not shipped." Handles the blank case, which is where hand-written formulas usually break.
Text cleaning. Extracting a domain from an email, splitting first and last names, normalising case. TEXTBEFORE / TEXTAFTER / TEXTSPLIT appear where appropriate — modern functions many users don't know exist.
Running totals and rankings. SUMIFS with a self-referencing range, or RANK — typically right, occasionally off by an absolute-reference slip. Read it.

Patterns that disappoint:

Anything that needs a second sheet Copilot can't see. It reasons about the selected table. Cross-sheet logic works only if you name the other table and it's a real table.
Whole-model restructuring. "Turn this into a proper financial model" isn't a formula column; it's a project.
Regulatory or tax rules. It'll produce a plausible formula for "UK VAT" or "US overtime"; plausible is not the same as right.

Always click Explain on the generated formula. It's the fastest formula tutor Excel has ever had, and reading the explanation is how you catch the one-in-ten that's subtly wrong.

Highlight, sort, filter

"Highlight rows where Status is Overdue," "bold the top 10 by Margin," "sort by Region then by Date descending" — these are conditional-formatting and sort actions, and Copilot applies them directly. Reliable, and a good way for non-experts to get conditional formatting without touching the rules dialog. Note that Copilot applies formatting; it doesn't create a reusable rule you can easily inspect afterwards, so for anything permanent, check the Conditional Formatting Rules Manager and tidy up.

Charts and PivotTables

Ask for "a chart of Revenue by Month" and Copilot inserts a native Excel chart — a real chart object you can restyle, not an image. Ask for "sales by region and product" and you'll get a PivotTable, usually on a new sheet, with sensible field placement. Both are correct far more often than not, and both are the kind of thing users previously did wrong (a Pivot with Sum where Count was meant, a line chart on categorical data).

Where it gets weaker: asking for a specific chart type that doesn't fit the data, combo charts with secondary axes, and anything requiring a calculated field inside the Pivot. Do the Pivot with Copilot, then add the calculated field yourself.

The Insights prompt ("show me insights" or "analyse this data") generates a handful of charts and observations. Treat it as a starting point for exploration, not a report. Some of the "insights" are trivially obvious; some are genuinely useful; none are validated.

The COPILOT() function

Newer builds include a =COPILOT() worksheet function that takes a prompt and optional ranges and returns AI-generated text or values directly into cells — classify sentiment, summarise a comment, extract a product name from a description, generate a category. It recalculates like any function, which is both the appeal and the hazard: results can change on recalc, and a column of a thousand COPILOT() calls is a thousand model requests. It's rolling out progressively; check whether your channel and platform have it before building on it, and think of it as a rough-classification tool rather than a deterministic formula. Anything downstream that depends on stable values should paste-as-values once.

Practical prompting rules

Name the columns exactly as they appear. "the revenue column" works; "Revenue" works better.
State the edge case. Blank cells, zero divisors, missing lookups. Copilot handles them if you mention them.
One operation per prompt. Formula, then format, then chart. Compound prompts get partially executed.
Read the explanation. Every time.

Copilot in Excel isn't a replacement for knowing Excel. It's a replacement for remembering syntax, which turns out to be most of what stops people. Give it a clean table and clear column names and it's the best productivity feature Excel has added in years.