Excel Automation in an AI World: The Good, The Bad, and the Truth

AI is here and it is not going away. Is it going to replace me as an experienced Excel automation designer and developer? Not anytime soon. But it will replace most sub-par developers who do not have a seasoned and effective grasp on building efficient, robust, maintainable, and extensible solutions.

Experienced Enough

I have been building Excel automation solutions for at least 25 years. I was a Microsoft Excel MVP for ten of those years and have coauthored two books about using Excel and Excel automation. I have millions of lines of code in my libraries that I leverage in most of my more complex solutions — some of that code functions to generate more code. I have built a business on quickly creating highly effective and robust solutions for companies in all sectors of the business world. Before I turned my attention to Excel, I spent years building mainframe and personal computer software applications for banks, manufacturers, retail companies, and individual consumers. This experience allowed me to hone my design skills not just regarding UI/UX, but with software architecture. I have made the mistakes, and I have learned from the best.

Traditional Development Practices

Well-designed software solutions not only function well from the user's perspective — reducing time spent on tasks and mitigating errors — they also provide a reliable code base for other solutions using reusability models. Of course, good coding practices such as structured coding techniques, consistent naming and documentation standards, and thorough unit and regression tests also contribute to long-term reliability. The question we face is not just how to replicate this process with AI — everyone following the AI world is already trying to do this. The more important question is how we manage AI's role while preserving the judgment-driven aspects of development that AI cannot replicate.

Integrating AI Into the Process

In short, AI fits nicely into this process — with some caveats. AI is not an end-all solution, at least in the context of Excel automation. AI can make us dramatically more productive, but only when used as a subordinate collaborator rather than a substitute for software engineering judgment. Without well-defined instructions and constraints, AI can turn a good idea into an unmitigated and costly mess.

Think of AI as a well-spoken and well-behaved toddler with an extraordinary amount of energy and an incredible breadth of knowledge. It can churn out incredible amounts of code in a tiny fraction of the time it takes a human — even if that human has millions of lines of pre-existing library code to leverage.

In the following text, I refer to the AI LLM agent as simply AI. For those curious as to which AI model I am currently using, it is Claude.

Caveats

But that toddler readily makes costly assumptions and lacks a critical element of a good developer's toolbox: wisdom — the hard-won understanding that comes from accumulated mistakes and their consequences.

You might think that AI has perfect memory, but it does not. A recent exchange with AI revealed that the further back in the thread my instruction was communicated, the less weight AI gave that instruction. It is not the elapsed time, it is the sheer volume of the back-and-forth communications that render past instructions increasingly irrelevant. I asked AI what I could do to keep it focused on certain instructions (a couple of coding standards in this case) and it told me to repeat the relevant standards with every new request. So much for longer-term memory and thus any sense of consistent priority or importance. When we move to a new thread to reduce credit usage per prompt (AI reviews the entire thread every time a new prompt is posted), all those previous instructions are effectively lost — even with a request to review the earlier thread.

In another exchange, I asked AI to count the number of actual comment and code lines in two separate code files. The numbers were nowhere close to what I was expecting. When I challenged AI, the response was "These are still estimates rather than a verified line-by-line count [...] My recount of file [the second file] was wrong — I likely copy-pasted my prior totals with only a minor adjustment instead of actually recounting." After restating the instructions with "Specifically count the comment lines and the code lines in each [file]," AI gave counts that seemed more reasonable.

So, we have an incredibly powerful and productive resource with limited and volatile short-term or context-related memory, coupled with an almost total lack of practical wisdom and a propensity for costly assumptions.

A Case Study

A few months ago, a client with many bright ideas but a definite lack of software development prowess thought of an idea: an Excel macro that reads parameters on a worksheet, queries an external system using those parameters, and writes the results of that query to the worksheet. It was far from a simple fetch and post function, as it involved additional complexities to filter, consolidate, and transform the data pulled from the external system. Using AI, the client had an initial solution produced in seconds. But it took days to test and fix issues. The resulting code was riddled with bad Excel VBA coding practices such as poorly named variables, hard references to specific objects such as worksheets and worksheet cells, and little to no use of reusable code. The macro did eventually work as intended, but it was far from reusable and created a troublesome maintenance pattern. Each revision brought a familiar cycle: AI would generate code instantly, followed by a much lengthier process of debugging and correction. The solution was effective but, because the code was difficult to maintain by human developers, grew much more costly with additional small changes to the functionality because AI was the only practical path forward. How long a traditionally developed solution would have taken to build and maintain through a few modification cycles is not known, so I cannot provide that comparison. But this discussion is not about whether AI is effective — it is about how to make it as effective as possible given its current capabilities.

Redirection With Clarity

I took the code from the above effort and, with some stringent coding standards and clear instructions about how the macro was to be used, asked AI to rebuild the solution from scratch using the previous code as a design for the core functionality (filtering, consolidating, and transforming). The end solution was still not as maintainable as I would have liked, but it was better structured and embodied the crucial elements of a well-constructed Excel automation solution: reusable code, better coding standards, and a clean, easily understood calling structure.

These results illustrate a key benefit of clear high-level guidance:

• First round: approximately 3,500 lines of code.
• Second round: approximately 1,160 lines of code.

A final note on the first versus second solution: the second solution can be repurposed without any modifications, while the first solution requires some modifications for every new application. And these modifications will inevitably be more costly than desired or expected given the relatively limited and simple nature of the changes required. This specific attribute was achieved by communicating the big picture to AI upfront, alongside clear coding standards that promote reusable, generic code.

Summary and Conclusion

AI is here and it's real. Most people are talking about how to use it: clear specifications and prompts, using AI for acceleration, not decision-making, reviewing everything AI produces, and so on. They are also talking about the impending loss of jobs as employers look to replace their most costly resources with much less expensive alternatives that, at least on the surface, appear to replicate traditionally manual efforts in truly spectacular ways. This does make sense in many areas such as customer service, problem diagnosis, and content summarization. And it is proving to be valuable in software solution development — with a few caveats.

As seasoned Excel automation developers, we are best served by embracing this new technology with the awareness that it needs guidance the same way we guide novice programmers. The process benefits from our accumulated wisdom over years of practice to shape good architecture from top to bottom while delivering an effective UI/UX experience. AI can generate and regenerate large amounts of code in the blink of an eye, but that code is only as good as the guidance it receives. And generating code that works well given the environment in which it is used is not as straightforward as some might assume.

I would argue that a new kind of role is emerging: something like an AI Supervisor: part translator, part workflow designer, part governance specialist. This position directs AI systems, validates outputs and catches mistakes, improves processes, ensures quality, and, most importantly, connects business goals to technology and vice versa. Good judgment and collaboration are becoming more important, not less, and are crucial to making AI a genuinely effective tool — particularly in Excel automation.

"AI is very good at left-brain things. We live in a left-brain world — lists, very complicated things, like going to the moon. Very complicated, but it's not a right-brained thing. Right-brained things are about the gestalt, the whole. That's music and laughter and connection. AI cannot do that." — Jimmy Carr

A final note on AI-generated text. For the observant reader, you will notice that I have used the em dash surrounded by spaces. This has been perceived as a blatant tell that the text is AI generated. While I have, for years, used an en dash with spaces, I have learned that AI has the dash standard correct. According to the AP Stylebook, the em dash with spaces is preferred by "many newspapers, magazines, and digital publications to prevent the dash from looking like a hyphen and to improve readability on digital screens." This article was written by me, a human, with a few rounds of copyediting using AI. AI has schooled me, and I'll be using em dashes with spaces from now on.

© 2026 Kevin Jones, Data Automation Professionals