Help! Understanding Flash Fill in Excel

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Have you ever wanted to quickly split up data, substitute functions, or perform more complex tasks in Excel with a quick and simple shortcut? Well, may I introduce you to the magic known as Flash Fill? 

This intuitive tool considers your top cells and the values in previous columns to identify a pattern and auto-fill the rest based on that observed outline.

While Flash Fill is a no-doubt game-changer in Excel, it does have its limits. Specifically, it can only build upon the perception of a pattern. However, with a bit of practice, Flash Fill can become one of your favorite go-to features in Excel.

Understanding Flash Fill

If you’re a regular user of Microsoft Excel, you’re familiar with the auto-fill feature. But have you heard of its superpowered cousin, Flash Fill? It’s a powerful tool that saves time and effort when working with data.

Flash Fill takes the data in the columns to the left and your manual input in the cells above to form a structured output for the remaining rows. It’s much like a formula that way. But where a formula has specific rules to handle every situation, Flash Fill takes its best guess to do the hard work for you.

how to use flash fill in excel

Flash Fill can help you with a variety of tasks, such as combining separate parts of a name into one, extracting the middle initial, reformatting phone numbers, and cleaning up your data. However, it does have its limitations. For example, Flash Fill can only build upon a pattern, and you need to give it more examples to identify a successful pattern.

Overall, Flash Fill is a simple and powerful tool that can save you time and effort when working with data in Excel.

Enabling and Using Flash Fill

To use Flash Fill, you must first ensure it’s enabled in your Excel settings. 

To do this, select File, then Options, and under Advanced, check the box to automatically flash fill your cells if it isn’t already enabled.

enable flash fill

Once enabled, there are two ways to use Flash Fill. The first is to navigate to the Data tab and, under Data Tools, select the Flash Fill icon. The second is to use the keyboard shortcut CTRL + E.

To use Flash Fill, enter the desired result in the corresponding cell and have Flash Fill do the rest. For example, if you want to combine separate parts of a name into one, type the name in the format you’d like and hit enter. With the cell below it selected, hit the Flash Fill button on the ribbon or press the CTRL + E shortcut. Excel will concatenate the remaining rows to match your manual entry.

While Flash Fill is intuitive, it’s important to note that it’s limited by the perception of your intended pattern. You may need to give Flash Fill more examples in the form of extra manually inserted values to identify a successful pattern, especially when extracting data or cleaning up your data.

Flash Fill Limitations

Yes, Flash Fill does a heck of a job interpreting what you want, but let’s not pretend it’s a mind reader. When comparing it to other, more rule-based tools in Excel, the differences are clear.

Flash Fill vs. Formulas and Dynamic Functions

flash fill vs formulas

One of the main limitations of Flash Fill is that it’s only as reliable as the routine it deciphers. If the pattern is not clear or consistent, then Flash Fill may not be able to accurately fill in the data. This is where formulas and dynamic functions can be more helpful, as they can handle more complex situations and provide more control over the data.

For example, let’s say you have a list of full names in the format “Last Name, First Name,” and you want to split them into separate columns. Flash Fill can do this, but it may not always be accurate if there are variations in the data. Formulas and dynamic functions, on the other hand, can handle these variations and provide more control over the output.

Another limitation of Flash Fill is that it can only fill in one column at a time. This means that if you want to fill in multiple columns at once, you may want to try your hand at a formula or dynamic function. Otherwise, you can Flash Fill each column individually.

Combining Data with Flash Fill

Do you want to manually combine data from different columns in Excel? Flash Fill can help make this process a breeze. With Flash Fill, you can combine data from multiple columns into one by simply typing the desired result in the corresponding cell and letting Excel do the rest.

Let’s say you have three columns with first, middle, and last names and want to combine them into one column. Simply type in the desired result in the corresponding cell and hit enter. With the cell below it selected, press the Flash Fill button or use the CTRL + E shortcut. Excel will then use the observed pattern to auto-fill the rest of the column.

