3 Ways to Integrate ChatGPT into Excel

Deepanshu Bhalla 95 Comments , ,

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 integrate ChatGPT into Excel

How to Get ChatGPT API

To get an OpenAI API Key, please follow the steps below -

  1. Sign up using this link- platform.openai.com with your existing Google or Microsoft account.
  2. Click on the API keys tab in the dashboard page to access the API key page.
  3. 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
Data Privacy : OpenAI's data privacy policy states that they do not train their models on any user data submitted through their API. The inputs and outputs of the API do not become part of training their models.
3 Methods to Use ChatGPT in Excel
  1. Excel Plugin for ChatGPT
  2. Excel Macro for ChatGPT
  3. Excel Function for ChatGPT

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.


Update (July 24,2024) : The add-in has been updated for the latest OpenAI models, GPT-4o-mini and GPT-4o.
Demo Video

See the video below on how to use and install this add-in

How to install 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.

  1. Go to the folder where the downloaded add-in file is located.
  2. Right-click the file and choose Properties from the menu.
  3. 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.

  1. Open Excel and click on the File tab in the ribbon.
  2. Click on Options and then select Add-ins from the left-hand menu.
  3. In the Manage drop-down menu at the bottom of the screen, select Excel Add-ins and click on the Go button.
  4. Click on the Browse button and locate the add-in file that you downloaded.
  5. Select the add-in file and click on the OK button.
  6. 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.
  7. Once you are done with the above steps, a new tab called ChatGPT should be visible in your Excel workbook.
How to use ChatGPT's Excel add-in

Follow the steps below to use ChatGPT add-in for MS Excel.

  1. Open a new or existing MS Excel Workbook
  2. Enter text you want to ask ChatGPT in any cell
  3. Click on ChatGPT Tab > AI Assistant.
  4. Enter your API Key and select Model Type.
  5. Select the cell in which you entered text in step 2.
  6. Output will be generated and will appear within a few seconds.
  7. When you want ChatGPT to run on multiple cells, you can use the AIAssistant function. See the details in the next section.
Features of ChatGPT's Excel Add-in

This add-in can generate both text and images and has the following features:

  1. Text Generation
  2. Multi-turn Conversations (Chat)
  3. Fill Missing Data
  4. Extract Key Information
  5. Translate Text
  6. Learn Excel Formulas with ChatGPT
  7. Data Insights
  8. Building Question and Answering System
  9. Handling Image Inputs
  10. Image Generation

Text Generation

The AIAssistant function generates text output based on a search query using ChatGPT.

Syntax
AIAssistant(text, [word_count])
Arguments
  • text : Text you want to search
  • word_count : Optional. Specify the maximum number of words for the output generated by ChatGPT.
Example
=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.

ChatGPT Excel Function
Handling Non-English Text

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.

Syntax
AIAssistant_Chat(text, [reset])
Arguments
  • text : Text you want to search
  • reset : Optional. Set it to TRUE to start/reset chat session.
Example
ChatGPT's Excel Plugin: Remember previous chats
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.

  1. Click on the Generate Insights button in the ChatGPT tab on the ribbon in Excel.
  2. In the message dialog box, select cells containing data (including the header) and then click "OK". For example, A1:D101.
  3. Wait for the insights generated from ChatGPT API.
  4. In the message dialog box, select a cell where the output will be saved. For example, F2.
  5. Select "yes" or "no" if you want the output to be populated in multiple rows or a single row.
Data Insights using ChatGPT Excel Plugin

The function AIAssistant_Insights is used to generate insights from data.

Syntax
AIAssistant_Insights(rng_data, [prompt])
Arguments
  • rng_data : Specify cells containing data (including the header).
  • prompt : Optional. Provide specific information you want about the data.
Example
=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.

Syntax
AIAssistant_Translator(text, language)
Arguments
  • text : Specify the cell that contains text you want to translate.
  • language : Name of language. E.g. Spanish, French etc.
Example
=AIAssistant_Translator("Hi, How are you doing?", "german")
Translate text with Excel Plugin for ChatGPT

Extract Key Info

The AIAssistant_Extractor function can be used to fetch key information such as name, location etc from the text.

Syntax
AIAssistant_Extractor(prompt, keyword)
Arguments
  • prompt : Specify the cell that contains text from which you want to extract key data.
  • keyword : Keyword can be name, place, organization etc.
Example

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")
Data Extractor using ChatGPT for Excel

Fill Missing Data

The AIAssistant_FillData function can be used for a variety of purposes. See some of the real-world use cases below.

  1. 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.
  2. Industry/Sector Classification You have companies name and wish to find out their industries/sectors.
  3. 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.
Syntax
AIAssistant_FillData(rng_existingdata, rng_fill)
Arguments
  • rng_existingdata : Range of training data
  • rng_fill : Specify the cell that needs to be filled in.
