If you’re a Microsoft Excel spreadsheet user, then you already know that not so many of us are experts when it comes to writing the formulas that are needed to produce correct data sets.
So, instead of wasting hours of your precious time and energy trying to figure out what formulas to use with your Excel files, why not enlist the help of OpenAI’s amazing artificial intelligence chatbot, ChatGPT, to support you in these annoying tasks?
When you have a lot of data that needs processing, knowing how to use ChatGPT to write Excel formulas may just help to save the day. In this blog, we’re going to show you how to do just that with a simple example that will get you started…
Setting Up The ChatGPT API In Microsoft Excel
First off, if you don’t already have the Microsoft Office suite of applications on your computer, then you can access Excel and the other Office programs for free with a month’s trial. However, in order to do this, you’ll need to create a Microsoft account (if you use Skype, then you will already have one).
First off, if you don’t already have the Microsoft Office suite of applications on your computer, then you can access Excel and the other Office programs for free with a month’s trial. However, in order to do this, you’ll need to create a Microsoft account.
To create an account, visit office.com and click ‘Sign in’ to enter your details. If you don’t yet have an account, choose ‘Sign up for the free version of Office’ and follow the instructions.
Once you’ve logged into your account, you then need to click on the Create option, which you’ll find underneath the Home option on the left-hand side of the screen.
Next, click on the Excel workbook option, and it will take you to a new spreadsheet from which you can now add ChatGPT to play along with.
When you’ve got your spreadsheet open, you’ll now need to click on File to bring up another list of options.
Add In the ChatGPT Add-In
Scroll down to Get Add-ins, and then click on Visit the Add-in Store. You’ll probably need to input ‘ChatGPT’ into the search box onscreen.
When you find it, click on it to continue the installation. Once all of this has been done, a pop-up box will appear, guiding you to log into your OpenAI account so that you can get a ‘new secret’ API key. This is what you will need to so you can use ChatGPT with Excel seamlessly.
When you’re in your OpenAI account, click on API keys under the User tab on the left-hand side of the screen.
You will then be prompted to create your new secret API key by giving it a name, which is optional. We chose to call it ‘Excel Office365’ to avoid any confusion. Once you click on create, your new secret key will be generated.
Make sure that you take a note of your secret API key, because you won’t be able to access it again if anything goes wrong. This is due to OpenAI’s security policy.
You can always make a new one if you screw up the setup process, though.
However, it should be noted that some people have been experiencing issues when trying to integrate Excel spreadsheets with ChatGPT, just like we did. We persisted though, after reading through some of the Help files, and were able to get our secret API key integrated after a couple more attempts.
How To Use ChatGPT To Write Excel Formulas
We created a quick table in Excel as an example. It is shown below. For details on how to use ChatGPT to work with tables, see here. Also see How to give ChatGPT a table for additional information.
Now it’s time to look at how ChatGPT can be used to write formulas in Excel. We’re going to use a simple data set to do a basic calculation with the formulas that the AI chatbot was able to generate for us.
We have a table of fruits with their projected and actual prices per kilo. Using ChatGPT, we want to calculate the differences in the prices (Column D) and add the subtotals of Columns B, C, and D.
Our prompt for ChatGPT, using the AI.ASK command was:
AI.ASK: write a formula subtracting the value in cell C2 from the value in B2
But unfortunately for us, like other users on the Discord boards, we were having trouble with ChatGPT working with Excel. With luck this won’t happen to you, but if it does, you can go right to a ChatGPT window to do it.
So, went to our ChatGPT account and asked there:
And here was the answer:
So, we added it…
And it worked:
The really cool thing about using ChatGPT to help you to write Excel formulas is that all you have to do is give it the right prompt describing what you want to do, and you will get a formula! You can then simply copy and paste into the relevant spreadsheet cell.
In addition, the AI chatbot’s generated response for your formula will include an explanation on how you should use it, which takes all the pain out of trying to create the formula yourself. The correct answer to your specific query should appear in the right cell automatically.
The AI chatbot’s generated response for your formula will include an explanation on how you should use it, which takes all the pain out of trying to create the formula yourself. The correct answer to your specific query should appear in the right cell automatically.
Final Thoughts
It’s common knowledge that some people find spreadsheets difficult to navigate, so if you’re not an expert, then the formulas you need to use to make things work can become the stuff of nightmares.
But now that you know how to use ChatGPT to write Excel formulas for you, in just a few simple steps, you’ll easily be able to do basic calculations with the support of generative AI doing all the hard work for you.
