The Fast Excel Development Method was built because Excel couldn’t do what we needed. Dynamic ranges had to be created with OFFSET formulas. Adding column calculations to tables meant we had to develop VBA macros to paste down and replace with values. Connecting to data meant ODBC drivers dedicated to particular databases or importing/exporting text files. Automating a processing flow? Pure VBA.
Over 25 years, Microsoft has slowly caught up. Excel Tables, dynamic arrays, Power Query, structured references — many of the things we pioneered are now native to the product.
But “caught up” does not mean “replaced.”
What it means is that we now have more tools in the box. And the real skill, as always, lies in knowing what tool to use and when. This article maps the territory — what has changed, what still holds, and where we are headed as we modernise the Fast Excel Development Method for the next generation of planning systems.
This is the first in a series. We will survey the landscape here, and then go deeper into each area in follow-on articles.
This webinar covered some of the topics here with examples: Fast Excel vs Modern Excel Webinar Recording (60 mins)
Tables and Ranges: The Foundation Has Shifted
If you bring in a dataset through Power Query — say, PS Cycles sales orders from a SharePoint folder — it arrives as an Excel Table object. Automatically. It gets a name, like SalesOrders, and you can refer to columns with structured references: SalesOrders[ItemCode].
This is significant. Structured references are inherently dynamic. They grow and shrink with the data. And that was the entire purpose of the OFFSET-based dynamic named ranges that the Fast Excel Development Method has relied upon since around 2000.
When you type =COUNTA(SalesOrders[ItemCode]), you get the same result as the old =COUNTA(SalesOrders_Data_ItemCode) dynamic named range. No Ctrl-Shift-D required. Name Manager. No OFFSET formula. All the _Data and _Table named ranges could be replaced by referring to the name of the table or a column within it.
The # operator takes this further. If you have a formula that returns a dynamic array — say, =UNIQUE(SalesOrder[ItemCode]) you will — referencing that cell with # gives you the entire spilled range. This is really helpful. Combined with SEQUENCE, SORT, FILTER, and UNIQUE, you can replicate operations that previously needed PivotTables or VBA pastedowns, all in live formulas that update instantly.
Note, the example above has multiple tables on the same sheet and breaks one of the cardinal rules! I do it as a convenience to show you.
So, for example, in a recent webinar I showed how to duplicate an entire table with =Load# — headers and all. Or extract a unique item list with =UNIQUE(Load[ItemCode]) instead of creating a pivot table. These are things that used to require the template. Now they are built into Excel.
Here is the thing, though. If you combine Power Query with legacy formulas, and you don’t get an automatic expansion of the Table to the new columns, you hit a problem.
Power Query loads data as a Table object. You then want to add calculated columns alongside — maybe an INDEX/MATCH from the routing table, or a runtime calculation. The default setting will automatically expand the table. But if it doesn’t and the formatting will tell you, looking like the example above without the blank columns in between. Those formula columns sit outside the Table boundary. You end up with a sheet that is half Table object, half ordinary range. And the FEDT automation functions — CreateNames, PasteDown — expect the FEDM conventions: headings in row 10, formulas in row 8, continuous data below. They do not work on Table objects.
The workaround today is to expand the Table to include the new columns — right-click, Resize — after which it auto-expands for new rows. But it is an inelegant bridge between two worlds. A proper modernisation would address this at the template level. That is work we intend to do.
Excel as a Programming Language
There is something else worth pausing on. With the introduction of LET, LAMBDA, and MAP, Excel formulas are now technically Turing complete. What that means, in plain language, is that you can write any computable algorithm using nothing but Excel formulas. You can define variables, create reusable functions, and iterate through collections — the building blocks of a proper programming language.
LET allows you to define named intermediate values within a formula, so instead of repeating the same calculation four times, you calculate it once and reuse the result. LAMBDA lets you create your own functions — proper, named, reusable functions — without VBA. And MAP applies a function across every element in an array, which is essentially a loop.
Put these together and you can write some quite complex logic in a single cell. In the forecasting and demand modeling article I used a workbook with pure dynamic array formulas (download it here: Signal_and_Noise.xlsx) there is a MAP formula that iterates through 208 periods of demand history, calculates a moving average and tracks seasonal factors — all live, updating instantly when you change a parameter.
The catch is readability. These formulas can get dense. They start to look more like programming code than a simple formula, and debugging them is harder than stepping through VBA or reading M code step by step. But the trade-off is attractive: no VBA, no macros to enable, no external dependencies, and it works in the browser. For interactive tools and what-if analysis, this is a genuine alternative to the paste-down method.
Data Sources: From C:\ to the Cloud
In the original method, all data lived as tab-delimited text files in a folder like C:\Production_Scheduling\Data. The path was hardcoded because it was universal. Every Windows machine has a C: drive. If I build a tool and send it to a client or colleague, the queries will not break because they point to the same location, even on a different machine.
That was the theory. The reality in 2026 is that Windows policies are making it harder and harder to access the root of the C: drive. The C: drive has all your program files, your Windows system files. Systems admins do not want people creating folders there. It is becoming increasingly locked down, particularly in enterprise environments.
So, in the development template v4.4, we built a PARA_User_Folder parameter. It dynamically resolves your user folder — C:\Users\{your username}\ — every time you open the workbook. You concatenate it with your project folder, and now you have a data path that works for whoever opens the file. You can test this by opening the latest development template and checking this value.
I personally prefer to keep my documents on a separate partition or physical drive from Windows, so I would point this to a different location entirely. Either way, the mechanism is flexible. This works. But it is still a local file approach. The real step forward is to bypass local files entirely.
In the webinar, I demonstrated importing data directly from a SharePoint folder. Get Data → From SharePoint Folder → enter the site URL → select the file → transform and load. No text files on disk. No C: drive. And because the workbook and data are both in SharePoint, users can refresh it in a browser without even having Excel installed. It works really well if all your users are on the same Microsoft domain.
There are trade-offs. SharePoint presents a flat file listing — you can see all files from the site regardless of folder, which can be confusing if you have files with the same name in different folders. You need to check the folder path column to tell them apart. It is a bit of a flatter arrangement than a normal file system. OneDrive sync folders carry latency risks, and Microsoft’s sync engine can block uploads during processing. So connecting directly to a OneDrive or SharePoint-synced folder has risks.
The emerging frontier is APIs. We have been working with an ERP system, Microsoft Dynamics 365, populating an entire instance through REST APIs and custom templates. Soon, I will demonstrate full data integration between Excel and the source ERP and all examples will have a live link to a demo ERP instance. I chose Dynamics 365 because of it’s relative accessibility, but once we prove the concept, it can work for any ERP.
Power Query’s Web connector can pull from OData endpoints and database replicas. This is the path toward connecting to the actual source of truth — or to supermarkets and mirrors replicated from production databases. Data integration and APIs make this kind of connection much more accessible now than it was even a few years ago. The world of cloud data has opened up considerably.
Beyond VBA: The Automation Crossroads
This is the thorniest part of modernisation.
The entire FEDM automation framework — CreateRunAllAuto, the no-code directives, RunAllAuto processing sheets left to right — is VBA. It works brilliantly. VBA is the reliable workhorse of Excel automation and is used by literally millions of companies around the world. It is going to be around for a long time.
However, Microsoft has effectively put VBA on the shelf to grow stale and mouldy. There is no new development. The security constraints are tightening all the time: macro-blocking policies in Office 365, Trust Center defaults that disable macros on download, enterprise group policies that restrict VBA execution entirely. And here is the critical constraint — VBA depends on an interactive Excel application. You cannot reliably run scheduled, jobs on a server or in the cloud. If you need your planning system to run at 2am when nobody is sitting at a desk to push buttons, you can set up a PC and leave it on 24/7. Or spin up a Virtual Machine in the Cloud. But neither of these are reliable and the reason why we have servers and datacenters is because a PC is limited in the reliability it can offer once you need 24/7 computing or 99.9%+ reliability.
The FEDT’s no-code automation remains a real advantage. You put Sort2 ItemCode DueDate in row 6, and the template generates the VBA for you — no coding required. Gabriele Tettamanzi‘s work on this has been brilliant, and the latest version even supports loops and conditional branching without writing a line of code.
As we have a fairly long range on this analysis of the future, we have to take the ever-so-slow retirement of VBA and look to modern computing as a long term future for Fast Excel Development Method.
So what are the alternatives? The landscape is scattered and, frankly, a bit confusing:
Power Query and M
Power Query is the most natural complement to Excel. It is a functional, declarative language that is brilliant for data transformation. I have built a complete finite scheduling routine in pure Power Query — calendar-aware, with shift patterns and everything — and a MRP tool that explodes a 10-level BOM and handles gross-to-net with lead-time offsets. It is more capable than people think.
Its great advantage is portability to Power BI. Build your transformations in Power Query in Excel, and the same code runs in Power BI for reporting. Its great frustration is the separate editor. You cannot see Power Query and your worksheet at the same time. When you are in the Power Query editor, you cannot access your Excel. When you close the editor and go back to Excel, you cannot see the query logic. I use the technique of opening two separate instances of Excel side by side just to work around this (hold down ALT and click Excel from the Start Menu). It is frankly crazy, and I hope Microsoft fixes it.
Power Query is also case-sensitive, strongly typed, and not very tolerant of changes in column names or data shapes during development. If your data is still fluid or you are quite early in the development process, it can fight you. Excel is a lot more forgiving in these things. So particularly if your data is changing, it might be a good idea to stick with the Fast Excel method for calculations, because it is a lot more flexible when working with data that is not fully stable.
Excel Add-ins: A Confusing Ecosystem
If you want to extend Excel without VBA, you enter the world of add-ins. And here things get confusing, because Microsoft has two quite different add-in technologies, and they are not well joined up.
The older approach is .NET / VSTO add-ins (Visual Studio Tools for Office). These are Windows desktop add-ins written in C# or VB.NET. They are powerful — they have deep access to the Excel object model, similar to VBA — but they only work on Windows desktop Excel. They cannot run on the web, on Mac, or on mobile. And they require Visual Studio and .NET development skills. Microsoft is not actively developing this platform.
The newer approach is Office.js web add-ins, written in TypeScript or JavaScript. These work across web, desktop, and Mac. They are Microsoft’s official direction for the future of Excel extensibility. They use a web-based architecture — your add-in runs in a browser panel alongside Excel and communicates through a JavaScript API. They can be deployed through the Microsoft 365 app store or sideloaded for development.
The problem is that the Office.js API surface is significantly smaller than what VBA or VSTO can do. Microsoft’s pace of adding new APIs has been slow. And the two ecosystems — .NET and Office.js — are not well integrated. You cannot easily mix them or migrate from one to the other. It can feel like Microsoft is asking you to jump to a new platform that is not yet fully capable of replacing the old one.
That said, Office.js is where the future lies. We are exploring ways to bring Fast Excel Development Method concepts into VBA-free workbooks using TypeScript add-ins. The idea is that the no-code automation principles — reading sheet structures, generating processing routines, running sheets in sequence — could be reimplemented in TypeScript and delivered as an Office.js add-in that works in the browser, on desktop, and on Mac.
This is early-stage work, but the direction is clear: the FEDM automation layer needs an alternative to VBA, and Office.js is the most viable path for that.
Python
Python offers the richest ecosystem for anything algorithmic — simulation, optimisation, forecasting, machine learning. For deployment with Excel, I recommend xlwings over Microsoft’s Python in Excel. Microsoft’s approach runs on Azure, requires all inputs to come from Excel cells, and cannot pass data between functions, which severely limits how you can design algorithms. xlwings is much more flexible, and xlwings Lite uses WebAssembly to run Python directly in the browser — no local Python installation required, no runtime dependencies to manage.
Our PyPs-Algorithms repository on GitHub already has calendar-aware scheduling, BOM explosion, and forecasting functions in Python. Each example has the code to generate a working version in Excel using xlwings Lite and you do not need a lcoally installed version of Python.
The strength of Python is its flexibility and (relatively) simple readable syntax. The intention is to use Python to define core algorithms and methods for building planning and scheduling tools, and then use it to port to TypeScript for Office.js add-ins, and to Power Query M for an alternative VBA-free method and for Power BI as well. The core logic stays the same; only the implementation language changes.
PowerShell
PowerShell (integrated since FEDT v4.4) handles OS-level operations — file stacking, folder management, system automation. It bridges between Excel and the operating system. It is not a calculation language, but it is useful for the kind of file management tasks that surround a modular planning system.
How These Languages Work Together – Five Paradigms
In practice, you do not pick one and throw away the others. They have different strengths and a modern planning system uses them in combination.
A programming paradigm is simply the style or approach a language uses to solve problems. Think of it like cooking styles — you can grill, bake, or stir-fry the same ingredients and get different results suited to different situations.
An imperative language like VBA gives you step-by-step instructions: do this, then do that, then do the next thing. It reads like a recipe.
A functional language like M or Power Query describes transformations: take this table, filter it, join it with that table, sort it. You describe what you want rather than how to get it, and the steps chain together like a pipeline.
A reactive approach, which is what dynamic array formulas give you, means that changing one value automatically recalculates everything that depends on it — like a spreadsheet has always done, but now with much more powerful formulas.
Event-driven programming, which is how Office.js and TypeScript add-ins work, is built around responding to things that happen — the user clicks a button, a worksheet changes, a timer fires, data arrives from a server. You write code that waits for events and responds to them. It also uses object-oriented design, where you organise your code around objects that represent things like worksheets, ranges, and tables, each with their own properties and methods. This is the natural style for building interactive add-ins and web applications.
Python is multi-paradigm — you can write it in any style, which is why it is so flexible but also why it needs more discipline to keep things organised. The reason this matters is that each paradigm has natural strengths: imperative is easy to read and debug, functional is excellent for data transformation, reactive is perfect for interactive tools. Knowing which paradigm fits which problem is a large part of choosing the right language for each layer of your planning system.
Power Query handles data ingestion — connecting to SharePoint, APIs, databases. It does the joins, filtering, type conversion. It is the ETL layer. And because it is also the transformation language in Power BI, anything you build in Power Query is immediately reusable for reporting.
Excel formulas, including the new dynamic arrays, handle interactive calculations — what-if analysis, parameter sensitivity, small tools where you need instant feedback when changing a value. The FEDM paste-down method handles larger batch calculations where stability and auditability matter more than interactivity.
Python handles algorithmic work that goes beyond what formulas can do — simulation, optimisation, complex scheduling algorithms, statistical forecasting. It runs externally via xlwings or as a service, and reads and writes data through Excel or text files.
Office.js provides the cross-platform automation that VBA cannot — scheduled refreshes, web-based interfaces, integration with modern web services.
Here is the key insight, and it is an important one. The Fast Excel Development Method is fundamentally about the logic — the pseudo-code, the data flow, the separation of concerns. Not about VBA. Not about any particular language. We are building toward a world where core planning logic is defined once and can be implemented in VBA, M, Python, TypeScript, or dynamic array formulas depending on the deployment context. The scheduling algorithms project is a concrete step in this direction: one set of algorithms, multiple implementations. You describe the logic of a finite scheduling routine, a BOM explosion, or a lead-time offset calculation, and then you implement it in whichever language fits the deployment target.
And I should mention the LLM accelerator. In the webinar, I showed Power Query code that I did not write by hand. I described the logic in plain language — to Claude, in this case, using an Anthropic add-in that I have been testing — and iterated on the results. It cannot access Power Query directly, which is one of the frustrations of the separate editor, but it can see everything in the Excel workbook, generate the M code, and you paste it in.
The AI coding assistants are remarkably good at generating M code, dynamic array formulas, and Python from descriptions of planning logic. This dramatically lowers the barrier to using unfamiliar paradigms. If you can explain what a routing explosion should do with the PS Cycles data, an LLM can generate working M code in minutes rather than the hours it would take to learn the language from scratch. It takes a couple of iterations sometimes — you paste in the code, get an error, paste the error back — but it is a really quick process compared to learning all the commands yourself.
Execution Environments
Where and how your code runs is becoming as important as the code itself. Here is a quick survey:
Desktop Excel with VBA is the workhorse for existing FEDT systems. Proven, fast, with no-code automation. But desktop-only, and increasingly restricted by security policies.
Excel for the Web runs Power Query and dynamic array formulas but not VBA. Workbooks in SharePoint with Power Query connections to SharePoint data can be refreshed in a browser — no desktop installation needed. This matters for distributed teams. You can put the workbook and data on the same SharePoint site, and anyone on the domain can refresh it without even having Excel installed.
Excel (Web or Desktop) extended with Add-ins. Office.js makes Excel extensible through using TypeScript (a form of JavaScript, hence the .js). This is the method that fits with the Microsoft Ecosystem, which is good and bad. It is good to have the security, user authentication, and native cloud and API access. It is bad to rely on Microsoft and get trapped in the ecosystem, particularly when the development for Office.js is slow.
Local Python via xlwings or command-line scripts gives full algorithmic power but requires a Python runtime. The uv package manager has simplified dependency management considerably compared to the old pip and virtualenv approach.
xlwings Lite / WASM runs Python in the browser via WebAssembly. No installation. No server. No dependencies. It is early days but promising for distributing Python-powered planning tools to people who do not want to install anything.
Server and scheduled jobs require Python scripts, PowerShell, or Office.js. VBA cannot reliably serve this use case. If you need your planning system to run at a specific time, respond to events, or be triggered by another system, you need to be outside VBA.
Where Fast Excel Still Wins
With all these modern alternatives, what keeps the Fast Excel Development Method relevant?
Visibility. Formulas in row 8, data below, sheets ordered left to right. You can see the entire data flow without opening a separate editor. You can click the button per sheet, see how many sheets run, see the order. This is still unmatched. Power Query buries logic in a separate application. Complex dynamic array formulas can look quite intimidating. FEDM’s transparency is the most powerful feature for auditability, training, and handing over a system to someone new.
Forgiveness. Excel is case-insensitive, loosely typed, tolerant of column name changes during development. Power Query is strict, case-sensitive, and works on strong data types — you need to get your data types right. When you are iterating rapidly on a planning model with data that is not yet stable, the FEDM’s flexibility saves enormous time.
No-code automation. The directive vocabulary — Sort, Output, NEXT, FOR, RUN — is simpler than any alternative for building automated processing pipelines without programming. It means planners who are not programmers can build real systems. Arguably, going into Power Query or complex dynamic array formulas means you are deviating from the path that leads to low-code simplicity, and you are going into needing to manage, maintain, and sometimes understand a lot of complex code.
Proven scale. Hundreds of planning systems in daily production at manufacturing companies worldwide. The PS Cycles examples we share are simple illustrations, but the real systems handle tens of thousands of rows reliably. And the Fast Excel Development Template has been free and open for over 15 years.
These are pros and cons. It is not that the Fast Excel method is completely superseded by what you have in modern Excel. It is still a really powerful tool for doing things in a rapid way.
What Comes Next
This is the first in a series on modernising the Fast Excel Development Method. The territory is mapped. Now we go deeper:
Tables vs. Dynamic Named Ranges — practical patterns for making them coexist in the same workbook, including modifications to the FEDT automation.
Cloud Data Integration — detailed patterns for SharePoint, APIs, Business Central, and database connections with Power Query.
Beyond VBA — practical migration paths, with the scheduling algorithms library in Python and TypeScript, and a roadmap for Office.js add-ins.
The Excel Computation Lab — a hands-on environment for exploring all five calculation paradigms with finite scheduling algorithms.
Python + Fast Excel — integration patterns with xlwings and the PyPs-Algorithms library.
The goal is not to abandon what works. It is to extend it. The Fast Excel Development Method has always been about the discipline of building reliable planning systems — separating data from calculation from reporting, defining logic in one place, controlling the process flow. Those principles do not change. The tools to implement them are getting better.
Download the Fast Excel Development Template and try the examples. If you have questions or comments, reach out by email or leave a comment below. Your feedback is really helpful — it helps me develop more useful content. And if you are watching the webinar recording and have questions, do not hesitate to get in touch.