Clean data quickly with Flash Fill

Posted on August 1st, 2015 in Excel Howtos - 32 comments

flash-fill-excelExcel has many powerful & time-saving features. Even by Excel’s standard, Flash Fill is magical. Introduced in 2013, Flash Fill is a rule engine to Excel’s fill logic. Every time you type something in a cell, Excel will try to guess the pattern and offers to fill up the rest of cells for you. That is some serious time saving magic.

Let’s understand what Flash Fill is and few sample use cases.

Flash Fill, a smart rule engine

Flash fill listens to your every key stroke and tries to guess what you are doing. Remember Clippy from Office 97? Think of Flash Fill as Clippy’s less annoying & invisible cousin. Once Flash Fill identifies a pattern in your data entry, it offers a way to type rest of the data for you. If you accept the suggestion, the rest of the cells are automatically filled up.

Flash fill may not be a convenient option for simple patterns (like 1,3,5… or a bunch of dates or month names). But once you go beyond the realm of simple patterns, Flash Fill can be very useful.

Especially, when it comes to cleaning data.

Example 1 – Extracting numbers from text

Let’s say you are looking at some text data and want to extract the number portion.

Now, there is no simple way to do this. Any formula or VBA approach can be tedious.

But see what happens when you unleash Flash Fill on this unruly data.

flas-fill-extract-first-number-from-text

Example 2 – Extracting first name from list of names

Again, writing a formula can be tricky ( LEFT(name, FIND(" ",name)) should work – more here).

But Flash Fill is faster and simpler. Just type the first few names and let Flash Fill do its magic.

flash-fill-extract-first-name

Example 3 – Writing a bunch of formulas

Humor me with a scenario where you have customer names and you must lookup some corresponding data. Obviously you plan to use VLOOKUP for this. But the lookup table has other plans. Instead of customer name, the lookup table has firstname-initial_of_lastname.  So for Bill Gates, the lookup table lists the name as Bill-G.

Of course, you can write a complex VLOOKUP. But why bother? Use Flash Fill to do the dirty work for you.

See below illustration to understand how this works.

vlookups-written-with-flashfill

Once the lookups are written, you can use FIND REPLACE (Ctrl+H) to add = at the front.

Flash Fill tips & tricks:

  • Press CTRL+E to trigger flash fill. Excel will look at previously typed data and guesses the rest.
  • To ignore Flash Fill suggestion, press ESC.
  • By default, Flash Fill will be always listening and offers suggestions whenever it can. If you want to disable this, Use File > Options > Advanced and uncheck “Automatically Flash Fill” option. Click here for a screenshot of this process.

Do you Flash Fill?

Flash Fill is a fun and powerful way to clean data and get what you want. I use it often, when dealing with complex datasets.

What about you? Do you Flash Fill? When do you use it? Please share your tips and use cases in the comments.

If you have never Flash Filled, go ahead and try it today. See the magic yourself and share your story in the comments.

Remember, your comments on this post qualify for $31 amazon gift card giveaway.

More fun & powerful ways to fill data:

If you like Flash Fill, check out below tutorials for more powerful ways to automate data entry & cleanup processes.

This post is part of our Awesome August Excel Festival.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

32 Responses to “Clean data quickly with Flash Fill”

  1. David Hager says:

    Honestly never used it, great feature. You can use it to remove initials from full names.

  2. Jack Roberts says:

    I've been using Excel for decades, writing\converting cells long-hand. Thank you for insight into a truly useful tool.

  3. Patricia McCarthy says:

    How Funny.. I was just talking about Flash Fill yesterday. Time to retire =LEFT and =RIGHT and a few other text functions! This is fast and efficient and great to use when the data is not all the same length.

  4. Denise R says:

    I have appreciated auto-fill for a long time. So I was excited to learn of Flash Fill. When I replicate your first example in Excel I type the first 2 cells and begin the 3rd I see the flash field results (which is totally awesome...) but the cells that started with a number and ended in text - brought the text in with it. Cells that started with text extracted the number perfectly. Is there something I missed? Even with the text behind the number - this is still a cool tool that I will be using.

  5. cinnabar says:

    Nice! I always thought the flash fill was just a fancy take on the fill option from previous versions of excel, rather than such a nifty tool. Cheers!!

  6. hooroy says:

    Looks good, but I didn't upgrade from Office 10 to 13. Any Office 10 add-in available for this, or do I have to wait till Office 16 ships? Thanks.

  7. Vihy says:

    This is infact interesting and saves a lot of time for people who are not good with functions or VBA coding. I recently used this to convert phone numbers which was within text to (xxx)-xxx-xxxx format.

    Done in seconds ! 🙂

  8. NARAYAN says:

    Hi ,

    The only disadvantage would be that unlike a formula , which can be copied down to take care of data which might be entered later , this will work only where the data is already available , whether it is a normal range or an Excel table. Is this so ?

    Is this available in VBA ?

  9. Eli says:

    "you can use FIND REPLACE (Ctrl+H) to add = at the front"
    Can anyone explain how we can do that with Find&Replace?

  10. SYED ANZAR HUSSAIN says:

    can any one tell me is this flash fill option supports in ms-office 2007,
    because i couldnt able to find this enabling flash fill option

    • hooroy says:

      Flashfill first appeared in MS Office 2013.

      Assuming your data is structured consistently, the following formula should work:
      =RIGHT(A1, LEN(A1)-FIND("CHQ.#",A1)-5)

      Hope this helps

    • Hui... says:

      @Syed
      It is built in functionality in 2013 and later versions
      It is not available in 2010

  11. SYED ANZAR HUSSAIN says:

    SETTLEMENT OF INV.# 2390, MCJV-25669, CHQ.# 17650
    SETTLEMENT OF INV.# 10347,10556,10558, CHQ.# 17551 PIE-13516,
    SETTLEMENT OF INV.# 717,758, GRL-16098,16104, CHQ.# 17553

    can any one please help me how to extract only chq numbers mentioned in these three different cells
    suggest me any convenient formula

  12. Kaushik Pola says:

    Its Awesome !!

  13. excel says:

    Can we have a VBA script that will mimic the flash fill magic in excel. ( May be such VBA can be used prior to Excel 2013 , to get flash fill effect).

  14. Candy says:

    Ah, this is awesome, esp if we have this feature for 2010.

    Loving your Aug series and all the daily posts so far, thanks for sharing!

  15. Francesco M says:

    thank you chandoo! It's a pity that is not available for Excel 2010. Interesting also LEFT(name, FIND(" ",name)) . I usually use Left formula , but for simple extractions

  16. Jeff Davis says:

    I haven't used this yet. I am going to, now that I know it exists.

  17. Dan says:

    Flash fill seems great! I will try it. BUT, don't you think this may make many excel users slightly LESS awesome when they are less challenged to develop creative solutions for problems?

  18. Gary Lundblad says:

    I like Flash Fill in concept; however; most of the time when it starts to suggest, it disappears before I can hit enter to accept it, and then I usually can't get it back. Has anyone else had this happen? It's really annoying.

    Thanks!

    Gary

  19. Kate says:

    I tried using this to separate surnames from a list which contained full names and titles. I also wanted them in Proper case instead of Upper case. It worked quite well, with one weird exception. The first name in the list was MCGEORGE, A.B., Mr., and I typed McGeorge. The next few names did not begin with MC, and Excel autofilled the whole column and I found that while most were exactly correct, I also had some where the first three letters were replaced with McG, eg RICHARDSON, A became McGhardson and INNES, B.C. became McGes. Meanwhile names that began with MC, eg McMILLAN, M became Mcmillan. So, not bad, but it pays to check the results. Can anyone explain why some names that didn't start with MC ended up with McG?

  20. Ramesh says:

    Good Morning,

    Flash fill is very useful and time saving when dealing with more data.

    It available in Excel 2013 only.

    Is it possible to add in this feature in Excel 2010???

    Ramesh.S

  21. James Paris says:

    Thanks for highlighting this great feature! I can definitely use it at work tomorrow and save myself lots of time writing formulae.

  22. Kumar says:

    Hi,

    great feature but not in Excel 2010 🙁

  23. Ravi says:

    I cannot find flashfill option in my Excel, I am using Microsoft Excel 2010 in the Package of Windows 7 Enterprise, 32 bit OS.

  24. James Perry says:

    Flash Fill the e-z way.

    First, select the blank cells u want to flash fill down.
    Enter data only of the first corresponding cell.
    (Data can be number or single character or text or even blanks in between) and press Enter.
    Then press Ctrl + E

  25. Andy says:

    Wow...that is a useful tool indeed. I regularly have to parse text to get the last name from a column of data. This will be a great time-saver for me!

Leave a Reply