Flash Fill is one of the best automation tools that’s already built into Microsoft Excel. Rather than spending hours typing out repetitive data in each individual cell, you can have Flash Fill anticipate your needs and take care of the job for you. But if you don’t have Flash Fill turned on or you don’t know how it works, you may not be able to take full advantage of its unique capabilities.
So, what does Flash Fill do? Is there a Flash Fill formula you need to learn? How does Flash Fill differ from AutoFill? Finally, how can you get help with Excel to maximize the power of tools like Flash Fill?
In today’s guide, we will answer all of these questions and more, but first, let’s look at how the Flash Fill function really works:
What Is Flash Fill In Excel?
Essentially, Flash Fill is like the Excel version of predictive text. Whenever Excel thinks there is a pattern to what you’re doing, the Flash Fill function offers you the chance to automate the process and (potentially) complete hours of work in seconds. You can either choose to have Flash Fill running by default in the background to help automate various data entry tasks, or you can turn it on manually whenever you need it.
You might be thinking that Flash Fill sounds a lot like AutoFill, and you’re not wrong. The two have similar functions, but Flash Fill goes above and beyond what AutoFill can do. While AutoFill can predict very basic patterns and fill in the rest, Flash Fill can predict more complex patterns and even adapt to changes you make as needed. This means you don’t need to use complex formulas for many different tasks related to data entry — you can just rely on Flash Fill.
Where Is The Flash Fill Button In Excel?
Depending on the version of Excel you use, you may not have to do anything to make Flash Fill work. However, if Flash Fill is turned off or you simply want to manually activate the feature, it’s pretty easy to do. Simply navigate to the Data tab and click on “Flash Fill.”
If you’re running Excel on a Windows machine, you can find the button located to the right of the “Clear” button, to the left of the “Consolidate” button, and directly above the “Remove Duplicates” button. Just remember that you first have to navigate to the Data tab near the top of your screen to see the Flash Fill button. You can also use the keyboard shortcut CTRL + E to turn the feature on.
If you’re running Excel on MacOS, the layout is a little different. To find the Flash Fill button, you’ll need to navigate to the Table tab and locate the Fill dropdown menu on the far right of your screen. When you click on Fill, you’ll need to choose Flash Fill at the button of the menu. You can also manually activate Flash Fill by using the keyboard shortcut CONTROL + E.
How To Use Flash Fill In Excel
The great thing about this function is that there’s no Flash Fill formula that you’ll need to learn. Plus, Flash Fill works the same way, regardless of the kind of data you’re inputting. However, to see it in action, it can be beneficial to consider some common uses for the tool. In the sections below, we’ll look at some typical use cases and walk you through the process of making Flash Fill do the heavy lifting for you:
Flash Fill Dates
Let’s say that you need to input hundreds of dates using a specific format. Column A includes date formats that are not recognized by Excel. You want to convert all of the dates into something that Excel will recognize in Column B, like this:
| Column A | Column B |
| Mon Jan 17 of 2023 | 01/17/23 |
| Tue Jan 18 of 2023 | |
| Wed Jan 19 of 2023 |
Once you begin typing the correct date format into Column B, Flash Fill will be able to sense a pattern. If you already have Flash Fill turned on, you’ll see a preview of the Flash Fill dates appear in the rest of Column B. From here, you can choose to have Flash Fill input the rest of the dates for you. Even if Flash Fill isn’t turned on, you can activate it manually from the Data tab (as outlined in the previous section).
Flash Fill Names
But what else can Flash Fill predict? While Flash Fill may not be able to anticipate every single imaginable pattern, it is good at predicting patterns of repetitive tasks that you can automate, including names.
Let’s say you have a list of client names that are separated by first and last names. The first names are in Column A, while the last names are in Column B. You want to keep these columns, but you also want to have the first and last names put together in Column C. You can see how this would look in the table below:
| Column A | Column B | Column C |
| John | Smith | John Smith |
| Sarah | Jones | |
| Mark | Hoffman |
Once again, you can get the Flash Fill feature to kick in by simply typing the information into Column C. As soon as Flash Fill determines that there is a repeatable pattern, it will give you a preview of all of the names listed correctly. All you have to do is accept the changes and voila! You’ve got all of the data ready to go in Column C.
Flash Fill Numbers
When working with numbers, you may need to input a little more information so that Excel can understand the pattern. Let’s say you want to enter numbers sequentially in a given column. You can still use Flash Fill to do most of the work for you. All you need to do is begin typing the first few numbers in each cell. Then, drag the fill handle across the range you want to be filled. Finally, click on the Flash Fill button to finalize the change.
Remember that this doesn’t just work with sequential lists of numbers. As long as there is a numerical pattern that’s detectable by Excel, you could use Flash Fill to automate the work. For example, you may want to fill each cell with even numbers, numbers that increase using a specified formula, or even numbers that correlate to data in a different column.
How To Turn Off Flash Fill In Excel
You may find that having Flash Fill enabled can get distracting. After all, Flash Fill may predict patterns incorrectly, which could cause you to accidentally fill out hundreds of cells with the wrong data. Fortunately, the processes to activate and deactivate Flash Fill are essentially the same.
To enable Flash Fill in Excel, follow these steps:
- Go to Tools
- Select Options > Advanced > Editing Options
- Check the box for “Automatically Flash Fill”
To disable Flash Fill in Excel, follow these steps:
- Go to Tools
- Select Options > Advanced > Editing Options
- Uncheck the box for “Automatically Flash Fill”
Keep in mind that having Flash Fill on by default will not force you to use the feature. It will simply offer you a preview of the function if and when it detects a pattern. You can always choose to ignore the preview and continue inputting data manually.
Need help from dependable software experts? Reach out to Geeker today for on-demand IT and software solutions!