50+ Microsoft Copilot Prompts for Excel: Formulas, Dashboards & Automation
Share
50+ Microsoft Copilot Prompts for Excel: Formulas, Dashboards & Automation Workflows
Last updated: July 2026 | Applies to: Microsoft 365 (Excel with Copilot), Office 2024
Table of Contents
- Summary
- What Is Microsoft Copilot in Excel?
- How Copilot Works in Excel
- Requirements & Licensing
- The Prompt Formula That Gets Results
- Financial Analysis Prompts
- Sales Analysis Prompts
- Marketing Analysis Prompts
- Human Resources Prompts
- Inventory & Operations Prompts
- Formula Generation Examples
- Dashboard Creation with Copilot
- Automation Workflows
- Copilot vs. Manual Excel: Feature Comparison
- Expert Tips & Best Practices
- Troubleshooting Guide
- Frequently Asked Questions
- Conclusion
Summary
Microsoft Copilot in Excel is an AI-powered assistant embedded directly into your spreadsheet environment. It can analyze data, generate formulas, build dashboards, and automate repetitive reporting tasks — all through natural language prompts.
This guide provides 50+ production-ready prompts organized by business function, along with formula examples, dashboard workflows, troubleshooting steps, and expert recommendations from enterprise IT professionals.
Key Takeaways:
- Copilot requires a Microsoft 365 subscription with Copilot add-on (or Microsoft 365 Copilot plan)
- Prompt quality directly determines output quality — specificity is everything
- Copilot works best on structured, table-formatted data
- It can generate complex formulas (XLOOKUP, SUMIFS, nested IF) from plain English
- Dashboard creation that previously took hours can be completed in minutes
- Copilot does not replace Excel expertise — it amplifies it
What Is Microsoft Copilot in Excel?
Microsoft Copilot in Excel is an AI assistant built on large language model (LLM) technology, integrated directly into the Excel ribbon. It was introduced as part of Microsoft 365 Copilot in 2023 and has been continuously expanded since.
Unlike traditional Excel add-ins or macros, Copilot understands natural language. You describe what you want in plain English, and Copilot translates that into formulas, pivot tables, charts, or data summaries.
For enterprise users, this represents a significant productivity shift. Analysts who previously spent hours building reports can now generate them in minutes. Finance teams can automate variance analysis. Sales managers can produce territory breakdowns without writing a single formula.
Copilot is not a replacement for Excel knowledge. It is a force multiplier. The more you understand Excel, the better you can guide Copilot to produce accurate, useful results.
How Copilot Works in Excel
Copilot in Excel operates through the Copilot pane, accessible from the Home tab in the Excel ribbon. When you open it, you interact with Copilot through a chat-style interface.
Copilot reads the data in your active worksheet — specifically data formatted as an Excel Table (Insert > Table). It analyzes column headers, data types, and values to understand the structure of your dataset.
When you submit a prompt, Copilot:
- Parses your natural language request
- Maps it to your table structure
- Generates the appropriate Excel action (formula, chart, pivot table, or summary)
- Presents the result with an explanation
- Allows you to insert the result directly into your workbook
Copilot can also suggest follow-up actions, highlight anomalies in your data, and propose additional analyses you may not have considered.
Microsoft Expert Tip: Always format your data as an Excel Table before using Copilot. Unstructured ranges produce inconsistent results. Use Insert > Table and ensure every column has a clear, descriptive header.
Requirements & Licensing
System Requirements
- Windows 10 (22H2 or later) or Windows 11
- Microsoft 365 Apps for Business, Enterprise, or Education
- Excel version: Microsoft 365 (Current Channel or Monthly Enterprise Channel)
- Internet connection required (Copilot is cloud-powered)
- Minimum 4 GB RAM (8 GB recommended for large datasets)
Licensing Requirements
| Plan | Copilot Included? | Notes |
|---|---|---|
| Microsoft 365 Personal/Family | Limited (Copilot Free) | Basic features only |
| Microsoft 365 Business Basic | No | Requires Copilot add-on |
| Microsoft 365 Business Standard | No | Requires Copilot add-on |
| Microsoft 365 Business Premium | No | Requires Copilot add-on |
| Microsoft 365 Copilot (E3/E5 add-on) | Yes | Full Copilot in Excel |
| Office 2024 LTSC | No | Perpetual license, no Copilot |
Licensing Note: Microsoft Copilot for Microsoft 365 is a paid add-on priced per user per month. It requires an eligible Microsoft 365 base subscription. Office 2024 LTSC (perpetual license) does not include Copilot. If you need Copilot, you need a Microsoft 365 subscription.
If you are currently on a perpetual Office license and want to access Copilot, consider upgrading to a genuine Microsoft Office or Microsoft 365 license.
The Prompt Formula That Gets Results
Most users underperform with Copilot because they use vague prompts. The quality of your output is directly proportional to the quality of your input.
Use this four-part framework for every prompt:
Action + Data Reference + Goal + Output Format
Examples
❌ Weak prompt:
Analyze this data.
✅ Strong prompt:
Analyze the Sales table, calculate total revenue by region for Q2 2026, identify the top three regions by growth rate, and present the results in a summary table with percentage changes.
The difference is specificity. Copilot needs to know what data to use, what calculation to perform, what the goal is, and how to present the result.
Common Mistake: Asking Copilot to "analyze" without specifying what metric to analyze. Always name the column, the calculation, and the expected output format.
Financial Analysis Prompts
Finance teams generate the most measurable ROI from Copilot. These prompts cover the most common financial reporting tasks.
1. Monthly Revenue Trend Analysis
Analyze the Revenue column by month, calculate month-over-month growth rates, identify the three highest-growth months, and summarize findings in a table.
2. Gross Profit Margin by Product
Calculate gross profit margin for each product using the Revenue and Cost columns. Sort results from highest to lowest margin and highlight products below 20% margin.
3. Quarterly Revenue Forecast
Using the past 12 months of revenue data, forecast revenue for the next quarter using a linear trend. Show the forecast alongside historical data in a chart.
4. Expense Category Breakdown
Summarize total expenses by category, calculate each category as a percentage of total spend, and identify the top three expense categories.
5. Budget vs. Actual Variance Report
Compare the Budget column against the Actual column for each department. Calculate variance in dollars and percentage. Flag any department where actual exceeds budget by more than 10%.
6. Cash Flow Summary
Summarize monthly cash inflows and outflows, calculate net cash flow for each month, and identify months with negative cash flow.
7. Year-over-Year Growth Analysis
Compare revenue for 2025 and 2026 by quarter. Calculate year-over-year growth rates and present results in a comparison table.
8. Unusual Transaction Detection
Identify transactions in the Amount column that are more than two standard deviations above the average. List them with dates and descriptions.
9. Financial KPI Dashboard
Create a summary showing total revenue, total expenses, net profit, profit margin percentage, and month-over-month revenue growth for the current period.
10. Break-Even Analysis
Using fixed costs, variable costs per unit, and selling price per unit from this table, calculate the break-even point in units and revenue.
Sales Analysis Prompts
Sales managers can eliminate hours of manual reporting with these prompts.
11. Top-Selling Products
Rank all products by total revenue. Show the top 10 products with their revenue, units sold, and percentage of total sales.
12. Regional Sales Comparison
Compare total sales by region for the current quarter. Calculate each region's share of total revenue and identify the top and bottom performers.
13. Average Order Value
Calculate the average order value by month and by sales representative. Identify which rep has the highest average order value.
14. Sales Forecast
Using the past 18 months of sales data, forecast sales for the next three months. Show confidence intervals if possible.
15. Declining Product Identification
Identify products where sales have declined for three or more consecutive months. List them with their decline percentage.
16. Sales Rep Performance Dashboard
Summarize performance for each sales representative: total revenue, number of deals closed, average deal size, and quota attainment percentage.
17. Customer Purchase Behavior
Analyze customer purchase frequency. Identify customers who have purchased more than five times and calculate their average spend per order.
18. Most Profitable Products
Calculate profit per unit for each product using the selling price and cost columns. Rank products by total profit contribution.
19. Monthly Sales Growth Rate
Calculate month-over-month sales growth for the past 12 months. Highlight months with growth above 10% and below 0%.
20. Quarterly Sales Report
Generate a quarterly sales summary showing total revenue, units sold, new customers acquired, and top product by revenue for each quarter.
Marketing Analysis Prompts
21. Campaign ROI Calculation
Calculate ROI for each marketing campaign using the Revenue Generated and Campaign Cost columns. Rank campaigns from highest to lowest ROI.
22. Conversion Rate by Channel
Compare conversion rates across all marketing channels. Identify the top two channels by conversion rate and the channel with the lowest cost per conversion.
23. Website Traffic Trend Analysis
Analyze monthly website sessions, identify growth trends, and flag months where traffic declined more than 15% from the previous month.
24. Top-Performing Campaigns
Rank all campaigns by revenue generated. Show the top five campaigns with their spend, revenue, and ROI.
25. Cost Per Acquisition Analysis
Calculate cost per acquisition for each channel by dividing total spend by new customers acquired. Identify the most cost-efficient channel.
Human Resources Prompts
26. Employee Turnover Rate
Calculate monthly employee turnover rate using the Headcount and Departures columns. Identify months with turnover above 5%.
27. Attendance Trend Analysis
Analyze attendance data by department. Identify departments with the highest and lowest average attendance rates.
28. Productivity by Department
Calculate output per employee for each department using the Output and Headcount columns. Rank departments by productivity.
29. Overtime Expense Analysis
Summarize overtime hours and costs by department. Identify departments where overtime exceeds 10% of total labor hours.
30. Training Completion Report
Calculate training completion rates by department and by course. Identify employees who have not completed mandatory training.
Inventory & Operations Prompts
31. Stock Shortage Risk
Identify products where current stock levels are below the reorder point. List them with current quantity, reorder point, and average daily sales.
32. Inventory Turnover Rate
Calculate inventory turnover for each product category using Cost of Goods Sold and Average Inventory columns.
33. Demand Forecasting
Using the past 12 months of sales data, forecast demand for each product for the next 90 days.
34. Slow-Moving Inventory
Identify products that have not sold in the past 60 days. List them with their current stock value and last sale date.
35. Supplier Performance Comparison
Compare suppliers by on-time delivery rate, defect rate, and average lead time. Rank suppliers from best to worst overall performance.
Formula Generation Examples
One of Copilot's most powerful capabilities is generating complex Excel formulas from plain English descriptions. This eliminates the need to memorize syntax or search documentation.
IF Formula
Prompt:
Create an IF formula in column F that marks any sale above $10,000 as "High Performance" and all other sales as "Standard".
Copilot generates: =IF(E2>10000,"High Performance","Standard")
Nested IF with Multiple Tiers
Prompt:
Create a formula that assigns a performance tier: "Platinum" for sales above $50,000, "Gold" for $25,000–$50,000, "Silver" for $10,000–$25,000, and "Bronze" for anything below $10,000.
XLOOKUP Formula
Prompt:
Create an XLOOKUP formula that finds the customer name in the Customers table based on the Customer ID in column A of this sheet.
Copilot generates: =XLOOKUP(A2,Customers[CustomerID],Customers[CustomerName],"Not Found")
SUMIFS Formula
Prompt:
Create a SUMIFS formula that calculates total revenue for the "North" region in Q2 2026.
Dynamic Array Formula
Prompt:
Create a formula that returns a unique list of all product categories from the Category column, sorted alphabetically.
Copilot generates: =SORT(UNIQUE(Table1[Category]))
Commission Calculation
Prompt:
Create a commission formula that pays 3% on sales up to $10,000, 5% on sales between $10,000 and $25,000, and 7% on sales above $25,000.
Date-Based Calculations
Prompt:
Create a formula that calculates the number of business days between the Order Date in column B and the Ship Date in column C, excluding weekends.
Copilot generates: =NETWORKDAYS(B2,C2)
Pro Recommendation: After Copilot generates a formula, always review the logic before applying it to your full dataset. Copilot explains each formula — read the explanation to confirm it matches your intent. For complex nested formulas, test on a small sample first.
Dashboard Creation with Copilot
Building dashboards traditionally requires advanced Excel skills: pivot tables, slicers, chart formatting, and layout design. Copilot dramatically reduces this barrier.
Executive KPI Dashboard
Prompt:
Create an executive dashboard showing total revenue, total expenses, net profit, profit margin, month-over-month revenue growth, and top product by revenue. Format it as a clean summary table.
Sales Performance Dashboard
Prompt:
Build a sales dashboard showing monthly revenue trend, top 5 products by revenue, sales by region, and quota attainment by sales rep.
Marketing Dashboard
Prompt:
Create a marketing performance dashboard showing total leads, conversion rate, cost per lead, revenue by channel, and top campaign by ROI.
Inventory Dashboard
Prompt:
Build an inventory dashboard showing total SKUs, items below reorder point, inventory value by category, and top 10 products by sales velocity.
Microsoft Expert Tip: After Copilot generates a dashboard summary, use Excel's built-in chart tools to add visual elements. Copilot can suggest chart types — ask it: "What chart type best represents this data?" for guidance.
Automation Workflows
Beyond one-time analysis, Copilot can help you build repeatable workflows that reduce manual effort every reporting cycle.
Monthly Financial Report Workflow
- Paste new monthly data into your structured Excel Table
- Open Copilot and prompt: "Update the monthly financial summary with the new data and highlight any significant changes from last month."
- Review Copilot's output and insert into your report template
- Use Copilot to generate the executive summary narrative: "Write a three-sentence executive summary of this month's financial performance."
Weekly Sales Report Workflow
- Export CRM data to Excel
- Format as a Table
- Prompt Copilot: "Compare this week's sales to last week. Identify top performers and flag any reps below 70% of weekly quota."
- Insert results into your weekly report template
Inventory Review Workflow
- Export inventory data from your ERP or Shopify admin
- Prompt Copilot: "Identify all products below reorder point, calculate days of stock remaining based on average daily sales, and sort by urgency."
- Use the output to generate purchase orders
Security Note: Copilot processes your data through Microsoft's cloud infrastructure. Ensure your Microsoft 365 tenant's data residency and compliance settings align with your organization's data governance policies before using Copilot with sensitive financial or HR data. Review your Microsoft Purview compliance settings if operating in regulated industries.
Copilot vs. Manual Excel: Feature Comparison
| Task | Manual Excel | Excel with Copilot | Time Saved |
|---|---|---|---|
| Build XLOOKUP formula | 5–15 minutes | 30 seconds | ~90% |
| Create pivot table summary | 10–20 minutes | 1–2 minutes | ~85% |
| Monthly financial report | 2–4 hours | 15–30 minutes | ~80% |
| Identify data anomalies | 30–60 minutes | 2–5 minutes | ~90% |
| Build KPI dashboard | 3–6 hours | 30–60 minutes | ~80% |
| Write executive summary | 30–60 minutes | 2–5 minutes | ~90% |
| Forecast next quarter | 1–3 hours | 5–10 minutes | ~85% |
Expert Tips & Best Practices
1. Always Use Excel Tables
Copilot performs significantly better on data formatted as Excel Tables. Unstructured ranges produce inconsistent results. Use Insert > Table and give your table a descriptive name.
2. Use Descriptive Column Headers
Column headers like "Rev" or "Q1" confuse Copilot. Use full descriptive names: "Q1 2026 Revenue (USD)". This directly improves output accuracy.
3. Iterate Your Prompts
If the first result is not what you expected, refine your prompt. Add more specificity, reference exact column names, or break complex requests into smaller steps.
4. Combine Copilot with Power Query
Use Power Query to clean and transform raw data before bringing it into Excel. Then use Copilot to analyze the clean dataset. This two-step approach produces the most reliable results.
5. Save Effective Prompts
When you find a prompt that produces excellent results, save it in a prompt library. This creates a reusable asset for your team and ensures consistency across reporting cycles.
6. Validate Formula Output
Always verify Copilot-generated formulas against a manual calculation on a small sample. Copilot is highly accurate but not infallible, particularly with complex multi-condition formulas.
Common Mistake: Applying Copilot-generated formulas to thousands of rows without testing on 5–10 rows first. Always validate before scaling.
Troubleshooting Guide
Issue: Copilot button is grayed out or not visible
Cause: Your Microsoft 365 subscription does not include Copilot, or your IT administrator has not enabled it for your account.
Resolution: Verify your subscription includes Microsoft 365 Copilot. Contact your Microsoft 365 administrator to confirm the Copilot license is assigned to your account. Check that you are using the Current Channel version of Microsoft 365 Apps.
Prevention: Confirm Copilot licensing before deployment. Copilot requires a separate license assignment in the Microsoft 365 admin center.
Issue: Copilot says "I can't help with that" or produces irrelevant results
Cause: The prompt is too vague, the data is not formatted as a Table, or the column headers are ambiguous.
Resolution: Format your data as an Excel Table. Rename column headers to be fully descriptive. Rewrite your prompt using the Action + Data + Goal + Output Format framework.
Prevention: Always prepare your data structure before opening Copilot.
Issue: Copilot generates an incorrect formula
Cause: Ambiguous column references or complex multi-condition logic that Copilot misinterpreted.
Resolution: Reference exact column names in your prompt. Break complex formulas into simpler components. Ask Copilot to explain the formula logic before applying it.
Prevention: Test all formulas on a small sample before applying to the full dataset.
Issue: Copilot is slow or times out
Cause: Large dataset, slow internet connection, or Microsoft service degradation.
Resolution: Check the Microsoft 365 Service Health dashboard for any active incidents. Reduce dataset size by filtering to the relevant date range before prompting. Ensure a stable internet connection.
Issue: Copilot is not available in my region
Cause: Microsoft 365 Copilot availability varies by region and is being rolled out progressively.
Resolution: Check the Microsoft 365 Copilot availability page for your region. Contact your Microsoft reseller or licensing partner for the latest availability information.
Frequently Asked Questions
1. Does Microsoft Copilot in Excel require an internet connection?
Yes. Copilot in Excel is a cloud-powered feature that requires an active internet connection. It sends your prompts and relevant data context to Microsoft's AI infrastructure for processing. It does not function offline. If you need offline Excel functionality, you can still use standard Excel features without Copilot. Microsoft 365 Apps continue to work offline for core functionality.
2. Is my data safe when using Copilot in Excel?
Microsoft states that Copilot for Microsoft 365 does not use your data to train its underlying AI models. Your data is processed within Microsoft's compliance boundary and is subject to your organization's Microsoft 365 data governance policies. For regulated industries (healthcare, finance, legal), review your Microsoft Purview compliance settings and consult your compliance officer before using Copilot with sensitive data.
3. Can I use Copilot in Excel with Office 2024 LTSC?
No. Office 2024 LTSC is a perpetual license product and does not include Copilot. Copilot requires a Microsoft 365 subscription with the Copilot add-on. If you are on Office 2024 LTSC and want Copilot, you would need to transition to a Microsoft 365 subscription. Office 2024 LTSC remains an excellent choice for organizations that do not require cloud-connected AI features.
4. What is the difference between Copilot Free and Microsoft 365 Copilot?
Copilot Free (available in Microsoft 365 Personal and Family) provides limited AI assistance with basic prompts and a restricted number of interactions per day. Microsoft 365 Copilot (the paid add-on for business plans) provides full integration across Excel, Word, PowerPoint, Outlook, and Teams, with no daily usage limits, access to your organizational data through Microsoft Graph, and advanced analysis capabilities in Excel.
5. Can Copilot write VBA macros?
As of mid-2026, Copilot in Excel focuses on natural language analysis, formula generation, and data summarization rather than VBA macro authoring. For VBA generation, you can use Microsoft Copilot in the broader Microsoft 365 context or GitHub Copilot if you have that subscription. Microsoft continues to expand Copilot's capabilities, so VBA support may be added in future updates.
6. How many rows of data can Copilot analyze?
Copilot works best with datasets up to approximately 1–2 million rows, though performance may degrade with very large datasets. For optimal results, filter your data to the relevant subset before prompting Copilot. Microsoft has not published a hard row limit, but practical performance is best on focused, well-structured datasets rather than entire data warehouses.
7. Can Copilot create charts and visualizations?
Yes. Copilot can suggest and create charts based on your data. You can prompt it with requests like "Create a bar chart showing monthly revenue by region" and it will generate the chart and insert it into your workbook. It can also recommend the most appropriate chart type for your data when asked.
8. Does Copilot work with Power Query data?
Yes. Once Power Query data is loaded into an Excel Table, Copilot can analyze it like any other structured dataset. The recommended workflow is to use Power Query for data transformation and cleaning, then use Copilot for analysis and reporting on the clean data.
9. Can multiple users collaborate with Copilot in a shared workbook?
Each user interacts with Copilot individually through their own Copilot pane. Copilot interactions are not shared in real time between collaborators. However, the results Copilot inserts into the workbook (formulas, tables, charts) are visible to all collaborators in a shared workbook, just like any other Excel content.
10. What Microsoft 365 plan do I need for full Copilot in Excel?
You need a Microsoft 365 Business Standard, Business Premium, E3, or E5 base subscription, plus the Microsoft 365 Copilot add-on license. The Copilot add-on is priced per user per month. Microsoft 365 Personal and Family subscribers get limited Copilot access. Contact your Microsoft licensing partner or visit the Microsoft website for current pricing.
11. Can Copilot analyze data from multiple sheets?
Copilot primarily analyzes the active sheet's data. For multi-sheet analysis, the recommended approach is to consolidate your data into a single Table using Power Query or Excel's built-in data consolidation features, then use Copilot on the consolidated dataset. Microsoft is expanding Copilot's cross-sheet capabilities in ongoing updates.
12. Is Copilot available in Excel for Mac?
Yes. Microsoft 365 Copilot is available in Excel for Mac for users with eligible Microsoft 365 subscriptions. Feature parity between Windows and Mac versions of Copilot in Excel is generally maintained, though some features may roll out to Windows first before appearing on Mac.
13. Can I use Copilot to clean and deduplicate data?
Yes. You can prompt Copilot to identify duplicate rows, flag inconsistent data entries, or highlight missing values. For example: "Identify rows where the Customer Email column contains duplicate values" or "Flag rows where the Revenue column is blank or zero." Copilot can identify these issues, though the actual data cleaning actions are performed by you in Excel.
14. Does Copilot support languages other than English?
Microsoft 365 Copilot supports multiple languages, including Spanish, French, German, Japanese, and others. The quality of results may vary by language, as the underlying models are most extensively trained on English-language data. Microsoft continues to expand multilingual support with each update.
15. How do I get the best formula results from Copilot?
Reference exact column names from your Table in your prompt. Specify the exact calculation you need. Describe the expected output. For example: "Create a formula in column G that calculates the 12-month rolling average of the Monthly Revenue column, starting from row 13." The more precise your prompt, the more accurate the formula. Always review Copilot's explanation of the formula before applying it.
Conclusion
Microsoft Copilot in Excel represents a genuine productivity shift for anyone who works with data regularly. The 50+ prompts in this guide cover the most common business analysis tasks across finance, sales, marketing, HR, and operations.
The key to getting value from Copilot is prompt quality. Vague prompts produce vague results. Specific, structured prompts — using the Action + Data + Goal + Output Format framework — consistently produce accurate, actionable outputs.
Copilot does not replace Excel expertise. It amplifies it. The more you understand your data and what you need from it, the more effectively you can direct Copilot to deliver it.
To use Copilot in Excel, you need a Microsoft 365 subscription with the Copilot add-on. If you are currently on a perpetual Office license, consider whether upgrading to Microsoft 365 makes sense for your workflow. For users who do not need Copilot, Office 2024 LTSC remains an excellent, cost-effective choice for core Excel functionality.
Recommended Microsoft Office Licenses
To use Excel with full functionality, you need a genuine Microsoft Office or Microsoft 365 license. SoftwareKeep offers genuine Microsoft licenses with instant digital delivery and lifetime activation support.
- Microsoft Office 2024 Home — Best for home users who need Word, Excel, and PowerPoint without a subscription. View license
- Microsoft Office 2024 Home and Business — Adds Outlook for professionals and remote workers. View license
- Microsoft Office Home & Business 2024 — Ideal for entrepreneurs and small business owners. View license
All licenses include genuine product keys, instant delivery, and activation support from our team.
This article was written by the SoftwareKeep technical team. Information is accurate as of July 2026 and reflects Microsoft 365 Copilot capabilities at that time. Microsoft updates Copilot features regularly — refer to the Microsoft Copilot in Excel documentation for the latest feature updates.