Your ultimate Copilot in Excel cheatsheet

We all know you can do a lot with Excel, add Microsoft 365 Copilot and suddenly you're becoming an expert in Excel without knowing every formula. I created a Copilot in Excel cheatsheet for you.

I wrote this cheatsheet in Dutch too, see it here!

First: Copilot is not magic, it's working for you

Copilot isn’t a wizard knowing what you want, it’s a smart assistant. So let's understand clearly how you can achieve the most out if it and how it works best for you:

• Know your goal (for example summarize, visualize or built)

• Provide context (What’s in the sheet? What output do you want?)

• Ask critical questions, as you are talking to a colleague of yours

  
  

The key prompting tips

Copilot isn’t reading your mind and it definitely doesn’t “just get it” from a few words. Think of it like explaining your job to a far-away uncle at a family reunion. If you say, “I work in finance,” he just nods without knowing what you do. But if you explain what you actually do, he understands. (And lucky for you, Copilot won’t follow up with five unrelated questions about your life.)

Here's a example on how to prompt and what to avoid:

Stop doing this:	But be more precise.
“Analyze this”	“Summarize sales trends by region for Q3 and highlight anomalies.”
“Filter this”	“Filter this dataset to show only customers with revenue over 10.000.”
“Make a chart”	“Create a chart comparing monthly spend vs. forecast.”
“Check for duplicates”	“Find duplicate values in column B and highlight them.”
“Clean the data”	“Exclude rows with empty values in column C before calculating averages.”
“Summarize everything”	“Ignore test data in Sheet2 and summarize only Sheet1.”

A few more tips to think about:

• Talk to Copilot as if it is a human being, be descriptive but precise.

• Avoid negative prompting. So don't mention "do not use" but instead say "use only data in".

• If Copilot keeps failing your data, ask to start over instead of following up on your first prompt

• If the spreadsheet is chaotic and hard for you to follow, Copilot will likely struggle too. Keep your data organized, and let Copilot handle the heavy calculation over the sheet.

How to include sources?

Copilot is most powerful when it understands the context and that can be broader than just the rows your spreadsheet.

Here’s how to connect that work content to your Excel Copilot session:

Add a Link to a SharePoint or OneDrive File (easiest & fastest)

You can paste a direct link to a file stored in SharePoint or OneDrive into your Copilot prompt.

Copilot will only access the file if you have permission to view it.

Use “Add work content” from the Copilot Panel

In the Copilot sidebar, click “Add work content” to browse through recently used files and shared documents

Upload local files or media

You can also upload content from your own PC directly into Copilot like images, PDFs or other documents.

Prompting tips

If you know the information exists elsewhere but isn’t in your sheet, tell Copilot where to look.

“Based on the Q4 Sales Report stored in SharePoint, identify top product categories by margin and compare to the data in this sheet.”

Or guide Copilot like this:

“Search my recent reports for customer insights and apply findings to this retention dataset.”

Also, don't forget to mention what Copilot should to with your added documents, like:

 “Based on the product categories in this sheet and the pricing guide in this document, recommend a new pricing structure.”

The new =COPILOT feature (Frontier Program)

Microsoft is bringing out one of the most exciting Excel features yet: the =COPILOT() function, now available in preview for if your administrator has enabled the Frontier Program.

Unlike the Copilot side panel or chat window, this function lets you directly generate content based on natural language in a cell

The Formula

Here’s how the function is structured:

=COPILOT(prompt_part1, [context1], [prompt_part2], [context2], ...)

• Prompt_part: This is the instruction you give Copilot, like what you want Copilot to do for you.

• Context): A cell or range that gives Copilot data to work with. You can include one or more data references.

Example

Let me show you some examples you can use in your cell, when asking with =COPILOT.

=COPILOT("List netnumbers from cities in", B2:B65)

Gives back all netnumbers of the cities found in the selected cells.

=COPILOT("Classify the feedback", D4:D18)

Interpetate the feedback given in the cells and analyze them, while after that gives back the found feedback.

Tip: You can include multiple prompts and contexts for more tailored responses, e.g.:=COPILOT("Summarize trends", A1:A100, "focus on revenue")

Wrapping up

Got a favorite prompt? A use case that surprised you? Or want more tips for a specific Excel scenario? Drop it in the comments, I’d love to hear from you, and I’m always happy to dive deeper in a future post.