In this article, we’re going to show you the top 5 reasons along with solutions for Flash Fill not working in Excel. To describe our methods to you, we’ve selected a dataset with 2 columns: “First Name”, and “Last Name”.
[Solved!] Flash Fill Not Working in Excel: 5 Solutions
1. Flash Fill Not Working in Excel If It Is Turned Off
Firstly, if Automatically Flash Fill is turned off then the Flash Fill feature will not work in Excel. We’ve added a column named “Full Name” to our dataset. We’re gonna use the Flash Fill to join columns B & C in that new column D.
If the feature is turned on, we could’ve seen the Flash Fill prompt. However, there is nothing shown on the screen.
Now, we’re going to turn on Automatic Flash Fill. To do that –
Steps:
- Firstly, press ALT + F, then T to bring up the Excel Options window.
- Secondly, go to the Advanced tab.
- Thirdly, put a tick mark on “Automatically Flash Fill”.
- Finally, press OK.
This time, when we type the second name we’ll see the Flash Fill prompt.
- Then, press ENTER.
Thus, we can make the Flash Fill work in Excel.
Read More: How to Turn Off Flash Fill in Excel
2. Flash Fill Not Working When Dataset in Horizontal Orientation
There are a few limitations of the Flash Fill feature. This feature only works for datasets arranged in the vertical orientation. Hence, we need to change our dataset orientation if it is not vertical.
We can see Flash Fill is not working here.
Now, we’re gonna change this horizontal dataset into a vertical dataset.
Steps:
- Firstly, remove the values from cells C6 and D6.
- Secondly, select the cell range B4:F6 and copy it.
- Thirdly, Right Click on cell B8 to bring up the Context Menu.
- Finally, from the Paste Options >>> select Transpose.
Thus, we’ll prepare our dataset to use Flash Fill.
- Then, we can type the first one and half of the data and the Flash Fill will work.
- Finally, press ENTER.
In conclusion, we’ve shown you the second reason for Flash Fill not working and the solution to that.
3. If Spelling Error Occurs Flash Fill Will Not Work in Excel
If we type wrong, then the Flash Fill will not be working in Excel. Notice, that we’ve added an extra “e” on the first name, therefore, the Flash Fill is not working.
To fix that –
Steps:
- Firstly, correct the spelling mistake by removing the extra “e”.
- Secondly, start typing on cell D6.
- Finally, press ENTER when the Flash Fill feature kicks in.
In conclusion, we’ve shown you yet another method of solving our problem.
4. Flash Fill Not Working in Excel When There Is a Hidden Column
When there is a column between our columns we’ll not be able to use the Flash Fill feature. Notice, that column D is hidden in our case.
Now, we’ll unhide column D.
Steps:
- Firstly, select columns C, D, and E.
- Secondly, Right-Click to bring up the Context Menu.
- Thirdly, select Unhide.
This will show column D. Then, we’ll delete the extra column.
- Then, delete column D.
- After that, we can start typing and Flash Fill will start working.
- Finally, press ENTER.
Therefore, this is another reason and solution for Flash Fill not working.
5. Existing Value in Row Will Prevent Flash Fill From Working
If there is existing value in any of the cells then Flash Fill may not work. We need to remove it to solve our problem.
Steps:
- Firstly, remove the value from cell D8.
- Secondly, start typing the pattern, and the Flash Fill will work.
- Finally, press ENTER.
In conclusion, our Flash Fill will start working in Excel.
Read More: Flash Fill Not Recognizing Pattern in Excel
Things to Remember
- This feature was introduced in Excel 2013. If you have an older version, you’ll not be able to use it.
Practice Section
We’ve added practice datasets in the Excel file, hence you can follow along with our methods easily.
Download Practice Workbook
Conclusion
We’ve shown you the top 5 reasons for Flash Fill not working in Excel and solutions to that problem. If you have any problems regarding these, feel free to comment below. Thanks for reading, keep excelling!
