extra-credit-header-2018

Use Excel’s Flash Fill to automatically fill in data quickly

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

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.

flash-fill


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.

SPONSORED REPORT

Get your clients ready for tax season

These year-end tax planning strategies address recent tax law changes enacted to help taxpayers deal with the pandemic, such as tax credits for sick leave and family leave and new rules for retirement plan distributions, as well as techniques for putting your clients in the best possible tax position.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.