Q. It would be much easier to use ChatGPT for Excel assistance without having to go to the website and log in every time. Is there a way to build ChatGPT into Excel?
A. Many online resources demonstrate multiple codes or other ways to build ChatGPT into Excel and other applications. The one that I like best is code created by Leila Gharani. I made slight modifications, but this is her code that she has made public. Thanks, Leila! If you are not familiar with ChatGPT, there are many articles and videos online, including this one.
Note that this process currently works in Microsoft Excel 365, Microsoft Excel 365 for Mac, and Excel for the web. The demonstration shown here was done using Microsoft 365 for a PC. Other devices may work differently.
I made an Excel spreadsheet to use for this demonstration. Note that the spreadsheet has the data I use in this example, but to get it to work with ChatGPT, you will need to follow the instructions below to integrate ChatGPT with the spreadsheet.
Open the sample spreadsheet or start with a blank spreadsheet if you’d prefer. On the Ribbon, click the Automate tab, which is available to most business users with a Microsoft 365 subscription, and choose New Script from the Office Scripts group. In the Code Editor window, paste the code listed here, starting on line 1, replacing any code that is there by default. Leave the spreadsheet open, including the Code Editor window. You will come back to it in a moment.
Next, go to openai.com. If you do not have an account, sign up and log in to your account. Click on the Developers tab at the top of the webpage. Click API reference and select your account settings at the top right corner of the screen, similar to the one shown in the screenshot below.
Choose View API keys and select +Create new secret key. Click the green box to the right of your generated key to copy. Now go back to the Excel spreadsheet you previously started.
Create a tab in your workbook called “API.” Paste the secret key in cell A1 on the API tab of the Excel workbook. In the Code Editor window, give your script a name. I named mine “ChatGPT,” as you can see in the screenshot below.
Then click Save script.
Next, let’s create a submit button to run our code. Click the ellipsis in the Code Editor window and choose + Add Button (see the screenshot below).
Left-click on your new button and choose Format Shape. Then you can change the formatting of your button, including dragging the button to the location you like on the spreadsheet and changing the text. See the screenshot below for the spreadsheet I set up.
If you are creating your own spreadsheet, be sure to format it where the question/request can be entered into cell B1 of a sheet called “Request” and the answer populate in B3 of that same sheet. Also, create another sheet called “Result.” Otherwise, you will need to make some edits to the code for it to run properly.
At this point, your spreadsheet should be ready to use ChatGPT to answer questions/requests entered into cell B1 on the Request tab. Let’s look at some examples of how to use this workbook. I have a spreadsheet that can be included in the workbook with ChatGPT or in a different workbook. I included mine in the same workbook. The spreadsheet contains a list of auditors, total amount to bill to the client for each auditor and day, and the department, Financial Reporting (FR) or Information Technology (IT). Several rows have been hidden to save space. I would like to sum all amounts from column E, for just the FR spend, in cell C46. Then, in cell C47, sum all amounts for just the IT spend.
I went back to the request tab of my workbook and entered the question shown in the screenshot below to determine the formula for cell C46.
The response I received is shown in the screenshot below.
Copy and paste the formula into cell C46 of the spreadsheet containing your auditors. It works! See the screenshot below.
Do the same for the total IT spend.
Let’s do another example. I would like a formula that describes each of my customers as being over budget, near budget, or neither over budget nor near budget. See the spreadsheet in the screenshot below.
I wrote my instructions in cell B1 of my request tab. See the screenshot below. Notice, I included in the last sentence to leave the cell blank if the customer was not over budget or near budget.
ChatGPT responded with the formula shown in the screenshot below.
When I copied this formula into cell D2 and copied it down, it worked perfectly.
Besides using ChatGPT for assistance with formulas, you can use it to analyze financial statements, create lists of information, create datasets based on real or fictional information, and the list goes on and on. Notice that all of the answers in this built-in ChatGPT appear in cell B3 only, so just one cell. This could be a problem if you do use this to create a list or dataset, since manipulating or analyzing data that is all in one cell would not work. This is the reason for the Results tab. This type of data will be separated into different rows on the Results tab, making it possible to use effectively.
Note that I tried different ways of asking my questions and also regenerating the answers, and sometimes I received an inaccurate response. This is to be expected. Just give the response a try in Excel and see if it works. Be sure you check the accuracy. Although it may sometimes give inaccurate information, ChatGPT is still a great tool to help with those complex formulas, learning a new feature, and all kinds of other things.
Below is a video I made building ChatGPT into Excel and using it for the examples above.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
