Copilot in Excel Prompts — Free AI Prompts for M365 Copilot

Set up a budget tracker with monthly columns for budget and actual, auto-calculated variance, conditional formatting, YTD totals, forecast, and dashboard summary.

Create a formula that [DESCRIBE WHAT YOU NEED — e.g., calculates the priority score based on severity (High=3, Medium=2, Low=1) multiplied by the number of days open, then sorts into Critical/High/Medium/Low tiers]. Explain how the formula works step by step so I can modify it later.

Create a chart that best shows [WHAT YOU WANT TO VISUALISE — trends over time / comparison between categories / distribution of values] from this data. Choose the most appropriate chart type. Add clear labels, a title, and a legend. Then explain in one sentence what the chart tells us.

Create a formula for this table that flags rows where the [DEADLINE COLUMN] has passed and the [STATUS COLUMN] is not "Completed". Show "Overdue" in a new column. Then add conditional formatting to highlight overdue rows in red and items due within 3 days in amber.

Design a one-page Excel dashboard for [PURPOSE — sales tracking / project status / IT operations / team performance]. Include: (1) 4-6 KPI cards with current value and trend, (2) Main chart showing the primary metric over time, (3) Breakdown chart by [DIMENSION — region / category / team], (4) Data table with detail, (5) Refresh date. Specify chart types and layout.

Analyse this data and give me: (1) The top 5 key insights or trends, (2) Any notable outliers or anomalies, (3) A summary of totals and averages for the key metrics. Explain each insight in plain English — I need to present this to [AUDIENCE — management / the team].

Design data validation rules for this shared spreadsheet with columns: [LIST COLUMNS]. For each column specify: (1) Allowed data type, (2) Validation rule (dropdown list, range, format), (3) Input message for users, (4) Error message if invalid, (5) Whether blank is allowed. Goal: prevent data quality issues before they happen.

Create an employee directory template with name, role, department, location, email, phone, manager, start date, skills. Add filters and headcount calculation.

Scan this table for data quality issues. Look for: (1) Duplicate rows, (2) Blank or missing values in required columns, (3) Inconsistent formatting (dates, currencies, text case), (4) Values that look like typos or outliers. List each issue with the row number and a suggested fix.

Analyse this data by month and show me: (1) Month-over-month change (percentage), (2) The best and worst performing months, (3) Any seasonal patterns you can detect, (4) The overall trend direction (growing, declining, or flat). Add a trend line chart and forecast the next 3 months based on the pattern.

I need to answer this business question from my data: [QUESTION — e.g. Which region had the highest sales growth last quarter?]. My data has these columns: [LIST COLUMNS]. Design: (1) The pivot table layout (rows, columns, values, filters), (2) What aggregation to use (sum, count, average), (3) Any calculated fields needed, (4) Recommended chart to visualise the result.

Build a what-if scenario comparison for [DECISION — hiring plan / budget allocation / pricing change / expansion]. Create 3 scenarios: (1) Conservative, (2) Base case, (3) Optimistic. For each: define assumptions, calculate outcomes, and show the difference. Include a sensitivity analysis on the [KEY VARIABLE]. Present as a side-by-side comparison table.