Excel Labs: advanced formula editor, AI functions and Python scripting

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Excel Labs: advanced formula editor, AI functions and Python scripting
Excel Labs is an experimental toolkit being developed as part of Microsoft Garage. This blog shows how you can use it to call AI functions, use the advanced formula editor and use an Excel-based Python editor.

Posted by Andy Brown on 25 September 2024

In this blog

Installing Excel Labs
The Advanced Formula Editor
The GenerativeAI function
Python Scripting

You can add the Excel Labs add-in to Excel from the Microsoft Garage project to use an advanced formula editor in Excel, create a function to call AI tools and to use a Python editor within Excel:

The Excel Labs icon in Excel 365.

Be warned: Microsoft Garage tools are experimental, and Microsoft explicitly warn that they may never be incorporated into production software.

Installing Excel Labs

I have to admit that I did this a while back on my laptop, but I believe the procedure is as follows. First, get the software from this Microsoft site:

Note that Excel Labs doesn't seem to be included on the Microsoft Store, presumably because it's experimental software.

After signing in to Office 365, you should then be able to click on a button which will take you to desktop Excel :

Clicking on this button should install the add-in and take you to Excel.

You can now see the 3 current features which are part of Excel Labs:

The Excel Labs sidepane.

The Advanced Formula Editor

This for most people will be the main reason to get Excel Labs. It allows you to see the steps of a formula you are creating or editing:

Showing formula steps clearly like this is a great idea.

You can indent formulae and add blank lines easily:

Being able to press a simple Enter to get a blank line is a joy!

Creating and viewing range names is easier:

This makes Name Manager look very primitive!

You can also use the advanced formula editor to create lambda functions, which you can reference in formulae:

An example of a module in the advanced formula editor.

There are a lot of nice-to-have features in the advanced formula editor, although I think I'd be tempted to wait until they're included in a future roll-out of Excel to use them.

The GenerativeAI function

This is not part of Copilot - it's competely separate. The first thing you'll need to do to use this is to sign up to the OpenAI API. This will cost $10 initially, and thereafter is charged on a per-usage basis:

Once you've created and signed on to an OpenAI API account you'll be able to get your API key.

Once you have an account and an API key you can copy this key into Excel Labs:

Understandably the API key doesn't show up in plain text!

You can now use calls to OpenAI in a function:

But what to ask for?

OpenAI can answer any question, although there will be a slight delay:

Seriously? OpenAI's favourite Excel function is VLOOKUP?

Here's what this formula looks like in the function wizard:

Interestingly this doesn't show as a "volatile" function, so presumably it's only recalculated as needed.

The question is, how could this be useful? It's hard to think of a good example, but here's an attempt. Suppose you have a list of films:

Here we have a list of 4 films, and we want to know which one has the best review.

You could then use a call to the Lab.GenerativeAI function to find out which is considered the best and worst film:

It turns out the ET is the best film, and The Sound of Music the worst.

My problem with this is not the AI tool (I think ChatGPT and its ilk will change the world, and teach as much on this two-day course) but using Excel as an intermediate stage. It's hard right now to think of how mixing ChatGPT and Excel like this could be useful, particularly as you have to pass all of your source text to the Lab.GenerativeAI function as a parameter.

Python Scripting

This is surely a niche subject! You can run Python scripts within Excel, and return the results to an Excel cell (or cells). To illustrate this, suppose that you have a table with the range name Groceries:

So far, so straightforward.

You could now click on a blank cell (I chose E2) and write Python code:

Here we're showing a summary of the Pandas dataframe based on the Groceries named range.

You now need to save this:

Click to save this code in the cell.

Crucially, you also need to switch to viewing this as an Excel cell or block of Excel cells:

First click on this drop arrow ...

... then choose this option.

Excel will then fill your cells with a dynamic array showing the output from the Pandas describe method for this dataframe:

I did say this was niche!

If you like Python, surely you'd write your code in Jupyter Notebooks or another dedicated IDE? And if you like Excel, surely you'd use built-in Excel functions? I'm struggling to see why you would want to combine the two in this way, but let me know if you think I've missed the point.

Some other pages relevant to the above blog include:

Excel training classes

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Installing Excel Labs

The Advanced Formula Editor

The GenerativeAI function

Python Scripting

Head office

London

Manchester