Excel LABS.GENERATIVEAI Function

With AI gradually making its way into every aspect of our lives, working in Excel has become much easier.

You no longer need to research the web and copy over the data into Excel. Simply prompt the AI in natural language, and it will spill out the results for you within Excel! All thanks to the Excel LABS.GENERATIVEAI function.

Using LABS.GENERATIVEAI you can even reference other cells or named formulas in your workbook.

---

We now have ChatGPT inside an Excel formula!

---

The image above illustrates the LABS.GENERATIVEAI function returning the airport codes for a list of airports in column J.

Note: LABS.GENERATIVEAI is available in Microsoft 365 & Office 2021 or later.

 

Watch the Video

Download Example File

LABS.GENERATIVEAI Function Overview

The Excel LABS.GENERATIVEAI function is a custom function created by Excel Labs under Microsoft Garage. It allows you to send prompts directly from your Excel sheet to OpenAI’s generative AI model and quickly get the results you need.

The model analyses the information available to it, processes data, and produces a response based on the prompt you provide.

Overall, it works like a personal assistant that can speed up your spreadsheet creation process, reduce your research, and minimize the formula writing time. Now, you can just focus on the more important aspects of your analysis.

Let’s look at a few examples to understand what all LABS.GENERATIVEAI is capable of!

Generating Lists from the Web

The simplest application of LABS.GENERATIVEAI is to pull out standard information from the web, such as state abbreviations, airport codes, or international mobile phone codes.

For example, you need to create a state-wise demographics report of the US and need a list of all the 50 states with their abbreviations.

Instead of copying from the web, you can simply use the LABS.GENERATIVEAI function.

To list down all the 50 states, use the below formula:

=TRANSPOSE(TEXTSPLIT(LABS.GENERATIVEAI("return a comma separated list of the 50 states in the united states of america with no leading text"),", "))

By default, LABS.GENERATIVEAI renders the output in a single cell. Use TEXTSPLIT to split the comma-separated list and then TRANSPOSE it to return each state in a separate row.

Once you have that, simply reference this column to generate state abbreviations, using the below formula:

=LABS.GENERATIVEAI("return the state abbreviation for: "&B4)

Limitations

1. Returns long outputs: At times, if you are not very specific with the LABS.GENERATIVEAI function, the output can be a full sentence instead of just what you need.

You can fix this by limiting the maximum output length or using the max_tokens argument of the function that we discuss later:

2. Spilled array referencing constraint: The function can’t reference spilled arrays using the # operator, so you need to reference each individual cell within the array.
3. Can be unreliable when asked to return tables: Even when specified in the prompt, at times, the output is a list instead of a table. So, it’s best to return the list and then use TEXTSPLIT and TRANSPOSE to return a table.

Creating Content

While extracting lists of data from the web is one application, LABS.GENERATIVEAI can also create new content for you.

Let’s say, you are starting an online gadgets store. You want to list 100 products in the store, and you need a short description for each of them.

You can go ahead and write them yourself, or simply use LABS.GENERATIVEAI to do it for you. In fact, you can even scrape the product specifications from the web using this function.

Let’s find out how.

Create a 3-column table in Excel as shown below:

Add your product list in the Product Name column.

In the Description column, use the below formula to generate short descriptions:

=LABS.GENERATIVEAI("Return a short description for this product: " &@[Product Name],0)

And in the Specs column, use the below formula to scrape specs from the web:

=LABS.GENERATIVEAI("Get the specs for the " &@[Product Name],0)

And within seconds you will have a snazzy-looking table with products, descriptions, and specs.

Limitations

1. AI model can be outdated: AI models are updated periodically, so AI output can lag real-time information. For example, the Samsung Galaxy S23 has been released, but the AI still considers them unreleased because the model precedes the product release date.
2. AI output is not error-free: As AI is still very new, some output might contain errors. It’s always a good practice to verify it after generation and clear out the errors.

Analyzing Text

LABS.GENRATIVEAI can even conduct sentiment analysis of your text. For example, here I have some comments from one of my videos on BYCOL and BYROW functions. Let’s analyze, and classify them into Positive, Negative and Neutral categories.

To do this, I will use the following formula:

=LABS.GENERATIVEAI("Use one word to summarise this text into positive, negative or neutral: "&[@Comment])

