AI and Excel Integration (Plugins)

Until now, you've been switching between ChatGPT window and Excel window. Ask AI, copy the answer, paste it in Excel... It's been cumbersome.

Now you can call AI directly from within Excel! Type =GPT("translate this") in a cell and the result appears instantly. You can batch apply AI analysis to hundreds or thousands of data rows.

Integrating AI with Excel dramatically increases work efficiency. Text summarization, sentiment analysis, translation, category classification... You can use these tasks like Excel functions.

In this post, we'll explore various ways to integrate AI with Excel. From Microsoft 365 Copilot to ChatGPT API integration and Google Sheets, we'll cover methods you can apply directly at work, step by step.

1. Microsoft 365 Copilot

Copilot Basic Features

Microsoft 365 subscribers can use Copilot built into Excel.

Key Features:

1. Analyze data in natural language
   "Show me the top 5 products by sales this month"

2. Auto-generate formulas
   "Calculate month-over-month growth rate"

3. Chart recommendations
   "What chart best represents this data?"

4. Data organization
   "Remove duplicate rows and sort by date"

5. Discover insights
   "Find unusual patterns in this data"

Copilot Usage Examples

[Copilot Prompt]
"Analyze the last 12 months of sales data and
tell me seasonal trends and best-selling products"

[Copilot Response]
Analysis results:
- Sales increase 25% on average in summer (June-August)
- Product A accounts for 35% of total sales
- Exceptionally high sales in December (year-end demand)

Created charts and pivot tables.

2. ChatGPT API Integration

OpenAI API Setup

AI Prompt:

"Create a VBA function to call ChatGPT API from Excel.
Store API key securely and return response to cell."

AI Generated VBA:

' Store API key in VBA project properties
' Or save encrypted in separate file

Public Const OPENAI_API_KEY As String = "your-api-key-here"

