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

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.