How to Use LLMs for Intelligent Data Filling in Excel

You can use Large Language Models (LLMs) like OpenAI GPT for intelligent data filling in Excel. It can streamline tasks such as completing missing values, generating categorical data, or performing complex data analysis directly within a spreadsheet. By integrating an LLM with Excel, you can automate data filling based on context or natural language instructions. In this article, we will show how to use LLMs for intelligent data filling in Excel.

What is LLM?

A Large Language Model (LLM) is an advanced machine-learning model trained to understand and generate human-like text. LLMs are trained on vast datasets, making them capable of interpreting context, understanding complex prompts, and generating relevant responses. It is ideal for tasks like filling data gaps in Excel intelligently.

Step 1: Set Up Your OpenAI Account to Get API Key

To use LLM you will need to get OpenAI’s API from your OpenAI account.

If you don’t have an account, sign up at OpenAI and get your key.
If you already have an account, log in to the OpenAI account.
From Dashboard >> select API Key >> click on Create new secret key.
In Create new secret key box;
- Name the key >> select Permissions >> click on Create secret key.

In the Save your key dialog box;
- Copy the generated API key and store it in a safe place to authenticate your API requests. It won’t be visible once you create an API key.
- Click on Done.

Never share your API key publicly or expose it in client-side code.

Step 2: Use VBA to Call GPT for Data Filling

You can integrate LLMs OpenAI GPT with Excel using VBA (Visual Basic for Applications). The LLM will help fill in missing data, generate suggestions, or analyze existing data intelligently.

Go to the Developer tab >> select Visual Basic.
In the VBA editor, go to Insert >> select Module.
Insert the following VBA code in the Module to create a user-defined function.

VBA Code:

    Dim ApiKey As String
    Dim inputData As String
    Dim response As String
    Dim url As String
    Dim xhr As Object
    Dim requestBody As String
    Dim jsonResponse As Object
    Dim jsonText As String
    Dim Prompt As String
    Dim cell As Range
    Dim namesArray As Variant
    Dim i As Integer
    Dim cleanedText As String
    
    ' Your OpenAI API key
     ApiKey = "Your_OpenAI_API_key" ' Replace with your actual API key.
    
    ' Collect input data from a specific range, e.g., A1:A10 with missing data
    For Each cell In Range("A1:A10")
        inputData = inputData & cell.Value & ", "
    Next cell
    inputData = Left(inputData, Len(inputData) - 2) ' Remove the last comma and space
    
    ' OpenAI API endpoint for chat completion
    url = "https://api.openai.com/v1/chat/completions"
    
    ' Prompt for GPT-3.5 Turbo
    Prompt = "Fill in the missing data based on this data series: " & inputData
    
    ' Construct the request body for chat completion
    requestBody = "{""model"": ""gpt-3.5-turbo"", ""messages"": [{""role"": ""user"", ""content"": """ & Prompt & """}], ""max_tokens"": 100, ""temperature"": 0.5}"
    
    ' Create the XMLHttpRequest object
    Set xhr = CreateObject("MSXML2.XMLHTTP")
    
    ' Open a POST request to the API
    xhr.Open "POST", url, False
    
    ' Set the necessary headers
    xhr.setRequestHeader "Content-Type", "application/json"
    xhr.setRequestHeader "Authorization", "Bearer " & ApiKey
    
    ' Send the request with the JSON body
    xhr.send requestBody
    
    ' Get the JSON response from the API
    response = xhr.responseText
    
    ' Parse the JSON response to extract the assistant's message content
    Set jsonResponse = JsonConverter.ParseJson(response)
    jsonText = jsonResponse("choices")(1)("message")("content")
    
    ' Clean the output string by removing leading phrases (like "The missing data is:")
    cleanedText = Replace(jsonText, "The missing data is:", "")
    cleanedText = Trim(cleanedText) ' Remove any leading/trailing whitespace

    ' Split the names by comma and trim any extra whitespace
    namesArray = Split(cleanedText, ",")
    
    ' Place each name in cells B1 to B10
    For i = 0 To UBound(namesArray)
        Range("B" & i + 1).Value = Trim(namesArray(i)) ' Trim to remove extra spaces
    Next i
    
End Sub

Explanation:

API Key Setup: Store your OpenAI API key in the ApiKey variable, replacing “Your_OpenAI_API_key” with your actual API key.
Data Collection: It will loop through cells A1 to A10 to collect non-empty values and concatenate them into inputData, separated by commas.
Constructing the API Request:
- Set the API endpoint URL for OpenAI’s chat completions.
- Prepared a prompt in Prompt that asks the model to fill in missing data based on the collected input.
- Created requestBody as JSON, specifying the model (gpt-3.5-turbo), prompt, and additional parameters like max_tokens.
  - Temperature: Control the randomness of GPT-3’s response. A lower value like 0.1 results in more deterministic outputs, whereas a higher value like 0.8 adds more creativity.
  - Max Tokens: Set the length of the response, depending on how much information is needed.
Send API Request: Created an XMLHttpRequest, set headers, and send requestBody.
Parse Response: Store and parse the JSON response to get generated content (requires JsonConverter).
Output to Excel: Clean and split the response by commas into namesArray. Then loop through namesArray and write each value into cells B1 to B10, trimming any extra.

Step 3: Install JSON Parsing Library

As Excel does not natively support JSON parsing you will need to download VBA-JSON from GitHub to parse JSON responses from OpenAI.

Download JsonConverter.bas from GitHub.
In the VBA editor, go to File tab >> select Import File.

In the Import Box >> select JsonConverter.bas to add it to the project.

Enable References:

You will need to enable Microsoft Scripting Runtime reference it is necessary because the JsonConverter library relies on Dictionary objects to manage JSON data structures.

Go to Tools tab >> select References.
In the Available References box >> check Microsoft Scripting Runtime >> click OK.

Step 4: Prepare Your Data and Run the VBA Script

Insert some incomplete data in your range (e.g., A1:A10). We inserted some names where some cells are empty.
Go to the Developer tab >> select Macros.
From the Macro dialog box >> select Intelligent_Data_Fill_LLM >> click Run.

Output:
LLMs intelligently filled in the missing names in Excel.

Use Cases for Intelligent Data Filling

If your dataset has missing values, LLMs can intelligently infer what those values should be based on the surrounding data.

If a few names are missing from a list of employees, it can suggest names based on the context or pattern.
You have a list of items that need to be classified. It can infer categories based on existing patterns.
You can fill in missing dates or steps in a time series dataset.

Conclusion

You can save time, reduce errors, and enhance your Excel experience by using LLMs for intelligent data filling in Excel. It speeds up workflows with the right prompts that involve incomplete or messy data, making the process smoother and more efficient. This step-by-step guide will help you to intelligently fill data gaps using OpenAI’s large language models directly in Excel.