Save Time in Data Entry by Using Flash Fill in Excel

Microsoft Excel offers many functions that help you save time when working on something repetitive. One such tool is the flash fill in Excel.

This article will help you gain basic and advanced-level knowledge of the Excel flash fill functions, so that you can use this tool efficiently in real-world scenarios.

What Is Flash Fill in Excel?

Whenever Excel finds any pattern in your data entry task, it can reproduce it for you. And you don’t need to manually enter any data if the pattern is a good match.

For example, if you’re manually combining first names and last names from two columns and writing the full names in another column, Excel will offer you the flash fill option provided the function is active.

Therefore, it’s a Data Tools of Excel that helps you transform, extract, or combine data when you make Excel learn the pattern of data entry. It’s only available in Excel 2013 and later versions.

Where Can You Find Flash Fill in Excel?

The function is active on every worksheet by default. If Excel detects a pattern, it can complete the rest of the data entry in columns or rows when you:

• Extend the fill handle
• Use the Flash Fill command from the Data menu
• Hit the keyboard shortcut

Various Modes of Using Flash Fill in Excel

1. Spontaneous Flash Fill in Excel

Whenever you work on something repetitive that Excel can do in a flash, you’ll see a light gray suggestion box spanning the entire column from where you’ve started writing.

You can simply hit Enter to accept the auto-fill suggestion. The feature will only work if it’s active in the Options menu of Excel. Additionally, the flash fill can only fill cells downward and can’t do the same in the upward direction.

2. Flash Fill in Excel Command in the Ribbon Menu

If Excel doesn’t show you automatic flash fill suggestions but you think there is a pattern in the data entry work, then try these steps:

• Click on the Data tab of the Excel ribbon menu.
• In the Data Tools section, you’ll find the Flash Fill function.
• If the function is active or clickable, it means you can use it.
• Hit Flash Fill to automatically fill the column.

3. Apply Flash Fill Using the Fill Handle

Whenever you click on a cell and take the cursor to the bottom right corner of that cell, you’ll see a small plus icon. That’s the Fill Handle.

If you think your data fits a pattern, you can simply click on the Fill Handle and drag the mouse downwards to fill the column with the data you want.

3. The Flash Fill in Excel Keyboard Shortcut

Once you’ve typed one or two examples for Excel to follow in a flash fill process, you can hit the hotkey Ctrl+E to apply the function in a single click.

Conclusion

So far, you’ve read through an in-depth discussion of the flash fill in Excel function that works by finding patterns in your workbook data. You now possess the skill to apply it in the followings scenarios:

• Extraction of last name and first name
• Form full names from first name and last name data
• Get initials from names
• Convert the case of text to upper case or lower case
• Extract names from data

You may also read the ways to make a phone call from a computer.

Read Full Bio

Tamal Das

Tamal is a freelance writer at AddictiveTips. After gaining substantial experience in technology, finance, and business processes in his previous job in an IT consulting company, he adopted writing as a full-time profession 3 years ago. While not writing about the latest technology, he loves to play COD and binge-watch

Latest from Author

How to Check a Box in Word Doc How to Type an Em Dash: Top 5 Methods