flash fill multiple cells to one

However, remember that it’s not always going to be that simple. For instance, if some rows are missing middle initials, Excel may add an extra blank space between the first and last names. This is where manually entering a second row can give Flash Fill a helping hand.

Extracting Data with Flash Fill

Flash Fill can be a lifesaver if you have a list of data in one cell with jumbled columns from which you want to extract specific information. Think of a downloaded CSV file.

With Flash Fill, you can extract parts of a cell or string and have Excel fill in the rest of the column based on your example.

To use Flash Fill for extracting data, start by typing the desired result in the corresponding cell. For example, type the middle initial in the first cell to pull it from a list of full names. Then, hit enter and select the cell below it. Press CTRL + E to activate Flash Fill, and Excel will fill in the rest of the column for you.

flash fill extract middle initial from name

Again, expecting Flash Fill to get this correct 100 percent of the time is a fool’s errand. Like before, providing additional examples to develop a baseline for the output can only help you. 

And as an alternative for this same task, you could look into using the Text to Columns tool or even Power Query.

Reformatting Data with Flash Fill

If you’re looking to reformat data in Excel, Flash Fill is an excellent tool in your arsenal. Flash Fill lets you quickly and easily manipulate data to fit your needs. Whether you need to split data into multiple columns, combine data from multiple columns into one, or reformat dates and times, Flash Fill has got you covered.

To use Flash Fill in this way, enter the desired output in the first cell of the column, hit enter, and press CTRL + E. 

Do I sound like a broken record yet?

Using this to make a column of dates in various formats into a uniform output is simple. But to produce accurate results, you may have to add your own expertise if Flash Fill is struggling with the desired result. For example, open the format window with CTRL + 1 and set some boundaries for the fill.

format dates with flash fill

Cleaning Data with Flash Fill

Cleaning your data can be a tedious and time-consuming task, but with Flash Fill in Excel, it can be a breeze. This tool lets you quickly format your data and remove unnecessary blank spaces, change case sensitivity, and more.

To cleanse your data in one step versus using a nested formula, enter the desired values in the appropriate cell or cells and Flash Fill. But, if those extra blank spaces and other inaccuracies still exist, give Flash Fill a second or third row to reconsider its perceived outline.

Generating Passwords with Flash Fill

Now, let’s talk about using Flash Fill to generate passwords for different users, create an email address list, and do other multifaceted tasks. 

Let’s say you have four columns of unique identifiers for each user, and you want to create a secure password based on those values. The original cells might include their name, favorite color, favorite number, or some other distinguishable characteristic.

To start, type in the password for your first user, mix up the upper and lower case spelling and add in some special characters, like a hashtag, underscore, period, etc. Separate each section of the password with one of each special character and limit the output from being too predictable.

For instance, only include the first couple of letters of a word or the first digit of a number. Once you have the first password entered, Flash Fill can do the rest. 

use flash fill to generate passwords

One downside to this method is that you can’t exercise more imagination. What I mean is if you wanted to take your favorite color and have it spelled backward to add a higher level of security, Flash Fill wouldn’t pick up on this reversal, even with an extra row to help it learn the pattern. It’s reading your values from left to right and won’t comprehend the connection. 

So, while it’s a quick way to manipulate your source data, it won’t perform the same way formulas and dynamic functions will.

Easier Now?

Congratulations! You’ve just learned how to use Flash Fill in Excel. This powerful tool can save you time and effort by automating tasks that would otherwise take longer to master. Remember that while Flash Fill is a great feature, it can’t think at your level. 

Excel can only work with the data you give it, so it’s crucial to ensure your data is clean and well-structured before using Flash Fill. If your data is messy or poorly formatted, you may need to spend some time cleaning it up before you can use Flash Fill effectively.

But with some practice and experimentation, you’ll soon be using Flash Fill to automate all sorts of tasks and streamline your workflow. So go ahead and give it a try – you won’t be disappointed!

Leave a Comment