Write Excel Formulas with Built-In AI (Not ChatGPT)

Today we’re going to be discussing how to use AI to write formulas in excel. This new capability is available now in Excel online, and may be available in other versions of Excel in the future.

Video

Note

At the time of this post, this feature is available in some version of Excel Online, and is not available in all versions of Excel (including Excel for Windows, Mac, mobile, and some online editions).

Details

When we want to extract characters, split cells, or combine cells, we’ve been able to use Flash Fill for many years now. Flash Fill allows us to give Excel a sample and it will extend the pattern by entering the values into those cells. But what if, instead of entering the values, Excel could write and enter the formulas for us? This way, if we add a new data row, Excel will extend that formula down. That’s what we’re going to be talking about today!

Example 1 – split

Let’s go ahead and take a look at an example. In this case, we have a column of SKUs, like A101 and XY202:

We want a new column that pulls the part number out like 101 and 202. Instead of typing these or using Flash Fill, we actually want Excel to write a formula that does this for us.

The first step is to convert this ordinary range into a table. We do this by selecting any cell within the range and then selecting the Insert > Table command. Then, we type a column header like Part for our new formula column:

Next, we give it a couple of samples. In this case, we want to write a formula that will return 101 and 202, so we enter those in our new column. Once we do, Excel automatically displays the Fill cells with this formula pop up:

Excel gives us a preview of the results, and they look exactly right. We click Show Formula to see the suggested formula:

We click Apply and bam … Excel enters the formula and fills it throughout the column:

What is very cool is that if we type a new SKU, the Table auto-expands and the formula auto-fills down so that the new row has the Part value calculated instantly:

Let’s take a look at another example.

Example 2 – combine

Here, we have two columns: First and Last.

We want to combine them into the full name where it’s last name comma first name. We convert the range into a Table and add a new Full column:

We give it a couple samples like “Rios, Eric” and “Gray, Joe”, and then we get the pop-up with a suggested formula.

Let’s take a look at the suggested formula by clicking Show Formula:

If we take a look at the formula, it’s taking the last name column, using “concatenate” to add a comma space, and then it’s grabbing whatever is in the first column. That looks good to us, so we click “apply”, and now we’ve got it.

Let’s take a look at the next example.

Example 3 – extended

Here, we have a “price” column and a “quantity” column.

We want to compute an “extended” column. While this formula would be relatively easy to write, compared with the previous ones, let’s just see if it works.

We convert the range into a Table and add an Extended column:

We type in a couple examples like 20 and 5 and bam … the pop up appears:

Let’s view the formula:

Looks good so we click Apply:

Very cool!

Conclusion

I wish I had access to this features YEARS ago because it would have saved so much time 🙂