' ChatGPT call function
Function AskGPT(prompt As String, Optional model As String = "gpt-4") As String
    On Error GoTo ErrorHandler

    Dim http As Object
    Dim url As String
    Dim requestBody As String
    Dim response As String

    Set http = CreateObject("MSXML2.XMLHTTP")

    url = "https://api.openai.com/v1/chat/completions"

    ' JSON request body
    requestBody = "{" & _
        """model"": """ & model & """," & _
        """messages"": [{""role"": ""user"", ""content"": """ & _
        Replace(prompt, """", "\""") & """}]," & _
        """max_tokens"": 1000," & _
        """temperature"": 0.7" & _
        "}"

    ' HTTP request
    With http
        .Open "POST", url, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & OPENAI_API_KEY
        .send requestBody

        If .Status = 200 Then
            response = .responseText

            ' JSON parsing (simple method)
            Dim startPos As Long, endPos As Long
            startPos = InStr(response, """content"": """) + 13
            endPos = InStr(startPos, response, """")

            AskGPT = Mid(response, startPos, endPos - startPos)

            ' Handle escape characters
            AskGPT = Replace(AskGPT, "\n", vbCrLf)
            AskGPT = Replace(AskGPT, "\""", """")
        Else
            AskGPT = "Error: " & .Status & " - " & .statusText
        End If
    End With

    Set http = Nothing
    Exit Function

ErrorHandler:
    AskGPT = "Error: " & Err.Description
End Function

' Use as Excel worksheet function
Function GPT(prompt As String) As String
    GPT = AskGPT(prompt)
End Function

Usage Examples

// Use directly in cell
=GPT("Translate this sentence to English: 안녕하세요")

// Data analysis
=GPT("Analyze this sales data and provide insights: "&A2:A100)

// Text summarization
=GPT("Summarize this customer review in one sentence: "&B2)

// Category classification
=GPT("Classify this product name as Electronics/Clothing/Food: "&C2)

3. Advanced AI Function Library

AI Prompt:

"Create a custom function library for various AI tasks:
- Text summarization
- Sentiment analysis
- Translation
- Category classification
- Keyword extraction"

AI Generated VBA:

' AI Function Library

' Text summarization
Function AI_Summarize(text As String, Optional maxLength As Integer = 100) As String
    Dim prompt As String
    prompt = "Summarize the following text in " & maxLength & " characters or less:" & vbCrLf & text
    AI_Summarize = AskGPT(prompt)
End Function

' Sentiment analysis (Positive/Neutral/Negative)
Function AI_Sentiment(text As String) As String
    Dim prompt As String
    prompt = "Identify sentiment of this text as 'Positive', 'Neutral', or 'Negative' only:" & vbCrLf & text
    AI_Sentiment = AskGPT(prompt)
End Function

' Translation
Function AI_Translate(text As String, targetLang As String) As String
    Dim prompt As String
    prompt = "Translate the following text to " & targetLang & ":" & vbCrLf & text
    AI_Translate = AskGPT(prompt)
End Function

' Category classification
Function AI_Categorize(text As String, categories As String) As String
    Dim prompt As String
    prompt = "Classify this text into one of these categories (" & categories & "):" & vbCrLf & text
    AI_Categorize = AskGPT(prompt)
End Function

' Keyword extraction
Function AI_Keywords(text As String, Optional count As Integer = 5) As String
    Dim prompt As String
    prompt = "Extract " & count & " key keywords from this text, comma-separated:" & vbCrLf & text
    AI_Keywords = AskGPT(prompt)
End Function

' Data validation
Function AI_Validate(value As String, rules As String) As String
    Dim prompt As String
    prompt = "Does this value meet these rules? Answer only 'Yes' or 'No'." & vbCrLf & _
             "Rules: " & rules & vbCrLf & _
             "Value: " & value
    AI_Validate = AskGPT(prompt)
End Function

' Email generation
Function AI_Email(situation As String, tone As String) As String
    Dim prompt As String
    prompt = "Write an email in " & tone & " tone for this situation:" & vbCrLf & situation
    AI_Email = AskGPT(prompt)
End Function

' Data cleaning (remove unnecessary characters, standardize)
Function AI_CleanData(text As String) As String
    Dim prompt As String
    prompt = "Clean and standardize this data, return only clean form:" & vbCrLf & text
    AI_CleanData = AskGPT(prompt)
End Function

Batch Processing Macro

Sub AI_BatchProcess()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim taskType As String

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Select task type
    taskType = InputBox( _
        "Select task type:" & vbCrLf & _
        "1: Summarize" & vbCrLf & _
        "2: Sentiment analysis" & vbCrLf & _
        "3: Translate (English)" & vbCrLf & _
        "4: Extract keywords", _
        "AI Batch Process", "1")

    Application.ScreenUpdating = False

    ' Progress indicator
    Dim progressForm As Object

    For i = 2 To lastRow
        Dim inputText As String
        Dim result As String

        inputText = ws.Cells(i, 1).Value

        ' Perform task
        Select Case taskType
            Case "1"
                result = AI_Summarize(inputText)
            Case "2"
                result = AI_Sentiment(inputText)
            Case "3"
                result = AI_Translate(inputText, "English")
            Case "4"
                result = AI_Keywords(inputText)
        End Select

        ws.Cells(i, 2).Value = result

        ' Update progress
        Application.StatusBar = "Processing... " & _
            Format((i - 1) / (lastRow - 1), "0%") & _
            " (" & i - 1 & "/" & lastRow - 1 & ")"

        ' Consider API limits (requests per second)
        Application.Wait Now + TimeValue("00:00:01")
    Next i

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox "AI batch processing complete.", vbInformation
End Sub

4. Google Sheets Apps Script

AI Prompt:

"Create Apps Script for ChatGPT API integration
to use in Google Sheets."

AI Generated Apps Script:

// Google Apps Script
// Tools > Script Editor

const OPENAI_API_KEY = 'your-api-key-here';

/**
 * ChatGPT API call
 * @param {string} prompt Question or command
 * @param {string} model Model name (default: gpt-4)
 * @return {string} AI response
 * @customfunction
 */
function GPT(prompt, model = 'gpt-4') {
  if (!prompt) return '';

  const url = 'https://api.openai.com/v1/chat/completions';

  const payload = {
    model: model,
    messages: [{
      role: 'user',
      content: prompt
    }],
    max_tokens: 1000,
    temperature: 0.7
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText());

    if (json.choices && json.choices.length > 0) {
      return json.choices[0].message.content;
    } else {
      return 'Error: ' + JSON.stringify(json);
    }
  } catch (e) {
    return 'Error: ' + e.toString();
  }
}

/**
 * Text summarization
 * @param {string} text Text to summarize
 * @param {number} maxLength Maximum length
 * @return {string} Summarized text
 * @customfunction
 */
function AI_SUMMARIZE(text, maxLength = 100) {
  const prompt = `Summarize this text in ${maxLength} characters or less:\n${text}`;
  return GPT(prompt);
}

/**
 * Sentiment analysis
 * @param {string} text Text to analyze
 * @return {string} Positive/Neutral/Negative
 * @customfunction
 */
function AI_SENTIMENT(text) {
  const prompt = `Identify sentiment as 'Positive', 'Neutral', or 'Negative' only:\n${text}`;
  return GPT(prompt);
}

/**
 * Translation
 * @param {string} text Text to translate
 * @param {string} targetLang Target language
 * @return {string} Translated text
 * @customfunction
 */
function AI_TRANSLATE(text, targetLang = 'English') {
  const prompt = `Translate this text to ${targetLang}:\n${text}`;
  return GPT(prompt);
}

/**
 * Add menu
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('AI Tools')
    .addItem('Batch Process', 'AI_BATCH_PROCESS')
    .addToUi();
}

Conclusion

Methods for AI and Excel integration:

• ✅ Using Microsoft 365 Copilot
• ✅ Custom functions with ChatGPT API
• ✅ Google Sheets Apps Script integration
• ✅ Cost optimization with caching and monitoring

In the next post, we'll design office automation workflows that integrate all these techniques.