If you use Excel often, you already know how quickly “small” tasks can take over your day. A file arrives, and before you can even analyse it, you have to check for errors, clean inconsistent values, write formulas, build summaries, format important rows, and explain the results to someone else.

That is where Copilot in Excel can save serious time. But instead of discussing Copilot in theory, this blog shows it in action. We will use one downloadable workbook, Copilot_Excel_tblSales_Dataset.xlsx, and walk through five real-world scenarios you can follow on your own machine.

By the end, you will not only see what Copilot can do; you will also know when to let Copilot make changes, when to keep it in explanation mode, and how to verify the results before trusting them in a real report.

Demo Dataset Used in This Guide

For this walkthrough, we will use the file Copilot_Excel_tblSales_Dataset.xlsx. The dataset contains fields such as Order ID, Order Date, Region, Salesperson, Product Category, Customer, Revenue, Cost, Quantity, Invoice Status, Due Date, and Priority. These fields give Copilot enough context to demonstrate data cleaning, formulas, insights, filtering, and summary reporting.

How to use the dataset file: Download Copilot_Excel_tblSales_Dataset.xlsx, open it in Excel, and confirm that the data is formatted as an Excel Table named tblSales. If it is not already a table, select the data, press Ctrl + T, confirm that your table has headers, then rename the table to tblSales from the Table Design tab.

The dataset is deliberately not perfect. It includes issues you are likely to see in real business files: a duplicate Order ID, blank Customer values, inconsistent Region spelling, mixed invoice statuses, and one unusually high Revenue value. That is intentional because it gives us something useful to test with Copilot.

Figure 1: The downloaded Copilot_Excel_tblSales_Dataset.xlsx workbook opened in Excel, with the sales data formatted as an Excel Table named tblSales.

Note that Copilot can only work on users who have the Microsoft Copilot license. Secondly, the workbook must have been saved to OneDrive or SharePoint for Copilot to activate successfully.

How to Choose Between Chat Mode and Edit Mode

Before we start prompting Copilot, let’s clear up another important idea. Sometimes you want Copilot to explain what it sees. Other times, you want it to change the workbook. Knowing the difference helps you avoid unwanted changes and makes the walkthrough easier to follow.

Use Chat mode when you want Copilot to review, explain, summarize, or suggest next steps without immediately changing the worksheet. This is the safer option when you are still investigating the data.

Use Edit mode, or allow Copilot to update the workbook, when you want it to take action. Examples include adding a formula column, applying a filter, sorting rows, highlighting values, or creating a summary output.

Here is the simple rule I recommend: if you are still trying to understand the data, start with Chat mode. If you already know the change you want, allow Copilot to edit the workbook.

Scenario	Recommended Copilot approach	Why
Detect data quality issues	Chat mode	Ask Copilot to identify and explain possible issues before changing the dataset.
Generate a formula column	Edit mode	Copilot needs to add a new calculated column directly to tblSales.
Summarize insights	Chat mode	Start by reviewing trends and explanations before creating any output.
Highlight, sort, and filter records	Edit mode	Copilot needs to apply formatting, filtering, or sorting to the worksheet.
Create a first-draft report summary	Chat mode first, then Edit mode if needed	Start with a written explanation, then allow changes only if you want Copilot to create a worksheet output.

Scenario 1: Ask Copilot to Detect Data Quality Issues

Always start with data quality. If the source data is wrong, every formula, summary, chart, and management update built from it can also be wrong. In our demo workbook, the issues are intentional: blank Customer values, a duplicate Order ID, inconsistent Region spelling, mixed invoice statuses, and an unusually high Revenue value.

Recommended Copilot approach: Start in Chat mode. At this point, you want Copilot to point out possible issues, not automatically fix the dataset.

Prompt to use: Review tblSales and identify possible data quality issues, including missing values, duplicates, inconsistent spelling, unusual dates, and outliers.

Figure 2: Copilot reviewing tblSales and listing possible data quality issues, such as blanks, duplicates, inconsistent spelling, and outliers.

What to verify: Treat Copilot’s response as a starting point, not a final verdict. Check the exact rows it mentions, compare suspicious values with your source, and decide whether each issue should be corrected, removed, or kept.

Before moving to Scenario 2: clean up the important issues. You can fix them manually or ask Copilot for suggestions, but do not continue to formulas and summaries until you have reviewed the problems Copilot found.

For this demo, correct lagos to Lagos, review the blank Customer values, decide whether to remove or keep the duplicate Order ID, and confirm whether the high Revenue value is a real transaction or an error. Once the dataset is clean enough for analysis, move to Scenario 2.

Scenario 2: Generate a Formula Column

Now that the key data quality issues have been reviewed, we can start adding useful calculations. A common example is Profit. You could write the formula yourself, but Copilot can help you create the column faster and explain the logic at the same time.

Recommended Copilot approach: Use Edit mode or allow Copilot to update the workbook because you want it to add a new formula column directly to tblSales.

