In a recent article, we explored the concept of separating data from the analysis or processing that takes place after extracting data into another environment, traditionally a spreadsheet. The spreadsheet, in this case, serves as a mechanism to extract, interrogate, and process data. The ideal outcome of this process should be to trigger changes in corporate data–data that is accessible, up-to-date, and capable of participating in other business processes. This interaction forms a key element in how business processes operate effectively.
The Need for Data Flow Integration.
Consider the example of a call handler process. When stock availability data is updated, this change directly impacts stock availability indicators on the corporate call handler spreadsheets. The update drives a sequence of cascading drop-downs for the call handlers, enabling them to manage sales effectively. When a sale is made, this becomes a new entry in the corporate database, which triggers subsequent processes such as dispatch and delivery. Once delivery is complete, the data is updated to confirm the transaction, thus closing the loop on the process.
This cycle of extracting, updating, and triggering business processes exemplifies the importance of real-time data integration. If stock is dispatched, the inventory levels are adjusted accordingly, and when inventory reaches a certain threshold, a reorder is triggered. This automated chain of processes relies on the seamless interaction between spreadsheets and corporate data systems.
The goal is to establish that the purpose of this cycle of interconnected processes is to update corporate data in real-time, triggering further corporate processes. This should be a fundamental aspect of business data management.
Training and Content Gaps in Bidirectional Data Flow.
Ideally, training, teaching, and content around data interaction between corporate systems and spreadsheets would cover both the extraction and the updating of data. This setup can be likened to a client-server model, where various satellite applications not only source data but also consume and update it. However, a peculiar trend emerges when examining social media content on this topic: there is an overwhelming focus on data flowing from external systems into spreadsheets, while very little content addresses the reverse process–updating corporate data from spreadsheets.
One of the most powerful tools facilitating data import into spreadsheets is Power Query. Countless training courses and YouTube videos highlight how data can be imported and processed using dynamic array formulas and other Excel functions. The ability to import, analyze, and manipulate data within the Excel ecosystem is impressive. Yet, there is an alarming lack of discussion about the other crucial part of the process–updating centralized corporate data from these spreadsheets.
The Need for Centralized Corporate Data Updates.
It is strange that so little attention is paid to updating corporate data, given its critical role in ensuring business processes function efficiently. Imagine a diagram where corporate data sits at the center, with arrows representing data flow. There would be numerous arrows pointing from corporate data into spreadsheets, but hardly any illustrating the reverse flow. This imbalance was highlighted in a discussion shared by Nelson Mwangi, who pointed out the necessity of addressing this issue. It is not just useful–it is essential.
If data is only flowing into spreadsheets without being updated back to a central database, then a parallel, isolated ecosystem of corporate data is being created within the spreadsheet realm. This phenomenon, documented as far back as 25 years ago, leads to inefficiencies where crucial business processes are impaired because updated information resides only in individual spreadsheets, not in the centralized systems that drive corporate functions.
Consider again the earlier example of the call handler process. If stock data updates are not connected to the call handler’s cascading drop-downs, there is no real-time reflection of stock availability. Similarly, if sales are not immediately logged in the corporate system, the stock levels will be inaccurate, impacting the entire supply chain. These examples illustrate the need for seamless bidirectional data flow to keep corporate data accurate and responsive to business activities.
The Issue with Isolated Spreadsheet Ecosystems.
A recent article published on October 15, 2024, in *The Economist* titled “Why Microsoft Excel Won’t Die” covered various aspects of Excel’s enduring relevance. One of the criticisms mentioned was the creation of alternative ecosystems of crucial corporate data within spreadsheets. Historically, spreadsheets have allowed users to develop custom solutions, but the data captured in these solutions often remains siloed, preventing efficient updates to centralized corporate databases.
For instance, in the call handler example, if stock data sits in 70 different spreadsheets across 20 warehouses and 50 call handlers, each potentially being updated manually via daily spreadsheets, the system becomes chaotic and unreliable. This manual, fragmented approach leads to delays and inaccuracies, disrupting downstream processes like dispatch and delivery.
The Current Social Media Landscape on Data Flow.
Interestingly, almost all of the content across training courses and social media posts focuses on the flow of data from external systems into the spreadsheet realm. Whether it’s through CSV files, external databases, or other spreadsheets, the conversation centers around data import. There is also significant emphasis on sharing data within the spreadsheet environment, often manually, via email. This manual process is inherently inefficient and prone to errors.
To address these inefficiencies, it is imperative that the focus expands to include content and training on updating centralized corporate data. It should be as common, seamless, and straightforward as the data import process. Without this, organizations will continue to rely on manual, outdated processes that hinder overall business efficiency.
Real-World Examples of Efficient Data Integration.
I can draw from my own experiences, having implemented solutions across four industries where clients recognized the value of integrating real-time data updates into their corporate systems. These implementations not only streamlined business processes but also led to a threefold increase in my pay rate as a consultant. The increased pay reflected the significant value these solutions brought to the businesses, by ensuring corporate data was up-to-date, thus enabling smooth and efficient operations.
For example, my recent project with Castairs highlighted the transition from a manual, paper-based system – that is, sending physical spreadsheets for collaboration – to a structured data flow model – where no spreadsheets are sent or received. In this project, the system was initially reliant on physical spreadsheets being shared around, leading to inefficiencies and errors. By implementing a seamless, automated process where data could flow between spreadsheets and a centralized database, we were able to bring order and clarity to what was previously a chaotic system. This transformation from a paper flow to a data flow environment allowed the business to operate more effectively, minimizing manual intervention and reducing the risk of errors.
Conclusion: Addressing the Imbalance in Data Flow.
The key message here is that data must be allowed to flow in both directions–to and from the spreadsheet realm. Currently, the focus is disproportionately on data import, but organizations need to realize that effective data management requires the ability to update corporate data seamlessly. Only then can businesses move away from inefficient, manual processes towards streamlined, automated workflows that enhance productivity and reliability.
To summarize, the crucial shift from a “paper flow” environment to a “data flow” environment allows businesses to operate with greater efficiency and accuracy. This transition ensures that data remains centralized, organized, and reliable, reducing reliance on isolated manual processes and enabling better decision-making.
Let us recognize this imbalance and address it, so that the immense potential of spreadsheets is fully realized–not just as a tool for importing and analyzing data, but as a dynamic component of an integrated, real-time business system.
This is s a podcast by Hiran de Silva. Narrated by Bill.
Add comment