In 2013, Excel introduced the Flash Fill feature that automatically applied formulas to the adjacent cells. Flash fill works for functions and self-generated patterns.
There are, however, a few things you must keep in mind before you use Flash Fill. For starters, the cell whose formula you’re looking to apply formulas from must be adjacent to your current column. Similarly, Flash Fill may run into issues recognizing patterns in numbers so, you must provide additional cells as references.
In this article, I have listed out the ways you can use Flash Fill to apply the formula to the entire column. I’ve also included examples so be sure to check them out!
Using Flash Fill
Now that we’ve established that you can use Flash Fill to apply a formula to the entire column, let’s look into how you can use this tool.
There are three ways you can use Flash Fill in Excel. Firstly, you can drag the fill handle to paste formulas, use the Data ribbon, or, my favorite, use a shortcut.
Drag the Fill Handle
You can drag the cell with the formula down to your column. This will apply the formula to each cell in between. This is how you can drag the fill handle to apply formulas to your column.
- Place your cursor on the bottom-right corner of the cell.
- Once the cursor changes to +, drag it to the last cell you wish to copy your formula.
- Drop the cursor.
Use Excel Ribbon
You can also use Excel’s ribbon to use Flash Fill to fill in formulas. If you’ve accidentally hidden your ribbon, use Ctrl + F1 to bring it back to view.
- Select the range you wish to apply the formula to, including the cell with the formula.
- Go to the Data tab.
- From the Data Tools section, select Flash Fill.
Use Shortcut
Instead of using the ribbon, you can also use a shortcut to apply formulas to the adjacent cells in the column. Select your range and use the following shortcut:
Ctrl + E
Examples: Using Flash Fill on Different Data Types
Flash Fill works differently according to the type of formula you wish to apply. Your formula may constitute functions and operators, or may just be a pattern you’re wishing to apply to your column.
Apply Formulas Containing Functions and Operators
Flash Fill works quite conveniently with functions and operators. This is because it’s easier to identify patterns in these formulas. Remember, that you can only use the fill-handle to autofill the formulas in your column.
We have two ranges in column A and column B. We’re looking to add the corresponding values in each cell. So, we’ll be using the SUM function to make this operation. If I were to use an operator instead of a function, I’d be using the + operator.
In cell C2, I entered the following formula:
=SUM(A2,B2)
The ranges I wish to add are extended to nine more rows. Instead of repeating this formula nine more times, I’ll be using Flash Fill.
- Select cell C2.
- Place your cursor on the bottom right corner of the cell.
- Drag and drop the fill handle.
Format Numbers as Dates
You can automatically apply certain formatting to your column using Flash Fill
In column A, I have fifteen number values, that I wish to separate as dates. In column B, I converted three of the dates manually to establish a pattern. Most of the time, Excel will auto-suggest the fill once it recognized a pattern. You can hit Enter to fill in the formulas.
If it doesn’t, select the range and use Ctrl + E to paste the formula to the adjacent cells. If you see numbers instead of your Date value, go to Home > General > Short Date.
Extracting First and Last Name
You can also use Flash Fill to extract first and last names out of an individual’s full name. Just like using flash fill with numbers, the reference column must be adjacent to the column you’re looking to use flash fill.
In column A I have names of 10 individuals. The delimiter separating the first name and last name is a space. I will be using flash fill to extract the first and last names from these values.
Extract First Name
In Cell B2, I manually entered the first name from Cell A2, John. Then, I selected the cells B2:B11 then used the Ctrl + E shortcut.
Extract Last Name
As I did while extracting the first name, I manually entered the last name from cell A2, Jenkins. Again, you can use any of the above-mentioned methods to perform flash fill to extract the last names of all individuals in range A2:A11.
Flash Fill Not Working?
Flash Fill needs to identify a pattern in order to automatically apply your formula.
If fill is not working, make sure the column you’re entering your new data is directly adjacent to the source column. Similarly, there are a number of combinations Excel can generate using Number values, so make sure you give more than one cell as a reference.