Automate routine data entry with Excel Flash Fill
Q. I enjoy learning about ways to increase my efficiency and save time in Microsoft Excel. Do you have any other tips to share?
A. Flash Fill is one of my favorite features in Excel because it can do a lot of routine tasks. If Excel can sense a pattern in what you are doing, it will try to complete the task for you. Flash Fill can be used with Excel 2013 and later versions on Windows and Excel 2016 and later versions on Macs. You can download a workbook I created with examples of using Flash Fill. You can access an accompanying video at the end of this article.
Let’s go through a couple of examples. In the first, let’s extract just the last names from column A and have them listed in column B. See the screenshot below.
To do this, simply start typing the last names in column B. I type Barnes in cell B2, and by the time I type just the S in cell B3, Excel thinks it has picked up on a pattern of what I am doing, and all the last names appear in column B, as shown in the screenshot below.
Notice that the remainder of the last names are in gray. At this point, you can click Enter to accept the update or Esc to decline.
Excel can also pick up on more complicated patterns. Look at the screenshot below. Let’s say we want to extract information from columns A, B, and C and have the name in column D be formatted as last name, followed by a comma, then a space and the first name, then a space and middle initial with a period.
In cell D2, I type Deen, Kent W., then go to cell D3 and start typing the next name. By the time I type the H, Excel has already picked up on the pattern and fills in the remaining names in the correct format, as shown in the screenshot below.
Click Enter to accept the update.
If Flash Fill does not automatically work for you, you may need to enable it on your device. To do this, click File on the Ribbon, then Options. Click Advanced, and put a check next to Automatically Flash Fill. Click OK.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
