Flash Fill in Excel (Ctrl+E): Split and Format Data

Part of the free Module 2: Fill, Auto Fill and Flash Fill · Lesson 3 of 6 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019, 2016 and 2013; not available in Excel 2010 or earlier.

Flash Fill in Excel recognises a pattern from one or two examples you type and completes the rest of the column instantly, without a formula. Press Ctrl+E and Excel extracts first names, splits addresses, combines columns, adds dashes to phone numbers or changes case. This lesson shows three worked examples and explains when a formula is the safer choice.

Table of Contents

What Flash Fill is and where to find it

Flash Fill arrived in Excel 2013. It sits on the Data tab in the Data Tools group and answers to Ctrl+E. You type the result you want beside the first row of data; when you press Ctrl+E in the cell below, Excel studies your example, infers the transformation and fills the whole column. Excel also offers a live suggestion while you type the second example: the column appears in grey, and pressing Enter accepts it. The output is static text, so use Flash Fill for one-off clean-ups and a formula such as TEXT, LEFT or TEXTSPLIT when the source data will keep changing.

Flash Fill button in the Data Tools group of the Excel Data tab — Flash Fill on the Data tab

After a fill, a small Flash Fill Options button appears next to the range. It lets you undo the fill, accept the suggestions permanently, or select all changed cells so you can check them. If nothing happens when you press Ctrl+E, confirm that File > Options > Advanced > Automatically Flash Fill is ticked.

What Flash Fill can do and the formula that does the same

Task	Flash Fill example you type	Formula alternative (updates automatically)
First name from full name	Priya	`=LEFT(A2,FIND(" ",A2)-1)` or `=TEXTBEFORE(A2," ")`
Last name from full name	Sharma	`=TEXTAFTER(A2," ",-1)`
Domain from an e-mail address	example.com	`=TEXTAFTER(A2,"@")`
Combine first and last name	Priya Sharma	`=A2&" "&B2` or `=TEXTJOIN(" ",TRUE,A2:B2)`
Month name from a date	January	`=TEXT(A2,"mmmm")`
Format a phone number	98765-43210	`=TEXT(A2,"00000-00000")`
Change case	PRIYA SHARMA	`=UPPER(A2)`, `=PROPER(A2)`
Initials	PS	`=LEFT(A2)&MID(A2,FIND(" ",A2)+1,1)`
Remove non-numeric characters	9876543210	No simple formula; Flash Fill or Power Query

TEXTBEFORE, TEXTAFTER and TEXTSPLIT are Excel 365 and 2021 functions. In older versions use LEFT, RIGHT, MID and FIND.

Worked example 1: extract day and month from a date

Column A holds dates. We want the day number in column B and the month name in column C.

A: Date	B: Day (you type row 2)	C: Month (you type row 2)
01-Jan-2026	1	January
14-Feb-2026	14 (filled)	February (filled)
23-Mar-2026	23 (filled)	March (filled)

Column of dates used as the source data for Flash Fill in Excel — Dates in column A

In B2 type 1, the day of the first date.

First Flash Fill example value typed manually in cell B2 — First example typed in B2

Select B3 and press Ctrl+E (or Data > Flash Fill). The day numbers fill the column.

Day numbers filled down column B by Flash Fill after pressing Ctrl+E — Day numbers completed with Ctrl+E

In C2 type the month name of the first date, select C3 and press Ctrl+E.

Month name typed manually in C2 as the Flash Fill example — Month example typed in C2

Month names extracted from dates by Flash Fill in Excel — Month names completed

Flash Fill can also build a sentence from the date. Type one or two examples such as “1st day of January” and press Ctrl+E.

Example sentence built from a date typed as the Flash Fill pattern — Example comment built from the date

Comments generated from dates for every row by Flash Fill — Comments generated for every row

Worked example 2: split, abbreviate and combine text

The table lists IPL teams. The yellow columns must hold the first part of the name, the last word, the initials and a summary sentence.

Team data with empty highlighted columns to be completed by Flash Fill in Excel — Team data with target columns highlighted

Type Mumbai in I2. One example is not enough here, because three-word names should keep two words, so also type Kolkata Knight in I3 to show the rule.

Two manual examples typed so Flash Fill learns the first-name rule — Two examples define the pattern

Select I4 and press Ctrl+E.

First part of each team name filled by Flash Fill — First names completed

For the last word type one example and press Ctrl+E.

Example last word typed for the team name before Flash Fill — Example for the last word

Last word of each team name filled by Flash Fill — Last names completed

For initials type MI for Mumbai Indians and press Ctrl+E.

Example abbreviation MI typed for the short name column — Example abbreviation typed

Team short names generated by Flash Fill in Excel — Short names completed

To combine several columns into a sentence, type one full example that uses each column and press Ctrl+E.

