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.

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).

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.

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

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.

Flash Fill Not Recognizing Pattern in Excel: 4 Ways

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.

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

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.

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….

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.

➤ 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.

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

➤ 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.

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.

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.

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

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 1^st one. Go to the Home tab > Select Clear (from the Editing section) > Choose Clear Contents.

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

➤ 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.

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.

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.

➤ 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.

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

Download Excel Workbook

Ways to Resolve Flash Fill Not Recognizing Pattern.xlsx

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.