Excel Copilot prompt ideas for spreadsheet work

Naming the exact Excel Table and column headers in your Copilot prompt produces reliable XLOOKUP and SUMIFS formulas on the first try.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

Agent Mode in Excel: Build and Edit Spreadsheets with Copilot
Microsoft 365
Open on YouTube (opens in a new tab)
Copilot Excel Tutorial - Excels Biggest Upgrade Ever
Kevin Stratvert
Open on YouTube (opens in a new tab)
AI assistants are genuinely useful in Excel, and they are useful in a narrower band than the marketing suggests. They are good at translating an intention into syntax, at explaining a formula somebody else wrote, and at suggesting an approach you had not considered. They are unreliable at knowing what your data means.
The practical consequence: prompts that supply context get useful answers, and prompts that assume the assistant can see what you can see get confident wrong ones.
Prepare the data before you prompt
This is the step that changes results most, and it has nothing to do with phrasing.
Format the range as a Table (Ctrl+T) and give it a real name —
tblSales, not Table1. Copilot in Excel works best against structured data,
and several of its features will not engage at all on an unformatted range.
Give the columns meaningful headers. InvoiceDate, NetAmount, Region.
An assistant reasoning about a column called Column3 is guessing, and it will
guess plausibly.
Clean the types first. A date column stored as text will produce a formula that works on the two rows you check and fails on the rest — exactly the failure described in cleaning data before a pivot table.
Ten minutes of that beats any amount of prompt engineering.
Prompts for formulas
Be specific about the inputs, the output, and the edge case. Compare:
“Write a lookup formula.”
with:
“In tblInvoices, add a column that returns the Customer name from tblCustomers by matching CustomerID. Return ‘Unknown’ when there is no match.”
The second gets you something close to:
=XLOOKUP([@CustomerID],tblCustomers[CustomerID],tblCustomers[Customer],"Unknown")
Other framings that reliably work well:
- Explain, not write: “Explain what this formula does, step by step:
=SUMPRODUCT(...).” This is the strongest use of these tools — understanding an inherited model is slow work and they are good at it. - Debug: “This formula returns
#VALUE!on some rows but not others. What are the likely causes?” Paste the formula and describe the data. - Simplify: “Rewrite this nested IF as something more readable.” Nested IFs
five levels deep usually want
IFS,SWITCH, or a lookup table. - Translate: “What is the Excel equivalent of this SQL
GROUP BY?”
Ask for alternatives: “Give me two ways to do this and explain the trade-off.” You will often learn that the dynamic-array version you were not considering is simpler than the one you asked for.
Prompts for cleanup
Use an assistant to find problems; decide yourself what to do about them.
- “Which columns in this table have inconsistent formatting or mixed data types?”
- “List the values in the Country column and group the ones that look like the same country written differently.”
- “Which rows look like duplicates of another row, allowing for spelling differences?”
- “What would you check before using this table as a lookup source?”
That last one is a good general prompt, and the answers are often a better checklist than you would have written yourself.
Review every suggestion before applying it. An assistant asked to “clean this list” will happily merge two genuinely different companies with similar names — the near-duplicate problem from how to clean a customer list, where human judgement is the entire job.
Prompts for summaries and analysis
- “Summarise the three biggest changes between this month and last month in this table.”
- “Which five products contributed most to the change in total revenue?”
- “Are there values in this data that look anomalous, and why?”
- “Suggest three charts that would show what is happening in this data, and say what each one would reveal.”
Then check the numbers. The useful mental model is that you are getting a
hypothesis, not a result. “Revenue fell because the North region declined” is a
claim to verify with a SUMIFS, not a finding to put in a board pack.
Always verify three things
Whatever comes back, check:
The ranges. Does the formula cover all the data, or does it stop at row 500 because that is where the sample ended? This is the most common error in generated formulas and the least visible one.
The edge cases. What happens on a blank, a zero, a text value in a numeric column, a date before the range starts? Test with one deliberately awkward row rather than a clean one.
The totals. Compare against something computed a different way. If a
generated SUMIFS summary is right, its total will tie to
=SUM(tblSales[Amount]). If it doesn’t, a criterion is excluding rows you did
not intend to exclude.
The habit worth building is the one from the formula audit checklist: a result you have not tied out is a draft, regardless of who or what produced it.
Keep sensitive data out
Do not paste payroll, customer personal data, medical information, unpublished financials, or anything under NDA into a tool your organisation has not approved for it.
The distinction that matters is where the data goes. Copilot operating inside Microsoft 365 on your tenant’s files is governed by your organisation’s data agreements; a general-purpose chatbot in a browser tab is a third party, and pasting a customer list into it is a disclosure regardless of intent.
A safe working pattern: describe the shape of the data rather than the data. “I have a table with CustomerID, InvoiceDate, and NetAmount” gets you the same formula as pasting a hundred real rows, with none of the exposure.
What to use it for, and what not
Good: writing syntax you know you need, explaining a formula you inherited, suggesting an approach, generating sample data, drafting documentation for a model, converting between Excel and other dialects.
Less good: deciding what a number means, judging whether two records are the same customer, choosing thresholds for a KPI, or anything where being confidently wrong is expensive and hard to detect.
If you are weighing up which assistant to use at all, Claude vs Copilot for Excel and AI extensions for Excel compare what is available, and the September 2026 Copilot function change covers a behaviour change worth knowing about if you use the worksheet function.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Use AI as a drafting and review helper while keeping control of formulas, assumptions, and sensitive data. For this article, the goal is to practice: Prompts that work for formulas, cleanup, and summaries — why naming your Table and columns changes the answers, how to verify what you get back, and what to keep out of the prompt box entirely.
Practice workbook setup
Use a sample workbook with non-sensitive data that resembles your real workflow.
Practice workflow
- Ask for a plan before asking for formulas or changes.
- Prompt for one task at a time and review each result.
- Ask the tool to explain assumptions, edge cases, and possible errors.
- Compare the AI result with a manual formula, filter, or PivotTable check.
Quality checks
- No confidential data is pasted into unapproved tools.
- Every formula reference points to the intended cells.
- The final workbook still makes sense without the AI conversation.
Common mistakes
- Accepting a suggested formula without testing edge cases.
- Using vague prompts that hide the real business rule.
- Letting AI rewrite workbook structure without a backup copy.
Next actions
- Save prompts that worked well in a reusable prompt library.
- Create a human review step before sharing AI-assisted workbooks.
Formula focus: test XLOOKUP(), SUMIFS(), IFERROR() with normal rows, blank inputs, and at least one edge case.






