Flash Fill in Excel: Fill Blanks or Detect Patterns

How-to basics · Updated November 21, 2026

Flash Fill in Excel recognizes a pattern in your examples and fills an adjacent column with matching results; it does not automatically copy neighboring values into empty cells. To fill missing cells with the value above, use Home > Find & Select > Go To Special > Blanks, then fill the selected cells down instead.

What Flash Fill Does

Flash Fill is a pattern-recognition feature in the desktop version of Excel. You show it one or more examples of how you want existing data transformed, and Excel predicts the same transformation for the remaining rows. For example, from a column of full names, you can type a person’s first name in the next column. Flash Fill can recognize that you want first names extracted and produce results for the other rows.

It is useful for changes such as separating first and last names, joining text from multiple columns, extracting part of an email address, or making text follow a consistent capitalization pattern. You do not write a formula or specify a set of text rules. Instead, Excel uses your examples to infer the pattern.

Flash Fill writes values into cells. Its results are not formulas linked to the source, so they will not update if the original data changes. It is also not a general-purpose tool for locating empty cells and filling them from surrounding data. That distinction matters: a blank you want to infer from a text pattern is a different problem from a blank you want to copy down.

How to Use Flash Fill

For a straightforward result, put the source data in one column and the examples or intended output in the neighboring column. In Excel for Microsoft 365 on Windows, use either the keyboard shortcut or the ribbon:

  1. Enter one or two examples in the output column beside the source data. For instance, type the first name from the first full name.
  2. Select the next empty cell below your example, or select the output cells where you want results.
  3. Press Ctrl+E, or choose Data > Flash Fill.
  4. Review the suggested entries before relying on them. If Excel does not produce the pattern you need, add another example and run Flash Fill again.

Flash Fill may show a preview of its proposed results while you type. Accept the preview if it matches your intention; otherwise, correct the example or use the ribbon command. On a Mac, use Data > Flash Fill when the feature is available in your Excel version; the Windows Ctrl+E shortcut is not the Mac equivalent. Excel for the web does not provide the desktop Flash Fill command, so open the workbook in the desktop app to use it.

Examples of Patterns It Can Recognize

Suppose column A contains full names such as Jordan Lee and Priya Nair. If you type Jordan in B2 and run Flash Fill, Excel may fill column B with the first names from the remaining rows. If you instead type Lee in C2, it may extract last names into column C. The examples should make the desired result unambiguous: a single sample can be insufficient when different transformations could fit it.

Other practical examples include creating a username from an email address, combining a city and region into a display label, or standardizing a phone-number presentation. Results depend on the actual data. If names contain middle names, suffixes, inconsistent separators, or missing parts, Flash Fill may not reliably interpret every row. Check exceptions rather than assuming that a plausible-looking column is correct.

Flash Fill is most helpful when the intended transformation is recognizable from nearby examples. It is not a substitute for rules that need to be precise and repeatable, such as splitting text at a specific delimiter in a large, variable dataset. For those cases, a formula or Power Query may be more dependable.

How to Fill Blank Cells With the Value Above

If your goal is to replace empty cells with the value from the row above, use Go To Special rather than Flash Fill. This approach is useful for a column where blank rows mean that the previous label or category continues. First, make a copy of the data or confirm that filling the blanks is what the records mean; a blank can represent unknown information rather than a value to inherit.

  1. Select the range that contains the blanks. Include the first data row, but avoid selecting a heading if you do not want it affected.
  2. Choose Home > Find & Select > Go To Special, select Blanks, and click OK. Excel selects the blank cells within the chosen range.
  3. Type an equals sign, then press the Up Arrow key. Excel creates a reference to the cell directly above the active blank.
  4. Press Ctrl+Enter to enter the reference in the selected blank cells. Check the results, then copy the filled range and use Paste Special > Values if you want fixed values instead of formulas.

For example, with values in B2:B8 and B4 blank, if B3 contains the value that should carry into B4, the reference entered for B4 is =B3. This returns the contents of B3. The same selection-and-fill method adjusts the relative reference for each selected blank, so each blank refers to the cell directly above it. If the range contains consecutive blanks, the upper blank may itself be empty; filling down through the range carries the nearest preceding nonblank value into the sequence.

