If you were asked, even a few years ago, where Excel formulas obtained their data, the answer would have been remarkably straightforward. They calculated values that already existed within the workbook. Whether those values had been entered manually, imported from another system, or generated by previous calculations, the worksheet itself represented the boundary of the formula engine. Everything outside that boundary belonged to a different part of the application. External databases were queried through connections. Web services required Power Query or VBA. Files were imported through dedicated tools. Formulas, for the most part, simply consumed the results.

That distinction is beginning to soften. Modern Excel is steadily moving toward a model in which the worksheet is no longer merely the final stage of a data pipeline. Increasingly, it is becoming an active participant in acquiring, transforming and analysing information from outside the workbook itself. While Power Query remains the preferred solution for many large-scale extraction and transformation tasks, the capabilities available directly within formulas continue to expand, allowing workbooks to become more dynamic than ever before.

Understanding this evolution is important because it changes how we think about automation. Rather than asking how to import data into Excel before working with it, we can increasingly ask how Excel itself can remain connected to information that is constantly changing.

The Workbook Is No Longer an Island

Traditional spreadsheet design assumes that data arrives in batches. A report is exported from an ERP system, downloaded from a website, or copied from another workbook. Once imported, the workbook performs its calculations, produces its reports, and waits for the next update. This workflow has served organisations well for decades, but it reflects a world in which data moved relatively slowly. Modern businesses rarely operate that way.

Cloud applications expose APIs. SharePoint lists update continuously. SQL databases change throughout the day. Online services publish live information that may be refreshed every few minutes. The value of a workbook increasingly depends not simply on its calculations, but on its ability to remain connected to these evolving sources.

Excel has gradually adapted to this reality.

Dynamic Data Has Become the Norm

Many users already encounter dynamic data without consciously thinking about it.

A workbook connected to a SQL Server database refreshes itself each morning. A Power Query retrieves the latest transactions from a folder. A stock price updates automatically. A linked SharePoint list reflects changes made by colleagues elsewhere in the organisation. In each of these examples, the worksheet is no longer analysing static information. It is responding to data that exists beyond its own boundaries. This distinction may appear subtle, but it fundamentally changes the role of the workbook. Rather than representing a snapshot in time, it becomes a living model whose outputs evolve alongside the systems from which the information originates.

Formulas Continue to Expand Their Reach

Although Power Query remains the principal tool for acquiring external information, Microsoft has steadily introduced functions that allow formulas themselves to participate more directly in connected workflows. The arrival of dynamic arrays demonstrated that formulas could return datasets rather than individual values. More recent developments have continued this trend by allowing formulas to interact more naturally with linked data types and cloud-connected information.

The significance of this development lies less in any individual function than in the overall direction of travel. The formula language is becoming increasingly capable of operating on information that is not confined to the worksheet grid. Instead of viewing external data as something that must first be imported and normalised, Excel increasingly treats it as another source from which calculations can be derived.

Connected Data Changes Workbook Design

As external information becomes easier to consume, workbook architecture begins to change.

Historically, much of a workbook's complexity arose from the need to prepare imported data before calculations could begin. Staging worksheets, intermediate tables, manual refresh procedures, and supporting macros often existed simply to bridge the gap between the source system and the analytical model.

Modern Excel encourages a different approach. The data acquisition layer becomes more reliable and more automated, allowing the workbook itself to focus primarily on transformation and analysis. Dynamic arrays, FILTER, SORT, LET, and LAMBDA then operate on continuously refreshed information rather than static extracts. The analytical model becomes increasingly independent of the mechanics of data acquisition.

Automation Is Becoming Declarative

This evolution mirrors a broader trend that extends well beyond Excel. Traditional automation tends to be procedural. A sequence of steps is defined explicitly: open a file, import the contents, clean the data, perform calculations, generate a report, and save the output. Modern automation increasingly favours declarative models.

Rather than describing each individual step, we define relationships. This report depends upon this dataset. This calculation depends upon these records. When the source changes, the outputs recalculate automatically.

Excel's formula engine fits naturally within this philosophy. As more data becomes available dynamically, formulas become less concerned with how information arrived and more concerned with what should be done with it.

Where VBA Still Fits

This evolution should not be mistaken for the disappearance of procedural automation. There remain countless scenarios in which VBA provides capabilities that formulas cannot approach. Authenticating against external systems, processing files, coordinating workflows, communicating with other Office applications, and responding to user interaction all remain firmly within the domain of code. What has changed is the division of responsibility.

Increasingly, VBA performs the orchestration while formulas perform the analysis. A macro might retrieve information from several external systems, refresh Power Query connections, and trigger recalculation. Once the data is available, however, the workbook's logic can remain almost entirely within the worksheet itself.

This separation often produces cleaner solutions because each component focuses on the tasks for which it is best suited.

Building Workbooks That Age Gracefully

One of the less obvious benefits of connected data is longevity. Workbooks built around manually imported reports often accumulate maintenance overhead over time. File locations change, report formats evolve, and manual preparation steps become increasingly error-prone. By contrast, workbooks designed around stable connections and dynamic transformations tend to adapt more naturally as requirements evolve.

The analytical logic remains largely unchanged because it operates on structured datasets rather than on manually prepared extracts. Improvements to the data acquisition process rarely require substantial changes to the calculations themselves. This separation between acquiring data and analysing it is one of the hallmarks of well-designed analytical systems.

Thinking Like a Data Platform

Taken together, the developments explored throughout this series point toward a broader conclusion. Excel is gradually evolving beyond the role of a traditional spreadsheet application.

Dynamic arrays encourage us to think in datasets rather than individual cells. LAMBDA introduces reusable functions. Higher-order functions allow logic to be applied systematically across collections of data. Modern text functions simplify transformation. External connectivity ensures that those datasets remain current. The workbook increasingly resembles a lightweight analytical platform, capable of receiving information, transforming it, applying business logic, and presenting meaningful results with remarkably little procedural intervention.

This does not diminish the importance of VBA, Power Query, or external databases. On the contrary, it strengthens the relationship between them by allowing each technology to contribute according to its strengths.

Looking Beyond the Grid

Perhaps the most remarkable aspect of modern Excel is not any individual feature, but the cumulative effect of them all.

Each addition, taken in isolation, appears incremental. Dynamic arrays remove the need to copy formulas. LET improves readability. LAMBDA introduces reusable logic. TEXTSPLIT simplifies parsing. Connected data reduces manual imports. Viewed together, however, they represent something much larger.

The spreadsheet is no longer merely a collection of independent calculations. It has become an environment for modelling, transforming and analysing information in ways that would once have required substantial amounts of procedural code.

For those of us who have spent years developing Excel solutions, this is perhaps the most important lesson of all. The future of Excel is unlikely to belong exclusively to formulas, nor exclusively to VBA. It will belong to developers who understand both, appreciate where each excels, and are able to combine them into solutions that are simpler, cleaner and more maintainable than either could achieve alone.

Bringing External Data into Excel Formulas