Working with AI in Excel

Working with AI in Excel – basic scenarios

AI models (also called LLMs, large language models, or artificial neural networks) are powerful tools for analyzing, processing, and transforming data. With the appearance of APIs for AI (the first widely known being ChatGPT from OpenAI), it became possible to connect them directly to Excel and speed up routine tasks.

In this article, I’ve collected real working scenarios I’ve personally used: data table analysis, help with formulas, code generation, and text processing. All of this is now possible directly in Excel through the panel in my !SEMTools add-in. At the end, you’ll find detailed instructions on how to set up access to the right models and where to get API keys.

Analyzing data tables with AI

Among analytical tasks, AI handles classification and categorization best. Common examples include:

• Sentiment analysis for product or service reviews
• Labeling products by attributes (size, shape, color, composition, purpose) from descriptions
• Classifying websites by industry based on meta tags and page descriptions
• Tagging support tickets by type based on call transcripts

Here’s an example of Excel classifying customer reviews using the OpenRouter AI API to detect sentiment and assign a score from 1 to 5:

This can be valuable for product managers, marketers, and business owners who want to find both growth opportunities and problem areas from customer feedback.

Working with functions and formulas

AI can help both in creating complex formulas and explaining their syntax. Describe your task clearly, and you’ll often get a working solution.

Creating formulas

If a solution can be expressed as a combination of functions, AI will usually generate it. If not, it may suggest creating a VBA function or an alternative in Google Sheets. Of course, not every task can be fully handled by AI without custom code.

Suppose you want to extract first names from a full name list, and you’re struggling with the second word. The Deepseek AI can help:

Here’s the formula in text format:

=TRIM(MID(A1, FIND(" ",A1)+1, FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1))

Not only did the AI create a complex formula, it also explained how each part works.

Function syntax reference

Excel keeps adding functions with each release. You can ask AI for examples, explanations, and comparisons of functions like VLOOKUP, XLOOKUP, INDEX+MATCH, and LOOKUP, or for help with array formulas and time series analysis.

Alternative and similar functions

AI can compare similar functions, list their pros and cons, and suggest which one is best for your dataset.

AI for writing VBA code

VBA (Visual Basic for Applications) can turn Excel into a powerful automation hub. AI makes writing VBA procedures and functions easier, even if you’ve never programmed before, by generating code and adding clear comments.

Example: UDF for REGEXREPLACE

Excel doesn’t have built-in regex functions like Google Sheets, but VBA can handle them. Here’s a UDF generated by AI that works like Google’s REGEXREPLACE:

Function REGEXREPLACE(input_text As String, pattern As String, replacement As String, Optional ignore_case As Boolean = True) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = ignore_case
        .pattern = pattern
    End With
    REGEXREPLACE = regex.Replace(input_text, replacement)  
    Set regex = Nothing
End Function

Usage:

=REGEXREPLACE(A2,"\d","X")

This replaces all numbers in A2 with “X”.

Text processing with AI

• Translation: adjust tone, formality, or style while translating
• Proofreading: check grammar, punctuation, logic, and facts
• Rewriting: refresh outdated articles while keeping the tone
• Audience adaptation: explain concepts at different complexity levels
• Summarization: turn long transcripts into concise summaries

Why integrate AI with Excel?

1. Process many prompts at once
2. Automate prompt sending without manual copy-paste
3. Work directly in Excel while AI runs in the background

Direct AI integration with !SEMTools

The !SEMTools add-in gives Excel direct access to APIs like ChatGPT, Deepseek, Gemini, and OpenRouter, letting you process data, translate text, and generate code without leaving Excel.

Installation guide: How to install !SEMTools

Conclusion

From analyzing tables to generating VBA code, AI makes Excel far more powerful. With careful prompts and validation, you can automate tasks, adapt content, and process data faster than ever.

This post is also available in RU.