Summary

• Microsoft Excel's Formula by Example helps automate repetitive tasks in structured tables in Excel for the Web.
• The tool is similar to Flash Fill, though it generates dynamic formulas that update with changes to your data.
• Formula by Example makes splitting and combining text, creating number sequences, extracting information from dates, and generating arithmetic formulas simple.

Are you tired of manually entering repetitive data into your spreadsheet? Or are you frustrated that Flash Fill doesn't always work as expected? Well, stop there, because Microsoft Excel's little-known tool, Formula by Example, can do the hard work for you.

How to Fill Excel Cells Automatically with Flash Fill and Auto Fill Microsoft Excel can automatically fill cells based on patterns.

PostsHowever, despite its benefits, this tool has a severe limitation—if the source data changes, Flash Fill can't go back and reapply the pattern to the new information. In other words, the values created by Flash Fill are static, which is useless if you're trying to create a dynamic spreadsheet.

Another drawback of Flash Fill is that all the work is done behind the scenes. Yes, it can recognize patterns and complete the data for you, but you have no way of knowing how it did this. If, like me, you're interested in understanding the inner workings of your spreadsheet, this is a frustrating shortcoming.

Luckily, Microsoft recognized these issues, and came up with an excellent solution—Formula by Example.

Formula by Example: Preparing Your Data

To use Formula by Example, first, go to Excel for the Web, and log in to your Microsoft account.

Then, either open an existing spreadsheet or click "Blank Workbook" to start with a blank canvas.

The important thing to note about Formula by Example is that it only works in formatted Excel tables, not regular ranges. So, select your data, and in the Insert tab on the ribbon, click "Table."

Related ##### Everything You Need to Know About Excel Tables (And Why You Should Always Use Them)

This could totally change how you work in Excel.

Posts 2 If your table has headers, check the box in the dialog box, and then click "OK."

Example 1: Transforming Text There are many ways to transform text from one column to the next, including using the Text to Columns tool or opening Excel's Power Query Editor. However, Formula by Example is probably the most intuitive.

Related ##### 4 Ways to Split Data Into Multiple Columns in Microsoft Excel

You're spoiled for choice!

Posts 1 In this example, let's say you want to extract people's names into column B from their email addresses in column A. You also want the names to be capitalized.

Start by typing the first couple of examples of how you want the text to be transformed. As soon as Excel recognizes a pattern, a floating window appears that offers to fill the remaining cells in that column with a formula. At this point, you can either cast your eye down the column to see whether the remaining values are as expected, or click "Show Formula" to see the formula that Excel has generated to transform the data.

In this case, the formula is:

<code>=PROPER(TEXTBEFORE([@Email], ".", 1)) & " " & PROPER(MID(LEFT([@Email], FIND("@", [@Email]) - 1), FIND(".", [@Email])   1, LEN([@Email])))</code>

where the PROPER parts of the formula deal with capitalization, and the TEXTBEFORE, MID, LEFT, FIND, and LEN parts of the formula deal with the positioning of the text to extract.

Take a moment to appreciate the complexity of this formula, and how long it might have taken you to generate it yourself. In situations like these, Formula by Example is a super handy tool that will save you lots of time.

If you're happy with what's been produced, click "Apply" to complete the column.

Now, because a formula has been used to create this text transformation, you can use it on other datasets—simply copy and paste the formula to the relevant cell, and update the parts of the formula that reference a specific column in the original table (in this case, [@Email]) with a new structured reference.

Related ##### Everything You Need to Know About Structured References in Excel

Use table and column names instead of cell references.

PostsWhat's more, you can learn about some functions you haven't previously seen by reviewing the formula closely. Most importantly, however—and the biggest benefit of using this tool over Flash Fill—is that if the values in the precedent cells change, the formulas in the dependent cells will pick up these changes and update the result automatically.

Example 2: Numbering Rows One issue with formatted Excel tables is that they're not compatible with dynamic array formulas, meaning you can't use functions that spill the result into more than one cell, like SORT or FILTER.

In this example, let's say you want to add dynamic numbers to column A so that you can keep count of the number of players in the game at any given point.

One way to do this in an unformatted area of a spreadsheet is to nest the COUNTA function within the SEQUENCE function to count the number of cells in column A that contain a value, and then use this to generate a sequence that increases by one in each row. However, this isn't possible in an Excel table, as the SEQUENCE function produces a spilled array.

Related ##### Everything You Need to Know About Spill in Excel

It's not worth crying over spilled references.

PostsHowever, thanks to Formula by Example, there's a workaround.

Start by typing the first few numbers into column A, and when Excel recognizes the pattern, check the formula, and click "Apply."

Example 3: Working With Dates Formula by Example doesn't just help you transform text—it works just as well with dates.

In this example, after typing the first few years into column B, Excel recognized that I was taking this information from the Date column, and produced a formula accordingly:

<code>=YEAR([@Date])</code>

In column C, the program saw that I was converting the month from the shorthand form to the full month name, so it generated the following formula:

<code>=TEXT([@Date], "mmmm")</code>

Finally, in column D, it used the DAY function to complete my dataset:

<code>=DAY([@Date])</code>

As with example 1, if any of the original dates in column A change, the corresponding splits in columns B, C, and D will update accordingly. Similarly, if you add more rows to the bottom of the table, they'll adopt the same formulas.

Related ##### 13 Microsoft Excel Date and Time Functions You Should Know

Why use a calendar or a clock when Excel can track it all for you?

Posts Example 4: Generating Arithmetic Calculations -----------------------------------------------

Formula by Example in Excel for the Web can also work out how a result can be generated using a formula.

In this example, when I complete the first few multiplications manually, Excel spots the pattern and suggests a formula.

![](/uploads/2025

The above is the detailed content of Flash Fill in Excel Is Good, but This Tool Is So Much Better. For more information, please follow other related articles on the PHP Chinese website!