Use Copilot Like Macros in Excel

This tutorial shows how to use Microsoft 365 Copilot like a reusable macro in Excel. Instead of writing VBA or building Power Query solutions, we describe a list of tasks in plain language, paste that list into Copilot, and let it take action directly in our workbook.

Video

Tutorial

We walk through three real-world exercises covering weekly sales reporting, daily support ticket processing, and monthly bank transaction reconciliation. Each exercise demonstrates how a saved prompt can replace what would have otherwise been a hand-coded macro.

Exercise 1: Weekly Sales Data Analysis

Let’s say we have a worksheet that holds our weekly sales data and a list of tasks we need to complete every single week. The purpose here is to analyze raw weekly sales data, with Copilot acting as a reusable macro that we run each time fresh data arrives.

Consider the following worksheet:

The instruction list reads as follows. We need to format the Revenue and Cost columns as numbers with no decimals and a comma separator. We need to add Margin $ and Margin % columns. We need to highlight any orders under 40% margin. We need to create a summary showing Total Revenue by Region. And we need to create a list showing the Top 3 Products by Revenue in descending dollar order.

Traditionally, we might reach for a VBA macro, a Power Query solution, or a PivotTable to handle all of this. Now, we have a new option. We simply copy those instructions, open the Copilot pane, paste, and send.

We hit Enter, and bam:

Copilot reports that it added the Margin columns, formatted the Revenue and Cost values, highlighted low-margin orders, and created both a regional revenue summary and a top-three-products list. Let’s take another look at the full output by scrolling right:

Copilot used a SUMIFS formula to calculate regional revenue and a LET formula combining SORTBY, UNIQUE, and TAKE to produce the top-three-products list. Pretty impressive for a paste-and-go operation.

Since Copilot used SUMIFS in the regional summary, here is a quick look at what it does. SUMIFS returns the sum of values in a range that meet one or more criteria. The function arguments are:

• sum_range: the range of cells to sum
• criteria_range1: the range to evaluate against the first criterion
• criteria1: the condition that cells in criteria_range1 must meet
• criteria_range2, criteria2, …: additional range and criteria pairs (optional)

Copilot also used the LET function to build the top-products list. LET assigns names to calculation results so they can be reused inside a single formula without repeating the same expression. The function arguments are:

• name1: the first name to assign within the formula
• name_value1: the value or calculation assigned to name1
• name2, name_value2, …: additional name-value pairs (optional)
• calculation: the final expression to evaluate and return, which may reference the defined names

One honest note: Copilot is probabilistic rather than deterministic. Each run may place outputs in slightly different locations or use different formula approaches. A VBA macro gives us identical output every time. Copilot is faster to set up AND more flexible, but it is worth verifying the results after each run.

Exercise 2: Daily Support Ticket Processing

Now that we have seen Copilot handle a sales reporting workflow, it is time to tackle a second recurring task: the daily support ticket export. We have a list that looks like this:

Several rows are missing a Category value. Our prompt asks Copilot to do three things: categorize the uncategorized tickets based on keywords in the Subject column, generate a summary by Category showing closed ticket counts and average resolution time, and list all Open tickets with High priority.

We copy the instructions from the worksheet, open Copilot, paste, and send:

Hit Enter, and bam:

Copilot filled in the missing categories and built a summary table. However, on inspection, the summary values are hard-coded numbers rather than live formulas. That means we cannot easily audit or verify the results. Not a problem. We can send a follow-up prompt asking Copilot to rebuild the summary using live Excel formulas.

We ask Copilot to update the summary by category to use formulas so we can verify them. After it responds, we check the cells:

Copilot responded by inserting COUNTIFS to count closed tickets per category. COUNTIFS returns a count of cells that meet one or more criteria across one or more ranges. The function arguments are:

• criteria_range1: the first range to evaluate
• criteria1: the condition that cells in criteria_range1 must meet
• criteria_range2, criteria2, …: additional range and criteria pairs (optional)

For average resolution time, Copilot used AVERAGEIFS. AVERAGEIFS returns the average of values in a range that meet one or more criteria. The function arguments are:

• average_range: the range of values to average
• criteria_range1: the range to evaluate against the first criterion
• criteria1: the condition cells in criteria_range1 must meet
• criteria_range2, criteria2, …: additional range and criteria pairs (optional)

=COUNTIFS(daily_tickets[Category],J17,daily_tickets[Status],"Closed")

With formulas in place, we can click into any summary cell and inspect the logic directly. Now we can confirm the results and trust the output. That is a simple but important prompt refinement to keep in mind.

