Flash Fill Practice Sheet: Excel Drills and Answers

To practice Flash Fill in Excel for Microsoft 365 on Windows, put source data beside an empty column, type the result you want for the first row, then press Ctrl+E or choose Data > Flash Fill. The practice sheet below gives you sample names and email addresses, five useful pattern drills, and expected results so you can check your work.

What a Flash Fill practice sheet teaches

Flash Fill recognizes a pattern in examples you enter and uses it to complete similar values in nearby rows. It is useful for reshaping text without writing a formula: splitting a full name into first and last names, making initials, or extracting part of an email address. The drills here start with straightforward patterns and then show where human judgment matters.

Unlike a formula, Flash Fill writes ordinary values into cells. Those results do not automatically update if you later change the source text. That makes Flash Fill convenient for a one-time cleanup, but important to remember when the source data may change or the transformation must stay repeatable.

Copy-ready sample data

Enter the following two columns in a blank worksheet, starting at A1. You can copy the table into Excel or type the headings and rows. The email addresses use the reserved example domain northwind.example; they are sample data, not working contact details.

Full name	Email
Maya Chen	maya.chen@northwind.example
Luis Rivera	luis.rivera@northwind.example
Aisha Patel	aisha.patel@northwind.example
Noah Brooks	noah.brooks@northwind.example
Priya Nair	priya.nair@northwind.example
Ethan Cole	ethan.cole@northwind.example
Sofia Garcia	sofia.garcia@northwind.example
Omar Hassan	omar.hassan@northwind.example

Keep the source columns unchanged while you practice. Put each answer in a new column with a descriptive heading, such as First name or Initials. The headings make it easier to compare your results and reduce the chance of overwriting the sample data.

Drill 1: Extract first names

In C1, enter First name. In C2, type Maya, matching the first name in A2. For a clearer pattern, type Luis in C3 as well. Select C4, then press Ctrl+E. Excel should fill the remaining rows with the first names. You can also select a cell in the output range and choose Data > Flash Fill.

The expected sequence in C2:C9 is Maya, Luis, Aisha, Noah, Priya, Ethan, Sofia, and Omar. Excel may sometimes identify a simple pattern after one example, but supplying two examples gives it more evidence, especially when the data varies.

Drill 2: Extract last names

Use a fresh column, such as D. Add the heading Last name in D1, then type Chen in D2 and Rivera in D3. Select D4 and press Ctrl+E. The expected values are Chen, Rivera, Patel, Brooks, Nair, Cole, Garcia, and Hassan.

This exercise shows that Flash Fill can take text from the end of a cell even though the names have different lengths. It is following the pattern demonstrated by your examples, not counting a fixed number of characters. If a source name includes a middle name or a suffix, the intended meaning may be less obvious; check those rows instead of assuming every name has exactly two parts.

Drill 3: Make initials

In another blank column, enter Initials as the heading. Type MC beside Maya Chen and LR beside Luis Rivera, then use Flash Fill on the cells below. The expected results are MC, LR, AP, NB, PN, EC, SG, and OH.

Initials can be ambiguous. A person with a middle name might have two or three initials, and some organizations use a different convention for hyphenated names. Flash Fill can reproduce the rule in your examples, but it cannot decide which naming convention your workplace intends. Include representative examples and review unusual records.

Drill 4: Extract the email username

Use a new column headed Username. In the first output row, type maya.chen, the part of the first email address before the at sign. Type luis.rivera in the next row, then select the following cell and press Ctrl+E. The completed values should be maya.chen, luis.rivera, aisha.patel, noah.brooks, priya.nair, ethan.cole, sofia.garcia, and omar.hassan.

This is a useful office-style transformation, but it is not the same as extracting a person’s first name. The username contains both names separated by a period. If some addresses include numbers, aliases, or a different domain format, add examples of those cases before accepting a fill.

Drill 5: Extract the email domain

In a column labeled Domain, enter northwind.example for the first two rows, then run Flash Fill on the rest. Every expected result is northwind.example. This simple case is still worth practicing: it helps you recognize that a repeated output is a valid pattern, even if it does not vary from row to row.

To make the exercise more challenging, change a few sample addresses so that some use a second domain, then try again with examples from both groups. Inspect the result carefully. A pattern that is obvious in a uniform practice list may be unclear when real data contains several formats.

How Flash Fill works in Excel