Example summary sentence combining several columns typed for Flash Fill — Example sentence from several columns

Summary sentences created for every team by Flash Fill — Sentences created for every row

Worked example 3: reformat and extract digits from numbers

Column A holds ten-digit mobile numbers. Type the first number with the format you want, for example 98765-43210, and press Ctrl+E in the next cell to reformat every row. Type the first four digits in another column and press Ctrl+E to extract them; do the same with the last four digits. The example does not have to be in row 2; Flash Fill learns from whichever row you fill.

Column of mobile numbers to be reformatted with Flash Fill in Excel — Mobile numbers before formatting

First mobile number typed in the new format as the Flash Fill example — Formatted example typed

All mobile numbers reformatted with dashes by Flash Fill — Numbers reformatted

First four digits typed as the Flash Fill example for extraction — Example for the first four digits

First four digits extracted for every number by Flash Fill — First four digits extracted

Last four digits typed as the Flash Fill example on a lower row — Example for the last four digits

Last four digits extracted for every number by Flash Fill — Last four digits extracted

Tips and common mistakes

Check the result. Flash Fill guesses; scan the column for rows that did not match the pattern and add a second example if needed.
Results are values, not formulas. They will not update when the source changes. Use a formula for live data.
Leading zeros vanish because output that looks numeric is stored as a number. Format the target column as Text before pressing Ctrl+E.
Keep the example next to the data. Flash Fill only reads the columns immediately beside the one you are filling; a blank column between them breaks the link.
Consistent input is essential. Mixed formats such as “Smith, John” and “John Smith” in one column produce wrong rows.
Nothing happens? Enable it under File > Options > Advanced > Automatically Flash Fill, and make sure there is at least one example.
Two examples beat one whenever the rule depends on word count, position or punctuation.

Errors and how to fix them

Problem	Cause	Fix
“We looked at all the data next to your selection and didn’t see a pattern”	Example is not adjacent, or the rule is unclear	Move the example beside the source column and type a second example
Some rows are wrong	One example did not define the rule	Correct a wrong cell, select the next cell and press Ctrl+E again
Leading zeros dropped	Result stored as a number	Format the column as Text first, then Flash Fill
Dates appear as serial numbers	Extracted text was converted to a date value	Format the column as Text, or use TEXT(A2,”dd-mmm”)
Ctrl+E does nothing	Flash Fill disabled, or Excel 2010 or earlier	Enable it in Options > Advanced; use formulas in older versions
Results stop updating	Flash Fill output is static	Re-run Ctrl+E after new rows arrive, or switch to a formula

Practice exercise

Download the Flash Fill practice workbook and try these:

Extract the year from the dates in column A into a new column with one example.
Build the sentence “[Team] plays at [City]” for every team with a single typed example.
Reformat the mobile numbers as +91 98765 43210 and then extract only the middle two digits.
Type a list of e-mail addresses and pull out the part before the @ sign.
Repeat task 1 with a TEXT or YEAR formula and compare what happens when you change a date.

Key takeaways

Flash Fill fills a column by pattern from one or two typed examples; the shortcut is Ctrl+E.
It extracts, combines, reformats and changes case, but its results are static values.
Keep the example adjacent to consistent source data and add a second example when rows go wrong.
Format the target column as Text to keep leading zeros.
Use LEFT, TEXT, TEXTBEFORE or TEXTSPLIT instead when the source data will change.

Related lessons

Frequently asked questions

What is the shortcut for Flash Fill in Excel?

Ctrl+E. Type the example result in the first cell beside your data, select the cell directly below it and press Ctrl+E, or click Data > Flash Fill. Excel fills the column and shows a Flash Fill Options button so you can undo or review the changed cells.

Why does Flash Fill give wrong results for some rows?

One example was not enough to define the rule, or the source data is inconsistent. Correct one of the wrong cells by hand, select the next empty cell and press Ctrl+E again so Excel learns from both examples. If names use different formats, clean them first or use a formula.

Is Flash Fill better than formulas?

Flash Fill is faster for one-time clean-ups because there is nothing to write. Formulas win when the data will change, when the logic must be auditable, or when the same task repeats every month. TEXT, LEFT, RIGHT, TEXTBEFORE and TEXTSPLIT cover most of the same jobs and update automatically.

Does Flash Fill keep leading zeros?

Not by default. When the result looks like a number, Excel stores it as a number and drops the zeros. Select the target column, set the number format to Text, then run Flash Fill. The same fix stops extracted digits from being turned into dates or scientific notation.

Which versions of Excel have Flash Fill?

Flash Fill is available in Excel 2013, 2016, 2019, 2021 and Excel 365 for Windows and Mac. It is not in Excel 2010 or Excel for the web. In those environments use text functions or Power Query to achieve the same result.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.