Categorize and Summarize Expenses in Excel Using Copilot, by Excel University on YouTube. 4 min 25 s.
- Video length
- 4 min 25 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Put every expense in one sheet, with one row per transaction. Include the date, description, amount and a blank category column.
On a second sheet, build a reference table. List each category name and the merchant names or keywords Copilot should use to match transactions.
Select the expense range and ask Copilot to assign a category to each transaction from your reference table. Review the suggestions before you accept them.
Ask Copilot to convert the expense range into an Excel Table. This lets the table expand automatically when you add new rows next month.
Create a summary area with one row per category. Use =SUMIF(category range, category, amount range) to total each category.
Compare the SUMIF totals with the full expense amount. Fix any blank, misspelled or unmatched categories before you continue.
Insert a PivotTable from the expense table. Put category in Rows and amount in Values, then set the value field to Sum.
Ask Copilot to write a step-by-step refresh guide on a separate Admin sheet. Save the workbook so your team can repeat the process each period.
Tips and common mistakes
Keep category names identical in the reference table and the summary sheet, or SUMIF will miss matches.
Remove blank rows and merged cells from the expense list before you create the Excel Table.
Treat Copilot categories as a draft and spot-check large or unusual transactions.
Save the finished workbook as a template so each month starts with the same structure.
Questions
Can I do this without Copilot?
Yes. You can type categories yourself, use SUMIF for totals and insert a PivotTable. Copilot only speeds up the setup.
Will the summary update when I add new expenses?
Yes, if the expenses are in an Excel Table and the formulas point to the table columns. Add rows inside the table, then refresh the PivotTable.
How do I fix a wrong category?
Change the category in the transaction row and refresh the PivotTable. If many rows are wrong, update the reference table and ask Copilot to recategorize.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
