Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)

Flash fill not recognizing pattern in Excel is a common caveat while inputting data using Excel’s Flash Fill feature. Generally, the main reason behind the Flash Fill feature not recognizing patterns is human error. In this article, we demonstrate some of the resolving ways in case Flash Fill doesn’t recognize patterns.

Let’s say, we have Full Names comprising First, Middle, and Last Names. And we use the Flash Fill feature to get various name types from the Full Name entries.

Dataset-Flash Fill not Recognizing Pattern in Excel


Download Excel Workbook


Excel Flash Fill

Excel Flash Fill is a feature or tool that analyzes entries when users enter them and fills the data after recognizing a pattern automatically. Excel offers Flash Fill feature from Excel version 2013 and onwards.

In order to apply Flash Fill, Go to the Date tab > Select Flash Fill (from the Data Tools section).

Applying Flash Fill-Flash Fill not Recognizing Pattern in Excel

There is also an alternative to executing the Flash Fill feature. Hover to the Home tab > Select Fill (from the Editing section) > Choose Flash Fill from the options.

Applying Flash Fill-Flash Fill not Recognizing Pattern in Excel

After selecting Flash Fill the other cells get filled up automatically with the First Names as Flash Fill recognizes the pattern.

Flash Fill result

We can apply Flash Fill just by pressing CTRL+E altogether. So, the nature of Flash Fill is that it first analyzes the inputted data pattern then fills the rest of the data complying with the pattern. However, in some user ill-handled cases, Flash Fill shows a notification that it fails to recognize any pattern entry or entries. In the latter sections, we demonstrate ways to resolve the issue.

Read More: How to Turn Off Flash Fill in Excel (2 Easy Methods)


4 Ways to Resolve Flash Fill Not Recognizing Pattern in Excel

Method 1: Providing More Entries to Mimic to Resolve Flash Fill Not Recognizing Pattern

Normally Excel’s Flash Fill fills data maintaining a pattern. But if we have data with random patterns, Flash Fill finds itself in difficulties to mimic the pattern.

Suppose we have Full Name entries as depicted in the following image and we want the First and Last Name. To explain the situation, we enter two types of inputs. In the single input type entry, we just enter First and Last Name for an entry. For the double input type entry, we enter two entries with First and Last Names.

more entries to mimic

Follow any of the ways to apply the Flash Fill feature shown in the Excel Flash Fill section for a single input type column.

single input

You see middle names also appear in the resultant cells. This happens because Flash Fill doesn’t have many inputted entries to mimic the pattern. Flash Fill assumes you want the Full Name entries in the First and Last Name column as it is in the Full Name column.

Now, apply the Flash Fill operation for the double input type. You see this time Flash Fill ignores the middle names that exist in the Full Name column and fills the data with desired outputs.

Double input-Flash Fill not Recognizing Pattern in Excel

Read More: [Solved!] Flash Fill Not Working in Excel (5 Reasons with Solutions)


Method 2: Removing any Hidden Blank Column to Resolve Flash Fill Not Recognizing Pattern

Now, we want to execute the Flash Fill tool and we provide sufficient entries to mimic the pattern. Nevertheless, Flash Fill shows an error window saying it looked at all the data next to the selection and didn’t see a pattern in the value….

removing hidden blank column

What’s gone wrong with the procedure? If we wonder, after inspecting the data we see a missing or hidden blank column between columns B and D. And hidden blank column C is the reason Flash Fill isn’t able to find any pattern in the dataset.

Hidden blank column C

Select both columns by the Column Number Header then Right-Click on the selection. The Context Menu (use SHIFT+F10) appears. From the Context Menu, Select the Unhide option.

Context Menu

In a moment the blank hidden column appears as shown in the following picture.

Reveling the hidden blank column

Just Type anything (i.e., First Name) on the column (i.e., Column C) header. After that apply the Flash Fill, you’ll see all the cells get filled with the First and Last Names as depicted in the image below.

Applying Flash Fill-Flash Fill not Recognizing Pattern in Excel

You can delete the hidden column and after that can apply the Flash Fill. When working with a huge dataset with numerous columns it’s normal to hide 1 or 2 columns for data representation. As a result, these hidden blank columns pose an issue applying the Flash Fill feature in datasets.

Read More: How to Fill Down Blanks in Excel (4 Quick Methods)


Similar Readings


Method 3: In Case of Existing Entries in Rows

For better representation, we demonstrate methods with a couple of entries. However, in reality, the number of entries is numerous and it’s normal to overlook some of them while applying features.

Suppose we want to apply Flash Fill in our Full Name Dataset. But there is an existing entry that we overlooked as shown in the below screenshot. In reality, there will be 100s of rows, and maybe 1 or 2 entries will be present in the column prior to the Flash Fill application.

existing entry-Flash Fill not Recognizing Pattern in Excel

Oblivion of the existing entries, we execute Flash Fill and find irrelevant outcomes as depicted in the picture below.

existing entry

We want the First Names in the First Name column. But as there was an existing Middle Name (i.e., Thomas) present prior to our Flash Fill application, Excel returns an amalgamation of First and Middle Names. And the outcomes are misleading to the core.

To make the Flash Fill work properly, Clear all the contents in the cell except the 1st one. Go to the Home tab > Select Clear (from the Editing section) > Choose Clear Contents.

Clearing contents

The Clear Contents command removes all the entries and leads to a scenario similar to the image below.

Clear Contents result

Apply the Flash Fill following one of the ways shown in the Excel Flash Fill section. You get all the First Names in the way depicted in the picture below.

existing entry final result

Not necessarily you have to apply Flash Fill for just First, Last or Middle Names. You can use it to fill any type of data having a pattern in its entries.

Read more: How to Repeat Rows a Specified Number of Times in Excel


Method 4: Enabling Automatically Flash Fill Option

Sometimes, the Automatically Flash Fill option gets unchecked, and Flash Fill doesn’t work or behave properly. To enable the Automatic Flash Fill option, follow the below sequences.

Go to the File ribbon.

enabling automatic flash-Flash Fill not Recognizing Pattern in Excel

In the File ribbon options, Select Options (in the left side of the window) > Choose Advanced (from Excel Options window) > Check the Automatically Flash Fill option > Click OK.

enabling automatic flash Now, after returning to the dataset, apply Flash Fill. You’ll get the desired result as it is supposed to be.

Final result

Read more: Applications of Excel Fill Series


Conclusion

In this article, we discuss Excel’s Flash Fill feature and ways to resolve the Flash Fill not recognizing the pattern issue. Providing insufficient entries, keeping hidden blank columns (if there are any), and any existing entries are the main cause of the Flash Fill issue. Hope you have a lucid idea of what not to do or have during the Flash Fill application in your dataset after going through this article. Comment, if you have further inquiries or have anything to add.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo