How to Use ChatGPT to write Formulas and Macros for Excel

Excel Reigns Supreme in the Spreadsheet World

Despite its reputation for being challenging to use, Excel continues to dominate the spreadsheet landscape. Its unique blend of powerful features, including formulas and macros, remains unmatched, even if they often leave users frustrated.

Excel Macros Still Relevant?

Historically, Excel macros have been one of the most significant sources of malware, leading Microsoft to repeatedly attempt to phase out this feature in Office.

Yet, here we are in 2024, and macros persist. Excel remains a staple in businesses and academic institutions for data management. Its enduring popularity stems partly from inertia and partly from its peculiar mix of tools. Many companies and educational organizations still rely heavily on Excel’s formulas and macros for critical operations. Even Python, a highly versatile and popular programming language, hasn’t completely replaced Excel for many data processing tasks.

Macros in Excel are created using VBA (Visual Basic for Applications), within an outdated interface that feels straight out of the mid-90s. When paired with Excel’s clunky built-in functions, automating tasks can become an exercise in patience for most users.

ChatGPT Excels at Writing VBA Macros

Because of this, writing Excel macros has become something of a specialized skill. Some seasoned accountants can outpace even the most skilled programmers when it comes to crafting Excel formulas and macros. These skills remain in demand across various industries, especially for maintaining and updating legacy spreadsheets critical to business operations.

But what happens when your Excel expert is unavailable, and you need a complex formula or macro created?

Surprisingly, ChatGPT might be your answer. This advanced AI model has absorbed decades of Excel knowledge from forums, tutorials, and guides. It’s surprisingly adept at interpreting plain-English instructions and turning them into functioning formulas or VBA scripts.

Example: Using ChatGPT to Create a Macro

Here’s a straightforward example of a request you might make to ChatGPT:
Create an Excel macro that finds the largest number in column A, divides it by 3, and adds it to the smallest number in column B. Then display the result in a dialog box.

ChatGPT’s output:

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 works as-is and even comes with helpful notes on how to copy and use it in Excel.

Complex Excel Formulas Made Simple

ChatGPT isn’t just good at macros—it’s also great for generating complex Excel formulas. Here’s an example:
Write an Excel formula that calculates the sum of all values in column A, then divides it by the average of the three largest numbers in column B.

ChatGPT’s response:

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

A Few Cautions When Using ChatGPT for Excel

As tasks become more complicated, there’s a higher chance that ChatGPT might produce errors. It’s crucial to thoroughly review and test any code or formulas before relying on them for important calculations, like financial or tax-related tasks.

Even with occasional adjustments, having a tool that generates most of the code or formulas for you can save significant time. You can also ask ChatGPT to add detailed comments to help you understand how the code works, making it easier to learn and refine as you go.