How to Use Copilot in Excel: A Practical Guide for Your Team

The short answer

To use Copilot in Excel, sign in with your work account, put your data in a clean table with clear headers, and select the Copilot icon in the lower-right corner of Excel. Then ask a plain question, like "which customers ordered less this quarter?" Copilot suggests formulas, charts and summaries. Check every answer before you use it.

The problem: the licence is paid for, the button is not clicked

Here is a pattern I see a lot.
The company buys Copilot licences.
A few people try it once.
They ask it something vague, get a vague answer, and go back to copy and paste.
Head of Copy and Paste was never the promotion anyone wanted.

Look at what they asked it to read.
Messy data. A vague question.
Fix those two things and Copilot in Excel gets useful fast.

If your team has licences nobody uses, read why your team is not using the AI tools you already pay for.

What you need first

Check these before your team starts. Microsoft updates requirements, so confirm them on Microsoft's support pages.

• The right Copilot access. Sign in with your work account and check the Copilot label under your name on the Microsoft 365 home page. M365 Copilot (Basic) gives standard Copilot in Excel without the add-on licence. M365 Copilot (Premium) means you have the add-on and the full experience. Copilot Chat (Basic) does not include Copilot inside Excel.
• Data in a table or a clean range. One header row. One type of data per column. No merged cells. No blank rows in the middle.

That last one matters most.
Copilot reads your headers to understand the data. "Cust", "Amt2" and "Col F" give it nothing to work with. "Customer", "Invoice date" and "Amount" do.

Step 1: Clean the sheet before you ask anything

Take a real file your team uses. An ERP sales export is perfect.

1. Delete title rows above the headers.
2. Rename headers in plain words.
3. Remove subtotal rows. Copilot will count them twice.
4. Select the data and press Ctrl + T to format it as a table.

Ten minutes here saves an hour of bad answers later.

Step 2: Ask plain, specific questions

Select the Copilot icon in the lower-right corner of Excel. A pane opens on the side.
Copilot opens in edit mode, which can change your workbook. Switch to chat mode if you only want answers.
Write the question the way you would ask a sharp analyst.

Weak: "Analyze this."
Better: "Show total sales by customer for 2025 and 2026, and highlight customers whose 2026 sales dropped more than 20 percent."

Good prompts for a manufacturer or distributor:

• "Which 10 customers bought the most last year?"
• "Add a column that shows the number of days since each customer's last order."
• "Make a chart of monthly sales by product line."
• "Which products have not sold in the last 6 months?"
• "Summarize the main changes between Q2 and Q3 in three bullet points."

Step 3: Let Copilot write formulas, then read them

Copilot can suggest a new column with a formula and explain what it does.
Before you insert it, read the explanation.
Then check a few rows by hand. Pick a customer you know and see if the number matches.

Copilot is fast. It is not always right.
I check its work every time. Your team should too.
Three rows by hand catches most mistakes.

Step 4: Use it for the reports you rebuild every week

The best use is the report someone rebuilds by hand every Monday. You know the one.
Ask Copilot to do the same steps: filter, group, sum, chart.
Write the prompts that work into a short note, so anyone on the team can rerun them.

When the weekly report needs data from several systems, Copilot in Excel has limits. That is where a proper setup on top of your ERP helps. See how manufacturers use AI without replacing the ERP.

Step 5: Set data rules before you roll it out

Decide what files can go into Copilot and who can see what.
Copilot works with files a person already has access to, so check sharing permissions first.
Write it down. Our post on employee rules for using AI at work is a good starting point.

Example: a weekly sales review in five prompts

Here is a sequence a sales manager can run every Monday on a fresh ERP export.
Save it in a shared note so anyone can repeat it.

1. "Format this data as a table and tell me the date range it covers."
2. "Show total sales by customer for the last 13 weeks compared with the 13 weeks before."
3. "Add a column for the percent change and sort it with the biggest drop first."
4. "List customers with no orders in the last 60 days who ordered at least three times in the year before."
5. "Make a bar chart of the 10 biggest drops and write a three-line summary for the sales team."

Each step builds on the last, and each is easy to check.
If step 2 looks wrong, stop there and fix the data before going further.
Most of the time the problem is a date column stored as text, or customer names typed two different ways.

Once the five prompts give the right answer two weeks in a row, the Monday report takes a few minutes of reviewing instead of an hour of copying.
That is the time your team gets back.

Common mistakes

Messy data. Merged cells and subtotal rows are the top reason for wrong answers.

Vague prompts. Name the columns, the time period and the output you want.

Trusting without checking. Spot check every new formula.

One training session and done. People need a few weeks of real use on their own files. Short follow-up sessions work better than one long day.

Copilot or ChatGPT for spreadsheet work?

If your team lives in Excel, Outlook and Teams, Copilot sits where the work already is.
If you need one AI workspace across many tools, ChatGPT may fit better.
Our Copilot vs ChatGPT comparison covers how to choose.

How we train teams on Copilot

We train teams on their own files, not sample data.
One workflow at a time: the weekly sales report, the inventory check, the quote follow-up list.
Then we come back to see what stuck. See Copilot training for your team and ways to work with us.

I spent 16 years in corporate sales, 10 at Pfizer.
Training only counts if people still use it a month later. Fair test. Hold us to it.

FAQ

Free 15-minute call

Talk to Steve

Paying for Copilot and not seeing the hours come back?
We will start with the one spreadsheet your team rebuilds every week.

Talk to Steve