top of page

Flash Fill


What is “Flash Fill”? I never heard of it until I saw a video on it and if I can describe it in one word, it was MIND-BLOWING and I wish I knew of it earlier!! So, to answer my question, flash fill “automatically fill in values” - Microsoft Excel

Flash fill is great if you have limited knowledge of Excel formulas or short on time. First, before I get started on how it works, let’s make sure I have “automatically enable flash fill” enabled.

*Please note I am using Excel 2013, so there might be a slight variation as to how to enable auto flash fill in other versions of Excel.

FILE > OPTIONS > ADVANCED. Under “Editing Options”, make sure the box next to “Automatically Flash Fill” is ticked.

Example 1 - Format Text

Example 1.1 - lower-case to all-caps

I have list of states in Australia. Unfortunately, they are all in lower-case. I want them formatted properly, that is in all-caps. Rather than re-typing each one, I’ll type the first state, in this case vic as VIC. And for the next one, if I just type in the first letter (N), Excel automatically predicts the format I want for the remaining states.

Alternatively, I can just format how I want the first State to be (VIC) and in the next row, use CTRL + E and the remaining States will populate with my desired formatting.

Example 1.2 - mixed-case to Capitalize Each Word

I have list of names which have a mixed case of lower-case and upper-case, e.g. some first names start with a capital letter but their surnames don’t and vice versa. However I want it all to be consistent; in other words both the first letter of the first and surname be capitalized.

Example 1.3 - concatenate

What does concatenate mean? According to Merriam-Webster dictionary, CONCATENATE means “linked together”.

I have list of first names & last names that I would like to link together. You might remember that I covered CONCATENATE formula in an earlier post. Instead of using the CONCATENATE formula, we can use flash fill - which is why I said flash fill is great for those with limited Excel knowledge.

Sometimes you might have to type in the first two rows with your desired format before Excel knows what you’re after (like the above). But that’s as complicated as it gets! :)

Example 2 - Format Numbers

Example 2.1 - insert 1000 Separator (,)

In this case, the auto-fill preview didn’t show up. But if you use the shortcut, CTRL + E, it will auto-fill.

Another example of time saving. Rather than highlighting the cells, and changing the number format, I can use auto-fill.

*The above screenshot was taken from Microsoft Excel on a Mac, but it's exactly the same as Windows.

Example 2.2 - format numbers as phone number format

I have a list of phone numbers (Australian format) but it’s not in the formatting of xx xxxx xxx (with area code).

Example 3 - Format Dates

Example 3.1 - insert (/) Separator between day, month, year

In this case, the auto-fill preview didn’t show up. But if you use the shortcut, CTRL + E, it will auto-fill.

I know in the above examples, there are less than 10 entries so you might be thinking it’s no biggie to re-type them. But what about if there are more than 50? This is where flash fill becomes useful, not to mention a super time saver!!

Have a go at replicating my examples here.

Reference:

Comments

bottom of page