To get the most out of a custom analytics agent in Microsoft 365 Copilot for Excel sales data, use a structured prompt that defines the goal, context, and specific output format. For the best results, ensure your data is formatted as an Excel Table.
Suggested Comprehensive Prompt
"Analyze the sales data in this file to identify the top 5 performing products by total revenue and regional growth. Perform a mathematical operation to calculate a new column for 'Profit Margin' (Revenue minus Cost), then aggregate the total sales and average profit margin by 'Sales Representative' and 'Region.' Finally, create a professional dashboard on a new sheet featuring a bar chart for product revenue and a line graph showing monthly sales trends over the last 12 months." Breakdown of Prompt Capabilities Visualizations: Ask for specific chart types (e.g., "Visualize this as a bar chart" or "Create a line graph") to help spot trends.
Mathematical Operations: Request new columns based on existing data (e.g., "Create a column for the difference between 'Projected' and 'Actual' sales").
Aggregations: Use natural language to summarize data (e.g., "Calculate the total sales by region") or generate PivotTables for deeper grouping.
Reference:
https://support.microsoft.com/en-us/office/get-insights-about-numerical-data-with-copilot-in-excel-
52d97339-86c0-431c-b46c-e7b07b2898dd