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

In: BlogDate: Jul 24, 2023By: Darren Andrews

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?

More about Happy

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

Why not sign up to our newsletter?

Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.

Sign up here

Improve your productivity in Excel with happy

Happy has high-quality, learner-focused Excel training courses for all skill levels, designed to improve your confidence and productivity. And our new online learning programme is just as interactive as our classroom sessions.

Here are our most popular options:

Our learners tell us that they save an average of 32 minutes a day with our Excel training courses. How much time could you save with us?

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

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.

More by Darren