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 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 (April 16, 2025) : The add-in has been updated for the latest OpenAI models - gpt-4.1, gpt-4.1-mini, gpt-4.1-nano, o4-mini and o3.

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.

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

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.

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")

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")

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

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)

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
  Dim jsonBody As String, model As String

  'API Info
  API = "https://api.openai.com/v1/chat/completions"
  api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxxxx"
  model = "gpt-4o"
 
  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, " ")


  ' Check if model starts with "o"
  If Left(LCase(model), 1) = "o" Then
      jsonBody = "{""model"": """ & model & """, ""messages"": [{""content"":""" & text & """,""role"":""user""}]}"
  Else
      jsonBody = "{""model"": """ & model & """, ""messages"": [{""content"":""" & text & """,""role"":""user""}], " _
            & """temperature"": 1, ""top_p"": 0.7, ""max_tokens"": 2048}"
  End If

  '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 jsonBody
   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
    
    ' Locate the "content": " substring
    startPos = InStr(jsonString, """content"": """)
    If startPos = 0 Then
        ExtractContent = "Error: 'content' key not found."
        Exit Function
    End If
    
    ' Move startPos to actual content start
    startPos = startPos + Len("""content"": """)
    
    ' Locate the closing quote for content
    endPos = InStr(startPos, jsonString, """")
    If endPos = 0 Then
        ExtractContent = "Error: Closing quote for 'content' not found."
        Exit Function
    End If
    
    ' Extract content
    Content = Mid(jsonString, startPos, endPos - startPos)
    
    ' Fix escaped quotes
    Content = Replace(Content, "\""", """")
    
    ' Prevent Excel formulas from auto-executing
    If Left(Content, 1) = "=" Then Content = "'" & Content
    
    ' Remove line breaks
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")

    ' Return cleaned content
    ExtractContent = Trim(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. o3-mini, o1-mini, o1, 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

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
  Dim jsonBody As String, model As String

  
  'API Info
  API = "https://api.openai.com/v1/chat/completions"
  api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxx"
  model = "gpt-4o"
  
  '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, " ")
  
  ' Check if model starts with "o"
  If Left(LCase(model), 1) = "o" Then
      jsonBody = "{""model"": """ & model & """, ""messages"": [{""content"":""" & text & """,""role"":""user""}]}"
  Else
      jsonBody = "{""model"": """ & model & """, ""messages"": [{""content"":""" & text & """,""role"":""user""}], " _
            & """temperature"": 1, ""top_p"": 0.7, ""max_tokens"": 2048}"
  End If
          
  '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 jsonBody
   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
    
    ' Locate the "content": " substring
    startPos = InStr(jsonString, """content"": """)
    If startPos = 0 Then
        ExtractContent = "Error: 'content' key not found."
        Exit Function
    End If
    
    ' Move startPos to actual content start
    startPos = startPos + Len("""content"": """)
    
    ' Locate the closing quote for content
    endPos = InStr(startPos, jsonString, """")
    If endPos = 0 Then
        ExtractContent = "Error: Closing quote for 'content' not found."
        Exit Function
    End If
    
    ' Extract content
    Content = Mid(jsonString, startPos, endPos - startPos)
    
    ' Fix escaped quotes
    Content = Replace(Content, "\""", """")
    
    ' Prevent Excel formulas from auto-executing
    If Left(Content, 1) = "=" Then Content = "'" & Content
    
    ' Remove line breaks
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")

    ' Return cleaned content
    ExtractContent = Trim(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 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

Excel Tutorials : Top 100 Excel Tutorials

Spread the Word!
Share Share Tweet

About Author:

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.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn