FLUID Excel Solutions
FLUID Excel Solutions are designed around one simple idea: let the data flow.
Data should move through an Excel solution as uninterrupted as possible—from its source, through any transformations and calculations, to wherever it ultimately needs to go.
Using Excel Tables, Power Query, the Data Model, Pivot Tables, Dynamic Array Functions, VBA, and other Excel 365 tools, much of that flow can happen automatically. Data can be imported, transformed, related, calculated, summarized, refreshed, and delivered without requiring a user to manually move it from one step to the next.
And often, the user is not needed at all.
If nothing needs to be typed, checked, or selected, there may be no reason for a user to be part of the process. The Excel solution can update automatically and allow the data to flow where it needs to go.
When user interaction is necessary, a FLUID solution minimizes it and automates as much of the remaining process as possible. What the user does should be intuitive, simple, and seamless.
Keep the data flowing. Remove unnecessary user interaction. When interaction is necessary, make it seamless.
That is a FLUID Excel Solution.
Let The Data Flow
External Data Sources
A FLUID Excel Solution begins where the data begins.
If the data already exists somewhere, connect to it rather than asking a user to manually retrieve, re-enter, copy, or paste it. The source might be a database, another Excel workbook, a CSV or text file, SharePoint, a folder of files, a web service, an API, or another business system.
The goal is simple: connect to the source and begin the flow as close to the data as possible.
Power Query
Once connected to the source, Power Query keeps the data moving.
Power Query can clean, filter, combine, merge, append, and reshape data before loading it where it needs to go. The steps used to transform the data are saved as a repeatable process rather than performed manually each time.
When the source data changes, refresh the query and the same transformations are applied again automatically. No copying, pasting, or rebuilding the process.
Connect once. Define the process. Refresh the data. Let it flow.
Onsheet or Offsheet?
Once Power Query has connected to and transformed the data, the next question is: where does the data need to go?
Not all data needs to be loaded onto a worksheet. A query can load to an Excel Table when the data needs to be visible or used on the sheet, load to the Data Model when relationships and analysis are needed, or remain as a connection that feeds other queries and processes.
In a FLUID Excel Solution, data goes where it is needed—not automatically to a worksheet simply because we are using Excel.
Onsheet when it needs to be. Offsheet when it doesn’t.
Power Query Tables Onsheet
Sometimes the data needs to come back to the worksheet. When it does, a Power Query output Table can serve several different purposes in a FLUID Excel Solution.
The Table may be the finished report. Power Query retrieves and transforms the data, loads it to an Excel Table, and refreshes that Table when the source data changes. Nothing else needs to be added simply because more Excel tools are available.
And sometimes the Table becomes something very different: a form.
Load the data where it needs to go. Add another layer only when the solution needs it.
Power Query Table as a Form
Sometimes a Power Query Table loaded to the worksheet becomes more than an output. It becomes the user interface.
The Table can be designed so the user provides only the information that requires human interaction—something that needs to be typed, checked, or selected.
The Power Query Table can then become a source for Power Query again. Power Query reads the updated Table, incorporates the user’s input, and continues the data flow through the solution.
This creates a self-referencing process: Power Query loads the data to the worksheet, the user provides only the information that requires human interaction, and the Table flows back into Power Query.
Once the required human interaction is complete, automation takes over again.
Bring the user into the flow only when the process requires a user.
Pivot Tables from Power Query
When the final requirement is a Pivot Table, there is usually no reason to first load the Power Query results to an Excel Table on the worksheet.
Power Query can prepare the data and feed the Pivot Table directly. The supporting data remains offsheet, while the Pivot Table presents the summarized information the user actually needs.
Loading the same data to a worksheet Table first adds an unnecessary step unless that Table has another purpose in the solution.
If the Table itself is a report, a form, or something the user needs to work with, load it to the sheet. Otherwise, keep the supporting data offsheet and let it flow directly to the Pivot Table.
Do not put data on the worksheet simply to move it somewhere else.
Data Model – BIG Data
Not every FLUID Excel Solution needs the Data Model. Use it when the solution requires it.
For many solutions, Power Query can transform the data and load it directly to an Excel Table for reporting and analysis. There is no reason to add another layer when it is not needed.
The Data Model becomes especially valuable when working with large amounts of data that do not need to be loaded onto a worksheet, or when Power Pivot and Pivot Tables need to analyze data across multiple related tables.
In those cases, related tables can remain separate and be connected through relationships rather than being flattened into one large table or filled with worksheet lookups.
Use the Data Model when the data requires it—not simply because it is available.
Power Pivot – Multiple Related Tables
When analysis needs to work across multiple related tables, Power Pivot extends the FLUID solution beyond a traditional single-table Pivot Table.
Tables loaded to the Data Model can be connected through relationships and analyzed together without first combining all of the data into one large worksheet Table.
Measures and calculations can be created in the model and used by Pivot Tables to analyze the related data. This keeps the supporting data and calculation logic offsheet while allowing the results to appear where they are needed.
The result is another path through the same FLUID architecture: Power Query prepares the data, the Data Model relates it, Power Pivot analyzes it, and Pivot Tables present the results.
Add relational power when the analysis requires it.
Dashboards & Final Output
Eventually, the different paths through a FLUID Excel Solution can come together in the final output.
A dashboard can use Pivot Tables and Pivot Charts based on Power Query output Tables, Pivot Tables and Pivot Charts based on the Data Model and Power Pivot, Excel Tables, charts, slicers, and other reporting elements.
The important point is that the dashboard is not where the process begins. It is where the results of the data flow become useful to the business.
When the source data changes, Power Query can refresh, Tables can update, the Data Model can update, Pivot Tables can refresh, and the dashboard can reflect the new results without someone manually rebuilding the reporting process.
If a user needs to explore the results, the interaction can be intuitive and focused through slicers and other controls. If no interaction is required, the refreshed dashboard can simply deliver the information.
Data comes in. Data flows through the solution. Information comes out.
Automation – VBA
Once the data is flowing and the reporting structure is in place, the next question is: what still requires someone to do something?
VBA can automate many of the actions surrounding a FLUID Excel Solution. It can refresh queries, update Pivot Tables, respond to worksheet events, control what a user can change, save or create files, determine what prints, and perform other tasks that would otherwise require manual steps.
VBA does not need to replace Power Query, the Data Model, or other Excel tools. Each tool should do the job it is best suited to do. Power Query manages the data flow; VBA can automate the actions around that flow.
When user interaction is required, VBA can also make that interaction simpler. A user may make one selection or check one box while VBA handles everything that needs to happen afterward.
Automate the process. Minimize the interaction. Keep the data flowing.
Office Scripts & Power Automate
Sometimes the next step in a FLUID Excel Solution needs to happen without someone opening the workbook at all.
Office Scripts can automate Excel tasks in the Microsoft 365 environment, while Power Automate can connect those tasks to events, schedules, files, emails, SharePoint, and other business processes.
A process can be triggered because a file arrives, a scheduled time is reached, data changes, or another business event occurs. Office Scripts can perform the Excel-specific work while Power Automate manages the larger workflow around it.
This allows the FLUID concept to extend beyond the workbook. Excel can become one component in a larger automated business process rather than the place where a user must begin every process.
Use VBA when it is the right tool inside desktop Excel. Use Office Scripts and Power Automate when the process needs to extend into Microsoft 365 and automated workflows.
The process does not have to wait for a user to open Excel.
SharePoint – Connect the Solution
A FLUID Excel Solution does not have to begin and end inside a single workbook. Sometimes the data and files need to be shared, centralized, and available to a larger business process.
SharePoint can provide a central location for Excel files, data, documents, and other resources used by the solution. Power Query can connect to data stored in SharePoint, while Power Automate can move files, trigger processes, send notifications, and connect Excel to other parts of the organization.
This allows the data flow to continue beyond an individual user’s computer. Files and data can be available to the people and processes that need them without relying on someone to manually move, email, or distribute them.
SharePoint can also become part of the automation itself. A file arriving in a SharePoint location can trigger a process, become a new data source, or provide the next step in a larger workflow.
Centralize the data. Connect the processes. Keep the solution moving.
Dynamic Array Functions & On-sheet Functions
Sometimes the right place to perform a calculation is on the worksheet.
Dynamic Array Functions such as FILTER can return changing sets of data from a single formula, allowing results to expand and contract automatically as the underlying data changes. Other modern Excel functions can calculate, combine, summarize, and manipulate data directly on the sheet when that is the appropriate place for the work to happen.
But in a FLUID Excel Solution, an on-sheet formula is not automatically the starting point. First ask whether Power Query, the Data Model, a Pivot Table, VBA, or another Excel technology is better suited to the requirement.
If a worksheet function is the right tool, use it. If the work belongs somewhere else, let another part of Excel do the work and return only the result that is needed.
That is FLUID
A FLUID Excel Solution starts with the data and follows it through the entire business process.
Connect to the data where it already exists. Use Power Query to move and transform it. Keep data offsheet when it does not need to be seen. Load it to a Table when the Table has a purpose. Use the Data Model and Power Pivot when the size or relationships require them. Use Pivot Tables and dashboards to turn the data into useful information. Use VBA, Office Scripts, Power Automate, and SharePoint to automate and extend the process. Use worksheet functions when the worksheet is the right place to perform the work.
At every step, ask the same questions: Where does the data need to go? What is the right tool for the job? Does a user actually need to be involved?
If nothing needs to be typed, checked, or selected, there may be no reason for a user to be part of the process. When human interaction is required, minimize it, make it intuitive, and automate everything around it that can be automated.
Follow the data. Choose the right tool. Automate what can be automated. Involve the user only when the process requires a user.
That is a FLUID Excel Solution.
Have an Excel process that needs a better way to flow?
Let’s talk about your project and how we can build a FLUID Excel Solution around your business process.
That’s a great summary of Excel’s latest features. It’s certainly a good time to be working in this space.
For larger projects you probably still use the traditional approaches. But for smaller projects these new features can simplify things significantly, which leads to easier builds and easier maintenance.
And thanks for the shout out to Excel Off The Grid. 😁
Really enjoyed this, Chris[topher] — you nailed it. Excel has become fluid, and it’s no longer enough to rely only on the old-school techniques. Staying sharp now means constantly learning, practicing, and adapting with tools like Tables, Power Query, and dynamic arrays.
For me, the real power comes from blending both worlds: the reliability of old-school methods with the scalability of new-school approaches. That’s where true Excel expertise shines today.
Fluid means flexible, and you’re spot on with your take on Excel, Christopher.
These new tools mean Excel lets you work with concepts rather than cells. Excel is a true programming environment. Tables and Power Query keep your inputs organized. Dynamic arrays and dynamic pivot tables(!) allow your output to automatically shrink and grow as your inputs change. And, even better, many old-school functions take advantage of these new tools, so we old hands can leverage our decades of experience in new ways.
Excel has changed a lot over the years. I remember using v4, when Microsoft introduced VBA for the first time. It was a game changer for me.
I am not sure about SharePoint, which I consider to be the Vista of Office, but the new fluid functionality you describe like pivotby, tables, dynamic array functions and power query are fantastic new additions to the application and you describe their uses very insightfully.
Long may Microsoft continue adding new, useful functionality.