Within a few minutes, the function has completed the categorization. Now, I can get an idea of how people are reacting to my videos and create more videos to generate more positive comments.

Limitations

1. Unreliable results: If a comment has both positive and negative comments, the model might not be able to analyze it perfectly, as seen in cell B9.
2. Time-Consuming: The more data you want summarized, the more time the model takes to run.

However, even with all these limitations, LABS.GENERATIVEAI is a promising function for the future of Excel.

Excel Formula Help

Point LABS.GENERATIVEAI at a formula and have it explain how it works, step-by-step:

Use the FORMULATEXT function to expose the underlying formula in the cell and add it to the prompt:

=LABS.GENERATIVEAI("explain this formula step by step: "&FORMULATEXT(D6))

Limitations

1. Not always correct: for example, point 1 above states that the table contains two columns, which isn’t true. The AI has assumed this because we only reference two columns in the formula, but the table actually contains 4 columns. While this isn’t going to cause any real issues, it’s a reminder that you can’t assume the AI is correct.
2. Inconsistent results: sometimes the AI is more thorough than other times. You may need to recalculate the formula until you get an explanation that’s adequate.

How to get LABS.GENERATIVEAI in Excel

Go to Excel > Home Tab > Add-ins

Get Add-ins > Search for Excel Labs > Press Add

Once installed, you can access the Add-in from the Home Tab. It will open the sidebar on the right.

Scroll down to it > Press Open

Generate an OpenAI API Key > Enter the key in the box.

Once done, you can type in =LABS in any Excel cell, and you will see LABS.GENERATIVEAI function in the suggested formula list.

Also, make sure to optimize your settings.

How to Optimize LABS.GENERATIVEAI Formula

The syntax for the function is LABS.GENERATIVEAI(prompt, [temperature], [max_tokens], [model])

1. Prompt is the only required argument in the function. It contains a description of what you want the generative AI model to return. It can be typed in double quotes or a reference to a cell containing the prompt, or a combination of both where the ampersand is used to concatenate the text and cell reference into a single prompt.
2. Temperature is an optional argument used to control the model randomness. Lower temperature means less randomness; higher temperature means more randomness. If you want the model output to not update every time you refresh the formula, set the Temperature to 0. Note: this does not guarantee it will always return the same result.
3. Max_tokens is also an optional argument that controls the output length. gpt-3.5-turbo offers 4097 tokens while gpt-4 offers 8192 tokens. One token is approximately 4 English characters. You can use the OpenAI Tokens guide to find out how many tokens you need to specify in your formula.
4. Model is also an optional argument. It allows you to pick the model you want to run with your LABS.GENERATIVEAI command. gpt-4 is the latest release and is much more reliable compared to its predecessors.

You can pass specific values for these parameters in your function to get more accurate results.

Tips and Best Practices

To get the desired results, you must follow the below best practices:

1. Be precise: The more accurate your prompt is the better results the function will render.
2. Keep your prompts simple: LABS.GENERATIVEAI returns better results when supplemented with other EXCEL functions, like we explained using TEXTSPLIT and TRANSPOSE instead of asking it to return a table.
3. Volatile: The LABS.GENERATIVEAI formulas appear to be volatile, recalculating every time any other formulas on the sheet are edited or structural changes like inserting or deleting rows and columns are made. To avoid this, paste the output as values, or while writing the formulas put the workbook into Manual calculation mode:

WARNING: Don’t forget to recalculate the workbook using the F9 key or by putting the workbook back into Automatic Calculation mode.

4. Fund your account: #N/A, #BUSY! errors can mean that you need to add funds to your OpenAI account at https://platform.openai.com/account/billing/overview When co-authoring, all accounts should be funded otherwise the formula breaks.
5. Daily token limits: watch out for the daily limits on tokens as these can be easily reached and will also result in errors like #BUSY! that eventually returns #N/A!. Limits vary based on your plan. You can see them when logged into your OpenAI account in the Settings > Limits section.

Tip: if you reach your daily limit, try changing the model you’re using in the Excel Labs settings for LABS.GENERATIVEAI.

AI Is Everywhere

There are many ways you can leverage AI both inside and outside Excel. Check out these examples next:

Automate Excel with ChatGPT

 

8 AI Game Changers - Official Microsoft Tools to Skyrocket Productivity

 

Excel Writes Formulas by Example

 

Excel AI Formula Writer: Advanced Formula Environment