In this article, I will challenge the notion that certain Excel tasks—such as budgeting and consolidation—are “rocket science,” a claim often made by experts like Paul Barnhurst and Mark Proctor. The goal is to dismantle the idea that these processes are beyond the capabilities of the average Excel user. With Excel’s built-in features, even complex tasks can be completed efficiently. I’ll also address Annette de Young’s sentiment about skill dependency and the issue of “key man dependency” when utilizing native Excel functionalities.
Tackling the Myth of Rocket Science in Excel
Let’s start with a typical budgeting scenario: we have a budget template with 28 rows and 12 columns, each line item linked to an account code from the chart of accounts. This template is used by 400 operating units, each with its own set of numbers, necessitating consolidation.
Traditionally, consolidating data from 400 units into a single report would require extensive formulas and external links, making the process risky and difficult to manage, especially if changes are needed. Even with Power Query, consolidating data from these templates would be a batch process—unsuitable for real-time reviews by regional managers. This delay undermines a fundamental requirement of the budgeting process: instant, on-demand access to updated data.
The Limitations of Power Query for Real-Time Budgeting
Power Query involves placing files in a folder, reading them, and then processing the data in batches. While this approach is possible, it is cumbersome and does not cater to the dynamic nature of budgeting, where frequent updates may occur. Power Query’s batch-processing nature creates delays, making real-time data access difficult.
When budget reviews are required at multiple levels of management, it is crucial to provide immediate access to the latest data, without waiting for manual consolidation or batch processes. This is where native Excel features offer a superior solution.
An Alternative Approach: Using Native Excel Capabilities for Real-Time Budgeting
Consider an alternative solution using Excel’s built-in capabilities. We create a central table that mirrors the budget template but includes additional columns for the operating unit, username, and timestamp. This table aggregates data from all 400 units in one place, allowing budget holders to submit their data through a straightforward interface.
To upload data to the central table, we can utilize Excel’s built-in data access component. Here’s how it works:
- Uploading Data: When a budget holder updates their numbers, they click a button that uploads the 28 rows to the central table in milliseconds. The process involves inserting the new data and deleting the old data for that operating unit, ensuring the table is always up to date.
- Automating the Process: The process can be broken down into simple steps:
- Delete existing data for the given operating unit.
- Insert the new 28 rows into the central table. This procedure can be easily understood and executed with a single line of code to specify the database connection and the data to be uploaded.
- Pulling Data (Querying): Similarly, pulling data from the central table into Excel is straightforward. We specify the data to fetch (e.g., the 28 rows for a particular unit) and populate the budget template accordingly. This step can also be done with one line of code, iterating through rows to fill the corresponding fields in the template.
- Scalability and Flexibility: This approach enables real-time consolidation without external links and can instantly reflect updates across all units, making it superior to batch processing methods like Power Query. And it is scalable to 4,000 operating units if required.
Debunking the Notion of “Key Man Dependency”
Some argue that such approaches introduce “key man dependency,” where only a few people understand how to implement the solution. This is not the case with Excel’s native capabilities. Enabling the data access component is as simple as ticking a checkbox. The steps involved can be documented on half a sheet of paper; no specialized knowledge is required.
Even for those who worry about hidden know-how, once the process is explained, it becomes straightforward. Unlike proprietary add-ins or complicated Power Query configurations, this solution is based solely on Excel’s built-in features, minimizing dependency on any individual.
Real-Time Consolidation Without Rocket Science
To consolidate data from all 400 units, we can modify the query slightly. Instead of pulling data for a single unit, we aggregate data across all units using simple SQL statements, such as SUM and GROUP BY. This provides a live, consolidated view without any manual intervention or external links.
The result is a dynamic, scalable budgeting process that allows instant updates and consolidation without external dependencies. The complexity is abstracted by Excel, just like with formulas or Power Query, meaning users don’t need to write “massive amounts of code.” In fact, the solution can be encapsulated in one line of code.
Comparing Power Query and Native Excel Approaches
Let’s revisit why this native Excel solution may be preferable to Power Query:
- Availability: Native features are always present in Excel, without requiring third-party add-ins.
- Accessibility: The solution is easier to understand and maintain.
- Reusability: The approach scales better across various scenarios without complex setup or dependencies.
Conclusion: Is This Really Rocket Science?
The budgeting and consolidation processes described here are not beyond the capabilities of a typical Excel user. The claim that they require “rocket science” skills is misguided. What’s needed is a shift from single-user tasks to real-time, enterprise-level data management. Anyone who teaches Excel or uses it professionally can grasp these concepts with ease.
If Power Query can be taught to the average user, then this straightforward method of real-time data management using native Excel features is certainly within reach. It’s time to demystify these techniques and empower users to achieve more with the tools they already have.
Poll: Do you think the budgeting process outlined here is rocket science? Why do you think some experts like Paul Barnhurst and Mark Proctor claim it’s too difficult? How do you compare the scope, accessibility, and scalability of native Excel methods versus Power Query?
This is a podcast by Hiran de Silva. Narrated by Charlie.
Add comment