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.
Flash Fill is one of the most useful features that was introduced in Excel 2013. It can automate repetitive data entry tasks and save you a ton of time. In this comprehensive guide, I will explain what Flash Fill is, how it works, when to use it and walk you through several step-by-step examples.
What is Flash Fill in Excel?
Flash Fill is an intelligent feature that detects patterns in the data you enter and automatically fills in additional data based on that pattern,
For example, if you type a few zip codes or phone numbers in a column, Flash Fill will recognize the pattern and fill in the remaining cells for you. All you need to do is enter a couple of examples and Flash Fill will do the repetitive work for you.
This enables you to accomplish in seconds what would otherwise take several minutes of manual work to complete
How Does Flash Fill Work in Excel?
Flash Fill works by examining the examples you provide and identifying the pattern. It tests millions of different patterns to determine which one fits your data.
Once it recognizes the pattern, it will automatically populate the remaining cells.
If Flash Fill guesses incorrectly, you can simply undo (Ctrl + Z) and provide additional examples to help it detect the correct pattern.
The key is providing a couple of representative examples that will allow Flash Fill to infer the proper pattern.
When to Use Flash Fill in Excel
Here are some common scenarios where Flash Fill can save you a lot of time:
-
Splitting data from one column into multiple columns (e.g. splitting full names into first and last)
-
Concatenating data from multiple columns into one column (e.g. combining first and last name)
-
Extracting or parsing certain elements from strings (e.g. extracting area code from phone numbers)
-
Formatting data such as dates, numbers or text
-
Cleaning data by removing extra spaces, special characters etc.
-
Rearranging and reformatting text
Basically, any repetitive pattern you need to apply to multiple cells is a perfect use case for Flash Fill.
Limitations of Flash Fill
While Flash Fill is very handy, it’s important to be aware of a couple limitations:
-
The output of Flash Fill is static. If you change the original data, it will not automatically update.
-
Flash Fill may fail to detect the proper pattern if your data is formatted inconsistently.
-
It skips over cells with errors or blank values.
-
Numbers may be converted to text strings in some cases.
How to Use Flash Fill in Excel Step-by-Step
Below I will walk through several examples to demonstrate how to use Flash Fill in Excel:
1. Split Text into Columns
If you need to break out data from one column into separate columns, Flash Fill can do this for you:
-
Insert a new column beside your data.
-
In the first cell, type the value you want to extract. For example, if you want to split full names into first and last name columns, type the first name in the new column.
-
Start typing the next value in the cell below. Flash Fill will predict the pattern and automatically fill the remaining cells with the extracted values.
-
Press Enter to accept the Flash Fill preview.
That’s all there is to it!
2. Concatenate Text from Multiple Columns
You can also use Flash Fill to combine values from different columns:
-
Insert a new column.
-
In the first cell, type the concatenated value you want. For example, to combine first and last name columns, type the full name.
-
In the next cell, start typing the combined value. Flash Fill will kick in.
-
Press Enter to accept the preview.
3. Format Numbers or Dates
To reformat numbers or dates, simply type an example of the desired format in the first cell of the new column. Flash Fill will apply that formatting to the remaining values.
4. Remove Unwanted Characters
You can clean data by removing extra spaces, dashes, parentheses etc.
For example, to remove the area code from phone numbers:
-
Type an example in the new column without the area code.
-
When you start typing the next value, Flash Fill will strip out the area code automatically.
5. Rearrange Columns
You can even use Flash Fill to rearrange and reformat data all at once.
For example, to combine first name, last name and domain into an email address:
-
Type the full email address in the first cell.
-
Flash Fill will combine the values from the other columns based on your example.
As you can see, the key is providing a representative example that illustrates the pattern you want applied.
Flash Fill will then work its magic to complete the tedious work for you!
How to Enable and Disable Flash Fill
Flash Fill is turned on by default in Excel 2013 and later versions. But you can disable it if you don’t want it kicking in automatically:
-
Go to File > Options > Advanced.
-
Uncheck the box next to “Automatically Flash Fill”.
To turn it back on, simply check the box again.
Troubleshooting When Flash Fill is Not Working
In most cases, Flash Fill works seamlessly. But if you are having issues, here are some things to check:
-
Make sure Flash Fill is enabled in the options.
-
Provide additional examples if Flash Fill is not detecting the pattern.
-
Manually invoke Flash Fill by clicking the icon if it’s not starting automatically.
-
Undo and delete any cells with errors before running Flash Fill again.
Hopefully these tips will help if you run into any problems with Flash Fill in Excel. It’s one of the most useful automatic features for streamlining repetitive tasks.
Flash Fill is a huge time saver that can automate a wide variety of repetitive tasks in Excel.
To use it effectively:
-
Provide representative examples to establish the pattern
-
Let Flash Fill work its magic to complete the remaining data
-
Fix any errors by providing more examples or undoing
-
Disable Flash Fill if you don’t want automatic suggestions
Learning this feature can help boost your productivity in Excel dramatically. Try using Flash Fill the next time you need to split, concatenate, format or clean your data!
Frequency of Entities:
how to do flash fill in excel: 20
Flash Fill: 41
Excel: 37
data: 22
pattern: 9
columns: 7
cells: 6
examples: 6
text: 5
values: 5
first cell: 4
remaining cells: 3
formatting: 3
reformatting: 2
splitting: 2
concatenating: 2
cleaning: 2
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:
- Split Text – a versatile tool to divide cell content into multiple columns or rows. Easily split string by any character, string, or mask.
- Split Names tool – fast and easy way to separate names in Excel.
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 For MAJOR Time Saving (7 Examples)
What is flash fill in Excel?
Flash Fill is a powerful and useful feature in Microsoft Excel that can help you format and clean your data with ease and speed. Whether you are working with large datasets or just need to quickly reformat a simple list of names, Flash Fill can help you achieve your goal in just a few clicks.
How do I use flash fill?
To accept the preview, press ENTER. If Flash Fill doesn’t generate the preview, it might not be turned on. You can go to Data > Flash Fill to run it manually, or press Ctrl+E. To turn Flash Fill on, go to Tools > Options > Advanced > Editing Options > check the Automatically Flash Fill box.
How do I activate Flash fill in Excel?
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.
How do I flash fill a column in Excel?
When Excel recognizes the pattern of your formatting, a drop-down menu will appear below the cell, with the option to “Flash Fill”. Click on Flash Fill, and Excel will automatically fill in the rest of the new column based on the pattern it identified. Remember, Flash Fill is a powerful feature, but it isn’t perfect.
