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.
How to Get Gemini API
To get an API Key for the Gemini AI model, all you need to do is visit this link - makersuite.google.com and sign up using your Google account.
The Gemini API is free as of now but it will most likely have charges in the future.
Method 1 : Excel Plugin
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.
- Go to the folder where the downloaded add-in file is located.
- Right-click the file and choose Properties from the menu.
- At the bottom of the General Tab, select the Unblock checkbox under security option and then click OK.
Refer the following steps to install Gemini add-in in MS Excel.
- Open Excel and click on the
File
tab in the ribbon. - Click on
Options
and then selectAdd-ins
from the left-hand menu. - In the
Manage
drop-down menu at the bottom of the screen, selectExcel Add-ins
and click on theGo
button. - Click on the
Browse
button and locate the add-in file that you downloaded. - Select the add-in file and click on the
OK
button. - You should see the name of the add-in file in the
Add-Ins
dialog box. Check the box next to the add-in name to activate it. - Once you are done with the above steps, a new tab called
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.
- Open a new or existing MS Excel Workbook
- Enter text you want to ask Gemini in any cell
- Click on Gemini Tab > Talk to Gemini.
- Enter your API Key.
- Select the cell in which you entered text in step 2.
- Result will be generated and become visible after processing.
- To run on multiple cells, you can use the
Gemini()
function. Refer to the animated image below.
Functions of Gemini's Excel Add-in
This add-in has the following functions.
-
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. -
Gemini_Chat(text, [reset]) : Converse like Bard chatbot which remembers prior conversations.
- text : Ask the question you want to know.
- reset : Optional. Start/Reset chat session. -
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. -
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. -
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. -
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. -
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. -
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.
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.
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)
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 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 = "XXXXXXXXXXXXXXXXXX" Set prompt = Range("B2") 'API Info API = "https://generativelanguage.googleapis.com/v1beta/models/gemini-pro: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 startPos = InStr(jsonString, """text"": """) + Len("""text"": """) 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 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
- Press Alt+F11 to open the VBA editor.
- Click Insert > Module to create a new module.
- In the module, paste the VBA code shown above.
- Update API key in the code highlighted in yellow above in the variable
api_key
. Once done, close the VBA editor. - 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 = "XXXXXXXXXXXXXXXXXX"
'API Info
API = "https://generativelanguage.googleapis.com/v1beta/models/gemini-pro: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
startPos = InStr(jsonString, """text"": """) + Len("""text"": """)
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, "")
Content = Replace(Content, "\n", vbNewLine)
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
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.
Share Share Tweet