How to Use Flash Fill in Microsoft Excel to Automagically Fill Cells with Correct Data

Chaitanya Tapase

Ah, yes, Microsoft Excel. That one tool that we love to hate and hate to love. If you’re working in a job where you needlessly have to work on Excel sheets throughout the day, then I really feel for you. I was in one such job ages ago and it’s good to sit back now and take a nonchalant look at it.

Parsing data in Excel? Do it our way. | Shutterstock
Parsing data in Excel? Do it our way | Shutterstock

If you, however, wanna be more productive while using similar functions while using Excel, let me suggest using Flash Fill.

It’s Like Magic

If you’ve never used Flash Fill before, it will feel like magic to you. It works on a simple principle of identifying patterns and filling up vacant rows with these patterns. Let’s look at an example.

Dividing Names

Let’s take a simple task of separating names, first names and initial of employees within Guiding Tech (and Albert Einstein) using MS Excel. Column B currently has names like you’d normally find in a database. Columns C onwards is where you want only one (or more) element of the full name.

Sample Sheet

So, all you need to do is first type the name of the first person manually. Hit Enter and in the next cell, don’t type anything. Instead, click on the Fill menu and then choose Flash Fill. And voila, all the other names will self-populate on their own.

If you don’t fully trust me, then here’s an unedited video (converted to GIF for easy viewing) for the next column.

Keyboard Shortcut

There is also a keyboard shortcut to achieve the same thing. Using the same sheet, you fill out the first cell manually like we did above and hit Enter. Now, hit CTRL+E at the same time and Flash Fill will automatically fill out the blank cells with the corresponding data.

You should, however, keep cross-checking if Flash Fill has filled the data correctly, or not. In the initials column, I saw that it wasn’t doing it right. But, when I entered the data for the first two names (instead of one) it was then able to fill out the rest without any issues. Hey, that’s still a lot of time saved.

Please Note: This method works as long as the input column has the same format throughout. If there is any deviation, it won’t recognize the pattern.

Works With Numbers too

Before everyone starts to freak out, let me state, it works fine with numbers too. Whether it be decimals with n numbers before or after the decimal point, or even dates that need to be segregated into dd/mm/yy format.

Another video here (converted to GIF again), showing that I entered the first cells info manually and Flash Fill was able to pick up the pattern and filled the missing info below automagically.

Avoid Complex Formulas

The whole idea to use Flash Fill is to avoid complex formulas within MS Excel. If you can think of other scenarios too, do let us know in our forums.

Also See

Join the newsletter

Chaitanya Tapase has varied interests in Technology from Android to Windows to DSLR Photography. On weekends, you'd find him catching up on TV Shows like Game of Thrones and enjoying House Music.