Introduction
The GPT extension for Excel provides a range of AI-assisted functions for text-based tasks such as summarization, classification, tagging, list creation, and general prompting.
The extension uses the OpenAI API to process user requests.
This article will take you through the included functions as well as go over the installation and configuration process of the extension.
Let's dive right in!
The included functions
1. GPT.Prompt
This is the simplest function in the package. It accepts a textual prompt and generates a textual response to it, similar to what you'd get in the ChatGPT web interface.
=Gpt.Propmt("Who is the the CEO of " & [CompanyName])
You can easily use cell values in the prompt text, which makes this function quite flexible. However, while you can do many with this function alone, for specific tasks like tagging and summarizing it's usually easier to use the specialized functions described below.
The only mandatory parameter of the function is prompts which is a list of one or more prompts to send to the AI. The temperature and model parameters are optional (in this and other functions) and are explained later on this page.
2. GPT.Summarize
This function summarizes large texts into a one-paragraph summary. The input text can be a single cell or a range of cells. The result of the function is always a single cell.
=GPT.Summarize("A1:A10")
The result will be roughly one paragraph long. If you need customizations (e.g. a single sentence summary), you can use the Extract function which offers a bit more flexibility (described below).
3. GPT.Extract
This function can extract arbitrary information from one or more texts, e.g. phone numbers, email addresses, dollar amounts, geographic locations, SEO keywords, grammatical mistakes, etc...
=Gpt.Extract("top 5 most used adjetives", A1:B20)
As mentioned before, you can use this function to summarize in cases where you need more control over what the desired summary should look like.
=Gpt.Extract("A one-sentence summary at 5th grade level", A1:A10)
3. GPT.Classify
This function helps categorize your text data based on the categories you provide. It accepts two parameters: input text(s) and a list of available categories and returns the category that best describes the input text.
The list of categories can be supplied from a range or as an array of literals. Here's how to pass them in as an array of string literals:
=Gpt.Classify("cleaning expenses", {"salary", "business expenses", "rent expense"})
And here's how to pass them in from a range of cells:
=Gpt.Classify("cleaning expenses", $A$2:$A$6)
When passing categories in from a range, if you plan to pull the formula down to other cells or use it in a table, be sure to use absolute cell references (e.g. $A$2:$A$6 instead of A2:A6) so that the reference doesn't change in the other cells. Otherwise, you might end up passing in blanks (or completely unrelated data) as the list of classes.
4. GPT.Tag
This function tags text with one or more tags from a provided list. It is similar to the Classify function, except that it doesn't have to return a single result. When multiple tags apply to a given text, they are returned as a comma-separated list. If no tags are relevant to the input text, the result is empty.
=Gpt.Tag("A group of explorers discovers a lost city deep in the Amazon jungle", { "Romance", "Drama", "Adventure", "Action", "Thriller" })
You can use the allowOtherTags parameter to specify whether the AI is allowed to return tags outside of the provided list or not.
As with the classify function, you can pass in the list of tags as an array of string literals or from a range.
5. GPT.List
This function generates a list of items based on a prompt. If you'd like the function to return an entire table, you can specify the list of columns to be included in the output.
You can pass in the list of columns as the second parameter, either as an array of string literals or as a range. The list of result columns is optional, if you omit it, the function will return only one column in the result.
One use case for this function is generating sample/test data. For example, I've used it here to generate a list of made-up bookkeeping journal entries for demo purposes:
You can use it to generate SEO keywords, newsletter ideas, to-do items for your home renovation project, or to get actual data (e.g. list of EU member states with their populations, omelet ingredients, etc...).
6. GPT.SuggestFormula
This function provides suggestions for Excel formulas based on a provided task description. You can specify how many suggestions the AI should return.
Common parameters
The functions have fairly self-explanatory parameters, but two parameters appear in all of them and deserve a more detailed explanation:
- temperature: the temperature influences the creativity of the AI model. A value of 0 means that the AI will not be creative at all and will always return the same result for a given prompt. The maximum value is 2 which makes the AI behave extremely creatively but can lead to weird and unexpected (and often useless) results. For most purposes, a value of 0.2 is a good compromise between accuracy and creativity. All functions have this parameter and have a default value for it, which the user is free to override if needed.
- model: the OpenAI API offers a choice of several models, each with its own strengths, weaknesses, and pricing. The most powerful and typically most useful model currently available via the API is the "GPT 3.5 Turbo" model. This is the default model, but the user is free to override this using the model parameter of each function. Keep in mind that the only function that is guaranteed to work with models that are not GPT 3.5 Turbo is the Prompt function. The other functions will fail with other models more often than not as they expect structured responses from the API.
Combining with other extensions
The GPT Extension can be very useful when combined with other extensions from the QueryStorm store. One example is the ForReddit extension, which can read posts and comments from Reddit into Excel.
Once we have the data in Excel, we can then use the GPT.Classify
function to perform sentiment analysis on it.
This can give us insights into trends on a given topic, providing valuable data for market research, social sentiment tracking, and more.
The interplay between these extensions showcases how you can combine different tools and data sources within Excel to create a sophisticated and incredibly powerful data analysis environment.
Excel is a particularly good platform for this because users can combine different tools and data in whichever way they like. They are not limited by a particular application's feature set. With Excel, the user is the developer and is free to build whatever they like or need.
Installation
The installation process is straightforward and exactly the same as with other QueryStorm extensions:
- Download and install the QueryStorm Runtime (if you don't already have it)
- Open the "Extensions" dialog in the QueryStorm ribbon in Excel
- Search for the GPT extension in the"Online" tab.
- Click the "Install" button and wait a few seconds for the installation to complete.
After installing it, you'll have to activate it and provide it with an OpenAI API key.
Activating the Extension
Upon first use, the GPT-4 Excel Extension will prompt you to activate it.
You can activate it using the activation key that you get when you purchase a subscription.
Before deciding to purchase a subscription, you may want to try it out first with a trial key. You can generate one using the "Get trial key" button near the top of this page, next to the "Buy extension" button.
Configuring the Extension
Another prompt that will pop up on first use is the "API key" dialog.
The GPT plugin uses the OpenAI API. In order to use the API on your behalf, you must supply it with an API key. You can create an API key by going to platform.openai.com, creating an account, and then creating an API key.
Aside from the API key, you can also configure other settings of the plugin by going to the extension's configuration page:
- General Settings: Here you can input your OpenAI API key and choose the default model. Models available are GPT-3.5-turbo and other variants.
- Connection Settings: These settings include maximum concurrent requests, request timeout, and maximum retries.
The default values of these parameters should be fine for most cases. If you need to change them, you can do so in this dialog.
Data protection
It's important to consider that this extension uses the OpenAI API for its functionality. This means that your prompts and any data you pass to the GPT functions are sent to OpenAI. Be careful with sensitive business data and make sure to adhere to your company's policies regarding data protection when using these functions!