IQ Accounting Solutions

I’ve given several tips over the years about how to take the data in Excel and split it apart or put things together, rearrange it, or replace it. The examples have ranged from simple to complex. But Flash Fill was added in Excel 2013 and it can do an amazing number of things without even requiring to you remember a function name or syntax. It’s something that’s easier to show than to write about, so I would encourage you to watch the video.

If you have the following list in Excel. Type Tony in the “First Name” column. Then go to the Data tab on the ribbon and choose Flash Fill, or just press Ctrl+e. Excel will fill in each of the first names based on the contents of the Contact column. Type “Aldred” in the Last Name column and Flash Fill again to fill in all of the last names.

Let’s say all of these people work at the same company and you want to build a list of email addresses. Type taldred@mycompany.com as Tony’s email address and click Flash Fill. In this specific case Excel will misinterpret what you want and fill in the other lines with “t” as the first letter of each email address. But correct the second line and Flash Fill again, and Excel will learn from your change and correctly fill in the rest of the list. In some cases you might have to make multiple manual entries in order for Excel to understand the pattern.

Here’s an example using numbers instead of text. The phone numbers are difficult to read because they’re not formatted. Simply type in the number the way you want it in the next column on the first line, such as (770) 555-0654, then press Ctrl+e or click Flash Fill and the rest of the column will be completed for you.

The next time you’re faced with retyping a list of information in Excel to get it arranged the way you want, remember Flash Fill.