In this article, I want to contrast traditional, standalone Excel techniques with a global, enterprise process mindset. This shift from the local paper-flow mindset to a digital data-flow mindset is essential to Excel’s evolution. By embracing the global approach, we can tap into Excel’s full power and enhance our ability to manage, scale, and streamline business processes collaboratively.
Paper Flow vs. Data Flow: A Paradigm Shift.
Historically, the traditional mindset treats Excel like a digital sheet of paper–an approach that limits our capabilities. This mindset constrains us to a top-down process where only high-level summaries, like group-level trial balances become the financial statements for board reporting. Detailed insights are often missing, creating a disconnect between the summarized reports and the granular data available in ERP systems. This gap results from broad adjustments that are generally not reflected in ERP systems, as they aren’t processed through the ledger. Indeed they cannot be as they’re at a higher level of granularity.
On the other hand, exporting data directly from ERP systems into tools like Power BI allows access to the lowest level of detail. While this ensures authenticity (since numbers are drawn straight from the General Ledger), the issue arises from a lack of budget-holder review, leading to mistrust of the figures presented. When data is not reviewed and contextualized, trust erodes, making budgetary control and responsibility accounting feel ineffective.
The Flaws of Traditional Methods.
Both approaches–manual summary adjustments and direct ERP system exports–have inherent limitations. Traditional manual adjustments, common in previous decades, allowed flexibility in reporting but lacked transparency and accountability. However, with today’s stringent corporate governance and risk management frameworks, transparency is paramount. Internal audit requirements mean that any adjustments must be itemized and visible, ensuring an audit trail from ERP to reported numbers. Or rather, in reverse.
Thus, while the ERP system provides structure, it lacks flexibility. To bridge this, we need an Excel-based monthly accounting framework that includes an accounts review process. This framework enables a central database that allows for transparent, flexible reporting without compromising integrity.
The Power of Centralized Data and Spreadsheets.
The solution is both simple and powerful: a single spreadsheet template for each function, accessing a centrally located, structured dataset. This Global Excel Framework supports unlimited replications while ensuring that each spreadsheet operates from the same data source. Traditional methods fail to achieve this level of interconnectedness, as they rely on separate, local files rather than a centralized data hub.
Such a setup is far from alien. It mirrors the architecture we use in digital technologies worldwide–an approach that’s been mainstream for over 30 years. Despite Excel’s built-in capability to support this, a lack of understanding has prevented its widespread adoption. The shift from standalone to collaborative use, where data flows to and from a central hub, remains unexplored territory for many Excel users.
Excel Tables, Power Query, and Beyond: Rethinking Data Structures.
In the Excel community, tables are often hailed as essential for handling data, as they vastly improve functionality compared to basic cell ranges. However, for collaborative use, these tables need to be centrally located, not stored within individual files. This is a natural progression, leveraging existing Excel capabilities for data flow beyond single-user files.
Excel’s integration with tools like Power Query and Power Pivot emphasizes data flow in popular culture, but a more powerful technique already exists in Excel in achieving bidirectional data flow. This allows data to move seamlessly between spreadsheets and a central database, a capability already present in Excel since 30 years ago and first demonstrated by a young engineer named Satya Nadella (when he had hair).
Dynamic Arrays and Lambda Functions: Simplifying Complexity.
Excel’s dynamic arrays introduce a robust way to handle data, pulling dynamic results directly from Excel tables. But this is local. In contrast, data source in globally accessible tables ensures that all connected spreadsheets reflect real-time updates, enabling efficient collaboration.
Additionally, LAMBDA functions encapsulate reusable logic, allowing for consistency across multiple spreadsheets and simplifying management. But that too are limited as they only have local reach. Excel can leverage reusable logic stored and served from the central data hub in the Global Excel mindset. Or Hub-and-Spoke.
The Hub-and-Spoke Architecture: A Proven Model.
The hub-and-spoke architecture offers centralized control and consistency, much like an ERP system but without the rigidity and cost. In this setup, the central hub holds key logic and functions accessible to every connected spreadsheet. By centralizing logic, we reduce errors and streamline updates, ensuring all users access consistent, reliable data.
Conclusion: Embrace the Future, It’s Already Here.
The methods I’ve outlined are not alien. They’ve been part of Excel’s capabilities for decades, and their benefits have been widely promoted in the Excel professional community. The only difference now is the mindset shift–from standalone, single-user techniques to a global, interconnected approach. The real innovation lies in thinking beyond the local scope, recognizing the transformative value of integrating data and processes, and people, across the organization.
This change isn’t just theoretical. It’s a practical, tangible approach to unlocking Excel’s full potential in a way that’s scalable, reliable, and aligned with the demands of modern business. The value is real, and the future is already here–let’s embrace it.
This is a podcast by Hiran de Silva. Narrated by Bill.
Add comment