To fill only one blank, select it, type the reference to the cell above, and press Enter. The keyboard sequence above is for Excel on Windows. On Mac, use the Go To Special command from the Home ribbon when available and use the Mac equivalent of Enter for completing a multi-cell entry; shortcuts can vary by keyboard and Excel version.

Choosing the Right Method

Start by deciding what a blank means in your worksheet. If you want to derive a new text value from other columns, Flash Fill may help. If a blank means repeat the previous row’s category or label, fill blanks from above. If you want to fill empty cells with a fixed value such as Not provided, select only the intended blanks and enter that value; Flash Fill is unnecessary.

  • Use Flash Fill to generate a new column from examples, such as extracting a first name.
  • Use Go To Special > Blanks to select empty cells in a chosen range and enter a reference or fixed value.
  • Use Fill Down when you want to copy the top cell of a selected range through the cells below it. On Windows, Ctrl+D fills down; use Home > Fill > Down if you prefer the ribbon.
  • Use a formula or Power Query when the transformation must be transparent, repeatable, or updated as the source data changes.

These methods have different effects. Flash Fill guesses a transformation from examples and returns static results. A formula calculates according to explicit instructions and can recalculate when source cells change. Fill Down copies content or formulas through a range. Choosing the method based on the meaning of the data helps prevent an efficient-looking operation from introducing incorrect values.

Common Problems and Fixes

If Ctrl+E does nothing, first confirm that you are using the Excel desktop app and that Flash Fill is available in your version. Check the Data tab for Flash Fill and try the ribbon command. In Excel for Microsoft 365 on Windows, the option to fill automatically as you type is under File > Options > Advanced; look for Automatically Flash Fill. If that setting is off, you can still invoke the command manually when available.

If Excel fills the wrong pattern, give it more informative examples. For example, if you want initials rather than the first letters of the first two words, demonstrate the exact intended initials in multiple rows. Review results for rows with different name formats, punctuation, extra spaces, or missing data. Flash Fill can apply an incorrect interpretation consistently, so visual inspection is part of using it responsibly.

If Flash Fill replaces output you wanted to keep, undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Run it only in the intended output range, and do not assume the results are linked to source cells. When filling blanks with references, remember that the operation adds formulas; paste values afterward if you need to remove those links.

Limits, Updates, and Data Care

Because Flash Fill produces static values, edits to the source column do not automatically refresh its output. If you need a live result, use a suitable formula or another repeatable transformation instead. Flash Fill also depends on the pattern being clear in the examples and consistent enough across the rows. It is not a guarantee that every unusual record will be interpreted correctly.

Before filling blanks in a shared or important workbook, consider whether blanks have meaning. In a list of departments, an empty cell might mean the department is the same as the one above, but it might also mean the information is not known. Copying a value into unknown fields can make incomplete data appear confirmed. Keep an untouched copy or use Undo if the result is not what you intended.

For workbooks with many records or transformations that must be repeated, Power Query can provide a more structured process. It can fill values down in a column and record the transformation as a query step, which is useful when refreshing data from the same source. For a small one-time cleanup, Go To Special or Flash Fill may be faster; for recurring work, repeatability and review are usually more important than the fewest clicks.

Frequently Asked Questions

Can Flash Fill fill blank cells with the value above?
No. Flash Fill detects patterns in examples and creates results, usually in an adjacent output column. To copy values into blanks, select them with Go To Special > Blanks and fill them from above.

What is the shortcut for Flash Fill in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+E. You can also choose Data > Flash Fill. On a Mac, use the Data tab command if Flash Fill is available rather than assuming the Windows shortcut applies.

Does Flash Fill update when the source data changes?
No. Flash Fill returns static values, not formulas. Run it again or use a formula or Power Query if the output needs to reflect later source changes.

Why is Flash Fill unavailable in Excel for the web?
Flash Fill is a desktop Excel feature and is not provided in Excel for the web. Open the workbook in the desktop app to use the feature, or use another supported method to transform the data.

Can I use Flash Fill to split names into separate columns?
Often, yes. Enter examples of the first and last names in neighboring columns, then run Flash Fill for each output column. Review rows with middle names, suffixes, or inconsistent formatting because those can make the pattern ambiguous.

Related

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.