Home » Excel » How to Integrate Google's Gemini AI into Excel

How to Integrate Google's Gemini AI into Excel

Deepanshu Bhalla 2 Comments , ,

This post covers various ways to integrate Google's Gemini AI Model into MS Excel. Google released a public API for Gemini which can be used to run it inside Excel.

Integrating Gemini AI Model into MS Excel
Update : The add-in has been updated for the latest Gemini models - Gemini 2.5 Flash and Gemini 2.5 Pro.

How to Get Gemini API Key

  1. First step is to visit this link - Google AI Studio and sign in using your Google account.
  2. Next step is to click on "Create API key" button and select existing Google Cloud projects and then create a new API key.

Method 1 : Excel Plugin

This is the fastest and most powerful free Excel plugin for Gemini. It supports parallel so you can get answers for thousands of prompts in seconds.

You can download the Excel plugin for Gemini by clicking on Download button below.


Note - This Gemini plugin is compatible with both Windows and macOS operating systems.

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.
Unblock Setting

Refer the following steps to install Gemini 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 Gemini should be visible in your Excel workbook.

How to Use Gemini's Excel Add-in

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

  1. Open a new or existing MS Excel Workbook
  2. Enter text you want to ask Gemini in any cell
  3. Click on Gemini Tab > Talk to Gemini.
  4. On Windows OS, enter your API Key. On macOS, enter the cell where your API key is stored (e.g., A1).
  5. Select the cell in which you entered text in step 2.
  6. Result will be generated and become visible after processing.
  7. To run on multiple cells, you can use the Gemini() function. Refer to the animated image below.
Gemini AI in MS Excel
Adding Your Own Model - If you don't see any model in the dropdown after clicking the 'Update Key' button, you can type your own model name by editing any existing model name in the dropdown.

Functions of Gemini's Excel Add-in

This add-in has the following functions.

  1. Gemini(text, [word_count]) : Returns output for the question you asked.
    - text : Question you want to ask
    - word_count : Optional. Specify the maximum number of words for the output generated by Gemini.
  2. Gemini_Search(text, [word_count]) : Performs google search and summarise output using Gemini.
    - text : Question you want to ask
    - word_count : Optional. Specify the maximum number of words for the output generated by Gemini.
  3. Gemini_Chat(text, [reset]) : Converse like gemini website which remembers prior conversations.
    - text : Ask the question you want to know.
    - reset : Optional. Start/Reset chat session.
  4. Gemini_FillData(rng_existingdata, rng_fill) : Fill missing data by training Gemini on existing data.
    - rng_existingdata : Range of training data
    - rng_fill : Specify cell that needs to be filled in.
  5. Gemini_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.
  6. Gemini_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.
  7. Gemini_Explain(cell_formula, [detail]) : It helps you understand in detail any Excel formula you want to know.
    - cell_formula : Cell that contains an Excel formula that you want Gemini to explain to you.
    - detail : Optional. Default value is TRUE. Set FALSE if you want less verbose reply.
  8. Gemini_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.
  9. Gemini_QnA(query, passage) : It returns an answer to a question based on the passage you provided.
    - query : Specify the cell containing a question.
    - passage : Specify the cell containing a large text.

Chat Feature

By default when we send API requests to Google, 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 Gemini's ability to remember prior conversations, we can use the function Gemini_Chat(text, [reset]). Set second argument reset as TRUE to start/reset chat.

Chat Functionality using Gemini

Fast Gemini : AI for Many Prompts Together

This add-in can handle many prompts at the same time so you get results for hundreds of questions in just a few seconds. In other words, it sends multiple prompts to the Gemini model simultaneously instead of one by one which makes it much faster.

Click on "Fast Gemini" button in the add-in ribbon, then select the cells where your prompts are entered and finally click "Okay". Results will appear in the adjacent cells of the prompts within seconds.
Warning : If you are on a free plan, sending too many requests at once may hit the rate limit and cause some prompts to fail.

Fill Missing Data

The Gemini_FillData function can be used to fill missing data. All you need is some existing data to train on and then populate the missing data accordingly. It can be beneficial for tasks such as labeling customer reviews and classifying companies into industries.

Fill Missing Data with Gemini

How to Develop Question and Answering System

The Gemini_QnA function helps create a question and answering system. You give it a paragraph or a text document, ask a question and it finds the right answer in that text. It's useful for getting information from big chunks of text without having to search manually. Check out the example below.

=Gemini_QnA(query, passage)
Question and Answering System using Gemini

Method 2 : Excel Macro

The following macro does the same what the Gemini() function of add-in does. It returns response from Gemini AI Model using API based on prompt.

Note - This macro only works on Windows OS. To run Gemini API Model in MS Excel on Mac OS, please use the add-in shown in the previous section of the article.

In the macro below, it assumes prompt or your search query in cell B2.

