3 Ways to Integrate ChatGPT into Excel

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
Table of Contents

How to Get ChatGPT API

To get OpenAI API Key, the first and foremost step is to sign up using this link - platform.openai.com. You can do it easily through your existing Google or Microsoft account. Once done, you need to get secret API key to use API. It will look like this. Copy your API key for future reference.

sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Is ChatGPT API Free?

The ChatGPT Plugin for MS Excel is absolutely free to use and can run on any version of Excel. It does not even require an Excel 365 license. However, please note that the OpenAI API does have charges associated with it. Charges for API usage are low and vary based on usage. No charges apply if API is not used in a month.

I have been using it daily for the past few months, and it has cost me less than a dollar per month (precisely 50-65 cents per month). For detailed information about the pricing, it's recommended to refer to OpenAI's pricing documentation.

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.

Method 1 : Excel Plugin for ChatGPT

The main benefit of Excel plugin is that it is not dependent on one file like macro workbook and can be easily shared between different workbooks and users. 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.


This ChatGPT plugin is compatible with both Windows and macOS operating systems.

Demo Video : ChatGPT inside Excel

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.
How to Select the Latest ChatGPT Model
gpt-3.5-turbo, gpt-4, and gpt-4-32k always point to the latest released models. To change the model type, click on Update Keybutton shown under the ChatGPT tab in the ribbon in Excel. To know more about the latest models, check out the documentation.
Functions of ChatGPT's Excel Add-in

This add-in has the following functions that you can use to obtain responses from ChatGPT and enter them into Excel.

  1. AIAssistant(text, [word_count]) : Generates output for the search query using ChatGPT.
    - text : Text you want to search
    - word_count : Optional. Specify the maximum number of words for the output generated by ChatGPT.
  2. AIAssistant_Chat(text, [reset]) : Converse like ChatGPT website remembers prior conversations.
    - text : Text you want to search
    - reset : Optional. Start/Reset chat session.
  3. AIAssistant_FillData(rng_existingdata, rng_fill) : Fill incomplete data by training ChatGPT on existing data.
    - rng_existingdata : Range of training data
    - rng_fill : Specify cell that needs to be filled in.
  4. AIAssistant_Extractor(prompt, keyword) : Extract key data from the text you provided. Key Data can be Name, Place, Organization details etc.
    - prompt : Specify the cell which contains text from which you want to extract key data.
    - keyword : keyword can be name, place, organisation etc.
  5. AIAssistant_Translator(text, language) : It translates text from one language to another.
    - text : Specify cell that contains text that you want to translate.
    - language : Name of language. E.g. Spanish, French etc.
  6. AIAssistant_Explain(cell_formula, [detail]) : Help you to explain Excel Formula which you don't understand and need support.
    - 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 reply.
  7. AIAssistant_Insights(rng_data, [prompt]) : It generates data insights from the provided dataset.
    - rng_data : Specify cells containing data (including the header).
    - prompt : Optional. Provide specific information you want about the data.
  8. AIAssistant_QnA(query, passage) : It provides an answer to a question from within a custom large text you provided.
    - query : Specify the cell containing a question.
    - passage : Specify the cell containing a large text.
To type the functions faster, first type =ai, then use the UP or DOWN arrow key to select the function you want, and finally press the Tab key.

Search with ChatGPT

The AIAssistant function uses ChatGPT to search for information and put the results in Excel. See the example below.

=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).

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 use =CleanMsg(AIAssistant(prompt)) function. Please refer to the example below.

=CleanMsg(AIAssistant("Write an Instagram post about 'Good morning' in Spanish"))

Recall ChatGPT Conversations

By default when we send API requests to OpenAI, it does not recall prior conversations. It considers every API request as a new chat. It means when it answers your current question, it does not remember your previous question.

To improve ChatGPT's ability to remember prior conversations, we can use the function AIAssistant_Chat(text, [reset]). Set second argument reset as TRUE to start/reset chat.

ChatGPT's Excel Plugin: Remember previous chats
Note : Both of these functions AIAssistant() and AIAssistant_Chat() generate output for your search query. However AIAssistant_Chat() also allows ChatGPT to remember your prior conversations which increases the token usage and cost more. 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.

You can also analyze data using the function AIAssistant_Insights(rng_data, [prompt]). The second argument of the function is optional, and by default, it extracts key insights from the data if you don't specify anything in the second argument. See the syntax of AIAssistant_Insights() function below.

=AIAssistant_Insights(A1:D101,"Generate Top 5 Insights")

Translate with ChatGPT

If you're looking to translate text using Excel Plugin, you should try the AIAssistant_Translator function. It uses ChatGPT to help you with accurate translations. Just enter the text you want to translate and the language name, it will use ChatGPT's language skills to give you translations.

=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. For 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.
Fill Missing Data in Excel using ChatGPT

Building a Q&A 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. See the example below.

=AIAssistant_QnA(query, passage)
Building a question and answering system in Excel using ChatGPT

Learn Excel Formulas with ChatGPT

The AIAssistant_Explain function assists you in understanding any Excel formula. For 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.

If you are happy with the above add-in, you don't need to jump to the following two methods of integrating ChatGPT into Excel. The methods below are just alternatives of using add-in but they are less robust and include manual steps.

Method 2 : Excel Macro for ChatGPT

The VBA code below fetch responses from ChatGPT using API and put it into Excel. It also cleans the response from ChatGPT and put it in structured format so that formatting gets maintained. This lets users edit response easily and easy to reuse in any other document or spreadsheet.

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-3.5-turbo"",  ""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 and Prompt
  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.
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. See the animated image below how to use the function.

Syntax of function is
AIAssistant(text, [word_count])
text : prompt. Question you want to ask.
word_count : optional. Maximum number of words in the response that will be generated.

In the example below as shown in image, we are extracting capital city of different countries. Since ChatGPT responses are generally in more words than are needed, we can limit to maximum number of words in the second argument of function.

ChatGPT Excel Function
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-3.5-turbo"",  ""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 GPT-4

To access GPT-4, you can replace gpt-3.5-turbo with gpt-4 in the above VBA code. The OpenAI Team regularly updates their models and they have various versions of them but they make sure that users get the latest model version in gpt-4 or gpt-3.5-turbo.

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 question 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 solve this problem, you can try a different approach. Instead of asking all the questions to ChatGPT at once, you can ask one question at a time and get its answer. 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
    
        ' 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

The code above uses the AIAssistant function from the add-in. If you want to include the second parameter word_count, use this Application.Run("AIAssistant", cell.Value, 2) instead of Application.Run("AIAssistant", cell.Value). Here 2 is set as word_count. Similarly, if you also want to use CleanMsg function, use this Application.Run("CleanMsg", Application.Run("AIAssistant", cell.Value))

If you want to use the function of Method III rather than the function from add-in, replace Application.Run("AIAssistant", cell.Value) with AIAssistant(cell.Value)

Follow the steps below to run the code.

  1. Open the VBA editor by pressing Alt+F11.
  2. Create a new module by clicking Insert > Module.
  3. In the module, copy and paste the above VBA code. Then, close the VBA editor.
  4. Execute the macro by pressing Alt+F8, selecting the macro AIAssistantLoop, and clicking the Run button.

How to use ChatGPT to write Excel formulas

You can also use the above add-in or macro and ask ChatGPT to act like an excel tutor. Within excel, you may be interested to know about excel functions, macros or other features related to data analysis. People who want to use Excel but don't know much about it are likely to find it really helpful. It not only saves your time but also makes data analysis easier. See the example below wherein we asked ChatGPT about simple excel related question.

=AIAssistant("Excel formula for 'Pass' if a value in cell F5 is less than 70, else 'Fail'")
ChatGPT for Excel formulas
ChatGPT Prompts for Excel formulas

Here are some examples that you can use to ask ChatGPT to write an Excel formula 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 am trying to manage my budget and want to know the percentage of expenses compared to my total budget. Can you help me create an Excel formula for this?
  6. 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?
  7. I am working with employee data and need to find the average salary for employees in the 'Sales' department. How do I make an Excel formula for this given Salary in range B2:B20 and Department in range A2:A20?
  8. In my sales report, I need to calculate the commission for each salesperson based on their performance. How can I create an Excel formula that considers different commission rates for different sales levels?
  9. I have an Excel sheet with student grades as percentages. I need to assign letter grades based on these percentages. Can you show me how to create an Excel formula for this?

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

Here 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. How to implement error handling in VBA for a specific block of code?
  5. VBA code to copy cells A1 to A10 from one worksheet to another?
  6. How can I format cells B2 to B5 in Excel using VBA to apply bold font and a specific background color?
  7. How do I add or subtract days from a specific date in VBA?
  8. How can I prompt the user for input and store the value in a variable in VBA?

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.

How to fix errors

1. Error: You exceeded your current quota, please check your plan and billing details : To resolve this error, you need to switch to a paid account where you will be charged based on your API usage.

  1. Go to OpenAI's website
  2. Log in with your account and then navigate to Billing page.
  3. Sign up for the Pay as you go plan by adding your debit/credit card.

If you are already on this plan, it means that you are making a large number of requests to ChatGPT, which it is unable to handle. OR your limit may be set too low. You can check your current quota in your account settings.

2. If you are getting this error - The model: gpt-4 does not exist it means you don't have access to GPT-4 API. You need to join waitlist for it. It does not matter if you have ChatGPT Plus subscription or not. Having a ChatGPT Plus subscription does not automatically grant access to the GPT-4 API.

3. If you encounter any issue related to VBA references, try to add Microsoft XML, v 3.0. I am using Office 2019 in which the above code works without it. References I am using are as shown below. References can be added by opening VBA Editor and then Tools > References.

VBA References
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.

74 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. 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
    3. 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. 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
  21. 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
  22. 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
  23. 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
  24. 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

Next → ← Prev