How to use Flash Fill in Excel 365 - 2013
by Svetlana Cheusheva | updated on March 9, 2022
The tutorial explains the basics of the Flash Fill functionality and provides examples of using Flash Fill in Excel.
Flash Fill is one of the most amazing features of Excel. It grabs a tedious task that would take hours to be performed manually and executes it automatically in a flash (hence the name). And it does so quickly and simply without you having to do a thing, but only provide an example of what you want.
- What is Flash Fill in Excel?
- Where is Flash Fill in Excel?
- Flash Fill shortcut
- How to use Flash Fill in Excel
- Flash Fill options
- Excel Flash Fill examples
- Flash Fill limitations
- How to enable/disable Flash Fill in Excel
- Excel Flash Fill not working
What is Flash Fill in Excel?
Excel Flash Fill is a special tool that analyzes the information you are entering and automatically fills data when it identifies a pattern.
The Flash Fill feature was introduced in Excel 2013 and is available in all later versions of Excel 2016, Excel 2019, Excel 2021, and Excel for Microsoft 365.
Started in December 2009 as an attempt of Sumit Gulwani, a senior researcher at Microsoft, to help a businesswoman he accidentally met at the airport with her merging challenge, a few years later it has evolved into a powerful ability to automate many Excel chores.
Flash Fill easily copes with dozens of different tasks that otherwise would require complex formulas or even VBA code such as splitting and combining text strings, cleaning data and correcting inconsistencies, formatting text and numbers, converting dates to the desired format, and a lot more.
Each time, Flash Fill combines millions of small programs that might accomplish the task, then sorts those code snippets using machine-learning techniques and finds the one that suits best for the job. All this is done in milliseconds in the background, and the user sees the results almost immediately!
Where is Flash Fill in Excel?
In Excel 2013 and later, the Flash Fill tool resides on the Data tab, in the Data tools group:
Excel Flash Fill shortcut
Those of you who prefer working from a keyboard most of the time, can run Flash Fill with this key combination: Ctrl + E
How to use Flash Fill in Excel
Usually Flash Fill starts automatically, and you only need to provide a pattern. Here's how:
- Insert a new column adjacent to the column with your source data.
- In the first cell of a newly added column, type the desired value.
- Start typing in the next cell, and if Excel senses a pattern, it will show a preview of data to be auto-filled in the below cells.
- Press the Enter key to accept the preview. Done!
- If you are unhappy with the Flash Fill results, you can undo them by pressing Ctrl + Z or via the Flash Fill options menu.
- If Flash Fill does not start automatically, try these simple troubleshooting techniques.
How to Flash Fill in Excel with a button click or shortcut
In most situations, Flash Fill kicks in automatically as soon as Excel establishes a pattern in the data you are entering. If a preview does not show up, you can activate Flash Fill manually in this way:
- Fill in the first cell and press Enter.
- Click the Flash Fill button on the Data tab or press the Ctrl + E shortcut.
Excel Flash Fill options
When using Flash Fill in Excel to automate data entry, the Flash Fill Options button appears near the auto-filled cells. Clicking this button opens the menu that lets you do the following:
- Undo the Flash Fill results.
- Select blank cells that Excel has failed to populate.
- Select the changed cells, for example, to format them all at once.
Excel Flash Fill examples
As already mentioned, Flash Fill is a very versatile tool. The below examples demonstrate some of its capabilities, but there is much more to it!
Extract text from cell (split columns)
Before Flash Fill came into existence, splitting the contents of one cell into several cells required the use of the Text to Columns feature or Excel Text functions. With Flash Fill, you can get the results instantly without intricate text manipulations.
Supposing you have a column of addresses and you want to extract zip codes into a separate column. Indicate your goal by typing the zip code in the first cell. As soon as Excel understands what you are trying to do, it fills in all the rows below the example with the extracted zip codes. You only need to hit Enter to accept them all.
Formulas to split cells and extract text:
- Extract substring - formulas to extract text of a specific length or get a substring before or after a given character.
- Extract number from string - formulas to extract numbers from alphanumeric strings.
- Split names in Excel - formulas to extract first, last and middle names.
Extracting and splitting tools:
- Text Toolkit for Excel - 25 tools to perform various text manipulations including splitting a cell by any character such as comma, space, line break; extracting text and numbers.
- Split Names tool - fast and easy way to separate names in Excel.
Combine data from several cells (merge columns)
If you have an opposite task to perform, no problem, Flash Fill can concatenate cells too. Moreover, it can separate the combined values with a space, comma, semicolon or any other character - you just need to show Excel the required punctuation in the first cell:
This method is especially useful for combining various name parts into a single cell as shown in How to merge first and last name with Flash Fill.
Formulas to join cell values:
- CONCATENATE function in Excel - formulas to combine text strings, cells and columns.
- Merge Tables Wizard - quick way to combine two tables by common columns.
- Merge Duplicates Wizard - Combine similar rows into one by key columns.
If some data entries in your worksheet begin with a leading space, Flash Fill can get rid of them in a blink. Type the first value without a preceding space, and all extra spaces in other cells are gone too:
Formulas to clean data:
- Excel TRIM function - formulas to remove excess spaces in Excel.
Data cleaning tools:
- Text Toolkit for Excel - trim all leading, trailing and in-between spaces but a single space character between words.
Format text, numbers and dates
Quite often the data in your spreadsheets is formatted in one way while you want it in another. Just start typing the values exactly as you want them to appear, and Flash Fill will do the rest.
Perhaps you have a column of first and last names in lowercase. You wish the last and first names to be in proper case, separated with a comma. A piece of cake for Flash Fill :)
Maybe you are working with a column of numbers that need to be formatted as phone numbers. The task can be accomplished by using a predefined Special format or creating a custom number format. Or you can do it an easy way with Flash Fill:
To re-format the dates to your liking, you can apply the corresponding Date format or type a properly formatted date into the first cell. Oops, no suggestions have appeared What if we press the Flash Fill shortcut (Ctrl + E) or click its button on the ribbon? Yep, it works beautifully!
Replace part of cell contents
Replacing part of a string with some other text is a very common operation in Excel, which Flash Fill can also automate.
Let's say, you have a column of social security numbers and you want to censor this sensitive information by replacing the last 4 digits with XXXX.
To have it done, either use the REPLACE function or type the desired value in the first cell and let Flash Fill auto fill the remaining cells:
Flash Fill in Excel can accomplish not only straightforward tasks like demonstrated in the above examples but also perform more sophisticated data re-arrangements.
As an example, let's combine different pieces of information from 3 columns and add a few custom characters to the result.
Supposing, you have first names in column A, last names in column B, and domain names in column C. Based on this information, you want to generate email addresses in this format: .
For experienced Excel users, there is no problem to extract the initial with the LEFT function, convert all the characters to lowercase with the LOWER function and concatenate all the pieces by using the concatenation operator:
But can Excel Flash Fill create these email addresses for us automatically? Sure thing!
Excel Flash Fill limitations and caveats
Flash Fill is an awesome tool, but it does have a couple of limitations that you should be aware of before you start using this feature on your real data sets.
1. Flash Fill results do not update automatically
Unlike formulas, the results of Flash Fill are static. If you make any changes to the original data, they won't be reflected in the Flash Fill results.
2. May fail to identify a pattern
In some situations, especially when your original data are arranged or formatted differently, Flash Fill may stumble and produce incorrect results.
For example, if you use Flash Fill to extract middle names from the list where some entries contain only the First and Last names, the results for those cells will be wrong. So, it is wise to always review the Flash Fill output.
3. Ignores cells with non-printable characters
If some of the cells to be auto-filled contain spaces or other non-printable characters, Flash Fill will skip such cells.
So, if any of the resulting cells are blank, clear those cells (Home tab >Formats group > Clear > Clear All) and run Flash Fill again.
4. May convert numbers to strings
When using Flash Fill for reformatting numbers, please be aware that it may convert your numbers to alphanumeric strings. If you prefer to keep the numbers, use the capabilities of Excel format that changes only the visual representation, but not the underlying values.
How to turn Flash Fill on and off
Flash Fill in Excel is turned on by default. If you do not want any suggestions or automatic changes in your worksheets, you can disable Flash Fill in this way:
- In your Excel, go to File> Options.
- On the left panel, click Advanced.
- Under Editing options, clear the Automatically Flash Fill box.
- Click OK to save the changes.
To re-enable Flash Fill, simply select this box again.
Excel Flash Fill not working
In most cases, Flash Fill works without a hitch. When it falters, the below error may show up, and the following tips will help you get it fixed.
1. Provide more examples
Flash Fill learns by example. If it is unable to recognize a pattern in your data, fill in a couple more cells manually, so that Excel could try out different patterns and find the one best suited for your needs.
2. Force it to run
If Flash Fill suggestions do not appear automatically as you type, try to run it manually.
3. Make sure Flash Fill is enabled
If it does not start either automatically or manually, check if the Flash Fill functionality is turned on in your Excel.
4. Flash Fill error persists
If none of the above suggestions has worked and Excel Flash Fill still throws an error, there is nothing else you can do but enter the data manually or with formulas.
That's how you use Flash Fill in Excel. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
- How to use Auto Fill in Excel
- How to fill empty cells with 0, with value above/below
- How to enter the same data into multiple cells
- How to auto fill dates in Excel
Excel: featured articles
- Merge multiple sheets intoone
- Combine Excel files into one
- Compare two files / worksheets
- Merge 2 columns in Excel
- Compare 2 columns in Excel for matches and differences
- How to merge two or more tables in Excel
- CONCATENATE in Excel: combine text strings, cells and columns
- Create calendar in Excel (drop-down and printable)
- 3 ways to remove spaces between words in Excel cells
Table of contents
11 comments to "How to use Flash Fill in Excel 365 - 2013"
Nikki says:October 19, 2021 at 10:50 pm
I have shut flashfill off, yet it still tries to flashfill. I've never ever had flashfill work as described and it comes up with very strange fills for me (all kinds of misspelled words that make no sense). How to I permanently kill this feature. As I said, it is shut off. I can't get it to stop and I can't even use my spreadsheet as everytime I try to type something it flash fills garbage and when I undo and try again, it just flashfills again.Reply
Alexander Trifuntov (Ablebits Team) says:October 20, 2021 at 7:59 am
Turn off Flash Fill as described in the article above. Then restart Excel.Reply
Bhupal giri says:July 9, 2021 at 11:19 am
I don't have flash fill option anywhere in edit menu of Advanced option,Reply
Sarah says:May 14, 2021 at 9:23 am
Good day. Thank you for the tutorial. I have one problem. I want to extract a year from a given date say I want to extract 2020 from the date 3/02/2020. How do I go about it? I keep getting wrong responses.Reply
Alexander Trifuntov (Ablebits Team) says:May 14, 2021 at 9:32 am
I recommend reading this guide: Excel YEAR function - convert date to year.
This should solve your task.Reply
Cassandra says:December 30, 2020 at 2:50 am
How can I disable flash fill and copy paste in a workbook?Reply
Kamran Imtiaz says:October 8, 2020 at 6:56 am
I just want to learnt about the Flash fill formula. My excel sheet requirement is as under;
let and example;
Ali-050512-25-07-20220 i want to extract only the number not the name and date and the said number has the different length.
kindly provide me the solutionReply
Alexander Trifuntov (Ablebits Team) says:October 8, 2020 at 7:58 am
Without seeing your data it is difficult to give you any advice.
Flash fill looks for a common pattern in your data. To do this, he uses several cells arranged sequentially in a column. From your example it is impossible to understand where the date is, where the number is. But if the number can have a different number of digits, then there is no pattern. Give an examples of the source data and the expected result.
Itll help me understand it better and find a solution for you.Reply
amin says:May 4, 2021 at 1:53 pm
You may use excel bulitin tools
Combination of these tools
Text to columns
AL says:May 12, 2020 at 8:08 pm
While I can see the benefits in some cases of flash fill; IT SHOULD NOT EVER BE AUTOMATICALLY ENABLED TO AUTOMATICALLY FLASH FILL! I have a document maintained by myself and 3 other people and this stupid auto enabled new feature started auto auto-filling the initials of 1 person over everything! If ya got a new feature then simply put it in a one time notification and don't assume everyone does things a stupid and/or unproductive and/or risky manner that would cause misinformation being typed in by someone not paying attention to the hidden fact that you added a feature but didn't tell ANYONE!Reply
Robin Cooper says:January 17, 2020 at 1:47 am
This is quit working:
2 9 /5day
39 116 /3day
18 49 /3day
I need the 2nd column to include the "N/3day" message.Reply
Post a commentClick here to cancel reply.
Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)