Hate Writing Excel Formulas and Macros? ChatGPT Is Actually Useful Here

Microsoft Excel is still king of the spreadsheet world. Despite it being generally user-unfriendly, there's really nothing that seems to do what it does, as well as it does. Part of that is to do with its super useful (and painful to use) formulas and macro functionalities.

Excel Macros? In 2024?

Excel macros have historically been one of the biggest malware vectors for users, to the point where Microsoft has tried to repeatedly kill the macro functionality in Office.

And yet, macros remain, and Excel is still one of the most popular tools in enterprise and academia for handling data. Part of this is inertia, and part of it is Excel's strange combination of tools. Excel formulas and macros are still powering entire businesses and educational departments. Even the popularity and versatility of the Python programming language doesn't seem to have completely displaced Excel for processing data.

Excel macros are written in VBA (Visual Basic for Applications) script, and are written within Excel using an antiquated interface that still looks (and works) like something from the mid 1990s. Combined with the clunky nature of Excel's built-in formulas and functions, automating Excel is an absolute pain for most users.

ChatGPT Is Surprisingly Good at Writing VBA Macros

Because of this, writing Excel macros has become a bit of a niche skill: there are middle-aged accountants out there who can run circles around even the most qualified hot-shot programmer when writing formulas and macros for Excel. It's also still a required skill in many industries, as all of those old business-critical spreadsheets occasionally need a bit of maintenance.

So who do you turn to when you need a complex formula or macro written and the Excel sage from finance just went on long-service leave?

Surprisingly, ChatGPT—it turns out that this large language model has sucked up enough Excel knowledge from 30 years of forum posts and tutorials that it's actually pretty good at taking plain-English instructions, and outputting working formulas and VBA code.

For example, here's a simple example prompt to ChatGPT 4o to write an Excel Macro:

Write an Excel VBA macro that takes the largest value in column A, divides it by 3, and then adds it to the smallest value in column B. Display the result in a dialog.

And here's ChatGPT's response:

Sub CalculateAndDisplayResult()
Dim maxA As Double
Dim minB As Double
Dim result As Double
' Find the largest value in column A
maxA = Application.WorksheetFunction.Max(Range("A:A"))
' Find the smallest value in column B
minB = Application.WorksheetFunction.Min(Range("B:B"))
' Calculate the result
result = (maxA / 3) + minB
' Display the result in a dialog box
MsgBox "The result is: " & result, vbInformation, "Calculation Result"
End Sub

This code worked without any modifications, and even came with some helpful instructions detailing where to copy and paste the code.

It's also good with formulas for use in spreadsheet cells, and means you don't have to look up all the different Excel functions you need to make a complex calculation. Here's an example ChatGPT prompt for a formula that will require a few Excel functions:

Write an Excel formula to paste into a cell that takes the sum of the values in column A and then divides it by the average of the largest 3 numbers in column B.

And ChatGPT's response:

=SUM(A:A) / AVERAGE(LARGE(B:B, {1, 2, 3}))

Of course, as your instructions get more complex, the chances of ChatGPT making a mistake increase, so it's important to proofread and thoroughly test its output before you start relying on it for any critical calculations (like doing your taxes).

Still, even if you have to make the odd correction, having the bulk of your Excel formulas and macro code written for you is a huge time saver, and you can even ask ChatGPT to include extra comments explaining what each function does if you want to learn how to write and understand the code as you go.