Prompt to use: In tblSales, add a new column called Profit that subtracts Cost from Revenue, then explain the formula used.

Figure 3: Copilot adding a Profit column to tblSales and explaining the formula used to subtract Cost from Revenue.

Once Profit is available, you can take the demo one step further by asking Copilot to classify performance into bands.

Second prompt to use: In tblSales, create a new column called Performance Band. If Profit is greater than 50000, return High; if Profit is between 20000 and 50000, return Medium; otherwise return Low.

Figure 4: Copilot creating the Performance Band column in tblSales using the Profit values and the defined High, Medium, and Low thresholds.

What to verify: Check the formula before accepting the result. Pick two or three rows and calculate the Profit manually. Also confirm that the Performance Band thresholds match your business rule, not just Copilot’s interpretation of it.

Scenario 3: Summarize the Dataset into Insights

At this point, tblSales is cleaner and more useful because it now includes a Profit column. Instead of building several PivotTables immediately, ask Copilot to help you explore the data first. This is a quick way to discover which questions are worth investigating deeper.

Recommended Copilot approach: Start in Chat mode. You want Copilot to explain patterns, trends, and possible outliers before creating any new worksheet output.

Prompts to use:

• Show total Revenue by Product Category in tblSales.
• Identify the top five customers by Revenue in tblSales.
• Summarize the main trends in tblSales.
• Find any unusual Revenue or Profit values in tblSales that may need review.

Figure 5: After running the prompt, your result should look similar to this.

What to verify: Compare Copilot’s totals with a quick PivotTable, SUMIFS formula, or manual check. If Copilot calls something an outlier, inspect the original row before deciding that it is wrong.

Why this saves time: Copilot helps you get an overview quickly. You still need to validate the result, but you no longer have to start every exploration from a blank worksheet.

Scenario 4: Highlight, Sort, and Filter Important Records

Sometimes you do not need a full analysis yet. You simply need to make the important records stand out: critical tasks, overdue invoices, top revenue values, or rows that need attention before a meeting.

This is where Copilot’s ability to highlight, sort, and filter data becomes practical. Instead of clicking through several menus, describe what you want and let Copilot apply the change to tblSales.

Recommended Copilot approach: Use Edit mode or allow Copilot to update the workbook, because this scenario requires Copilot to apply formatting, sorting, or filtering directly to tblSales.

Prompts to use:

• Highlight rows in tblSales where Priority is Critical.
• Filter tblSales to show only invoices that are overdue.
• Sort tblSales by Revenue from highest to lowest.
• Highlight the top 10 Revenue values in tblSales.

Figure 6: Copilot applying a filter, sort, or highlight to tblSales, such as showing overdue invoices or highlighting Critical-priority rows.

What to verify: Check the rule Copilot used. For example, “overdue” might mean Invoice Status is Overdue, or it might mean Due Date is earlier than today and the invoice is not paid. Your business rule should decide the final logic.

Scenario 5: Create a First-Draft Report Summary

The last step is communication. After you have explored the data, someone will usually ask, “So what does this mean?” Copilot can help turn the numbers into a first-draft summary that you can review and polish.

Recommended Copilot approach: Start in Chat mode to get a written summary first. If the summary is useful and you want it added to the workbook as a table, note, chart suggestion, or PivotTable-style output, then allow Copilot to make changes.

Prompts to use:

• Create a summary of tblSales by Region and Product Category.
• Suggest the best chart to show monthly Revenue trends from tblSales.
• Create a PivotTable-style summary showing total Revenue by Salesperson in tblSales.
• Write a short management summary explaining the key findings from tblSales.

Figure 7: Copilot creating a first-draft management summary, chart suggestion, or PivotTable-style summary from tblSales.

What to verify: Confirm the numbers before sharing the summary. Also check the wording carefully. Copilot may describe a trend clearly, but you should avoid claiming a cause unless the data actually supports it.

One thing you will notice across all five examples is that Copilot works better when your instruction is clear. Mention the table name, describe the output you want, and ask Copilot to explain anything that affects your final output.

Most importantly, do not skip validation. Copilot can help you move faster, but you should still check formulas, totals, filters, and summaries before sharing the workbook or using the output in a report.

Final Thoughts

The real value of Copilot in Excel is not that it replaces Excel skills. It helps you move faster when you already understand the task. In this walkthrough, Copilot helped us inspect messy data, create useful formula columns, summarize trends, highlight important records, and draft a simple report narrative.

That is the mindset I recommend: use Copilot as a capable assistant, but stay in control of the data, the business rules, and the final decision. When you combine Copilot with good Excel habits, you can save hours without sacrificing accuracy.

Ready to build smarter Excel workflows?

At UrBizEdge, we help professionals and teams use Excel, Power BI, Power Query, SQL, and Microsoft 365 Copilot to work faster, analyze better, and build practical business solutions.

If you want to move beyond manual spreadsheet work and start using AI-assisted workflows confidently, reach out today via info@urbizedge.com.