Exercise 3: Monthly Bank Transaction Reconciliation

Let’s bring this tutorial home with a third scenario: monthly bank transactions. The purpose here is to take a recurring bank or expense transaction feed and produce a categorized P&L-style summary with budget variance analysis. We have a list that looks like this:

The prompt asks for three things. Format the Amount column as a number with no decimals and a comma. Create a new BudgetCategory column by matching each transaction to the options in the Budget Categories reference table (or mark it TBD if unsure). And create a summary PivotTable showing Total Spend by BudgetCategory.

We copy those instructions, paste them into Copilot, and send:

We hit Enter, and bam:

The Amount column is formatted, the BudgetCategory column is populated, and a spend summary appeared. However, Copilot produced a formula-based table rather than a true PivotTable. No worries, we can send a follow-up prompt asking it to convert the summary to a real PivotTable and place it below the Budget Categories table.

After that follow-up, here is what we get:

Copilot moved the report to the requested location and built a real Excel PivotTable. We can now interact with it, refresh it, and drill into the data just like any other PivotTable we built by hand. Mission accomplished!

Summary

So those are a few different ways to use Copilot like a reusable macro in Excel. For recurring tasks such as weekly sales reporting, daily ticket triage, or monthly expense reconciliation, we can write our instructions once, save them right in the worksheet, and paste them into Copilot each time fresh data arrives. It is faster to set up than VBA, requires no programming knowledge, and handles multi-step tasks in a single prompt. The main trade-off is that Copilot is probabilistic, so we should always review the output and refine our prompts (for example, asking for live formulas instead of hard-coded values) when verification matters.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

Does this require a specific version of Microsoft 365 Copilot?

Yes. The Copilot pane inside Excel requires a Microsoft 365 Copilot license (sometimes called M365 Copilot Premium). It is not included in standard Microsoft 365 subscriptions. Check with your IT administrator or Microsoft account page to confirm your license.

Is using Copilot like this really a replacement for VBA macros?

For many recurring tasks, it can be. VBA is deterministic, meaning the same code always produces the same output. Copilot is probabilistic, so results can vary slightly from run to run. For tasks where exact, auditable output is required, VBA may still be the better choice. For quick, flexible automation where a human reviews the results, Copilot is a compelling and much faster alternative.

How do we save a prompt so we can reuse it every week?

The simplest approach is exactly what was shown in the exercises: type the prompt directly into the worksheet and label it clearly (for example, in a cell called Prompt). Each time we run the process, we select those cells, copy, open Copilot, paste, and send. No special saving mechanism is required.

What if Copilot places the output in the wrong location?

We can follow up with a plain-language instruction telling Copilot where to move or rebuild the output. For example, in Exercise 3 we sent a second prompt asking it to place the PivotTable below the Budget Categories table. Iterating with follow-up prompts is a normal part of the workflow.

How can we make sure Copilot uses live formulas instead of hard-coded values?

We add explicit instructions to the prompt. Phrases like ‘use Excel formulas so I can verify the results’ or ‘use COUNTIFS and AVERAGEIFS for the summary’ guide Copilot toward formula-based outputs. We can also send a follow-up prompt after the initial run, as we did in Exercise 2.

Can Copilot create a real PivotTable, or does it only create formula-based summaries?

Copilot can create genuine Excel PivotTables. However, it sometimes defaults to a formula-based table first, especially if the prompt says ‘pivot table’ in a general sense. Being specific in the prompt, for example ‘create an Excel PivotTable,’ or sending a follow-up prompt asking it to convert the summary to a PivotTable, helps get the desired result.

Does the data need to be formatted as an Excel Table before using Copilot?

Copilot works best when the data is formatted as an Excel Table (Insert, Table). Named tables give Copilot clear context about the data range and column names, which leads to more accurate results. Raw ranges can work too, but structured Tables are strongly recommended.

Can we use Copilot to categorize data based on a reference list, like the Budget Categories table in Exercise 3?

Yes, and this is one of the most powerful applications shown in the tutorial. We simply tell Copilot which table to use as the reference and instruct it to mark unmatched rows as TBD. Copilot reads both tables and maps the values based on the transaction descriptions.

What happens if Copilot makes a mistake in the output?

We can undo any Copilot action with Ctrl+Z, just like a manual edit. We can also send a corrective prompt describing what needs to change. Building the habit of asking for live formulas, as discussed in Exercise 2, makes it much easier to spot and fix errors because we can inspect the formula logic directly in the cell.