Automating Repetitive Excel Tasks with Copilot

If you spend time every week doing the same Excel tasks — sorting data, applying conditional formatting, creating summary rows, cleaning imports — Copilot can handle most of these from a simple text prompt. Here's how to automate the tedious stuff.

Sorting and Filtering

• "Sort this table by Revenue, highest to lowest"
• "Filter to show only rows where Region is North"
• "Show only the top 20 customers by total orders"
• "Remove all rows where the Status column is blank"

Copilot applies these changes directly to your table. You can undo with Ctrl+Z if needed.

Formatting

• "Bold all headers and add filters"
• "Highlight all cells in the Revenue column that are above average"
• "Add alternating row colours to this table"
• "Format the Date column as DD/MM/YYYY"
• "Add data bars to the Profit column"

Adding Formulas

• "Add a column that calculates the percentage of total for each row"
• "Add a RANK column based on Revenue"
• "Calculate the running total of Sales in a new column"
• "Add a column showing the difference from the previous month"

Copilot adds the formula to one row and automatically applies it to all rows in the table.

Data Summarisation

• "Add a total row at the bottom"
• "Create a summary table showing average revenue by region"
• "Calculate the year-over-year growth rate"
• "Show monthly totals in a new table"

Building a Weekly Automation Workflow

Here's a real example — processing a weekly sales report:

1. Import the raw CSV data into an Excel Table
2. Ask Copilot: "Remove rows where Amount is zero or blank"
3. Ask: "Add a Month column extracted from the Date column"
4. Ask: "Sort by Date, newest first"
5. Ask: "Highlight the top 10 deals in green"
6. Ask: "Create a bar chart showing revenue by sales rep"
7. Ask: "Add a summary table with totals by region"

What used to take 30 minutes of manual work now takes 2 minutes of typing prompts.

Limitations

• Copilot can't create VBA macros — use Claude AI for automation that runs on a button click
• Complex multi-step workflows still need a human to sequence the prompts
• File must be saved to OneDrive and data must be in a Table format

Copilot + Power Automate

For true automation (no prompts needed), combine Excel Copilot with Microsoft Power Automate. Create flows that automatically process new files, send notifications, and update dashboards — triggered by email, schedule, or file changes.