Let’s say you have student information in a worksheet where students’ last names, first names, and ID numbers are in Column A. If you want their first name and last name to appear in Column B, you could use the CONCATENATE function to extract the data from Column A. However, it is probably faster to use Excel’s Flash Fill feature.
To use Flash Fill, type the first and last name in the first cell in Column B next to the name and ID, exactly as you would like it to appear. Then go to the next cell down and start to type the name from the second row of data. You will see the rest of the first and last names appear in the rest of the column in gray as in the screenshot below. Press Enter to accept the filled names.
There is also a keyboard shortcut for Flash Fill. Once you type the first and last name in the first cell, click in the next cell down. Then hold down the Ctrl key and press E. Flash Fill will populate your column based on the pattern it detects.
In Excel for Mac, the keyboard shortcut is the only way to use Flash Fill. The keyboard shortcut for Mac is Ctrl+E, just as in the Windows version.
Note: If you have a large amount of data or there are a lot of similar values, you may have to type a few cells for Excel to detect the pattern.
See this short tutorial video for Windows users or this tutorial video for Mac users for a step-by-step overview of how to use Excel’s Flash Fill feature to automatically fill in data based on patterns that Excel detects.
Wendy Tietz, CPA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.
