Copilot prompts for Excel — 20 prompts Australian finance teams actually use

Copilot in Excel is the most conditional of the Microsoft 365 Copilot experiences: set up correctly it removes real hours from month-end, and set up wrongly it greys out and convinces the finance team the licence was wasted. The 20 prompts below are the ones that keep earning their place in Australian finance teams, grouped by job, each with what it actually returns. The setup rules come first because they are where most failed attempts die.

Before any prompt works

• Copilot in Excel needs the paid Microsoft 365 Copilot licence. The free Copilot Chat can analyse an uploaded spreadsheet in the chat window, but the in-app experience is paid only.
• Format the data as a proper Excel table (select the range, then Ctrl+T). Copilot works on tables and well-structured ranges, and most 'Copilot can't help with that' responses trace back to loose, merged or multi-header layouts.
• Keep the file in OneDrive or SharePoint with AutoSave on. Copilot performs best on cloud-saved workbooks.
• One dataset per table. Split the 14-tab monster into clean tables before asking questions of it.

Analyse: prompts 1–5

1. 1"Show me insights in this data." The broad opener. Returns suggested pivot tables and charts with a one-line finding attached to each: trends, concentrations, outliers. Useful as a first pass on an unfamiliar export.
2. 2"What are the top 10 customers by total invoice value, and what share of revenue do they represent?" Returns a ranked summary and the concentration figure, the fastest customer-concentration check available before a credit conversation.
3. 3"Show the trend of monthly sales for the last 12 months and describe what changed." Returns a line chart plus a written description of direction, turning points and the strongest month.
4. 4"Identify outliers in the Amount column and explain why each might be an outlier." Returns flagged rows with reasoning, a quick screen for duplicate payments, misplaced decimals and one-off items before they hit a report.
5. 5"Which expense categories grew the most compared with the prior period?" Returns a ranked change summary, the starting point for a variance commentary.

Formulas: prompts 6–10

1. 1"Add a formula column that calculates GST at 10% of the Amount column, and another for the GST-inclusive total." Returns the columns with formulas written and a preview before they are inserted.
2. 2"Add a column that flags any invoice more than 30 days past its due date as OVERDUE." Returns an IF-based status column, the backbone of a receivables chase list.
3. 3"Write a formula to pull the Region for each customer from the CustomerMaster table." Returns an XLOOKUP against the other table, replacing the VLOOKUP wrestling that used to eat the afternoon.
4. 4"Explain what the formula in cell F2 does, step by step." Returns a plain-English breakdown of an inherited formula. The single best prompt for a workbook whose author has left the business.
5. 5"Add a column that groups invoice dates into Australian financial year quarters starting 1 July." Returns a quarter label column with FY-aligned logic, saving the perennial July-quarter fencepost error.

Clean and format: prompts 11–14

1. 1"Highlight duplicate ABNs in this table." Returns conditional formatting applied to the duplicates, the two-second version of a supplier-master hygiene check.
2. 2"Apply conditional formatting so negative variances show red and positive show green." Returns the formatting rules applied, ready for a review pack.
3. 3"Sort by Region, then by Amount descending, and filter to show only FY2025-26." Returns the applied sort and filter. Faster spoken than clicked once the table is large.
4. 4"Split the Full Name column into First Name and Last Name." Returns the split columns. Works on consistent data; flag exceptions and check the edges.

Variance and month-end: prompts 15–17

1. 1"Add a column showing variance between Budget and Actual, in dollars and as a percentage, and flag anything beyond 10%." Returns the two variance columns plus the exception flags: the skeleton of the month-end pack in one prompt.
2. 2"Group these receivables into ageing buckets of current, 30, 60 and 90+ days and total each bucket." Returns an aged-receivables summary from a raw invoice list.
3. 3"Compare this month's expense table with last month's and list the five biggest movements with likely explanations." Returns a movement list with suggested narratives. Treat the explanations as drafts for the analyst to confirm, not answers.

Report: prompts 18–20

1. 1"Create a pivot table of revenue by customer and month." Returns the pivot on a new sheet, ready to slice.
2. 2"Add a chart showing budget versus actual by month, formatted for a board pack." Returns a clustered chart it will restyle on request (titles, axis labels, colours).
3. 3"Summarise this table in three plain-English paragraphs a non-finance board member can follow." Returns the narrative summary. With numbers checked, this is the fastest first draft of report commentary Frontrow has seen finance teams adopt.

Getting the habit to stick

The teams that keep these prompts in daily use share one habit: they keep a team prompt sheet, pinned in a Teams channel, with the exact wording that worked on their own workbooks. Generic prompts decay; tuned ones compound. Frontrow maintains a free Copilot prompt library by department at frontrowtech.com.au/copilot-prompts, covering sales, finance and other functions, written for Australian workflows with AUD and FY conventions baked in. And one discipline above all: Copilot's numbers get checked before they leave the finance team, the same as a graduate analyst's would. It drafts; the accountant signs.