Microsoft Copilot for building pivot tables and forecasts in Excel
- Graziano Stefanelli
- Sep 16
- 4 min read
Microsoft Copilot has added powerful data analysis features inside Excel, allowing users to generate PivotTables, forecasting models, and even time-series predictions using natural language. With support for prompt-based analytics, formula-level AI (=COPILOT()), and forecasting integration with Python, Copilot is reshaping how Excel is used for strategic insights and reporting. This article reviews how to use Copilot for PivotTables and forecasting, including licensing, examples, performance limits, and best practices.
Copilot is available in Excel via multiple interface options.
Microsoft Copilot integrates into Excel through several user-facing components. Depending on your license and update channel, you may access:
Entry Point | Description | Availability |
Copilot pane (right sidebar) | Chat-based natural language interface for PivotTables and forecasts | General availability since Sep 2024 |
Copilot button (Home tab) | Context-aware prompt button; auto-detects selected table and builds outputs | Rolled out to Current Channel in mid-2025 |
=COPILOT() function | Beta formula allowing natural language commands inside a spreadsheet cell | Beta Channel, Windows & Mac, Aug 2025 |
The sidebar and ribbon tools respond to full-sentence prompts and interact with the active range or table. The =COPILOT() formula is more granular, embedded directly into a cell, and supports focused commands like forecasts, summaries, or aggregations.
PivotTables can be created and modified entirely via natural language.
Copilot supports dynamic creation of PivotTables using descriptive prompts. It automatically infers fields from structured tables and places the PivotTable in a new worksheet by default.
Example prompt:
Create a PivotTable that summarizes Total Revenue by Region and Month. Add a slicer for Product Line.
PivotTable Feature | Copilot Capability |
Auto-field detection | Copilot detects relevant columns based on prompt context and cursor position. |
Recommended layouts | Copilot surfaces multiple Pivot layout options (Current Channel feature). |
Interactive filters | Slicers and timelines can be added via prompt instructions. |
Renaming and reformatting | Fields and measures can be renamed or reformatted using follow-up prompts. |
These capabilities have replaced the old "Recommended PivotTables" dialog with a smarter Copilot-powered experience, especially for Business and Enterprise users. The new panel previews Pivot designs based on cursor-positioned data.
Forecasting is available through Copilot’s Excel engine and Python integration.
Copilot supports both standard Excel forecasting tools and advanced predictive analytics via embedded Python code (in preview builds). Users can request time-series predictions directly using prompts, and Copilot generates either a Forecast Sheet or a Python-based model depending on environment support.
Forecasting Type | How It Works |
Standard Forecast Sheet | Copilot invokes Excel’s built-in forecast wizard with trend lines and confidence bands. |
Python forecast (ARIMA/ETS) | For Insider users, Copilot inserts and runs Python time-series models in cells. |
Formula command | =COPILOT("Forecast Q4 sales from range C2:C53") returns a dynamic forecast chart. |
Forecasts can be customized by horizon (e.g., next 6 months), method (linear, exponential smoothing), and granularity (daily, monthly, quarterly). The Python-based system is only available on Windows Insider builds with Python-in-Excel enabled.
Prompt design impacts the quality of Pivot and forecast outputs.
To maximize accuracy and usability, users should adopt structured natural-language prompts that include key context:
Prompt Element | Example |
Measure and dimension | “Summarize Total Profit by Product Category and Quarter.” |
Filters or scope | “Filter to FY 2025 and Region = North America.” |
Chart request | “Include a line chart comparing forecasted vs actual sales.” |
Interactive tools | “Insert a slicer for Region and a timeline for Date.” |
Forecast customization | “Use exponential smoothing with a 4-quarter prediction horizon.” |
Follow-up prompts can refine results, such as moving fields between columns and rows, changing aggregation types (sum, average), or formatting outputs.
System limits and channel restrictions affect forecasting performance.
While Copilot is highly responsive for most Excel datasets, certain limits apply:
Area | Limit or Requirement |
Max data size | Performs best under 1 million cells |
Forecast horizon | Varies by data volume and time granularity |
Python forecasts | Requires Windows Insider build with Python in Excel |
Web support for =COPILOT() | “Coming soon”; not yet available in web Excel |
Users dealing with very large datasets may encounter time-outs or delayed responses during PivotTable rendering. Additionally, not all builds support the =COPILOT() function or Python execution.
Security, protection, and admin controls apply as in traditional Excel.
Copilot adheres to Microsoft 365 security architecture. Generated content respects:
Workbook protection (locked cells, worksheet protection)
Information Protection labels (e.g., Confidential, Restricted)
Table permissions and DLP policies
Tenant administrators can enable or restrict Copilot via Microsoft 365 Admin Center, and may also block or audit the use of Python features for compliance. Copilot does not bypass existing protections or visibility rules.
Summary table: Excel Copilot for PivotTables and forecasting (Sep 2025)
Feature | Available in | Notes |
Prompt-based PivotTables | All Copilot-enabled Excel clients | Auto-field detection and layout previews |
Forecast generation | Copilot pane and =COPILOT() formula | Uses Forecast Sheet or Python where available |
=COPILOT() function | Beta Channel (Windows/Mac) | Accepts natural language commands inside cells |
Interactive slicers/timelines | Prompt-based request | Works on PivotTables created via Copilot |
Python-based time series | Windows Excel with Python (Insider only) | Supports ARIMA, ETS, regression models |
Enterprise compliance | M365 Admin controls, DLP, MIP | Fully governed and configurable via tenant policies |
Microsoft Copilot in Excel transforms how PivotTables and forecasts are created, offering both intuitive natural-language prompts and formula-level automation. With deep integration across the ribbon, pane, and formula bar, Copilot brings analytical power to casual users and analysts alike—provided they're on a compatible plan and build.
____________
FOLLOW US FOR MORE.
DATA STUDIOS