Why natural language AI scripting in Microsoft Excel could be a game changer

Microsoft is putting a Copilot natural language frontend on its Python scripting language for Excel. Does that mean you can tap all the power of Excel analysis and macros with no programming knowledge?

Also: Microsoft Copilot's Wave 2 is here. Everything you need to know

In this article, written before I have hands-on access to that Copilot feature, I look at the potential and limitations of providing a natural language interface to enterprise Excel users.

What we know

Right now, I'm working off one of Microsoft's infamous giant press releases. The company has a tendency to do brain dumps in press release form, so we both have to sift through the release to give you a survey of what it's announcing and also try to read the tea leaves from some under-documented nuggets we really won't be able to test until sometime later.

Also: The most popular programming languages in 2024

That's the case with its announcement of Copilot Wave 2 for what seems like enterprise customers. The press release itself doesn't mention which editions of Microsoft 365 Copilot will get these features, but the company leads off with a discussion of business chat and ends with some sound bytes from enterprise users.

As such, we won't know whether the Copilot feature I'm about to discuss will be available to consumers.

Before I dive into further speculation, here's the nugget that caught my eye and is forming the foundation of this article: "Today we're announcing Copilot in Excel with Python, combining the power of Python—one of the world's most popular programming languages for working with data—with Copilot in Excel. Now, anyone can work with Copilot to conduct advanced analysis like forecasting, risk analysis, machine learning, and visualizing complex data—all using natural language, no coding required. It's like adding a skilled data analyst to the team."

Also: The rise and fall in programming languages' popularity since 2016 - and what it tells us

Let's deconstruct that.

Scripting Excel

Almost everyone understands the basic function of a spreadsheet. At its most simple, it's an arrangement of rows and columns, powered by formulas that act on the data in those rows and columns.

Also: How ChatGPT scanned 170k lines of code in seconds and saved me hours of work

Keep in mind that Excel is nearly 40 years old, having launched on Macs in 1985. Almost since the very beginning, back when Ronald Reagan was president, when the teens in the Breakfast Club were still teens and not sexagenarians, when the NES was the hot gaming platform, and when Miami Vice was taken seriously and not eye-rolling ironically, Excel had macros.

Macros allow Excel users to create scripts that process spreadsheet data, or sections of spreadsheet data, automatically. There was even a company, Heizer Software, that made a living selling Excel templates and even entire applications based on Excel macros. Think of them like you might Notion templates today.

Sometime in the mid-1990s, VBA replaced the fairly crude original Excel Macro Language, although those old macros still work, even in today's Excel.

VBA, or Visual Basic for Applications, is a full object-oriented scripting language. This provided a much more functional and maintainable foundation for creating back-end code that powered sophisticated spreadsheets and financial applications based on spreadsheets.

Also: I put OpenAI's o1-preview through my 4 AI coding tests. It surprised me (in a good way)

In fact, it made it practical to build custom financial applications based on spreadsheets.

But Visual Basic and VBA have pretty much dropped out as popular programming languages. They were tied to the Windows platform, but also were just cumbersome compared to more modern languages like Python and C#.

Last year, Microsoft announced the integration of Python directly into Excel. As we've discussed elsewhere, Python has become the world's most popular programming language, and is deeply embedded into AI projects.

That integration included more than just adding a new language to Excel. It also included integration of the Anaconda distribution platform with Python and Excel. This opened up access to an enormous library of additional code that could be incorporated into Excel projects. Essentially, it made Excel a full-fledged Python client, with all the rights and privileges therein.

Of course, you still had to have programming skills to make use of that capability. Python put tremendous power into the hands of Excel users, but only those who were able to craft code.

What Copilot might do

Programming is second nature to me. I've been programming for long enough that it's just part of who I am. I coded algorithms in multiple languages before I had a license to drive (and I got that on the earliest day New Jersey law would allow). I'm telling you this not to show off but because I want to make it clear that even programmers sometimes want to find non-programming solutions.

Of course, those who can't program either need to leave their lives in the hands of programmers or find a non-programming solution.

