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.

VIDEO

Excel walk-through: Sparklines

Want to liven up your spreadsheets with some color and graphical elements? Kelly L. Williams, CPA, Ph.D., shows how to use Excel sparklines, which illustrate data trends and patterns via small charts that fit in a single Excel cell.

PODCAST

What’s next for potential CPA licensure changes

A new model proposed by NASBA and the AICPA is designed with an eye on the future for newly licensed CPAs. The AICPA's Carl Mayes, CPA, provides background on the project and a look ahead to 2020.