If you’re looking for an AI model to work with Excel, ChatGPT is one of the best candidates. It answers almost any question in a human-like way and solves tasks on the fly: writes texts and slogans, generates ideas on any topic, writes and fixes code in many programming languages, summarizes texts, rewrites them by your requirements and translates them.
How can ChatGPT help Excel users? Here are 5 ideas it suggested itself:
- explain how to use functions, for example how VLOOKUP finds data in a table;
- troubleshoot problems, for example when a file won’t open or a formula returns an error;
- give step-by-step instructions, for example how to create a chart or a pivot table;
- answer common questions, like how to remove formulas from all cells at once or what Match entire cell contents does;
- suggest ways to work more efficiently, for example a macro to automate a routine task.
All quite right. I’d add that ChatGPT can explain what a piece of VBA code does, optimize or extend it, build a complex nested formula or find a syntax error. More scenarios are collected in the article on working with AI in Excel.
ChatGPT and Excel integration
The ChatGPT API was released on March 1, 2023, and it became a turning point for the service. To keep up with progress, I built it into my !SEMTools add-in. At first, you could send one request at a time; now you can process whole ranges of prompts.
But first you need a personal API key, which identifies you to the API and is used for billing.
How to get an API key and start using it
- Sign up on the OpenAI platform and add a payment method or credits to your account.
- Open the API keys page (platform.openai.com/api-keys) and create a new secret key.
- Copy the key: it’s shown only once.
Then run the ChatGPT settings procedure on the !SEMTools tab:
A simple form opens with a model selector and a field for the key:
Paste the key, choose the model and click OK. The add-in saves the key and the model for future use. You can also change the API URL if you use an OpenAI-compatible service or proxy.
Important: the key is stored in the add-in file itself. If you share your .xlam file with other people, they’ll be able to use your API access. Until a key is saved, the processing procedure will complain that it’s missing.
Bulk processing of prompts with the ChatGPT API in Excel
Once the key is saved, you can process prompts in bulk: ask thousands of questions to a model that knows almost everything (as long as you ask correctly). The answers go into the next column.
The procedure works great together with another !SEMTools tool that can add text to all cells. For example, you have a list of 10 countries and want their 5 largest cities. Add the text “List the 5 largest cities of this country: ” to the beginning of every cell, and process the resulting list of prompts.
A few more ideas:
- classify product reviews as positive, negative or neutral;
- write short product descriptions from a list of attributes;
- normalize messy company names or addresses to one format;
- group keywords by topic or search intent.
Just keep the economics in mind: each request costs money, and for deterministic tasks like removing characters or extracting words, a regular procedure is faster, free and always returns the same result.
How much does the ChatGPT API cost?
Check the current prices at the source, on the OpenAI API pricing page. Progress in this area is fast: prices for the same model keep going down, and newer, more capable models keep appearing. The price depends on the model and on the number of tokens in the prompt and the answer, so short prompts and answers are cheaper.
Do you need the full version of !SEMTools?
The procedure is available in the free version of !SEMTools Pro too. But like other procedures, in the free version it doesn’t process about 1/3 of the data, replacing it with the note “Available in the full version”. No API request is made for these cells, so no money is spent.
You’ll probably find the other procedures of the add-in useful too. Unlike AI answers, they are deterministic: they always return the same result. And you don’t pay per request, however much text you process: you pay once for the full version of !SEMTools.
Send thousands of prompts to ChatGPT right from an Excel sheet and get the answers in the next column.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary
I hope you found what you were looking for on this page and were able to connect ChatGPT to Excel. Get an API key, paste it into the !SEMTools settings, and process whole columns of prompts. The same procedure works with other models: see the setup guides for DeepSeek, OpenRouter and local models with Ollama.