Flash Fill examines nearby values and examples you provide, then predicts the transformation that would produce the examples. It is pattern recognition, not a general-purpose understanding of your data. For example, it may infer that Maya Chen becomes Maya, but it does not know whether Chen is a surname in every row or whether a middle name should be included.

For best results, keep the source data in one column and the example output in the adjacent column. Put examples on consecutive rows, use consistent formatting, and give Excel more than one example when a transformation could be interpreted in multiple ways. If Excel does not fill automatically as you type, use the command or shortcut directly.

Run Flash Fill on Windows and Mac

In Excel for Microsoft 365 on Windows, enter one or more examples in the column beside the source data, select the next blank cell in that output column, and press Ctrl+E. The ribbon alternative is Data > Flash Fill. If you prefer, select the cells you want filled before choosing the command.

In Excel for Microsoft 365 for Mac, use Data > Flash Fill when the command is available in the ribbon. Keyboard shortcuts can differ by keyboard and Excel version, so the menu command is the dependable route if a Windows shortcut does not work. Flash Fill is a desktop Excel feature; if you are working in Excel for the web and do not see the command, open the workbook in the desktop app to use it.

On Windows, automatic Flash Fill can be managed through File > Options > Advanced, under the editing options. If Excel does not suggest a fill while you type, the setting may be off, or the pattern may not be clear enough. You can still try Data > Flash Fill manually.

Check results and avoid common problems

Flash Fill is fast, but it can make a plausible-looking mistake if examples do not cover the variation in your data. Before using the output, compare it with the source and scan for blank, inconsistent, or unexpected results. Sorting or filtering the output can help reveal exceptions, but keep each row’s source and result together when reviewing.

Nothing happens: Confirm that you entered an example beside the source data, selected the next output cell, and used Data > Flash Fill or Ctrl+E on Windows. Try providing a second example.
The wrong pattern appears: Undo the fill, replace the examples with clearer ones, and run Flash Fill again. An example that accidentally contains a typo can teach Excel the wrong result.
Some rows are wrong: Look for inconsistent source formats, extra spaces, middle names, or different email conventions. Correct exceptions manually or separate the data into more consistent groups.
The output does not change with the source: That is expected. Flash Fill creates values, not formulas. Run it again after changing source entries, or use a formula if the results need to update automatically.

Do not treat a completed fill as proof that the data is correct. For a contact list, for example, a first-name column that looks reasonable may still be wrong for names with multiple parts. Review the transformed data before sending messages, importing records, or using it in a report.

Flash Fill compared with formulas

Choose Flash Fill when you need a quick, one-time transformation and can verify the results. Choose a formula when the output should recalculate as source cells change, when the same operation will be reused, or when you need a transparent rule that another workbook user can inspect. Flash Fill is not a replacement for a formula in a recurring data process.

Flash Fill also differs from Text to Columns. Text to Columns is designed to split a column using a consistent delimiter or fixed width, such as splitting values at a comma. Flash Fill is more flexible about the shape of the output, but its prediction needs examples and checking. For a simple, uniform list of full names separated by spaces, either approach might help; the best choice depends on how consistent the source values are and whether the result must stay linked.

Frequently Asked Questions

What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, the shortcut is Ctrl+E. You can also use Data > Flash Fill. On a Mac, use the ribbon command if the shortcut differs on your setup.

Why is Flash Fill not working?
Check that the output column is next to the source data, that you entered an example, and that the Flash Fill command is available. Try supplying two or more examples and using Data > Flash Fill manually. On Windows, also check the automatic Flash Fill option under File > Options > Advanced.

Does Flash Fill update when the original data changes?
No. Flash Fill places fixed values in cells. If source values change, run Flash Fill again or use a formula when you need an automatically updating result.

Can Flash Fill split first and last names?
Yes, it can often infer the split from examples, as in the practice drills. Review names with middle names, compound surnames, suffixes, or inconsistent spacing because the intended split may not be obvious from the text alone.

Is Flash Fill available in Excel for the web?
If the Flash Fill command is not available in your browser version, open the workbook in desktop Excel for Windows or Mac and use the Data menu. Availability can depend on the app and version, so check the ribbon in the version you are using.

A simple way to build skill

Repeat the five drills with a copy of the sample data, then test a harder set containing middle names, extra spaces, or more than one email domain. For each task, write down the rule you want before entering examples. Compare every output with its source, and note which rows need special handling. This builds a practical sense of when Flash Fill is a time-saver and when a formula or careful manual cleanup is the safer choice.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.