Using Artificial Intelligence Chat Tools to Generate Excel Formulas

There’s so much “buzz” around Artificial Intelligence (AI) these days, that it’s easy to feel stung. The term is thrown around so much that it almost has no meaning by itself. Some use the term to refer to machine-learning programs that perform complex (but very narrow) analysis tasks like making movie recommendations or finding the fastest way from point A to point B on a map. Others might only use it when referring to self-aware and intelligent robots as imagined in science fiction.

What is AI?

While the use of the letters “AI” are used sloppily in most contexts, we can make at least one assumption when we see them:

• The writer is referring to computer systems that train algorithms on large (huge) datasets to discern patterns that could not be otherwise seen by traditional computing. This allows software to perform tasks that mimic human intelligence, such as language understanding, image recognition and decision-making.

This can mean anything from discerning consumer spending habits to identifying what pixels make up an image. Or what word should come next in a sentence.

OpenAI’s ChatGPT (GPT stands for Generative Pre-trained Transformers) is the technology that much of today’s AI Chat interfaces are built on. OpenAI pioneered the capability for software to understand and reply in natural language. Google’s Gemini, Adobe’s Firefly and Microsoft’s Copilot - just to name a very few - use OpenAI’s language models on their own data to create tools that their customers can query with a text prompt.

So, how does this help us with Excel?

One of the primary Excel challenges is mastering its extensive library of formulas and functions. Whether it’s a simple SUM formula or a complex nested IF statement, users often find themselves stuck or making errors. The mind-boggling advantage of Generative AI language models is that you can just ask a question – in words like you’re reading right now - and get an answer. Type: “Create a formula that averages the data in column A and B” and you can get a working function. Just like that.

Mostly.

The baffling disadvantage of Generative AI language models is that they can be horribly, horribly wrong while sounding so confident that you’re sure they must be right.

Let’s look at some examples of the good and the bad.

Generating Excel Formulas in Copilot and ChatGPT

For this article, we will use Microsoft’s free Copilot chat interface available at https://copilot.microsoft.com/ and ChatGPT at https://chatgpt.com/. Copilot is available as an integrated tool within the Microsoft Excel and Office 365 applications, but Microsoft charges additional fees for this. Both Copilot and ChatGPT’s free web interfaces limit the number of queries you can make in a specified timeframe, but that shouldn’t be a problem here.

Let’s start with a simple problem. We want to add the cells in each row and produce the average of the columns of data in the sheet.

In the Copilot chat box, then, we will ask it to:

To which it replies:

Click the copy icon to copy the formula and then paste it into your spreadsheet.

But…we get an error.

This illustrates the number one tip for working with ANY generative chat system – NEVER assume it’s answers are correct or complete without checking. You must always proof. In this case, Copilot assumed that your data was in Row 1. Not its fault really, we didn’t specify a row. And it warned us to check the cell references!

We could repeat our instructions in more detail, but another benefit of chats is that they remember your conversation. Instead of typing “create an excel formula that averages the data in column a through f in row 2”, you can just type:

And now, when we paste in the revision, we get a working value that we can quickly drag into the other cells on the column.

Now let’s look at a far more complicated question to demonstrate just how powerful this tool can be.

This is a snippet of the data:

We want to find out how much each salesperson sold. This involves calculating a total sale number for each row (“Negotiated Price” times “# In Order”) AND adding only the rows where the listed Sales Rep (starting at cell M9) is credited.

The prompt is a description of the formula needed using as much detail as possible, including cell numbers and the range of the data. Note that there are no actual data or files included in this example. We are just giving the tool the cell values it needs to work with to create a formula. It does not have or understand what is being evaluated.

This time using the OpenAI ChatGPT tool:

Not only does ChatGPT respond with a formula that can be quickly copied into the worksheet for testing, but it also takes the time to describe how it created the formula and explain what the formula is doing. In this case it cleverly uses the SUMPRODUCT function in combination with a condition to filter out all but the matching sales rep.

When we test the formula by calculating the result manually (see helper column K), we can see that it was accurate and can be used for the remaining sales team members.

Can you spot the next problem you might have with this formula, however?

=SUMPRODUCT((J2:J101=M9)*(H2:H101)*(I2:I101))

The cell references for the column ranges are not absolute. They will shift along with your variable in cell M9 when you drag, causing errors in the result.

We could fix that ourselves, or we could ask ChatGPT to do it for us:

Now we can safely drag the formula into the remaining Sales Rep rows.

Summary and Caution

AI chat can enable average users to accomplish more complex Excel analysis and calculations than ever before. They are powerful tools for filling knowledge gaps and offering solutions to novel problems.

With this power comes with a warning: You must be skilled enough to know when it’s wrong. In testing and writing this article, Copilot’s solution to the exact same sales rep prompt as ChatGPT was not only flawed in execution (the function didn’t include the correct arguments), but it would also not have calculated the data as we wanted even if the function had been written properly. There will be times when you will have to try another tool, or you may have to spend time crafting your prompts more clearly to reach a solution.

Similarly, you must be mindful of details even a correct formula might be missing, such as we saw with the absolute vs. relative references.

But if you are strong on your Excel fundamentals, Copilot, ChatGPT and others can be outstanding partners. If you need to brush up on those fundamentals, Pryor Learning offers many Excel courses from beginning to advanced skill levels.