Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
542 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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.
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.
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.
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.
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.