Working with AI in Excel

AI models, also called LLMs (large language models) or neural networks, are powerful tools for analyzing, processing and transforming data. When AI got APIs (the first one to make noise was, of course, ChatGPT by OpenAI), it became possible to connect them to Excel and solve routine tasks faster.

I’ve collected here the main working scenarios I know or have used myself: table analysis, help with formulas, code generation, text processing. All of this can be done with AI models that you can now call right from Excel, through the panel of my !SEMTools add-in. At the end, you’ll find how to set up access to the models.

Working with AI in Excel – basic scenarios

First of all, remember that AI is not a magic button, but rather a way to free your head from small stuff: quickly build a formula, process text or put together a quick VBA macro or function. Don’t trust everything an AI model does. It was trained on imperfect data and not always reliable facts, and the way it generates an answer is close to procedural generation: it looks extremely convincing, but it isn’t always true.

Still, simple tasks can safely be delegated to AI, especially if you quickly validate the returned data afterwards.

Analyzing data tables with AI

Among analytical tasks, AI is probably best at classification and categorization. For example:

• sentiment analysis of product or service reviews;
• labeling products by attributes (size, shape, color, material, purpose) based on their descriptions;
• classifying websites by industry based on their meta tags and descriptions;
• tagging support tickets by type based on call transcripts.

Here is how Excel handles a typical classification task. The OpenRouter API rates the sentiment of reviews and gives each of them a score from 1 to 5:

Tasks like this are useful for product managers, website owners, marketers and small business owners looking for growth opportunities and problem areas in customer feedback.

AI for Excel functions and formulas

AI can help both to build complex formulas and to explain their syntax. Describe the task clearly enough, and you’ll most likely get a working solution.

Creating formulas

If a task can be solved with a combination of functions, AI will usually build it. If not, it will suggest a custom VBA function or an alternative in Google Sheets. Of course, not every task can be solved by AI without custom code.

Suppose you want to clean text from punctuation marks. Here is what the Deepseek model suggests:

Sometimes the first solution is bad, but if you add hints, it eventually comes back with a much better solution that works for several languages:

Here are a few more questions I’ve devoted whole articles to, and AI can answer them about as well as I can:

• how to remove the first characters in Excel cells?
• how to add text to all cells in Excel?
• can you use regular expressions in Excel?
• how to translate text in Excel?
• how to remove all digits from an Excel cell?

Still, the answer of an AI model is a formula you have to check, type in and fill down. My articles also show how to do the same with !SEMTools right in place, in a couple of clicks.

Function syntax reference

Excel gets new functions with every release. Not everyone has figured out the order of the VLOOKUP arguments, and there is already XLOOKUP, which makes many workarounds unnecessary. AI works well as an interactive reference. Example prompts:

• Give 3 examples of using XLOOKUP to pull values from different tables.
• Write a formula that calculates age from the date of birth and today’s date.
• Write a formula that extracts the domain name from a list of email addresses.
• What are array formulas and how do I use them?
• Which functions are best for analyzing time series (dates, times, intervals)?
• Explain step by step how this formula works: (formula text).

Alternative and similar functions

Official function references don’t always show clearly why one function is better than another for your task. Ask AI to compare similar functions, for example VLOOKUP, XLOOKUP, INDEX+MATCH and LOOKUP, with their pros and cons, and to suggest which one fits your data.

AI for writing VBA code

VBA (Visual Basic for Applications) is a great addition to MS Office: it turns an Excel workbook into a machine that goes far beyond regular spreadsheet work.

One drawback: although it’s one of the simplest programming languages, you still need some skill to create your own scripts and functions. With AI, this has changed completely. Today you don’t need programming skills to create procedures and functions of basic and medium complexity. AI writes them for you and even explains how the code works, with detailed comments.

Writing macros for Excel

Some operations can’t be done with built-in Excel tools and require helper columns, complex formulas and so on. One of them is to remove duplicates without shifting rows. My add-in has such a macro, but you can create one yourself, with a little help from AI.

Creating UDFs (user-defined functions) in VBA

User-defined functions are a very convenient Excel feature, but they require programming skills in general and knowledge of the VBA object model in particular. AI removes this barrier.

The first scenario: you use a complex formula too often. Even if you know its syntax by heart, typing it every time is annoying. For example, this is the formula that simply counts words in a cell:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1

Let’s ask AI to turn it into a UDF, so that it can be called with a simple syntax like =WORDCOUNT(A1). The answer comes quickly:

Function WORDCOUNT(cell As String) As Long
    ' Counts words separated by spaces, ignoring extra spaces
    Dim t As String
    t = Application.WorksheetFunction.Trim(cell)
    If Len(t) = 0 Then
        WORDCOUNT = 0
    Else
        WORDCOUNT = Len(t) - Len(Replace(t, " ", "")) + 1
    End If
End Function

The second scenario is when built-in functions can’t solve the task at all. For example, Google Sheets has had the REGEXMATCH, REGEXEXTRACT and REGEXREPLACE functions for many years: they check a cell against a regex, extract text and replace by pattern. In Excel, they appeared only in recent Excel 365 updates. In older versions, these regex functions are available for free if you have my !SEMTools add-in installed, even the free version.

What if you want formulas in your own file that don’t depend on add-ins? You guessed it. Prompt: “Write a VBA function for replacing text by regular expression, like in Google Sheets. Example: replace all digits in a cell with X”. Here is the UDF the Deepseek model returned:

Example: UDF for 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

How to use it:

• Add the code to a VBA module: Alt+F11 → right-click your workbook → Insert → Module.
• Save the workbook as .xlsm or .xlsb (macro-enabled).
• Enter the formula in the cell you need:

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

