Excel is a powerful tool for data analysis, but did you know that there is a new function that provides the power of generative AI in Excel. The experimental LABS.GENERATIVEAI function allows you to send prompts from within Excel to a generative AI model and then return the results from the model back to your worksheet.
Can you really use generative AI in Excel?
Artificial Intelligence (AI) is an umbrella term for the simulation of human intelligence in machines, enabling them to perform tasks that typically require human intelligence. Generative AI is a subset of AI that can create new content such as images and text (often human-like) that is similar to the training data that is provided to it.
We don’t want to get too side-tracked on discussing AI in general, so let’s focus on what we could mean when we ask ‘can we use generative AI in Excel?’. AI is based on the principle that human intelligence can be defined in a way that a machine can easily mimic it and execute tasks, from the most simple to those that are more complex. When we use Excel, there are many tasks that we do on a day-to-day basis that are fairly simple and repetitive – a good example is opening a file from a specific location, performing some data cleansing and updating a report. We can certainly get AI to help in these types of situations, and Excel already has tools like Power Query which use AI-type processes to automatically clean data and update some pivot table reports.
So, up to now, Excel has a number of AI-powered features that use machine learning algorithms to automate certain tasks and natural language processing to search the web for information related to the data you have selected. However, it doesn’t use the more complex AI techniques such as generative AI. This is where the new LABS.GENERATIVEAI function comes in.
What is the LABS.GENERATIVEAI function in Excel?
The LABS.GENERATIVEAI function is a custom function that allows you to send prompts from within Excel to a generative AI model and then return the results from the model back to your worksheet. You can send the model simple or complex prompts to analyze information, process data, produce a response based on a sample, and more. It is part of Excel Labs, which is an add-in that allows the Excel team to release experimental Excel features and gather customer feedback about them.
Installing the Excel Labs Add-In
The LABS.GENERATIVEAI function is included in the Excel Labs Add-In. This add-in currently includes 2 features:
- Advanced formula environment – an interface designed for authoring, editing, and reusing formulas
- LABS.GENERATIVEAI function – provides access to OpenAI generative AI capabilities
Here are the steps to install the Excel Labs Add-in and configure the LABS.GENERATIVEAI function.
- Once you’ve installed the add-in, you’ll see the Excel Labs menu on the Home ribbon.
- The function uses generative AI technology from OpenAI, so to use it, you need to register an OpenAI account and generate a unique API key. The API Key is entered in the LABS.GENERATIVEAI section of the Excel Labs pane.
LABS.GENERATIVEAI function overview
The LABS.GENERATIVEAI function can be called from inside any Excel cell or named formula in a workbook. The function sends the “prompt” argument to a remote generative AI model (at OpenAI) and returns the response.
The function uses the following syntax:
LABS.GENERATIVEAI(prompt, [temperature], [max_tokens], [model])where:
- prompt is the ‘thing’ that you want the generative AI model to process
- temperature is an optional argument that controls randomness: Lower numbers result in less variety; higher numbers result in more variety.
- max_tokens is an optional argument that represents the maximum output length to generate – requests can use up to 2,048 tokens and one token is roughly 4 characters for standard English text.
- model is an optional argument that specifies the model to use to generate the result – some models are suitable for natural language tasks and others specialize in code
You don’t really need to worry too much about the optional arguments initially. Default values for them are specified in the Settings section. You can override the defaults by including specific values in an individual LABS.GENERATIVEAI call.
IMPORTANT : the LABS.GENERATIVEAI function connects to the OpenAI service, and so you are bound by OpenAI’s terms of use. The main thing to note here are the rate limits imposed on the service – the one that will impact you most is the limit of 3 requests per minute when using a free account .
What can you do with the LABS.GENERATIVEAI function?
It’s important to point out that the Excel Labs add-in is intended as a means for Microsoft to get feedback on experimental features. The LABS.GENERATIVEAI function is therefore quite rough around the edges, and there’s not a great deal of information on it at the time of writing.
I played around with the function for a couple of hours, and identified a few use cases for the LABS.GENERATIVEAI function:
- Get a summary of public information – you can get the answer to a general question or a summary of a complex topic as you would when using ChatGPT.
- Answer a factual question – get the model to return the answer to a factual question.
- Text analysis – get the model to analyze some text and make a judgement or categorization decision.
- Summarize data in a table – you can ask the model to return information in a tabular format.
- Provide a response based on a sample – you can show the model and example and get it to return responses for similar questions.
We’ll look at these in the following sections.
Examples of generative AI in Excel
Get a summary of public information
You can place any prompt you want into the function in much the same way as you would use ChatGPT. I used the following function and got the results shown in the screenshot below:
=LABS.GENERATIVEAI("Provide a summary of the main features of Excel")
Another good example is asking for help with a formula in Excel. In the example below, I asked for help to get the number of work days between 2 dates:
=LABS.GENERATIVEAI("What is the Excel formula to get the number of work days between the following dates: " & A1 & B1, 0)
Answer a factual question
Although generative AI models are accurate at creative or language-based tasks, they cannot guarantee the accuracy of data returned. However, it will work well with factual questions where there is only one answer – example below.
=LABS.GENERATIVEAI("What is the capital city of: " & A1, 0)
Note that I set the temperature argument to zero to make sure no creativity was used in the response.
Text analysis
Text analysis is a great use case for using an AI function. In the example below, I grabbed a sample of tweets from a dataset hosted at data.world and got the LABS.GENERATIVEAI function to analyze the sentiment of the tweet and decide whether it was positive, negative or neutral.
The function I used for this was:
=LABS.GENERATIVEAI("Analyze the following text and use one word to describe it - negative, positive or neutral: " & A2)The results are shown in the screenshot below:
As you can see, the function returned the same result as the analysis completed by a human in about half of the cases. In the ones where there was a difference in the results, the AI was correct in about half of them (although there is always some subjectivity in these kinds of results).
So, it’s fair to say that the sentiment analysis performed by the function produced mixed results. I daresay as the models develop, the results will improve greatly.
Summarize data in a table
With the use of an additional Excel function, we can format the way that the response data is displayed in Excel. In the following example, I’ve used the TEXTSPLIT function to get the data returned as a table (using the phrase “as a table” also helps):
=TEXTSPLIT(LABS.GENERATIVEAI("Return a table of the top 10 countries by population, with 3 columns: country, population, and percentage of global total", 0, 1000), "|", UNICHAR(10), TRUE, , "")
Provide a response based on a sample
In this use case, I ‘taught’ the generative AI model how to respond by providing an example in the prompt. The example that I gave it was that a dog can be classified as a mammal, or in other words if the question is dog, the answer is mammal – this can then be used to provide answers to prompts of a similar nature.
=LABS.GENERATIVEAI("Q: Dog A: Mammal Q: " & A1 & ". A: ", 0.1)Note that I set the temperature value to 0.1 to limit creativity.
Conclusion
In this article we looked at how to use generative AI in Excel. The experimental LABS.GENERATIVEAI function in Excel uses the generative AI technology (including the ChatGPT 4.0 model, amongst others) from OpenAI to get responses to prompts that you provide from within Excel. The “generative” aspect of ChatGPT means that it can generate human-like text based on the patterns and examples it has learned during its training. This is obviously something new for Excel where we normally deal with numerical processing and analysis.
Why would you need question answering or text generation capabilities in Excel? We looked at a few examples of generative AI use cases where it might be useful. One of the more interesting ones used the generative AI model to perform sentiment analysis on text in an Excel table – our example was a set of tweets relating to airlines in the US. The results were somewhat mixed (and hampered somewhat by the current rate limits on the free OpenAI account), but there is clearly potential for this kind of processing in Excel.
In the next article in this series, we’ll look at built-in tools in Excel that provide AI-based functionality.
