Would You Like Excel to Use AI to Write Formulas For You?

AI is big news at the moment, and it is going to change the way we use software forever. Microsoft is adding Copilot to Microsoft 365, and we’re seeing the first hints of what it can do. Copilot will offer AI driven help to all parts of Microsoft 365, from helping you manage your email inbox, to refining a report and polishing a PowerPoint presentation and, yes, even write Excel formulas for you! This type of AI-driven help has been turning up in Microsoft products for some time, especially in Excel. But how does it work? Darren explains more in this blog.

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your people to find joy in at least 80% of your work?

Keep informed about happy workplaces

Sign up to Henry's monthly Happy Manifesto newsletter, full of tips and inspiration to help you to create a happy, engaged workplace.

You may have noticed the Analyse Data icon on the home tab of the ribbon. This has been with us for a while now, but I think it’s somewhat underused. Simply click into your data, click Analyse Data and ask Excel a question.

Excel’s answer ususally comes in the form of a Pivot Table, even if the answer is a single value. If you want to keep the result you can just ask Excel to insert the Pivot Table, and it will place it on a new sheet.

If you haven’t tried this yet, you must give it a go!

I’m a big fan of anything that makes using software quicker and easier. But more is coming!

Excel will soon be able to write formulas for you

Full disclosure here, we’re massive Excel geeks here at Happy! But we do appreciate that not everyone has time to appreciate the beauty and elegance of a well written formula – you probably have much more to focus on. So how would you feel if Excel could write your formulas for you, or help you fix your formulas if they go wrong?

Soon Excel will be able to do just that, and, as the afore-mentioned geeks that we are, we have seen it! As Microsoft insiders we get to try out all the new stuff before it’s released to the world. Sometimes it’s not fully ready as so can be a bit “buggy”, but it’s always exciting.

Let me show you how it works.

This currently only works in the Excel web app (as of summer 2023).

Next, your data. It must be formatted as a table, as Copilot uses structured references to write your formulas.

Now here’s a couple of examples.

In this first one I would like to create a Full Name field in the dataset below.

As it’s an Excel table I can just type my heading in cell G1 and the table will expand.

I now need to let Excel know what I need it to do, so I show it by entering the first couple of full names. As soon as Excel has figured out what I’m trying to do, it will offer me a formula to finish the whole column off. Here I only needed to enter the first two names and Excel was right on it.

If I’m happy to accept the offer, I can just click apply.

Here’s another. Now in this one I’ve been asked to provide a field with the house number and postcode. Just as before I’ll give Excel a few clues, and here’s its suggestion:

You’ll notice a few errors. This is because the data is not as consistent as the first few entries might suggest. But I’m not going to worry about that just yet – I’m going to accept Excel’s formula, and suggest corrections.

All I need to do, is to go back to the cells with errors and enter the correct data. As I do this, Excel will learn from this and rewrite the formula to try and meet the new demands.

If you get a chance to try this, you may find it’s not quite perfect as yet and still has some limitations, but this is possibly the most exciting feature to come to Excel since the Pivot Table!

Related Blogs

How to Create a Pivot Table — If you’ve never used Pivot Tables before, start with this two-minute video.
Calculations on a Filtered List in Excel — In this blog, learn how to create a dropdown filter in your Excel spreadsheets.
Save Time in Excel with Autofill — Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
How Microsoft Excel Can Increase Your Productivity — Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.

Learn the 10 core principles to create a happy and productive workplace in Henry Stewart's book, The Happy Manifesto.

Support your aspiring and current managers to be empowering and confident leaders with Happy

Happy offers leadership programmes at Level 3, Level 5 and Level 7, from new managers/supervisor level all the way up to senior leadership teams and CEOs. These programmes are based on the ideas of trusting your people. They are practical and based on applying what yo’ve learnt. We aim to inspire and ignite change in your organisation, as well as giving you valuable management skills such as business strategy, decision-making, negotiation and project management.

We also offer programmes tailored specifically to people from Global Majority backgrounds. The content is the same, but have been designed to give new and experienced managers the skills they need to navigate organisational culture with a clearer perspective on their own potential, as well as building their confidence and expanding their professional strengths.

Darren Andrews

Darren is one of Happy’s Senior Trainers, able to train almost every IT course on our course programme. He worked for Happy for 12 years and has been an Associate Trainer since 2018.