fbpx
Search
Close this search box.

Clean data quickly with Flash Fill

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

42 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 ?

    • Duncan says:

      Flash Fill is better than that, It is often said that Flash Fill in Excel is really useful, except when it's not.

      If, for example, you do your Flash Fill thing and then realise there is an error in one of the rows of the data, Flash Fill will not update itself. However, the workaround I use in this situation is to delete the Flash Fill content up to and including the error row. I correct the error and then press Ctrl+E again at the current end of my Flash Fill List. It will update the whole list.

      Do something similar with your new data, step your Flash Fill output back a row or two and, with your new data in place, press Ctrl+E agian

    • Duncan says:

      I meant to add that Flash Fill in Power Query is, to quote Chandoo, even more awesome! You should try it!

  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!

  26. Terry K says:

    Flash Fill is AWESOME!!! This will save me tons of time. Thank you Chandoo!!!!

  27. Georgia says:

    Thank you! My fingers thank you too, so easy to use, I have to separate the first and surnames from some very long employee lists... this does the job in seconds! 🙂

  28. Etienne says:

    Wow, gave it a try and it's pretty intuitive as well!!! Thank you!!!

    One thing I found annoying was that it seems to make the suggestion (usually on the second cell entry) but as soon as you've typed the whole entry - as it would have suggested - and move on to the next cell, the suggestions stop! Thing is, many times you type so quickly that you don't even realise the suggestion came up or had time to see what it was trying to do.

    At least now I know of the Ctrl + E shortcut, I'm hopefully going to remember to every now an then just hit that and see if Excel fills in something for me.

    Why does this not work for a simple 1,3,5,7,... and does anyone know a quick way to do this. I'm currently forced to go through a rigmorale of Select two cells,Alt,H,F,I,S,T,Alt+T,Entr (a bit difficult to remember!)

  29. Beth says:

    I didn't know this existed.

  30. MITUL PARIKH says:

    Hi;

    I knew flash fill feature, and in this tutorial I learned Example - 3
    Thank you.

  31. Dinesh Arya says:

    Great!

    Never knew about this.

    Gave it a try. Amazing

  32. Karl says:

    I love your helpful hints. I am, however, the spelling police 🙂
    It's EXTRACTED not EXTRACED.
    Thank you Chandoo, keep up the good work!

  33. Uzezi Ernest says:

    Wow! This is awesome! I started following Chandoo recently and his videos are super explanatory. Thank you Chandoo.

Leave a Reply