The formula replaces all digits in A2 with “X” and returns the result in the cell with the formula. In Excel 365, REGEXREPLACE is already built in, so name your UDF differently, for example REGEXREPLACE_VBA, to avoid a conflict.

The AI also explains how regular expressions work (\d means digits, [A-Z] uppercase letters) and how to modify the function for other tasks.

Keep in mind that AI answers are non-deterministic, in other words, always different. Sometimes you get a function with a minimal set of options and comments, sometimes a more complex solution with detailed explanations. For more predictable results, always specify your request, before or after the first answer.

Text processing with AI

In Excel, AI is useful not only for formulas and data, but also for text: translate a column of descriptions, fix errors, rewrite outdated texts or summarize long transcripts.

Translation

AI translation is slower than specialized APIs like DeepL or Google Translate, but the quality can be very good. And by changing the prompt, you can add translation “settings”:

• how to address the reader (formal or informal “you” in languages that have it);
• more or less formal style;
• more or less vivid and literary language.

In addition, AI can take liberties with idioms and set expressions that can’t be translated literally, and choose the most fitting expression in the other language. For bulk translation with dedicated services like Google Translate and DeepL, !SEMTools has separate translation tools.

Proofreading

If you suspect errors in a text, ask AI to check and highlight them: not only spelling, punctuation and grammar, but also logical contradictions. You can even ask for a fact check, but only with models that can search the web in real time, with an explicit instruction to check facts against reliable sources. Without web access, fact checking is risky: AI models often invent facts that don’t exist.

Rewriting

You can refresh a text by asking AI to rewrite it without losing the original tone and facts. If an article was written long ago, you can check whether the information is still up to date at the same time.

Adapting content for different audiences

The same question can be explained at very different levels. Ask AI why the sky is blue “at a university level”, and then “for a seven-year-old”, and you’ll get two completely different texts.

Summarization

There is less and less time for careful reading. Give AI an article, a meeting transcript or an audiobook, and you save tens of minutes or hours without losing important information: the model extracts the key points itself. I often run live streams on Excel, and if you have the transcript of one of them, a summary is a great way to see whether it’s worth watching.

AI text analysis

A good text is not only the right words, but also a clear structure that both people and search engines understand. AI analysis is most useful in two areas.

SEO analysis

• keyword relevance: how evenly key phrases are distributed and whether they match search queries;
• semantic analysis: finding LSI phrases and topic clusters;
• search intent: does the text answer informational, commercial or navigational queries;
• structure: subheadings, lists, key phrases in the first paragraphs.

Editorial analysis

• style issues: bureaucratic language, tautology, inappropriate metaphors;
• tone and emotion: does the text fit the audience (formal, friendly, provocative);
• logical coherence: contradictions, unnecessary repetition, gaps in the narrative.

It works like an automatic checklist that finds weak spots in the text, from technical to stylistic ones.

Tips for working with AI in Excel

• Write prompts like instructions for an intern. The more specific the question, the more precise the answer. Bad: “Write something cool about a fitness tracker”. Good: “Write a sales text for a fitness tracker, 5 sentences, audience: men 30–45, focus on heart rate tracking and stylish design”.
• Use templates for typical tasks: build a “prompt library” for frequent jobs. Control the length and format of the answers.
• Give context and examples: if the model generates the wrong thing, show it a sample.
• Split complex tasks into steps: “Suggest the structure of an article about the benefits of electric cars” → “Write an introduction based on this structure” → “Add 3 arguments with data”.

Why integrate AI with Excel?

Why use AI right in Excel? A web interface, convenient as it is, gives you only one request at a time. And an API doesn’t answer instantly: depending on the question, an answer takes from a few seconds to more than a minute, and all this time you wait. With Excel:

• you process many prompts at once;
• you don’t send each prompt manually: they go out automatically as the answers arrive;
• you don’t copy and paste prompts: they are sent right from the sheet, and the answers land in the next column;
• you can do other work while the AI processes the list in the background.

Direct AI integration with !SEMTools

As the creator of the add-in and its first user, I can say that an add-in is the most convenient way to work with third-party APIs. I’ve built access to many of them into !SEMTools: Google Translate, DeepL, search suggestions and others. AI APIs were no exception: ChatGPT came first, then Deepseek and Gemini, then Claude and Perplexity with web search, and later OpenRouter, a universal API that works as a proxy between you and many different AI models. It’s very convenient for quickly comparing models from different vendors.

First, you need to install the add-in. A few important points before the full instructions:

• The add-in file is not an executable: you connect it to Excel instead of opening it with a double-click.
• To keep the add-in tab from disappearing and to stop Excel from blocking its code, unblock the file.

Detailed guide: How to install !SEMTools.

After you install !SEMTools Pro, the add-in tab appears in Excel, and among its commands, in the Web (Scraping, SEO, PPC) group, there is an AI section. That’s where you process your requests with AI models.

OpenRouter: 400+ AI models in one place

OpenRouter is interesting because it gives access to many models, including dozens of free ones, with one API key. The downside is that it can respond slowly, probably because it’s very popular and sometimes overloaded.

Working with local LLMs in Excel

Local large language models run on your own computer, with no cloud service involved. A powerful graphics card (GPU) is desirable, but some models give acceptable results for some tasks on the CPU alone.

Unlike ChatGPT and other online solutions, local LLMs keep your data completely private, because everything is processed locally. This matters for trade secrets, personal data and any other sensitive information that must not be passed to third parties. !SEMTools can work with local models run with LM Studio and Ollama.

Summary: getting the most out of AI in Excel

Well, we’ve done quite a lot of work together: from basic table analysis to advanced text processing and VBA code generation. Experiment with prompts, create formulas, UDFs and macros, adapt texts for any audience and analyze data on the go.

Thank you for reading to the end. If you have questions or ideas, write to me, I’m always happy to hear from you.