Excel Beyond the CELL
Excel is built on cells. We don’t build solutions around them.
At Excel and Access, LLC, we build Excel 365 solutions around the business process, the data and the result—not around individual worksheet cells.
Our background in Excel, Access, SQL Server and database programming, together with Microsoft Excel MVP and Access MVP recognition, shapes the way we approach an Excel project.
We don’t look at a workbook and see billions of cells waiting to be filled.
We see Tables. Objects. Blocks. Spill Ranges. Queries. Relationships. Processes. Outputs.
The cell is still important. We use cells all the time.
We just don’t assume the cell is where the programming has to happen.
The Cell Is Part of the Solution
A cell can hold an input.
A cell can receive an output.
It can contain a formula, a checkbox or a selection from a Data Validation list. It can have Conditional Formatting, be a Named Range or trigger VBA code when something changes.
There can be a lot more happening with a cell than the value you see on the screen.
But there is also a lot that doesn’t have to happen in the cell at all.
For example, a user might make a selection in a worksheet cell. That change can trigger VBA. VBA can bring the required data into memory, perform the work and return the result to the worksheet.
The user sees the input and the result.
The programming happened somewhere else.
The same principle applies to Power Query. The data can be connected, cleaned and transformed completely offsheet. Only the result needs to appear on the worksheet—and sometimes even the result doesn’t need to be there.
The cell can be the input or the output without being where the work happens.
Start With the Requirement
A lot of Excel development starts with a question like:
“What formula should go in this cell?”
We prefer to start one step earlier:
“Where should this happen?”
Maybe a worksheet formula is exactly the right answer.
Maybe VBA should do the work in memory.
Maybe Power Query should transform the data before it ever reaches the worksheet.
Maybe the data belongs in the Data Model.
Maybe the process should happen completely offsheet.
The business requirement determines the architecture.
The cell doesn’t.
SPILL Changes the Conversation
Excel 365 gives us another reason to stop thinking one cell at a time.
A formula can start in one cell and return an entire dynamic result.
SPILL.
=FILTER(tblSales,tblSales[State]="CA")One formula. One starting cell. An entire result set.
And we can work with that result as a block:
Sales#That # matters.
Instead of managing every cell inside the result, we can work with the Spill Range.
Functions such as FILTER(), LET(), LAMBDA(), GROUPBY() and PIVOTBY() continue that shift.
When a calculation belongs onsheet, we can increasingly think in arrays and blocks instead of individual formulas copied through thousands of cells.
The formula may start in a cell.
The result doesn’t have to end there.
Think in Objects and Blocks
The same idea extends beyond formulas.
We work with Tables, ranges, Spill Ranges, queries, Pivot Tables, Data Models and other Excel objects as building blocks.
Take an Excel Table.
Instead of building around:
D2:D50000we can build around:
tblSales[Amount]That is more than a different way to write a reference.
Amount belongs to Sales.
Now we’re describing the data instead of simply describing where it happens to be sitting on a worksheet.
And a Table doesn’t have to be just a place to store data.
It can receive a Power Query result. It can feed another query. It can become part of a report. It can provide structured input to VBA.
We can even use a Table as a data manipulation form, allowing users to type, select and check information that becomes part of the process.
One object. Multiple roles.
Onsheet or Offsheet?
This becomes a design decision instead of an assumption.
If the user needs to view the data, enter information, choose an option, check a box or work with the result, then the worksheet may be exactly where that part of the solution belongs.
We build onsheet solutions intentionally.
But if the user doesn’t need the supporting data, why put it there?
Power Query can connect, clean, transform, merge and append data without using the worksheet as an intermediate storage area.
A query feeding a Pivot Table doesn’t necessarily need to load the same data to a worksheet first.
Related datasets can remain offsheet in the Excel Data Model.
VBA can perform work in memory and return only what the user needs.
Onsheet is a design decision. Offsheet is a design decision.
Put the data and the programming where they belong.
Power Query: The Work Doesn’t Have to Be Onsheet
If the data already exists somewhere, our first question isn’t:
“Where should we paste it?”
It’s:
“Why are we pasting it at all?”
Power Query is a foundation of the solutions we build.
Connect. Clean. Transform. Merge. Append. Refresh.
M-Code gives us the programming foundation behind those transformations.
Then we decide where the result belongs.
Maybe it belongs in an Excel Table.
Maybe it belongs in a Pivot Table.
Maybe it belongs in the Data Model.
Maybe it feeds another process and never needs to appear on a worksheet.
We don’t put data on a worksheet just so we can move it somewhere else.
Relationships Over Repetition
Our database background also changes how we think about Excel data.
We don’t automatically flatten every business entity into one enormous worksheet Table.
Customers can remain Customers. Vendors can remain Vendors. Departments can remain Departments. Transactions can remain Transactions.
Then we relate them.
The Excel Data Model allows us to bring relational database principles into an Excel solution.
When the requirement calls for it, we can use keys, relationships, Composite Primary Keys, Power Pivot and DAX.
Joins over lookups.
Relationships over repetition.
The User Doesn’t Have to Be the Process
Not every Excel process requires somebody sitting in front of the workbook.
Sometimes user interaction is important. Sometimes it isn’t.
Automation is the accumulation of a variety of techniques.
We use the tools that fit the requirement: Power Query, M-Code, VBA, Office Scripts, Power Automate, the Data Model, DAX and on-sheet functions.
Sometimes the user should work directly with a Table, Pivot Table, dashboard or form.
Other times the solution can refresh, calculate, create, save or deliver the result behind the scenes.
The user can be part of the process without becoming the process.
Now Add FLUID
This is where Excel Beyond the CELL connects with FLUID Excel Solutions for Business.
They are related, but they aren’t the same thing.
Beyond the CELL is a shift in how we design the solution.
We stop assuming the individual worksheet cell has to be the center of the architecture and decide where each part of the process actually belongs.
FLUID is about how the data moves through that design.
Data can come from an external source. Power Query can transform it. VBA can work with it in memory. A Table can provide user interaction. A Spill Range can return a dynamic result. Supporting data can remain offsheet. The Data Model can establish relationships. Pivot Tables and dashboards can present the result. Automation can bring a variety of these techniques together so the process runs with fewer manual steps.
The right data. In the right place. Using the right tool.
That’s our approach.
Go Beyond the CELL
We’re not trying to eliminate the cell.
We’re eliminating the assumption that the cell has to be the center of the solution.
Use the cell when the cell is the right tool.
Use a Spill Range when one formula can return the block.
Use a Table when the data needs structure.
Use Power Query when the data needs transformation.
Use the Data Model when the problem is relational.
Use VBA when the work belongs in code.
Use automation when the process doesn’t need a person.
Don’t start with the cell.
Start with the business requirement.
The CELL isn’t the limitation.
Thinking only in cells is.
Go Beyond the CELL.
Let the Data Flow.
FLUID Excel Solutions for Business.
Excel and Access, LLC
Microsoft Excel and Access Experts
Microsoft MVP
Have an Excel project that needs a different approach?
Let’s talk about your project and how we can build a smarter Excel solution around your business process.
Call Excel and Access, LLC:
877-392-3539
|
Contact Us for a Free Consultation
Leave a Reply