Copilot Expense Categorization in Excel

Copilot in Excel can save a tremendous amount of time on routine finance tasks. In this tutorial, we walk through three exercises that show how Copilot can assign expense categories from a reference table, generate a formula-based summary, build a PivotTable report, and even write step-by-step documentation for future updates. If your team processes expense reports on a recurring basis, this workflow is worth knowing. Let’s dig in.

Exercise 1: Assign Categories with Copilot

Let’s say we have a list of transactions with dates, descriptions, and amounts, but the Category column is completely empty. Lower on the same sheet, we have a small Categories table that lists the valid options: Office, Meals, and Travel.

We open the Copilot pane and type a plain-language prompt. No formulas needed at this stage. We just describe what we want Copilot to do.

We hit send, and bam:

Copilot fills in all 12 rows instantly. The values it inserts are static text, not formulas. That means we should review them before accepting.

This is AI, and my motto is: never blindly trust an AI. A quick scan confirms the assignments look correct, and we click Done.

Exercise 2: Build a Formula-Based Summary

Now that we have categories assigned, it is time to summarize the data. Exercise 2 starts with the same categorized transactions. The goal is to create a formula-based summary below the data that totals spending by category.

We type our prompt into the Copilot pane and send it.

Copilot reasons through the request and places a formatted summary table in the Summary section, bam:

One row per category, each with a total. Let’s inspect the formula Copilot wrote.

Before diving in, a quick look at the SUMIF function. It returns the sum of values in a range where a corresponding range meets a single condition. The function arguments are:

• range: the range of cells to evaluate against the criteria
• criteria: the condition that determines which cells to include
• sum_range: the range of cells to sum when the criteria is met (optional — defaults to range if omitted)

=SUMIF($E$9:$E$20,B25,$D$9:$D$20)

The formula looks in the Category column for a match to the label in B25, then sums the corresponding amounts. Clean, correct, AND maintainable. If we want a quick grand total, we can select the total cell and press Alt+= to insert a SUM automatically.

Exercise 3: PivotTable Summary and Documentation

Now, it is time to make things more interesting. Exercise 3 asks Copilot to handle three tasks in a single prompt: convert the data range to an Excel Table, create a PivotTable summary by category below the data, and generate a new sheet called Admin that documents the monthly update steps.

We open the Copilot pane and type all three instructions at once. Copilot can handle multi-step prompts like this.

Copilot reasons and reports back as complete. We click Done and review the results, bam:

The data range is now a proper Excel Table named tbl_transactions_ex3. That matters for recurring projects because tables expand automatically when we add new rows. The PivotTable below it shows Category on rows and Total Amount on values, with a Grand Total of 1,294. That matches our formula-based report from Exercise 2, which is a great sign.

Let’s take a look at the PivotTable Field List to confirm the setup.

Category is in Rows, Total Amount is in Values. That is exactly right. Now let’s check the Admin sheet Copilot created.

Copilot wrote a complete six-step checklist covering how to add new transactions, enter them correctly, refresh the PivotTable, and verify the totals. That is genuinely useful documentation for any team member who inherits this workbook next month.

As a final verification step, we can cruise back to Exercise 3, click inside the table, go to Table Design, and tick the Total Row checkbox. Then we select Sum for the Amount column. The table total shows 1,294, matching the PivotTable Grand Total exactly.

Summary

In this tutorial, we used Copilot in Excel to categorize expense transactions based on a reference table, build a formula-based SUMIF summary, create a PivotTable report, and auto-generate step-by-step update documentation. Three exercises, three different Copilot prompt styles, and all of it completed in a matter of minutes. That is how we can use Copilot to speed up expense categorization and reporting. Mission accomplished!

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

Sample File

FAQs

Does Copilot in Excel require a special subscription?

Yes. Copilot in Excel requires a Microsoft 365 subscription that includes Copilot, such as Microsoft 365 Copilot (formerly M365 Business or Enterprise with the Copilot add-on). It is not included in standard Microsoft 365 Personal or Family plans as of this writing.

Are the categories Copilot assigns formulas or static values?

They are static text values. Copilot reads the data and writes the category labels directly into the cells. There is no live formula linking back to the Categories table. That is why reviewing the results before clicking Done is important.

What if Copilot assigns a wrong category?

Since the values are static, we can simply click into any cell and type the correct category manually. Alternatively, we can click Undo in the Copilot pane before accepting the changes, refine the prompt for more context, and try again.

Can we use Copilot if the data is not in an Excel Table?

Yes. In Exercises 1 and 2 the data was a plain range, not a Table, and Copilot worked just fine. Converting to a Table is beneficial for recurring workflows because it auto-expands, but it is not a prerequisite for using Copilot.

Why does the SUMIF formula use absolute references?

Copilot locked the criteria range and sum range with dollar signs ($E$9:$E$20 and $D$9:$D$20) so the formula can be copied down the summary table without the ranges shifting. Only the criteria cell (B25) is relative, allowing each row to pick up the correct category label.

Can we send Copilot multiple instructions in one prompt?

Absolutely. Exercise 3 demonstrates this. We asked Copilot to convert the range to a Table, create a PivotTable, and build an Admin documentation sheet all in one message. Copilot reasoned through 8 steps and completed all three tasks. More specific prompts tend to produce better results.

Does the PivotTable Copilot creates stay connected to the source table?

Yes. Copilot built the PivotTable from the Excel Table it created, so the two are connected. When new transactions are added to the table, we simply click Refresh on the PivotTable Analyze tab and the totals update. That is exactly what the Admin sheet documents.

How does the Total Row in the Table help verify the results?

Enabling the Total Row gives us a quick SUM at the bottom of the Amount column. We can compare that number to the Grand Total in the PivotTable. If they match, we have high confidence the data and the report are in sync. In our example, both show 1,294.

Can Copilot categorize expenses with more than three categories?

Yes. Copilot reads the categories from whatever reference table we provide. As long as the categories table is on the same sheet and our prompt references it, Copilot will use all of the available category values regardless of how many there are.

Should we always review what Copilot produces?

Always. AI-generated content can be incorrect. Copilot itself displays a disclaimer that reads ‘AI-generated content may be incorrect.’ A quick review of the categories, the formulas, and the summary totals takes only a minute and gives us much greater confidence in the output before sharing it with anyone.