Sub gemini()
  
  Dim request As Object
  Dim response As String, model 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
  
  'Update API Key and Cell containing prompt
  api_key = "xxxxxxxxxxxx"
  Set prompt = Range("B2")
  model = "gemini-2.0-flash"

  'API Info
  API = "https://generativelanguage.googleapis.com/v1beta/models/" & model & ":generateContent?key=" & api_key

  If api_key = "" Then
        MsgBox "Error: API key is blank!"
        Exit Sub
    End If
    
  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"
     .send "{""contents"":{""parts"":[{""text"":""" & text & """}]},""generationConfig"":{""temperature"":0.5}}"
   status_code = .Status
   response = .responseText
  End With

  'Extract content
  If status_code = 200 Then
    DisplayText = ExtractContent(response)
  Else
    DisplayText = "Error : " & 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 TextValue As String
    
    ' 1) Find the start of the value:
    startPos = InStr(jsonString, """text"": """) + Len("""text"": """)
    
    ' 2) Find the start of the next property (role)
    rolePos = InStr(startPos, jsonString, """role"": """)
    
    ' 3) Backwards from one character before rolePos to the first "
    endQuote = InStrRev(jsonString, """", rolePos - 1)
    
    ' 4) Extract
    TextValue = Mid(jsonString, startPos, endQuote - startPos)

    content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(content), 1) = "=" Then
      content = "'" & content
    End If
    
    If Right(content, 1) = """" Then
      content = Left(content, Len(content) - 1)
    End If
    
    ' 1) Turn literal \n into Windows CRLF
    content = Replace(content, "\n", vbCrLf)
    
    ' 2) handle tabs or escaped quotes
    content = Replace(content, "\t", vbTab)
    
    ExtractContent = Content
End Function

Function ExtractError(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """message"": """) + Len("""message"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas 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
    
    ExtractError = Content
End Function
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, paste the VBA code shown above.
  4. Update API key in the code highlighted in yellow above in the variable api_key. Once done, close the VBA editor.
  5. Run the macro by pressing Alt+F8 or by going to the Developer tab > Macros and select the macro gemini and hit Run button.

Method 3 : User Defined Function

Incase you want to run Gemini AI model as an excel function instead of macro, you can use the code below. It is a custom user-defined function which allows you to run Gemini AI model over multiple cells by just dragging the formula down.

The syntax of function is as follows -

Gemini(text, [word_count])
text : prompt. Question you want to ask.
word_count : optional. Maximum number of words in the result.
Function Gemini(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 status_code As Long
  
  'Update API Key
  api_key = "xxxxxxxxxxxxxxxxx"
  model = "gemini-2.0-flash"

  'API Info
  API = "https://generativelanguage.googleapis.com/v1beta/models/" & model & ":generateContent?key=" & api_key

    If api_key = "" Then
        MsgBox "Error: API key is blank!"
        Exit Function
    End If
       
   
   '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, " ")

  'Create an HTTP request object
  Set request = CreateObject("MSXML2.XMLHTTP")
  With request
     .Open "POST", API, False
     .setRequestHeader "Content-Type", "application/json"
     .send "{""contents"":{""parts"":[{""text"":""" & text & """}]},""generationConfig"":{""temperature"":0.5}}"
   status_code = .Status
   response = .responseText
  End With

  'Extract content
  If status_code = 200 Then
    DisplayText = ExtractContent(response)
  Else
    DisplayText = "Error : " & ExtractError(response)
  End If
    
  'Clean up the object
  Set request = Nothing
  
  'Return result
  Gemini = DisplayText

End Function


Function ExtractContent(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
	' 1) Find the start of the value:
    startPos = InStr(jsonString, """text"": """) + Len("""text"": """)
    
    ' 2) Find the start of the next property (role)
    rolePos = InStr(startPos, jsonString, """role"": """)
    
    ' 3) Backwards from one character before rolePos to the first "
    endQuote = InStrRev(jsonString, """", rolePos - 1)
    
    ' 4) Extract
    TextValue = Mid(jsonString, startPos, endQuote - startPos)

    content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(content), 1) = "=" Then
      content = "'" & content
    End If
    
    If Right(content, 1) = """" Then
      content = Left(content, Len(content) - 1)
    End If
    
    ' 1) Turn literal \n into Windows CRLF
    content = Replace(content, "\n", vbCrLf)
    
    ' 2) handle tabs or escaped quotes
    content = Replace(content, "\t", vbTab)
    
    ExtractContent = Content
End Function

Function ExtractError(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """message"": """) + Len("""message"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas 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
    
    ExtractError = Content
End Function
Make sure you enter your API key highlighted in yellow above.
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 2 Responses to "How to Integrate Google's Gemini AI into Excel"
  1. StanJuly 7, 2025 at 12:06 AM

    Hi,
    There is an error:
    "Error : Gemini 2.5 Pro Preview doesn't have a free quota tier. For more information on this error, head to: https://ai.google.dev/gemini-api/docs/rate-limits."
    Is there a plan to update the models?

    ReplyDelete
    Replies
    1. Deepanshu BhallaJuly 7, 2025 at 7:05 AM

      I have updated the models. Please download and reinstall add-in. Cheers!

      Delete
Next → ← Prev
Subscribe to: Post Comments (Atom)