Example
Fill Missing Data in Excel using ChatGPT

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.

Syntax
AIAssistant_QnA(query, passage)
Arguments
  • query : Specify the cell containing a question.
  • passage : Specify the cell containing a large text.
Example
=AIAssistant_QnA(B6, B3)
Building a question and answering system in Excel using ChatGPT

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.

Syntax
AIAssistant_Image(prompt, image_path, [detail],[max_tokens])
Arguments
  • 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.
Example
=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.

Syntax
createImage(prompt, [size])
Arguments
  • prompt : Specify the cell containing the image description.
  • size : Optional. Size of the image. Default value is 1024x1024.
Example
=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.

Syntax
AIAssistant_Explain(cell_formula, [detail])
Arguments
  • 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.
Example

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.

This macro only works on Windows OS. If you want to integrate ChatGPT into MS Excel on Mac OS, please use the add-in shown in the previous section of this article.
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
Enter API Key, Prompt and Model Type
  1. 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.
  2. Enter your question (prompt) in cell B3 and then follow the steps below.
  3. 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.
How to use VBA code
  1. Press Alt+F11 to open the VBA editor.
  2. Click Insert > Module to create a new module.
  3. In the module, copy and paste the VBA code mentioned above.
  4. Once you have entered the code, close the VBA editor.
  5. Run the macro by pressing Alt+F8 or by going to the Developer tab > Macros and select the macro chatGPT and hit Run button.
Demo Output
Demo Output

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'")
ChatGPT Prompts for Excel Formulas

The following are some examples of prompts you can use to ask ChatGPT to generate Excel formulas for you.

  1. Write an Excel formula to count the number of cells in the range B2:B10 that contain the value "Pass".
  2. 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?
  3. 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?
  4. 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?
  5. 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")
ChatGPT to write VBA Code
ChatGPT Prompts for Writing VBA Code

The following are a few examples that you can use to request ChatGPT to help you write VBA Code.

  1. How can I loop through cells A1 to A10 in VBA?
  2. How can I create a user-defined function in VBA to calculate the factorial?
  3. How do I create a message box in VBA?
  4. VBA code to copy cells A1 to A10 from one worksheet to another?
  5. 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.

