The new Formula by Exampe creates formulae for you

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

The new Formula by Example creates formulae for you
Tired of working out the syntax of a formula? Get Excel to do it for you by providing two or three examples of what you want to achieve.

Posted by Andy Brown on 05 November 2025

In this blog

Enabling Formula by Example
Example 1 - text formulae for generating names
Example 2 - working with dates
Example 3 - numbering rows

Formula by example suggests formulae for you based on examples that you type in:

You could use formula by example to fill in each person's formal name, for example.

This blog gives 3 examples of this amazing new feature, but we should begin with two important limitations.

Enabling Formula by Example

At the time of writing you need two things to be true for Formula by Example to work:

Constraint	Notes
Excel for the web	You must be using Excel 365 for the web (this new feature hasn't - yet - been incorporated into the desktop version of Excel).
You need a table	As things stand this feature only works if you've converted your data into a table, but Microsoft have announced that support for ranges of data is coming in a future release.

As a quick reminder, you can convert any block of data into a table like this:

Select any cell within the block of data and choose Table from the Insert tab of the ribbon as shown. You'll then need to confirm that your table contains a header row.

Example 1 - text formulae for generating names

For the example at the top of the page, you could generate a formula for each person's formal name like this:

Type in 2 or 3 example names to give Excel the idea. This takes faith, as initially absolutely nothing happens!

With luck (and usually on the 3rd data row, although this varies) Excel will get the hint:

Excel has finally got the message, and is showing you what data you'd get if you accept its formula.

You can (and should) click on the Show Formula link above to see what Excel is proposing to create on your behalf:

Excel is proposing to concatenate the last name, a full stop ("period" if you must) and the person's first name, which is what you want to do.

When you apply this, you'll get a column of formulae:

The results of clicking on the Apply button above.

If you're a Power Query user you may be experiencing deja vu. This feature has existed in Power Query for years, but Microsoft are now converting the formulae to Excel functions instead of to the M formula language.

Example 2 - working with dates

You can use a similar trick to get information from dates:

Here I'm on my 3rd line of typing in the month name.

Here's the formula this would create:

The formula converts each date to text using the mmmm long month name format.

I was a bit disappointed that some of the more ambitious date strings I tried didn't work, but I'm sure this feature will improve rapidly over time.

Example 3 - numbering rows

Suppose you now want to number the rows of your table. You could do this by first inserting a new column:

Select any cell in your first column and choose this option from the right mouse button menu (using this method ensures that your new column will be part of the table).

This time I had to type 4 rows to get the formula suggestion to appear (I'll be honest, I was beginning to give up hope):

Finally Excel is suggesting a formula for my row numbers.

This is the formula suggested:

This formula takes the current cell's row number and subtracts from this the row number of the table's header row.

A much simpler formula would have been =SEQUENCE(ROWS(Table1),1) to create a sequence of numbers from 1 to the number of rows in my named table range, but sadly you can't - as I've just discovered - use functions which generate spill arrays within a table.

The best way to try out the new Formula By Example feature is by experimenting, but remember to do this in Excel online and within a table to get the feature to work.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS

EXCEL BLOGS

Enabling Formula by Example

Example 1 - text formulae for generating names

Example 2 - working with dates

Example 3 - numbering rows

Head office

London

Manchester