I have long been a fan of using autofill shortcuts to save time in Excel. I recently learned about a smart feature in Excel that takes autofill to the next level. The Flash Fill feature appears to be able to read the mind of the user, allowing them to make bulk changes in an instant.
Auto-filling Cells
Flash fill is part of a larger set of autofill features that allow a user to click and drag a “fill handle” across a number of cells and choose how the selected cells fill. To find the fill handle on a cell, click the cell once so it is outlined, but make sure the cursor does not appear in the cell. Then look at the lower-right corner of the cell. There is a tiny box there that is your target to click and drag.
If I click and drag down on the fill handle, cells in the column below will highlight. When I release the mouse, the highlighted cells will fill with copies of the first cell.
If you just drag and let go, it copies the first cell by default, but you can see in the second image above that an icon has appeared just below the fill handle. This icon leads to the fill menu, which will allow you to select a different way to autofill the cells besides copying.
For the text example “Dave” we are given the option to copy only the formatting without the text, fill the text without formatting, or flash fill. If you are performing the same option on a cell with a number, you are presented with an option to fill series, which allows you to fill with consecutive numbers.
Both of these menus include the flash fill option, but we will need a new example to show how flash fill can shine.
Flash Fill
Flash fill is like autofill on steroids, and it is most helpful if you are working with a batch of raw data that needs to be reformatted. Some examples would include turning 10-digit numbers into phone numbers or splitting a full name column into first name and last name columns. Just show Excel the pattern you are looking for, and it will learn how to fill in the area the way you want.
For an in-depth look at flash fill, check out this Tech-Talk article. Tech-Talk is a technology training service provided to patrons by the library. Learn more about Tech-Talk in our Tech Tips article about finding answers to common tech questions. If you would prefer to watch a video demonstrating flash fill, this video from My Online Training Hub is quick and easy to follow.
Summary
Autofill and flash fill are two features that save Excel users valuable time in creating and formatting spreadsheets. Do you use autofill or flash fill? Do you have a different time-saving Excel feature that you’d like to share? Tell us about it in the comments.