Refer the detailed guide about how to run ChatGPT in MS Word and PowerPoint
ChatGPT Plugin for MS Word
ChatGPT Plugin for MS PowerPoint.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 95 Responses to "3 Ways to Integrate ChatGPT into Excel"
  1. 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.

    ReplyDelete
    Replies
    1. The Excel add-in has been released and updated in this post.

      Delete
  2. I 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?

    ReplyDelete
    Replies
    1. are you getting "no input" in a message box or in a cell? what exact steps did you follow?

      Delete
  3. It says "no input found" in a message box...

    ReplyDelete
    Replies
    1. Try 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?

      Delete
    2. The issue has been fixed with the new addin for Mac operating system

      Delete
  4. Hi, 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?

    ReplyDelete
    Replies
    1. It looks like you are running it on Mac OS. Try Excel Addin for Mac

      Delete
    2. I tried that. But it's still not working... I even deleted everything and repeated the steps again. It is still showing the same issue?

      Delete
    3. What error are you getting in Add-in?

      Delete
    4. Hi 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"

      Delete
    5. This macro doesn't work on MAC OS. Use addin instead for Mac OS

      Delete
  5. Hello! 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!

    ReplyDelete
    Replies
    1. Can 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?

      Delete
    2. This 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.

      Delete
  6. Hi 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)"

    ReplyDelete
    Replies
    1. I 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?

      Delete
    2. This error generally occurs when you input invalid characters. Enter "capital city of spain" in cell B2 (without quotes) and then use =AIAssistant(B2)

      Delete
    3. Hi 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!

      Delete
    4. Windows 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.

      Delete
    5. Hi 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!

      Delete
    6. I am glad it worked for you. Cheers!

      Delete
  7. This 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

    ReplyDelete
    Replies
    1. between 50 to 400 words

      Delete
    2. You can write a simple macro which loops over multiple cells one by one using AIAssistant() function

      Delete
  8. Thank 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.

    ReplyDelete
  9. Thank 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?

    ReplyDelete
    Replies
    1. If 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.

      Delete
  10. Hello, 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!

    ReplyDelete
    Replies
    1. I 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.

      Delete
    2. I added a function called CleanMsg( ) for non-English letters. Download and install the add-in again and see if it works for Czech language

      Delete
  11. Thank 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

    ReplyDelete
    Replies
    1. The 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

      https://openai.com/waitlist/gpt-4-api

      Delete
  12. 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.

    ReplyDelete
    Replies
    1. Sometimes, 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.

      Delete
  13. Hello! 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?

    ReplyDelete
    Replies
    1. Thanks for reporting this issue. I have fixed it. Please download the updated add-in/VBA code. Let me know if there are any issues.

      Delete
    2. i 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)

      Delete
    3. What error you are getting? are you sure you are using the updated add-in/code?

      Delete
    4. Vba code - "#value"
      Add-in - "unknown error"
      yes of course, using the updated code and add-in (mac_v3).

      Delete
    5. Could you upload the finished file with vba code (method 3) here or on a file hosting service? maybe I'm doing something wrong.

      Delete
    6. Try add-in if you are not comfortable with VBA code

      Delete
  14. Hello! 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.

    ReplyDelete
    Replies
    1. This 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.

      Delete
    2. I 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.

      Function: =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?

      Delete
    3. I fixed this issue by adding a function called CleanMsg( ) for the same . Use this
      =CleanMsg(AIAssistant("Translate the following from English to Norwegian: " & A1))

      Delete
    4. Amazing stuff! It works as it did before. Thank you very much Deepanshu, I wish you great health and be blessed.

      Delete
  15. Good 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

    ReplyDelete
    Replies
    1. ChatGPT 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.

      Delete
    2. Is AIAssistant() function working fine on these requests? Also try it in a new workbook.

      Delete
  16. Hi,

    I 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  17. Hello 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.
    I 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.

    ReplyDelete
    Replies
    1. The code below runs AIAssistant function from the add-in and run it one by one in loop...
      Sub 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

      Delete
    2. 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?
      I 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

      Delete
    3. 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?
      I 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

      Delete
    4. Use this -- Application.Run("CleanMsg", Application.Run("AIAssistant", cell.Value)) instead of Application.Run("AIAssistant", cell.Value)

      Delete
  18. Thank 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

    ReplyDelete
    Replies
    1. I have added this feature. Please download the plugin again and read the article on how to use it.

      Delete
  19. First 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?

    ReplyDelete
    Replies
    1. I have added this feature. Please download the plugin again and read the article on how to use it.

      Delete

  20. can you add another option for changing API Host to custom host , or can i have a password for your script

    ReplyDelete

  21. can you add another option Forwarding Host/API Host/custom endpoints for adding my custom host for my custom api key

    ReplyDelete
  22. Is it possible to add the 'stream':true parameter to VBA code?

    ReplyDelete
    Replies
    1. Sure I'll see the ways to add it in the next release of addin. Thanks!

      Delete
  23. Hi Deepanshu,

    Thanks 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  24. Deepanshu, 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

    ReplyDelete
    Replies
    1. Try this in the prompt - "Generate Insights. Make sure not to convert timestamp to decimal values in the reply."... See if it works..

      Delete
    2. I'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.

      Delete
    3. Alternative 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"))

      Delete
  25. Hi, this is unbelievable!

    Is 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)?

    ReplyDelete
    Replies
    1. You can concatenate multiple passages before passing it in the function. Hope it helps

      Delete
    2. In the updated version, it allows a range rather than an individual cell =AIAssistant_QnA(A2, A3:A10)

      Delete
  26. Hi 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!

    ReplyDelete
    Replies
    1. That'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 (--).

      Delete
    2. I've noticed that ChatGPT does not translate Italian. Using the add-in, it's the only Null column in my spreadsheet.
      Chat does a good job translating Vietnamese and other non-European languages.

      Delete
    3. I have just tried and it works. Example : =AIAssistant_Translator("I love you","italian")

      Delete
  27. Hello, the Api is not working anymore - it tells me all the time: "you exceeded you current Quota,... " but its Not!
    Can someone help me?

    ReplyDelete
    Replies
    1. Raise issue in OpenAI Developer Forum - https://community.openai.com/

      Delete
  28. Hi, 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.

    ReplyDelete
    Replies
    1. Why did you enter two == sign before the formula? Just put one equal to sign.

      Delete
  29. Deepanshu, 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.

    ReplyDelete
    Replies
    1. Thanks. I will see how to add this into the next release of the add-in. Cheers!

      Delete
  30. Hello,
    I 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., (€).

    ReplyDelete
    Replies
    1. Did you use the function CleanMsg? Read more about this function in the article. Example :
      =CleanMsg(AIAssistant("Write an Instagram post about 'Good morning' in Spanish"))

      Delete
  31. Hi Deepanshu, Thank you very much for giving this.
    I 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.

    ReplyDelete
  32. Hello Deepanshu,
    thank 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
  33. Is there anyway to refer to the cell in the middle of a sentence ? right now its
    =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

    ReplyDelete
    Replies
    1. 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.")

      Delete
    2. Thanks is there any way to send a table to chatgpt api rather than only a cell ?

      Delete
    3. Yes you can loop through cells to the last row of table using VBA

      Delete
Next → ← Prev