Excel remains the most important tool for planning, reporting, and analysis in controlling. At the same time, many well-known problems arise precisely here: local files, version chaos, manual consolidation, lack of traceability, and limited performance with large datasets.
IBM Planning Analytics for Excel (PAfE) allows you to continue using Excel as a familiar interface – but on a centrally managed, multidimensional database. This combines the flexibility of Excel with the stability and governance of an enterprise planning system.
This article explains how Planning Analytics for Excel works technically, what possibilities it offers for controlling and finance, and how it differs from traditional Excel or ERP approaches.
What is IBM Planning Analytics for Excel?
IBM Planning Analytics is based on the in-memory database TM1, which centrally stores planning, forecasting, and reporting data. Users access this data either via a web interface or directly through Excel.
The Excel add-in connects individual Excel cells directly to the cells in the TM1 database. Changes are not saved locally but are written directly to the central database. This ensures that all users always work with consistent and up-to-date data, regardless of whether they are using Excel or the web.
Why Excel and TM1 are such a good technical match
The key difference lies in the database type. TM1 is not a traditional relational spreadsheet, but a cell-oriented, functional database. Each cell is logically linked to other cells – similar to Excel itself.
This allows Excel cells to be directly linked to database cells. Users can continue to use all the familiar Excel functions:
- Formulas
- Charts
- Pivot Logic
- Formatting
- Side Calculations
- Sparklines
In the background, however, the data remains centralized, version-safe, and performant. Classic relational systems like SAP or HANA cannot map this cell logic in Excel in the same way.
How to link data in Excel with TM1
In the Excel add-in, the connection is established via special formulas, e.g.:
- DBRW (Database reference to individual cells)
- SUBNM (Selection of dimension elements via dropdown menu)
Each key figure in Excel thus directly references a defined combination of dimensions in the data cube. Changes are written immediately to the TM1 database – without local copies or manual uploads.
This creates a true bidirectional connection between Excel and the planning system.
Planning and reporting directly in Excel – without data breaks
Planners can enter or change values directly in Excel. These changes are immediately available to all other users – regardless of whether they are working in Excel or on the web.
Typical use cases:
- Budget planning
- Forecast analysis
- Scenario comparisons
- Sales planning
- Cost center planning
- Management reporting
All calculations and aggregations are performed centrally in the database, not locally in individual files.
Create reports flexibly: Three report modes in Planning Analytics Excel
1. Cell-based report
Each cell is individually linked to the database. This mode is particularly suitable for:
- Well-formatted reports
- Custom layouts
- Excel-specific calculations
Maximum design freedom with full data consistency.
2. Dynamic Report
Here, rows remain dynamically expandable and collapsible. Formatting is layer-based, ensuring that layouts remain stable even when the structure changes.
Suitable for:
- Hierarchical analyses
- Drill-down analyses
- Interactive reports
3. Universal Report
Both rows and columns are dynamic. Formatting is done using rules and conditional formatting.
Ideal for:
- Flexible analysis interfaces
- Complex pivot structures
- Self-service analysis
Slice & Dice: Interactive analysis of large datasets
Users can pivot, filter, and recombine data in real time. Thanks to TM1’s in-memory architecture, even large data models remain performant.
This makes it possible:
- Fast ad-hoc analyses
- Intuitive navigation within the data space
- High user acceptance in the business unit
Seamless integration between Excel and the web
Created Excel reports can be published centrally and automatically converted to HTML. They are then also available in the web interface – including writing and planning functions.
This creates a unified platform for:
- Centralized Distribution
- Authorization Control
- Versioning
- Collaboration
Limitations of classic Excel approaches – and why Planning Analytics overcomes them
Typical Excel problems in controlling:
- Multiple versions
- Manual consolidation
- Lack of transparency
- Performance issues
- Limited scalability
These weaknesses are systematically reduced through centralized data storage and direct cell linking – without sacrificing the flexibility of Excel.
Conclusion: Excel remains – but at an enterprise level.
IBM Planning Analytics for Excel combines the familiar workflow of Excel with a central, high-performance, and audit-proof data platform. This provides controlling organizations with a scalable alternative to isolated Excel solutions – without training requirements or data breaks.
Strategically develop Excel further instead of replacing it
If you’d like to explore how to structure and transfer your existing Excel plans into a central planning platform, a professional consultation can be helpful.
In a no-obligation meeting, we’ll review your current Excel environment, data structures, and planning processes together – and assess the best next steps.
