In this post, we will talk about various ways to integrate ChatGPT into MS Excel. OpenAI also has a public API which makes it easy to embed ChatGPT in any web application or software. We can use it in MS Excel as well using VBA code.
How to Get ChatGPT API
To get an OpenAI API Key, please follow the steps below -
- Sign up using this link- platform.openai.com with your existing Google or Microsoft account.
- Click on the API keys tab in the dashboard page to access the API key page.
- Click on the
Create new secret key
button to create a new API key. It will look like the key below. Copy your API key for future reference.
sk-xxxxxxxxxxxxxxx
Method 1 : Excel Plugin for ChatGPT
Unlike other ChatGPT plugins for Excel, this plugin is absolutely free to use and can run on any version of Excel. However, please note that the OpenAI API does have charges associated with it. I have been using it daily for the past few months and it has cost me an average of 50-65 cents per month. No charges apply if API is not used in a month.
For detailed information about the pricing, it is recommended to refer to OpenAI's pricing page. To set up billing, click on the billing section in your account settings and then followed by clicking on "Start payment plan" button and make payment.
You can download the Excel plugin for ChatGPT by clicking on Download button. In Excel, a plugin is referred to as an add-in. Don't get confused. Plugin and add-in are essentially the same in MS Excel.
See the video below on how to use and install this add-in
When you download add-in or macro file from internet, Microsoft blocks them from running and shows the source of this file is untrusted. You need to follow the steps below to make it working.
- Go to the folder where the downloaded add-in file is located.
- Right-click the file and choose Properties from the menu.
- At the bottom of the General Tab, select the Unblock checkbox under security option and then click OK.
Refer the following steps to install ChatGPT add-in in MS Excel.
- Open Excel and click on the
File
tab in the ribbon. - Click on
Options
and then selectAdd-ins
from the left-hand menu. - In the
Manage
drop-down menu at the bottom of the screen, selectExcel Add-ins
and click on theGo
button. - Click on the
Browse
button and locate the add-in file that you downloaded. - Select the add-in file and click on the
OK
button. - You should see the name of the add-in file in the
Add-Ins
dialog box. Check the box next to the add-in name to activate it. - Once you are done with the above steps, a new tab called
ChatGPT
should be visible in your Excel workbook.
Follow the steps below to use ChatGPT add-in for MS Excel.
- Open a new or existing MS Excel Workbook
- Enter text you want to ask ChatGPT in any cell
- Click on ChatGPT Tab > AI Assistant.
- Enter your API Key and select Model Type.
- Select the cell in which you entered text in step 2.
- Output will be generated and will appear within a few seconds.
- When you want ChatGPT to run on multiple cells, you can use the
AIAssistant
function. See the details in the next section.
This add-in can generate both text and images and has the following features:
- Text Generation
- Multi-turn Conversations (Chat)
- Fill Missing Data
- Extract Key Information
- Translate Text
- Learn Excel Formulas with ChatGPT
- Data Insights
- Building Question and Answering System
- Handling Image Inputs
- Image Generation
Text Generation
The AIAssistant
function generates text output based on a search query using ChatGPT.
AIAssistant(text, [word_count])
- text : Text you want to search
- word_count : Optional. Specify the maximum number of words for the output generated by ChatGPT.
=AIAssistant("First President of US")
If you want to limit the response from ChatGPT to a maximum of 2 words, specify '2' as the second argument of the function. For example, =AIAssistant("First President of US", 2)
.
In the example below, as shown in the image, we are extracting the capital cities of various countries.
Sometimes non-english letters do not get displayed correctly in the response coming from ChatGPT API due to UTF-8 encoding issue. To fix this issue you can apply the CleanMsg
function to the output of AIAssistant or any other function. Please refer to the example below.
=CleanMsg(AIAssistant("Write a post about 'Good morning' in Spanish"))
Chat Conversations
By default when ChatGPT API answers your question, it does not remember your previous conversations. It is important for AI to remember previous conversations in order to better understand you so that it can improve its responses each time.
The function AIAssistant_Chat
is used to activate multi-turn conversations.
AIAssistant_Chat(text, [reset])
- text : Text you want to search
- reset : Optional. Set it to TRUE to start/reset chat session.
It is recommended to set the second argument of the function reset
as TRUE when you do not need ChatGPT to recall your prior conversations.
Data Insights
Please follow the steps below to generate data insights or find hidden patterns in your data with this plugin.
- Click on the
Generate Insights
button in the ChatGPT tab on the ribbon in Excel. - In the message dialog box, select cells containing data (including the header) and then click "OK". For example, A1:D101.
- Wait for the insights generated from ChatGPT API.
- In the message dialog box, select a cell where the output will be saved. For example, F2.
- Select "yes" or "no" if you want the output to be populated in multiple rows or a single row.
The function AIAssistant_Insights
is used to generate insights from data.
AIAssistant_Insights(rng_data, [prompt])
- rng_data : Specify cells containing data (including the header).
- prompt : Optional. Provide specific information you want about the data.
=AIAssistant_Insights(A1:D101,"Generate Top 5 Insights")
Translate with ChatGPT
The function AIAssistant_Translator
is used to translate text. Just enter the text you want to translate and the language name, it will give you translations.
AIAssistant_Translator(text, language)
- text : Specify the cell that contains text you want to translate.
- language : Name of language. E.g. Spanish, French etc.
=AIAssistant_Translator("Hi, How are you doing?", "german")
Extract Key Info
The AIAssistant_Extractor
function can be used to fetch key information such as name, location etc from the text.
AIAssistant_Extractor(prompt, keyword)
- prompt : Specify the cell that contains text from which you want to extract key data.
- keyword : Keyword can be name, place, organization etc.
Suppose you have text "Dave went to New York for studies" in cell B2. You want to extract name and location from the text. Enter the following formula in cells C2 and D2.
=AIAssistant_Extractor(B2,"name") =AIAssistant_Extractor(B2,"location")
Fill Missing Data
The AIAssistant_FillData
function can be used for a variety of purposes. See some of the real-world use cases below.
- Sentiment Analysis You can label positive/negative/neutral sentiments on customer feedback data. You just need to provide a few labels in first argument of the function.
- Industry/Sector Classification You have companies name and wish to find out their industries/sectors.
- Text Patterns Extraction You have text data which contains both character and numeric values. By using this function, you can extract numeric values from the text.
AIAssistant_FillData(rng_existingdata, rng_fill)
- rng_existingdata : Range of training data
- rng_fill : Specify the cell that needs to be filled in.
Building Question and Answering System
The AIAssistant_QnA
function is used to build a question and answering system. You input a paragraph or a text document along with your question and it helps you find the relevant answer within that text. It can be useful for tasks like quickly extracting information from large text without the need to manually search through the text.
AIAssistant_QnA(query, passage)
- query : Specify the cell containing a question.
- passage : Specify the cell containing a large text.
=AIAssistant_QnA(B6, B3)
You can also specify multiple cells for a passage like in the example below.
=AIAssistant_QnA(A2, A3:A10)
Handling Image Inputs
The AIAssistant_Image
function takes an image as input and answers questions about it. As of now, only these models GPT-4o and GPT-4 Turbo have vision capabilities.
AIAssistant_Image(prompt, image_path, [detail],[max_tokens])
- prompt : Specify the cell containing a prompt (question).
- image_path : Specify the complete location of an image.
- detail : Optional. Low or high resolution of the image for processing.
- max_tokens : Optional. Maximum number of tokens.
=AIAssistant_Image("What's in the image?","C:\Downloads\Soccer.jpg") =AIAssistant_Image("How many people in the image?","C:\Downloads\Soccer.jpg")
Image Generation
The createImage
function generates images based on your description. It returns a URL which you can paste into the browser to access the created image.
createImage(prompt, [size])
- prompt : Specify the cell containing the image description.
- size : Optional. Size of the image. Default value is 1024x1024.
=createImage("young boy with a red background") =createImage("young boy with a red background", "1024x1024")
Learn Excel Formulas with ChatGPT
The AIAssistant_Explain
function helps you explain any Excel formula.
AIAssistant_Explain(cell_formula, [detail])
- cell_formula : Cell that contains an Excel formula that you want ChatGPT to explain to you.
- detail : Optional. Default value is TRUE. Set FALSE if you want less verbose resonse.
Let's say you have a formula in cell B2: =VLOOKUP(B2,$B$2:$D$5,2,FALSE). If you enter this formula =AIAssistant_Explain(B2) in cell C2, it will provide an explanation for the VLOOKUP function.
How to Handle Large Number of Prompts
Suppose you have more than 100 prompts distributed across 100 cells in an Excel sheet and you want to get answers for each prompt using ChatGPT. If you attempt to do this all at once by dragging the AIAssistant function to 100 rows, it may cause Excel to slow down or show errors because it can't handle so many requests together.
To fix this issue, you can ask one question at a time instead of sending all the questions to ChatGPT at once. Then move on to the next question and repeat the process. In the code below, we assumed prompts starting from cell B3.
Sub AIAssistantLoop() Dim ws As Worksheet Dim promptColumn As String Dim promptRng As String Dim startRow As Long Dim cell As Range Dim result As Variant Dim lastRow As Long ' Set the active sheet as the working sheet Set ws = ActiveSheet ' Set the column having prompts. For e.g., prompts starting from cell B3 promptColumn = "B" startRow = 3 ' Find the last row in column lastRow = ws.Cells(ws.Rows.Count, promptColumn).End(xlUp).Row ' Loop through cells to the last row promptRng = promptColumn & startRow & ":" & promptColumn & lastRow For Each cell In ws.Range(promptRng) ' Check if the cell is not empty If Not IsEmpty(cell.Value) Then ' Handle errors and continue with the next iteration On Error Resume Next ' Call the AIAssistant function with the cell value result = Application.Run("AIAssistant", cell.Value) ' If there was an error, skip to the next iteration If Err.Number <> 0 Then Err.Clear On Error GoTo 0 GoTo NextCell End If ' Reset error handling On Error GoTo 0 ' Paste the value back into the adjacent column cell.Offset(0, 1).Value = result End If NextCell: Next cell End Sub
Run the macro by pressing Alt+F8, selecting the macro AIAssistantLoop
and clicking the Run button.
If you are still getting errors, you can add a wait time between each iteration. You can use the Application.Wait Now + TimeValue("00:00:01")
code immediately after NextCell: in the code above to wait for one second before the next iteration.
If you also want to use CleanMsg
function, use this Application.Run("CleanMsg", Application.Run("AIAssistant", cell.Value))
Method 2 : Excel Macro for ChatGPT
This method is simply an alternative to using an add-in. It contains VBA code that fetches responses from ChatGPT using the API and places them into Excel. It also cleans the responses from ChatGPT and puts them in a structured format to maintain formatting.
Sub chatGPT() Dim request As Object Dim response As String Dim text, API, api_key, DisplayText, error_result As String Dim status_code As Long Dim cellr As Range Dim prompt, rng As Range 'API Info API = "https://api.openai.com/v1/chat/completions" api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxxxx" If api_key = "" Then MsgBox "Error: API key is blank!" Exit Sub End If Set prompt = Range("B3") Set cellr = prompt.Offset(1, 0) Set rng = Range(prompt.Offset(1, 0), prompt.Offset(2000, 0)) rng.Clear 'Input Text If prompt.Value = "" Then MsgBox "Error: Cell " & prompt.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is blank!" Exit Sub End If text = Replace(prompt.Value, Chr(34), Chr(39)) text = Replace(text, vbLf, " ") 'Create an HTTP request object Set request = CreateObject("MSXML2.XMLHTTP") With request .Open "POST", API, False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Authorization", "Bearer " & api_key .send "{""model"": ""gpt-4o-mini"", ""messages"": [{""content"":""" & text & """,""role"":""user""}]," _ & """temperature"": 1, ""top_p"": 0.7, ""max_tokens"": 2048}" status_code = .Status response = .responseText End With 'Extract content If status_code = 200 Then DisplayText = ExtractContent(response) Else DisplayText = ExtractError(response) End If 'Put response cellr.Value = DisplayText 'Split to multiple rows Call SplitTextToMultipleRows(cellr) rng.WrapText = True 'Clean up the object Set request = Nothing End Sub Sub SplitTextToMultipleRows(cell As Range) Dim splitArr() As String Dim delimiter As String delimiter = "\n" splitArr = Split(cell.Value, delimiter) For i = LBound(splitArr) To UBound(splitArr) x = splitArr(i) If Left(Trim(x), 1) = "=" Then x = "'" & x End If cell.Offset(i, 0).Value = Replace(x, "\""", Chr(34)) Next i End Sub Function ExtractContent(jsonString As String) As String Dim startPos As Long Dim endPos As Long Dim Content As String startPos = InStr(jsonString, """content"": """) + Len("""content"": """) endPos = InStr(startPos, jsonString, "},") - 2 Content = Mid(jsonString, startPos, endPos - startPos) Content = Trim(Replace(Content, "\""", Chr(34))) 'Fix for excel forumulas as response If Left(Trim(Content), 1) = "=" Then Content = "'" & Content End If Content = Replace(Content, vbCrLf, "") Content = Replace(Content, vbLf, "") Content = Replace(Content, vbCr, "") If Right(Content, 1) = """" Then Content = Left(Content, Len(Content) - 1) End If ExtractContent = Content End Function Function ExtractError(jsonString As String) As String Dim startPos As Long Dim endPos As Long startPos = InStr(jsonString, """message"": """) + Len("""message"": """) endPos = InStr(startPos, jsonString, """") If startPos > Len("""message"": """) And endPos > startPos Then ExtractError = Mid(jsonString, startPos, endPos - startPos) Else startPos = InStr(jsonString, """code"": """) + Len("""code"": """) endPos = InStr(startPos, jsonString, """") If startPos > Len("""code"": """) And endPos > startPos Then ExtractError = Mid(jsonString, startPos, endPos - startPos) Else ExtractError = "Unknown error" End If End If End Function
- You need to enter your API key in the code above in the variable api_key. This is the same secret key which we obtained in the previous step.
- Enter your question (prompt) in cell B3 and then follow the steps below.
- gpt-3.5-turbo, gpt-4, gpt-4-turbo, gpt-4o-mini and gpt-4o always point to the latest released models. To know more about the latest models, check out the documentation.
- Press Alt+F11 to open the VBA editor.
- Click Insert > Module to create a new module.
- In the module, copy and paste the VBA code mentioned above.
- Once you have entered the code, close the VBA editor.
- Run the macro by pressing Alt+F8 or by going to the Developer tab > Macros and select the macro
chatGPT
and hit Run button.
Method 3 : Excel Function for ChatGPT
You can also run ChatGPT as an excel function. The benefit of running it as function is that you can paste it down on multiple cells.
Function AIAssistant(text As String, Optional word_count As Long = 0) As String Dim request As Object Dim response As String Dim API, api_key, DisplayText, error_result As String Dim startPos, endPos, status_code As Long Dim rng As Range 'API Info API = "https://api.openai.com/v1/chat/completions" api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxx" 'Input Text If word_count > 0 Then text = text & ". Provide response in maximum " & word_count & " words" End If text = Replace(text, Chr(34), Chr(39)) text = Replace(text, vbLf, " ") 'Send request to API Set request = CreateObject("MSXML2.XMLHTTP") With request .Open "POST", API, False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Authorization", "Bearer " & api_key .send "{""model"": ""gpt-4o-mini"", ""messages"": [{""content"":""" & text & """,""role"":""user""}]," _ & """temperature"": 0.7, ""top_p"": 1, ""max_tokens"": 2048}" status_code = .Status response = .responseText End With 'Parse response from API If status_code = 200 Then DisplayText = ExtractContent(response) Else DisplayText = ExtractError(response) End If If word_count > 0 And Right(DisplayText, 1) = "." Then DisplayText = Left(DisplayText, Len(DisplayText) - 1) Else DisplayText = DisplayText End If 'Return result AIAssistant = DisplayText End Function Function ExtractContent(jsonString As String) As String Dim startPos As Long Dim endPos As Long Dim Content As String startPos = InStr(jsonString, """content"": """) + Len("""content"": """) endPos = InStr(startPos, jsonString, "},") - 2 Content = Mid(jsonString, startPos, endPos - startPos) Content = Trim(Replace(Content, "\""", Chr(34))) 'Fix for excel forumulas as response If Left(Trim(Content), 1) = "=" Then Content = "'" & Content End If Content = Replace(Content, vbCrLf, "") Content = Replace(Content, vbLf, "") Content = Replace(Content, vbCr, "") Content = Replace(Content, "\n", vbCrLf) If Right(Content, 1) = """" Then Content = Left(Content, Len(Content) - 1) End If ExtractContent = Content End Function Function ExtractError(jsonString As String) As String Dim startPos As Long Dim endPos As Long startPos = InStr(jsonString, """message"": """) + Len("""message"": """) endPos = InStr(startPos, jsonString, """") If startPos > Len("""message"": """) And endPos > startPos Then ExtractError = Mid(jsonString, startPos, endPos - startPos) Else startPos = InStr(jsonString, """code"": """) + Len("""code"": """) endPos = InStr(startPos, jsonString, """") If startPos > Len("""code"": """) And endPos > startPos Then ExtractError = Mid(jsonString, startPos, endPos - startPos) Else ExtractError = "Unknown error" End If End If End Function
Make sure you enter your API key highlighted in bold above.
Follow the steps 1 to 4 in the previous section "How to use VBA Code". Once you are through with these 4 steps, type =AIAssistant(B3)
in any cell. Here B3 refers to the cell wherein your prompt (question) is placed.
How to Use ChatGPT to Write Excel Formulas
You can use the above add-in or macro and ask ChatGPT to act like an excel tutor and help you in writing Excel formulas.
=AIAssistant("Excel formula for 'Pass' if a value in cell F5 is less than 70, else 'Fail'")
The following are some examples of prompts you can use to ask ChatGPT to generate Excel formulas for you.
- Write an Excel formula to count the number of cells in the range B2:B10 that contain the value "Pass".
- I am working on a project. I am asked to analyze stock prices. How can I create an Excel formula to calculate the percentage change between two consecutive days' closing prices, assuming prices in cells B1 and B2?
- I have a dataset with sales figures in column A and corresponding dates in column B. How can I use an Excel formula to calculate the total sales for October 2022?
- In my sales data, I want to calculate the total sales for a 'Product A'. Sales value in the range A1:A10. Product names in range B2:B20. How can I write an Excel formula for this?
- I have numbers for revenue in different months. How can I use an Excel formula to calculate the rate of growth from month to month?
How to Use ChatGPT to Write VBA Code
It also works well for advanced excel concepts like macros. It can write VBA code for you. Writing VBA code has never been easier before with ChatGPT.
=AIAssistant("Write VBA code to apply filter on sheet1 and paste filtered rows to sheet2")
The following are a few examples that you can use to request ChatGPT to help you write VBA Code.
- How can I loop through cells A1 to A10 in VBA?
- How can I create a user-defined function in VBA to calculate the factorial?
- How do I create a message box in VBA?
- VBA code to copy cells A1 to A10 from one worksheet to another?
- How can I format cells B2 to B5 in Excel using VBA to apply bold font and a specific background color?
How to Fine Tune ChatGPT Response
You would find temperature
parameter in the VBA code. It lies between 0 and 2. Higher values like 1.2 will generate the output more random, while lower values like 0.2 will be more focused.
Troubleshooting Errors
1. To fix the issue of non-English text not being shown correctly, use the CleanMsg( ) function.
2. Error: You exceeded your current quota, please check your plan and billing details : To resolve this error, check your usage limits and monthly budget. Refer OpenAI's Help Guide.
ChatGPT Plugin for MS Word
ChatGPT Plugin for MS PowerPoint.
Hey Deepanshu - really enjoyed your ChatGPT VBA code. Any chance you could create an add in so that it is not dependent on the personal workbook? I tried converting it to the add-in but no luck.
ReplyDeleteThe Excel add-in has been released and updated in this post.
DeleteI did everything, but I still get a "no input" when I try to use the Ai assitant... also I see no hits on my API code in Chat GPT... does that mean I have the API key wrong?
ReplyDeleteare you getting "no input" in a message box or in a cell? what exact steps did you follow?
DeleteIt says "no input found" in a message box...
ReplyDeleteTry with formula =AIAssistant(B2). Does formula work for you? "no input found" message box is returned when you don't select cell in the input box.. What's your operating system and Excel version?
DeleteThe issue has been fixed with the new addin for Mac operating system
DeleteHi, I tried to use the excel macro for ChatGPT, but it is always giving me an error saying run time error 429 ActiveX component can't create object. And when I try to debug the code, it points to this syntax -> Set request = CreateObject("MSXML2.XMLHTTP"). Any idea on what might be wrong?
ReplyDeleteIt looks like you are running it on Mac OS. Try Excel Addin for Mac
DeleteI tried that. But it's still not working... I even deleted everything and repeated the steps again. It is still showing the same issue?
DeleteWhat error are you getting in Add-in?
DeleteHi there Deepanshu, I am facing the same issue. When I run the macro, it takes me to the VBA Editor and highlights this formula Set request = CreateObject("MSXML2.XMLHTTP"). The error is "Run time error "429": Active X component can't create object"
DeleteThis macro doesn't work on MAC OS. Use addin instead for Mac OS
DeleteHello! Thank you for the solution. I tried inputting my API key (I am on mac) but it keeps saying that the key is incorrect even though I have tried many different times. It won't let me copy paste and I was wondering if you had any solutions!
ReplyDeleteCan you tell me the complete error message? It seems to be coming directly from OpenAI API. I am confused when you say "It won't let me copy paste", what does it mean?
DeleteThis issue is not related to add-in or Mac compatibility. OpenAI is unable to accept your key. I would suggest generating a new API key and deleting the previous one.
DeleteHi Deepanshu, thanks for your great tool. Unfortunately, I always get the following error as an answer: "We could not parse the JSON body of your request. (HINT: This likely means you aren't using your HTTP library correctly. The OpenAI API expects a JSON payload, but what was sent was not valid JSON. If you have trouble figuring out how to fix this, please contact us)"
ReplyDeleteI tried at my end and it's working fine. are you using windows version of add-in or Mac? What's the version of Excel?
DeleteThis error generally occurs when you input invalid characters. Enter "capital city of spain" in cell B2 (without quotes) and then use =AIAssistant(B2)
DeleteHi Deepanshu, we are using windows with excel 2013. Maybe thats the problem? I did everything correct with the excel fomular. Do i need a json converter or somthing? Thank you so much for your help!
DeleteWindows with Excel 2013 shouldn't be an issue. I'm not sure what's causing this issue. Can you try with the addin for Mac? I know you are using Windows OS, not MAC. But the addin for Mac is a bit complex and designed for both windows and mac.
DeleteHi Deepanshu, I use now the MAC version but a new error came: "You exceeded your current quota, please check your plan and billing details." I upgraded my OpainAI account now so it works now. Thank you for your help!
DeleteI am glad it worked for you. Cheers!
DeleteThis is great. Thanks for building it. --> I'm using a long text question to rewrite a big chunk of text in a cell. it works okay for 1 cell, but when I do more than 2 at a time it crashes. is there a way to add a delay so it doesn't run all the queries at once (I assume). W11 Excel 365
ReplyDeleteHow big is your text?
Deletebetween 50 to 400 words
DeleteYou can write a simple macro which loops over multiple cells one by one using AIAssistant() function
DeleteThank you for your macro, it works perfectly. Can you please make an upgrade to macro too (that ChatGPT will remember previous conversation)? Thank you, David.
ReplyDeleteThank you for sharing! I'm using a mac and the plug-in doesn't seem to be working. The cell stays blank after I select the cell with the question in the input window. The macro also doesn't work by displaying Run-time erro "429" ActiveX component can't create object, is there any solutions to this?
ReplyDeleteIf the cell stays blank, most likely api key is invalid or you exceeded your current quota. I have made a minor change in the add-in to display error. Download the add-in again. It will show you the exact error code.
DeleteHello, I want to have the result in czech language but the result is with the bad letters. How can I fix this? Here is a result: vynikajÃcà olej pro ÄtyÅ™taktnà motory motocyklů a skútrů. Thank you very much!
ReplyDeleteI have just tested the same on the Hindi language, and it works with non-English languages. I mean, if you prompt in the Czech language, it should respond in Czech language only. ChatGPT was primarily trained on English text. While it can handle certain non-English languages to some extent, the performance may vary, and it may not fully support or accurately respond in languages like Czech. I am really not sure if this is the limitation of OpenAI model or some API issue.
DeleteI added a function called CleanMsg( ) for non-English letters. Download and install the add-in again and see if it works for Czech language
DeleteThank you! This is neat. I have the add-in installed. I can run searches with GPT3.5 turbo, but not 4. The following message appears in the cell " The model: `gpt-4` does not exist" I have a gpt4 plus membership/account. Am I doing something wrong? Im on a windows, but i've tried both add-ins. Thanks again
ReplyDeleteThe openai API will say that the model doesn't exist if your api key doesn't have access to gpt-4. The gpt-4 plus subscription isn't same as the gpt-4 api key so you'll have to request an api key here
Deletehttps://openai.com/waitlist/gpt-4-api
First of all, I love your solution. I have tried a few options to integrate Excel with GPT and this by far works the best. Hoping you can help me with a problem. I have a few questions that are related in my cells. So the 1st question is to ask GPT to generate a description based on a product. I have a list of about 20 products that I want to loop through buy my actual questions are hardcoded across the top few rows in cells B5..B8. I wrote a small VBA app that uses the AIAssistant_Chat function first and sets the optional parameter to TRUE like this: Cells(i, "C").formula = "=AIAssistant_Chat($B$5 & B" & i & ", TRUE)". This works fine. For the next cell in my code I want to ask ChatGPT a question remembering the previous one so I use this code: Cells(i, "D").formula = "=AIAssistant_Chat($B$6, FALSE)". For the first row of data, it works fine and remembers the context of the 1st question but when I start a new row of data, I get a good response from the 1st call to GPT (with the TRUE parameter) but the next call with the FALSE parameter I get "Unknown Error" in the cell. No error is thrown during execution of the macro but the value returned from GPT is not correct. Let me know if you have any ideas. I can share my code separately as well.
ReplyDeleteSometimes, the ChatGPT API returns strange errors when there are multiple HTTP requests within a few seconds. You can try using the 'Paste Values' method after inserting a formula at each iteration in a loop. For example, Range('B5').PasteSpecial xlPasteValues. This is done to prevent Excel from recalculating the function, just in case. Additionally, you can try adding a few seconds of sleep time at each iteration of the loop. Please let me know if this solution works.
DeleteHello! I want to thank you for your work. I've been using your code for a week now. but today something happened and the code is not working. error #value don't know what the problem is?
ReplyDeleteThanks for reporting this issue. I have fixed it. Please download the updated add-in/VBA code. Let me know if there are any issues.
Deletei used method 3 (vba). changed vba code, but it didn't help. also tried using add-in for mac, it doesn't work either (yesterday it worked)
DeleteWhat error you are getting? are you sure you are using the updated add-in/code?
DeleteVba code - "#value"
DeleteAdd-in - "unknown error"
yes of course, using the updated code and add-in (mac_v3).
Could you upload the finished file with vba code (method 3) here or on a file hosting service? maybe I'm doing something wrong.
DeleteTry add-in if you are not comfortable with VBA code
DeleteHello! Compliments to you for creating this project and blog, I love it and it is very useful. I have used the add-in method to translate my excel sheets from English to Italian and from English to Norwegian for a month already. Unfortunately, since two-three days ago this functionality could not be performed properly. It substitutes the specific language letters with random characters. I see you are active on resolving issues that people have with this project so, I hope that this issue could be resolved too.
ReplyDeleteThis appears to be an issue stemming from the OpenAI API, as reported by several users on the OpenAI forum. They have noted a decline in the quality of responses over the past few weeks. Given that you have been using the add-in for a month, it is highly probable that you have been utilizing an older version. I recommend downloading the latest add-in and giving it another try. Let me know if it works.
DeleteI have downloaded it again just now and it still displays the weird characters. To verify that the issue I am getting is same here is an example that I tested.
DeleteFunction: =AIAssistant("Translate the following from English to Norwegian: " & A1)
Sentence in English: I like eating pork.
Translated result from the function: Jeg liker å spise svinekjøtt.
Translated result from google translate: Jeg liker å spise svinekjøtt
Is there something that could be done to solve this issue?
I fixed this issue by adding a function called CleanMsg( ) for the same . Use this
Delete=CleanMsg(AIAssistant("Translate the following from English to Norwegian: " & A1))
Amazing stuff! It works as it did before. Thank you very much Deepanshu, I wish you great health and be blessed.
DeleteGood Job - thanks for that! Recently I've faced with issue, when asking "=AIAssistant_Chat(A1)" A1 cell contains more than 250 characters - it shows me results #VALUE! (Previously it was working fine). Tried to reduce number of characters in request and found out that it works only if you request query below 70 characters. Could you help me here to solve this issue? (Using on Mac with ChatGPT_V4.xlam plug-in). Thanks in advance
ReplyDeleteChatGPT API sometimes returns weird errors. I just tested it with more than 500 characters, and it is working fine on my end. Make sure to reset the chat by using the second argument as TRUE. For example, AIAssistant_Chat(text, TRUE). AIAssistant_Chat keeps your previous history, so it's always recommended to use the second argument as TRUE or use the AIAssistant() function instead of AIAssistant_Chat() when you don't require chat functionality.
DeleteIs AIAssistant() function working fine on these requests? Also try it in a new workbook.
DeleteHi,
ReplyDeleteI have used your macro, connected API and it´s working fine. I have just one issue with formating.
It gives me czech language but in this format: Změna kódovánà v textovém.
Should be: Změna kódování v textovém.
Do you have any recommendation what to do with this, please?
Thank you
Tomas
I made a few changes in the add-in. It should fix the translation. Download and install the add-in again. Let me know if it works.
DeleteHello Deepanshu, first of all its a really nice work. Much better then the MS Plugin cause it works in every Excel Version, so I love it very much.
ReplyDeleteI got a question about using this as a makro plugin. is it possible to adapt the macro so that it can execute multiple prompts? As an example: I want to enter several prompts one below the other in column C, i.e. one prompt in C2, one prompt in C3, and so on.
Then I would like to execute the macro so that the results appear in column D. So the result of the prompt C2 in D2,
the result of C3 in D3, and so on.
If the rows are empty, there should be no response.
I know that I can also use the function method for this, but as a macro it would use less memory, because with several input prompts Excel is overwhelmed.
The code below runs AIAssistant function from the add-in and run it one by one in loop...
DeleteSub RunAIAssistantLoop()
Dim ws As Worksheet
Dim cell As Range
Dim result As Variant
Dim lastRow As Long
' Set the active sheet as the working sheet
Set ws = ActiveSheet
' Find the last row in column C
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
' Loop through cells C1 to the last row
For Each cell In ws.Range("C1:C" & lastRow)
' Check if the cell is not empty
If Not IsEmpty(cell.Value) Then
' Call the AIAssistant function with the cell value
result = Application.Run("AIAssistant", cell.Value)
' Paste the value back into the adjacent column
cell.Offset(0, 1).Value = result
End If
Next cell
End Sub
You are the very best, couldn't believe that it works. Of course I also asked ChatGPT before, but the GPT awnser didn't work at all. Seems that we already need humans :D Just one more question, cause I don't know much about the VBA syntax. I need exactly this, but forgot to mention that that I need the result nested with the CleanMsg function because I use umlauts. Which part do I have to modify and how?
DeleteI would like to make a small remark about the AIAssistant_Chat() function.
If a cell is specified in the first parameter, no second reset parameter can be added. So AIAssistant_Chat(B2, TRUE) would not work and gives an error message. I don't know if it is the same with the normal AIAssistant function in terms of word count
You are the very best! Couldn't believe that it works. But I forgot to mention that I need the result nested with the CleanMsg function because I use umlauts. Which part do I have to modify and how?
DeleteI would like to make a small remark about the AIAssistant_Chat() function.
If a cell is specified in the first parameter, no second reset parameter can be added. So AIAssistant_Chat(B2, TRUE) would not work and gives an error message. I don't know if it is the same with the normal AIAssistant function in terms of word count
Use this -- Application.Run("CleanMsg", Application.Run("AIAssistant", cell.Value)) instead of Application.Run("AIAssistant", cell.Value)
DeleteThank you for this plugin. Just a query, how do I select a dataset and ask chatgpt for key insights ? I believe excel 365 has that functionality. Is that possible in this plugin also ? Thanks and. Great work
ReplyDeleteI have added this feature. Please download the plugin again and read the article on how to use it.
DeleteFirst off Deepanshu Thank you for putting this out! I have been able to install and use, but I wanted to know if I could use this to query an already established spreadsheet full of data? I have many columns and many rows and would love to be able to 'ask it questions' and have GPT return answers around the data. Thoughts?
ReplyDeleteI have added this feature. Please download the plugin again and read the article on how to use it.
Delete
ReplyDeletecan you add another option for changing API Host to custom host , or can i have a password for your script
ReplyDeletecan you add another option Forwarding Host/API Host/custom endpoints for adding my custom host for my custom api key
Is it possible to add the 'stream':true parameter to VBA code?
ReplyDeleteSure I'll see the ways to add it in the next release of addin. Thanks!
DeleteHi Deepanshu,
ReplyDeleteThanks for this tool, it looks fabulous!
When I try using it (through the 'AI Assistant' button), however, I get an error message box that says 'No input found'.
When using the formula =AIASSISTANT(A1) with the question in A1, I get a #VALUE! error message.
What could be the problem?
Thanks,
Jason
It seems either the API is blocked on your end, your API key has expired, or it is not a paid one. I just checked again at my end and it works fine.
DeleteDeepanshu, Awesome tools! I'm a director of QA in a manufacturing facility so I mainly use "insights" on data retrieved from equipment PLC's. A problem I have is that I have to reformat any insights that have a timevalue as the bot returns decimal values. Is there a workaround? Here's an example of a return, "1. The maximum available time is 1 unit, with an average run state time of 2.04417618553133E-03 and 107 run states, accounting for 24.7% of the available time and 21.9% of the total time." Thanks so much, Jimmy
ReplyDeleteTry this in the prompt - "Generate Insights. Make sure not to convert timestamp to decimal values in the reply."... See if it works..
DeleteI've tried every way I can think of to tell it "... convert all time values to the format of hh:mm:ss), h:mm:ss, h:mm, etc.. it still returns insights with decimals for time.
DeleteAlternative way is to convert the output to time format using Excel formula - =SUBSTITUTE(A1,MID(A1, SEARCH("time of ", A1) + 8, SEARCH(" ", A1, SEARCH("time of ", A1) + 8) - SEARCH("time of ", A1) - 8),TEXT(MID(A1, SEARCH("time of ", A1) + 8, SEARCH(" ", A1, SEARCH("time of ", A1) + 8) - SEARCH("time of ", A1) - 8),"hh:mm:ss"))
DeleteHi, this is unbelievable!
ReplyDeleteIs it possible when using the QnA function to have multiple passages such as a range rather than an individual cell? As I have a table with previous Questions (Column x) and Answers (Column Y), and I'm wanting to answer new questions with information from previous answers (stored in that table)?
You can concatenate multiple passages before passing it in the function. Hope it helps
DeleteIn the updated version, it allows a range rather than an individual cell =AIAssistant_QnA(A2, A3:A10)
DeleteHi Deepanshu, I'm having a problem with the Translator function. I am using it to translate from Italian to English but the function doesn't always work properly. For example, for the Italian word "ingorgo", it gave the following result: "The term "ingorgo" is Italian and it translates to "traffic jam" in English." Why doesn't the function just give the result "traffic jam"? In another case, the result stated that the function could not determine the language of the source text. Would it be possible to add a parameter to the function that specifies the language of the source text? Thanks!
ReplyDeleteThat's the limitation of chatgpt model. The function returns what's coming from the API. You can try with specifying the language name before text separated by delimiter (--).
DeleteI've noticed that ChatGPT does not translate Italian. Using the add-in, it's the only Null column in my spreadsheet.
DeleteChat does a good job translating Vietnamese and other non-European languages.
I have just tried and it works. Example : =AIAssistant_Translator("I love you","italian")
DeleteHello, the Api is not working anymore - it tells me all the time: "you exceeded you current Quota,... " but its Not!
ReplyDeleteCan someone help me?
Raise issue in OpenAI Developer Forum - https://community.openai.com/
DeleteHi, I am using excel 2010. It seems the plugin does not work with this old ver? when I put ==AIAssistant(B2), I got error: excel found an error in the formula you entered.
ReplyDeleteWhy did you enter two == sign before the formula? Just put one equal to sign.
DeleteDeepanshu, great work! What I feel it would be useful to add, in addition to a great too, is logging: log each request made towards OpenAI. There's no easy way to get it from them in case of a billing dispute, hence this could be useful.
ReplyDeleteThanks. I will see how to add this into the next release of the add-in. Cheers!
DeleteHello,
ReplyDeleteI would like to report a bug.
The system does not function well when it needs to return special characters such as the accented characters typical of some languages: àòèì
These characters are replaced with incorrect characters, e.g., (€).
Did you use the function CleanMsg? Read more about this function in the article. Example :
Delete=CleanMsg(AIAssistant("Write an Instagram post about 'Good morning' in Spanish"))
Hi Deepanshu, Thank you very much for giving this.
ReplyDeleteI want to create a chat function with the help of macro code then Which code I can use so that it remember previous response and give a new one based on it?
I Already checked your ADD IN but I want to create something different for my daily needs.
Thanks a lot in advance.
Hello Deepanshu,
ReplyDeletethank you for your great work!
I keep getting an error message in random intervalls: Error 10001, Error parsing JSON Expectiing '{' or '[
I changed one line to get clean utf-8 code to: result = Application.Run("CleanMsg", Application.Run("AIAssistant", cell.Value)), the problem seemed to increase since then.
When many API requests are sent in a short interval, errors can occur. I've added error handling in the code to skip iterations when an error occurs. Additionally, you can include a wait time between each iteration by using the Application.Wait Now + TimeValue("00:00:01") code.
DeleteIs there anyway to refer to the cell in the middle of a sentence ? right now its
ReplyDelete=AIAssistant("First President of the following country" &A, 2) I want to add another sentence after this .Example : "First President of the following country" &A " The answer should be in all caps " ). So the refernce call is in the middle of the sentence
In MS Excel, Ampersand (& sign) is used to concatenate strings or cells. For example : AIAssistant("First President of the following country" &A2& "The answer should be in all caps.")
DeleteThanks is there any way to send a table to chatgpt api rather than only a cell ?
DeleteYes you can loop through cells to the last row of table using VBA
Delete