Also: The best AI for coding in 2024 (and what not to use)

I've done two fairly large experiments using AI (ChatGPT in these cases) to do fairly big data analysis assignments rather than programming a solution. I test AIs, so any time I have an excuse to use an AI for a project I do, just for the learnings. But I also used the AI because, while I wanted answers, I couldn't justify allocating the time to make finding them into a new programming project.

The first was when I used ChatGPT Plus to do sentiment analysis on a corporate data set of user uninstall data. I wanted to learn more about why those users were uninstalling my code. I fed ChatGPT a data set of 22,797 records, asked it a bunch of questions, and got instructive answers back out within a few hours -- and it took a few hours only because I exceeded the question asking allocation for the prerelease and had to log back in after getting kicked off.

The second was when I had ChatGPT scan 170,000 lines of 3D printer G-code to explain why one print took a third of the time of another print. This analysis took all of ten minutes.

When ChatGPT does these data analysis runs, it constructs its own Python scripts that it runs to do all the heavy lifting. In effect, ChatGPT writes the code so I don't have to. If I had to write the code for these two problems, it would probably have taken me days, at least a weekend of my free time to do each.

Also: The moment I realized ChatGPT Plus was a game-changer for my business

So, when Microsoft is saying that it's combining the power of Python with Copilot in Excel, I think it's doing the same thing: having Copilot write Python routines to analyze data. Except it's all built into Excel.

My ChatGPT projects originated in Excel, too. I brought all the data into Excel spreadsheets, did some massaging, and then dumped them out to tab-delimited files, which I then uploaded into ChatGPT Plus.

Presumably, this new Copilot capability would have just allowed me to tell Copilot what to do with my data from within Excel. That's cool.

Benefits and limits

At first glance (remember, I have yet to tinker with the actual Copilot feature), there are some definite benefits -- and some limits.

One benefit is the one I just mentioned: it's already integrated into Excel (although I have concerns about which editions of Excel will get this feature). That saves import and export time and errrors.

Also: 6 ways to write better ChatGPT prompts - and get the results you want faster

Another benefit may be security. Many of my readers called me on the idea that I was uploading corporate data into ChatGPT. In my case, it was just for my own product and my own company, so I was comfortable with the risk. But it's a valid concern, especially if you're uploading data from clients or that is more confidential than my little personal project.

Microsoft and the Microsoft Cloud aren't hacker-proof, because nothing is hacker-proof. But I think we can all trust Microsoft to take more responsibility for securing and protecting our data than companies that like to hoover up every bit and byte either as training data or advertising indicators, or both.

But I am concerned that Microsoft (and other AI vendors) may oversell the AI's capabilities. At this point, there's no way Copilot can build an entire custom fintech application based on scripting. That's still something programmers need to do.

Also, constructing data analysis queries still requires some understanding of how data is structured and how to prompt an AI. While it certainly takes less time to get some understanding of prompting than it does to learn Python, using Copilot with Excel data will require users to use discipline in how they specify their prompts. AIs, like programmers, are not mind readers.

Also: IBM will train you in AI fundamentals for free, and give you a skill credential - in 10 hours

With all that, I'm kind of excited (assuming my consumer version of Microsoft 365 will get this feature). The ability to query spreadsheet data (and lots of spreadsheet data at that) by typing a prompt into the formula bar and getting detailed analysis can well be a huge productivity boon -- if used correctly.

Remember GIGO: Garbage In, Garbage Out. If you ask analysis questions via an AI prompt that result in the wrong conclusions because you asked the wrong questions, that's not the fault of the AI.

"The fault, dear Brutus, is not in our AIs, but in ourselves."

With apologies to the Bard.

You can follow my day-to-day project updates on social media. Be sure to subscribe to my weekly update newsletter, and follow me on Twitter/X at @DavidGewirtz, on Facebook at Facebook.com/DavidGewirtz, on Instagram at Instagram.com/DavidGewirtz, and on YouTube at YouTube.com/DavidGewirtzTV.