• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Flash Fill

You are here: Home / Excel / Excel Flash Fill
Excel Flash Fill
August 25, 2015 by Mynda Treacy

Excel’s Flash Fill tool is so cool that you’ll be looking for reasons to use it! Introduced in Excel 2013 to rave reviews, Flash Fill uses a form of machine-learning techniques to reformat data automatically based on one or two examples you give it.

No more complex formulas or VBA, with Flash Fill you'll be able to split, reformat and combine text in a flash! Sorry, I couldn't help myself 😉

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Excel Flash Fill Example

Let’s say you have a list of names separated into columns. Some have middle initials, some have incorrect capitalisation. The point is they’re not consistently formatted. Not to worry, I’ll give Flash Fill a couple of examples and leave it to do the rest:

excel flash fill to join names and correct formatting

Mind blown? I’m sure you’ll agree it’s amazing. Let’s give it a harder challenge. This list was actually sent in by a member who had spent some time writing complex formulas to handle most of it, but Flash Fill does it in a cinch. Notice some names have spaces between the first and last and others are hyphenated:

correct spacing in list of names with excel flash fill

I've given it 4 examples in column B to cover all permutations which helps Flash Fill know what I want.

Flash Fill Reformat Numbers or Text

You can also reformat numbers and text by adding a character to the string like this:

reformat numbers using flash fill

And if your data isn’t consistent then all you need to do is complete the examples for each permutation, even if they’re not the first or consecutive items in your list:

excel reformat numbers to text using flash fill

Add or Append text to a List

It’s easy to create a list of email addresses from a list of names, even if the first and last names are in different columns:

create list of email addresses from names

Split Data

It can also split data into multiple columns:

split data with flash fill

Convert Text to Dates

Easily convert text to dates, although you need to enter the first date or two, and format the cell as a Date for it to work in this example:

format text as dates

Turn Flash Fill On

Flash Fill should be turned on by default, but you can check in the Excel Options:

File tab > Options > Advanced:

turn on Excel Flash Fill

It should automatically start filling your data when it detects a pattern but if it doesn’t you can start it manually via the ribbon: Data tab > Flash Fill (or CTRL+E):

manually invoke Flash Fill

Tips for Using Flash Fill

  1. To use Flash Fill you need to be in the column adjacent to the column(s) containing your original data.

  2. Format your headers different to your data to help Excel know that the top row is a header so it won’t use it in determining the pattern. This will also help trigger it automatically as you can see below:

help detect patterns

 
  1. Give Flash Fill an example of the final result you want for every permutation in your data. This will help it accurately determine the pattern.

  2. Beware, sometimes it gets the pattern wrong, other times it’ll leave them blank. If it leaves blanks then you can just go and add an example for the remaining items and it’ll finish the job:

flash fill gets it wrong

  1. If it makes a mistake you can just correct one of the entries and it will fix the rest:

correct mistakes

  1. Don't forget you need Excel 2013 or later for Flash Fill. It's almost worth upgrading but with Excel 2016 around the corner, I'd wait and get the latest and greatest.

History of Flash Fill

Flash Fill is the result of a serendipitous meeting between a senior researcher at Microsoft and a business woman on a flight. You can read the story here and learn more about how it was developed.

And if you liked Flash Fill please leave a comment below and give it a score out of 10. It's a 10/10 for me.

Excel Flash Fill

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: Excel
Previous Post:Excel forms, insert, update, delete dataExcel Forms – Insert, Update and Delete
Next Post:Add Data to Combo Box Drop Down List in Excel Formadd data to combo box drop down list in excel form

Reader Interactions

