Computer Tutoring Logo
Est. 2002
Menu Button

How to use Flash Fill in Excel?

Share

How to do Flash Fill in Excel?

Flash Fill - exercise file

1. Using Flash Fill to Split Data - Separate Full Names

Using Flash Fill to Split Data

Use Flash Fill to split data - starting point

You know the scenario, you need to separate a column of full names into first and last name. What's more is that the column of full names is over 100 long. What do you do?

Using Flash Fill to split data makes this process is quite straightforward. Want to give it a go? Do the following:

  • Click the cell to the right of the first name you want to separate
  • Use the Auto-fill square to drag down using the right mouse button to the last name
  • From the menu that appears choose Flash Fill

There you have it amazing, you can also use the short-cut key Ctrl & E

2. Convert US to UK dates using Flash Fill in Excel

Convert US to UK date in Excel using Flash Fill

Convert US to UK dates - starting point

Now we know that various countries around the world have various ways of displaying dates. But one of the most often encountered variation is the US/UK date divide. Basically, the US would display the 22nd of March 2020 as 03/22/2020. Whereas in the UK it would be displayed as 22/03/2020.

While neither of these is right or wrong, it is important to be consistent. Knowing how to convert US to UK dates using Flash Fill in Excel is one little trick you don't want to miss.

  • Click the cell to the right of the US date
  • Type in the date in the UK format
  • Flash Fill down by either:
    • Dragging Auto-fill with the right mouse button
    • Use the Ctrl & E short-cut key
  • Flash Fill will correct all of the dates

3. Create Email Address From Name

Create email address from name - Excel Flash Fill

Create email address from name - starting point

Flash fill is a great way to automatically create email addresses from names. So long as there is a consistent pattern to the email. For example, if the company email address consisted of first name, then a period, then last name Flash Fill in Excel will have no problem bringing the names together in an email address.

  • Click to the right at the top of the list of names you wish to convert to email addresses
  • Drag the Auto-fill square down to the bottom of the list of names
  • When you let go click on Flash Fill
  • Correct any email addresses that don't look correct
    (You might want to make the column wider so you don't accidentally click on the hyperlink and send the person an email.)

In addition to the 3rd point above. If Flash Fill should fail to bring together the email address in the correct way, you can correct the first occurrence. After that Flash Fill should automatically update the others. You may have to do this more than once, the more you make corrections the more Flash Fill can recognise the pattern.

4. How to Correct Date Format in Excel with Flash Fill?

How to correct date format in Excel

Correct date format - starting point

We often see dates entered in the incorrect format. This has either been entered incorrectly, or the date has been produced by a 3rd party database. However which way it happens if your date is the following format:

22.03.2020

Excel won't recognise the cell as a date; it will be classed as text. That being the case it means that all of those wonderful date calculations such as extracting the day of the week or month of the year won't work. What's more you will be unable to calculate the length of time between dates.

When entering a date in Excel it should be in the following format:

22/03/2020

So you want to know how to correct date format in Excel? Then Flash Fill can correct the date for you:

  • Click at the top of the range to the right of the cell that contains the date you wish to correct
  • Type in the correct date format
  • Flash Fill down using one of the above methods or try a new one:
    (Click the Data Tab then click Flash Fill)
  • The dates are converted to the correct format

5. Add Text Together in Excel

Add text together in Excel

Add text together in Excel - starting point

Sometimes you might want to add text together in Excel. Using Flash Fill this is possible. You might what to do this if you want to create a list of addresses.

  • At the top of the range of data click to the right of the row you want to have your address
  • Type in the address as you would want to see it, ensure that you use data from the same row
  • Flash Fill down by one of the ways above
  • Look down the list and make adjustments to any address that don't look right. You can even remove extra lines should some of the addresses not include an area or district.
  • See your results

It's amazing at how many people that use Excel overlook the Flash Fill feature. Whether they're looking to convert date formats or converting US date to UK Flash Fill makes the complicated easy. I especially love the short-cut Ctrl & E when using Flash Fill, it makes you look so professional. When you create an email address from a name in Excel, using Flash Fill makes this child’s play. Whether you want to create an email address from first and last name, or if you want to take into account the company name, Flash Fill will make it easy to convert text to email addresses in Excel.

Flash Fill is a fantastic tool in Excel and one that is often overlooked. Make sure that you know how to do Flash Fill in Excel to make your data entry that little bit easier.

Other Excel Videos Tutorials