Integrating ChatGPT with Excel (Part 1)

Welcome to the “Illumined Insights” newsletter! Thank you so much for subscribing. This weekly newsletter touches on all things analytics and data science with a focus on areas such as data visualization, AI, and sports analytics.

This week we kick off a series of posts exploring the integration of ChatGPT and Excel. Excel is the most ubiquitous analytics tool and Excel proficiency is “table stakes” for the analytics/data science professional. Enhancing Excel’s functionality by adding ChatGPT integration can be invaluable. Let’s get started!

Stephen Hill, Ph.D.

As a university educator in a Data Analytics program, I firmly believe that our students should be highly proficient in Excel and (as with many analytics topics) should be prepared for a journey of continuous learning. I’ve been an Excel user for years and learn new things about this tool on what feels like a daily (or at least weekly basis). I’ve also spent a considerable amount of time exploring ChatGPT. This newsletter post is an outgrowth of that exploration and the question that emerged: “How can we integrate ChatGPT with Excel?”

Let’s start with an example of using Visual Basic for Applications (VBA) to create a function to prompt ChatGPT directly from Excel. To do this, we’ll need access to the OpenAI API. First, you’ll need a free OpenAI account. Sign-up for an OpenAI account here: Link. Next, click on “API”.

At the top right of the next screen, click at top-right to access your OpenAI profile menu.

A drop-down menu will appear with an option to “View API Keys”. Click this option. The next screen will look similar to the screenshot below. I’ve created several keys at this point and they are listed here. Click “Create new secret key”. Give your key a name (if you wish).

Your API Key will then be generated. There is no cost to generate an OpenAI API key. Charges are incurred when the key is used to make API calls. For our purposes in this newsletter post, the charges that we will incur are negligible (much less than $1).

If you’re following along, you should copy your API key as you will need it momentarily. Note that once you leave the screen where your key is generated, you are not able to see the key again and would need to generate a new one. I’ve blurred out my key in the screenshot below. You should not share your key with anyone. A shared key could be misused and leave you with unexpected charges from OpenAI.

With our API key in-hand, we’re now ready to go to Excel and write some VBA code. To create VBA code in Excel, there are a couple of steps we need to go through first. After opening Excel (I’m assuming you’re using Excel on Windows PC, the steps may vary on a Mac), you’ll need to enable the Developer tab on the Excel ribbon.

To enable the Developer tab in Excel, click on the File tab, then Options, and then Customize Ribbon. Check the box next to Developer to enable the tab.

Next, click Visual Basic to access the VBA Workspace. It should look similar to what’s shown in the screenshot below.

EDIT: In the original version of this post I left out the requirement to add the JSON converter to Excel’s VBA. Installation instructions are available here: Link.

From the Insert menu, click Module to create a new VBA module. It’s in this module that we’ll place our VBA code. For complete transparency, I should note that I’m not a VBA expert by any means. I leaned heavily on ChatGPT (of course) to help out. Here’s what ChatGPT came up with asked to develop an Excel function using VBA to access the ChatGPT API:

An easier to copy and paste version of this code is available on my GitHub here: Link. You will want to replace the “YOUR_API_KEY” text with your OpenAI API key.

This code creates an Excel function that I’ve named CHATX. CHATX passes the contents of an Excel cell to ChatGPT as a prompt. The result from the ChatGPT prompt is returned to Excel and placed in the cell. For example, if we write:

CHATX("What is the square root of 9?")

in an Excel cell, ChatGPT will then return:

“The square root of 9 is 3.”

As another example, we might use the CHATX function to answer a non-mathematical question:

CHATX("What is the capital of Alabama?")

ChatGPT then returns:

“The capital of Alabama is Montgomery”

Because ChatGPT is probabilistic, the wording of your result may differ slightly from mine.

One potential issue with this function is that the answers that are provided by ChatGPT can be pretty lengthy. For example, in Excel, I probably don’t want the full text of the answer: “The square root of 9 is 3”. I’d rather just get the number “3” as an answer. For the “What is the capital of Alabama?” question, I’d be perfectly content with the single word “Montgomery” being returned as the answer. Let’s try to reduce the verbosity of ChatGPT’s responses.

We do this by writing a second function that I’m calling CHATSHORT. This function passes a more complex prompt. The prompt reads:

“Assume that a 'concise answer' is the most basic, minimal answer. For example, if the question provided by a prompt is 'What is the square root of 64, the answer would be simply 8. No period should follow the answer.'. Provide a concise answer to: "

The CHATSHORT function is shown in the screenshot below, but is easier to read and copy/paste from GitHub here: Link. NOTE that VBA frowns on the wrapping of text across multiple lines. The long prompt should appear on a single line (as in the file on GitHub). I noticed periods appearing occasionally on the end of ChatGPT’s responses, so I’ve also added a bit of code to strip those off.

Let’s see how the function performs. In the screenshot below, the text in cells A1 through A4 are prompts. The CHATSHORT function is in cells B1 through B4 and takes cells A1 through A4, respectively, as input. Looks like the function is performing as desired.

There’s a lot that we can do to enhance these functions and take full advantage of the power of ChatGPT. In Part 2 of this series we’ll look at building a function to perform basic sentiment analysis of text in Excel.

Are you interested in learning more about data visualization using R? Click below to get notified about my upcoming book “Data Visualization in R”.

Feedback?

Did you enjoy this week’s newsletter? Do you have a topic, tool, or technique that you would like to see featured in a future edition? I’d love to hear from you!

Support the Newsletter?

Support this newsletter with a “coffee” (optional, but appreciated).

Start Your Own Newsletter?

This newsletter is created on and distributed via Beehiiv, the world’s best newsletter platform. Want to start your own newsletter? Click below to get started. Please note that this is an affiliate link. I may receive a small commission if you sign up for Beehiiv via this link.