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:
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.
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.
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:
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:
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)
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