Comments

  1. Jon PInney

    September 3, 2022 at 1:12 am

    This was just what I needed.

    Reply
    • Mynda Treacy

      September 3, 2022 at 8:01 am

      Great to hear, Jon!

      Reply
  2. Raj Arora

    June 23, 2022 at 11:23 pm

    Lovely content!

    Reply
    • Mynda Treacy

      June 24, 2022 at 8:18 am

      Thanks so much, Raj!

      Reply
  3. Duncan Williamson

    December 7, 2019 at 4:13 pm

    When using Ctrl+E, you do not need to select the whole column. Your cursor can be anywhere in the column when you press Ctrl+E.

    A small time saver

    Reply
    • Mynda Treacy

      December 8, 2019 at 8:54 am

      Nice tip, thanks for sharing, Duncan 🙂

      Reply
  4. Raymond Smith

    June 1, 2017 at 3:51 pm

    Such a nice article thanks we liked it.

    Reply
    • Mynda Treacy

      June 1, 2017 at 4:20 pm

      Thanks, Raymond 🙂

      Reply
  5. Julian

    August 30, 2015 at 11:33 pm

    To learn Excel’s advanced features in depth, it’s the best way not miss every blogs put here on the hub.

    Reply
  6. Jon Acampora

    August 30, 2015 at 4:00 am

    Great article Mynda! Flash Fill is one amazing tool and I really like all the examples you showed. Awesome!

    Reply
    • Mynda Treacy

      August 30, 2015 at 10:21 am

      Thanks, Jon!

      Reply
  7. René

    August 28, 2015 at 12:21 am

    Wow, looks great!! Looking at the examples you give, I would give a 10/10!
    Unfortunately, at work we work in office 2007… (at home I am on a Mac). An extra reason to make an effort to get them make an upgrade!

    cheers, René

    Reply
  8. ZUR

    August 27, 2015 at 5:38 pm

    9/10

    Reply
    • Mynda Treacy

      August 27, 2015 at 7:40 pm

      Tough audience. Wonder what it needs to get 10/10…maybe being available in all versions!

      Mynda

      Reply
  9. Bud

    August 27, 2015 at 5:38 am

    Wow what a great feature. Since my daily job is entirely filled (pun intended) with crafting formula to effect the data manipulation being acheived via Flash Fill, I can appreciate the short-cuts and savings of time.

    Of course there obviously are blind delimiters like capital letters or the third character in a string, which apparently must be consistent in the data for Flash Fill to work as expected on all data in a range. But CTRL-H or =Proper(A:A) to capitalize the first letter in a name of all lower case, can assist.

    Bottom line I think those who may be intimidated by formula for string manipulation will find a ready friend in Flash Fill. I don’t have 2013 on my work computer, but I do at home. Hmm, I’ll not tell the boss less she suggest I take work home.

    Reply
    • Mynda Treacy

      August 27, 2015 at 9:04 am

      Hi Bud,

      Glad you liked it.

      “Of course there obviously are blind delimiters like capital letters or the third character in a string, which apparently must be consistent in the data for Flash Fill to work as expected on all data in a range” ….. which is why you give it a few examples in an attempt to cover all permutations of formatting, or fix it before Flash Fill. Which ever is quicker.

      Maybe tell your boss how great Flash Fill is and she will buy Excel 2013 for you! Probably a dream, I know.

      Mynda

      Reply
  10. Jude

    August 27, 2015 at 5:26 am

    Love and shared it! 10/10

    Reply
    • Mynda Treacy

      August 27, 2015 at 8:59 am

      Fantastic! Thanks, Jude.

      Reply
  11. Ted

    August 27, 2015 at 4:39 am

    Ok, I finally figured it out
    Do not format the column as a date format. Format it as general
    Then it will take 13/10/29 and make it 10/29/2013 and turn the cell into a date format
    Then drag down your box and hit Ctrl e or E and it will give you the correct dates.
    Then you can go to format and it will be in date format so you can choose the specific date format you want.
    If you make the column in a date format first, you will get garbage.

    Reply
    • Mynda Treacy

      August 27, 2015 at 9:02 am

      That’s interesting, Ted. Thanks for sharing.

      When I experimented with converting text to dates I found that formatting the cells as ‘Date’ before Flash Fill worked. I didn’t try general, though so this might work too.

      Mynda

      Reply
  12. Ted

    August 27, 2015 at 4:05 am

    I downloaded 6,000 lines from a website that entered the dates in general format
    I formatted my column as a date, choosing March 14, 2012.
    13/10/29 October 29, 2013 my entry 10/29/13
    13/09/05 September 5, 2013 my entry 09/05/13 I then did Ctrl e and got zilch
    So I deleted the above and ran it with just one example
    13/11.26 October 39, 2013
    13/12/08 October 89, 2013

    This formula worked well with no errors
    =DATE(2000+LEFT(F2,2)*1,RIGHT(LEFT(F2,5),2),RIGHT(F2,2))

    The flash fill worked ok on the other examples.
    I give Flash Fill a 5, since I need it for dates more than anything.

    Reply
  13. Julie Malinosky

    August 26, 2015 at 11:57 pm

    I was excited to try Flash Fill, but discovered that it is not available in Excel 2010 which is the current version I am using.

    Reply
    • Mynda Treacy

      August 27, 2015 at 9:00 am

      Yes, unfortunately it was only introduced in Excel 2013. A good reason to upgrade 😉

      Reply
  14. MF

    August 26, 2015 at 12:47 pm

    Even though I haven’t tried Flash Fill, your demonstration persuades me to a score of 10/10.
    Cheers,

    Reply
    • Mynda Treacy

      August 26, 2015 at 12:49 pm

      Yay! Thanks, MF. Glad you like the look of Flash Fill 🙂

      Reply
  15. pmsocho

    August 26, 2015 at 6:52 am

    I’ve been using this mechanism for a long time but you pointed out one thing I didn’t know:
    “If it makes a mistake you can just correct one of the entries and it will fix the rest”.
    Great article. Thanks!

    Reply
    • Mynda Treacy

      August 26, 2015 at 8:32 am

      Thanks, Pmsocho! Glad I was able to teach you something new.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.