Working with spreadsheets full of chaotic data can be a nightmare. For years, Excel has included a feature called Flash Fill that can identify patterns in what you type and automatically complete the rest of the column. The result: fewer formulas, fewer errors, and countless hours saved when you have to clean up names, addresses, phone numbers, or product codes.
In parallel, AI-based solutions have emerged that go a step further. Tools like Excelmatic allow you to transform and clean data simply by describing what you want to do in natural language , without needing a perfectly uniform pattern. In this article, we'll take an in-depth look at how to use Flash Fill in Excel with practical examples, and also explore the scenarios in which an AI option can offer significantly more flexibility.
What is Flash Fill in Excel?
Flash Fill is a smart feature available since Excel 2013 that observes what you type in a column adjacent to your data and, when it detects a clear pattern, fills in the rest of the cells for you. Instead of struggling with complicated formulas, you simply type a couple of examples of how you want the data to look.
The idea for this tool arose from a very common problem. A Microsoft researcher, Sumit Gulwani, encountered the typical question of how to combine first and last names in Excel , and he didn't have a simple answer without resorting to functions. From this real-life "pain point," a system was designed that could learn from the user's example and replicate it across all records.
In practice, Flash Fill is perfect for tasks such as separating first and last names, extracting postal codes from long addresses, changing the format of phone numbers , creating email addresses from names, or rearranging text strings without touching a single formula.
It is important to understand that Flash Fill works on the textual aspect of the data : it analyzes the characters it sees in the cells and tries to deduce the transformation, but it does not "reason" like a formula that understands data types, dates, or complex logical rules.
How to enable or disable Flash Fill in Excel
In most modern Office installations, Flash Fill is enabled by default , but it's worth knowing where the setting is in case it ever stops working or you want to turn it off to avoid automatic suggestions.
To review the settings, you need to go to the application options. The setting is located in Excel's advanced options , within the section related to cell editing, specifically in the checkbox that allows the program to automatically apply Flash Fill when it detects patterns.
If you choose to disable it, you can still use Flash Fill, but manually. The checkbox only affects automatic firing while typing ; the keyboard shortcuts and the ribbon button will still be available at any time.
Once you save the changes in the options dialog box, Excel's behavior will adjust to your preference . If you have it enabled, you'll see gray suggestions in the column when a pattern is detected; if you disable it, nothing will appear until you trigger it.
Ways to use Flash Fill: automatic and manual
The power of this feature lies not only in what it can do, but also in how easy it is to implement. Essentially, you have two ways to trigger Flash Fill : letting Excel suggest the rest of the data or manually forcing it to analyze the pattern when you deem it appropriate.
Autofocus is very convenient when the patterns are obvious. As you type the second or third example in the destination column , Excel connects the dots and displays the remaining suggested values for the rows below in gray. If what you see fits, simply press Enter and all the cells will be confirmed.
For example, imagine you have the name in column A and the position in column B, and you want something like “Give [CEO]”. You type the first result in column C with the format you like , and when you start the second row, Excel suggests the rest. Even if you make a small mistake with capitalization or spaces on the first try, the system usually adapts the pattern quite flexibly.
If no suggestions are displayed, it doesn't mean Flash Fill can't help you. In those cases, you can launch Flash Fill manually . Enter one or more examples, and then use the commands available on the ribbon or the keyboard shortcut.
On Windows, you can use Ctrl+E to force Excel to apply the detected pattern to the remaining rows. On Mac, the equivalent shortcut is Cmd+E. Alternatively, within the Data tab of the ribbon, you'll find a dedicated Flash Fill button that performs the same function.
Quick options after applying Quick Fill
Each time Flash Fill is run, Excel displays a small options button next to the last modified cell . This context menu allows you to quickly control what just happened and review the result without having to manually undo all the work.
One of the first options is to completely undo the Quick Fill action if you realize on the fly that the pattern used doesn't fit what you needed. This is more convenient than using the classic Ctrl+Z when you only want to remove the fill but keep other recent changes.
Another useful option is to mark the cells that the system was unable to generate. The "Select blank cells" function highlights only those records that failed to produce a result , usually due to inconsistencies in the original data or unusual characters that confused the pattern engine.
You also have the command available to identify which cells have been affected. “Select changed cells” marks all the positions that Flash Fill has filled at once , which is very useful when the column is long and you want to visually check that everything looks good before continuing to work.
Practical examples for mastering Flash Fill in Excel
The best way to understand the potential of Flash Fill is to look at real-world scenarios. In the day-to-day work of marketing, sales, finance, or operations, it's very common to encounter partially cleaned data coming from CRMs, web forms, or external CSV files. This is where these examples can save you a lot of time.
A classic use is separating full names. If you have values like "Laura Pérez Sánchez" in a column , you can create a column right next to it for the first name, type "Laura" in the first row, press Ctrl + A, and let Excel deduce the rest. The same applies to the last name(s) in another adjacent column.
Similarly, extracting a postal code from a long address is very easy . Simply enter the code in the first row of the destination column, select Flash Fill, and if the data is sufficiently consistent, you'll get the code for each record without having to manually copy and paste.
After applying Flash Fill in these types of cases, it's always a good idea to do a quick review. A glance at the rows with the most unusual addresses will help you detect if any cells have been filled incorrectly , for example, when the postal code is missing or appears in a different format.
AI-powered alternative: Excelmatic for extracting information
When data doesn't follow such a clear pattern, or when you want to chain several operations together, AI tools specializing in spreadsheets can become a very powerful ally . Excelmatic is a clear example: instead of teaching a pattern by writing examples, you simply describe what you need.
Imagine you upload your file to this type of platform. Instead of manually filling in the first name and hoping the pattern is recognized , you could write a natural language instruction like: “Create two new columns, 'First Name' and 'Last Name', from the 'Full Name' column.” The system will interpret your goal and generate both columns at once.
The same applies to addresses. If you want to reliably obtain the postal code even if the address structure changes , you can specify: “Extract the postal code from the 'Address' column and place it in a new column called 'CP'”. The AI doesn't just look at the visual pattern, but also at the context and meaning of what it sees in the cell.
Another major difference is that regenerating results is much more convenient . If you change the file, you simply run the instruction again instead of providing pattern examples, which is especially useful when working with multiple columns simultaneously.
Combine data from multiple columns without formulas
Flash Fill also shines when you want to combine information from different columns into a single text string . There's no need to write concatenations with the ampersand (&) symbol or use functions like CONCAT; simply display the desired format in the first record.
Imagine you have columns for first name, last name, and age, and you want a phrase like “Laura Pérez (35)”. In the new column, you write that exact result for the first person . When you start the second row with the same style, Excel deduces that it should follow the pattern and fills all the rows with “First Name Last Name (Age)”.
In these cases, the program can take into account separators such as spaces, commas, parentheses, or hyphens . If you've been consistent in your initial example, you won't need to change anything else. If it doesn't get it right the first time, you can always provide a couple more examples so it better understands your intention.
The options button that appears after filling is useful if you detect any anomalies. You can limit yourself to correcting only the problematic cells, keeping the rest of the combinations that were generated correctly , without losing the work already done.
How AI solves it: combining data with Excelmatic
When the merging logic is a bit more sophisticated, using an AI tool can save you a lot of trouble. With Excelmatic, for example, you can easily describe the final format you want instead of building it from manual examples.
Something like this would suffice: “Combine 'First Name', 'Last Name', and 'Age' into a new column called 'Summary'. Use this exact format: First Name Last Name (Age) ”. The tool will interpret the rule, generate the new column, and if the data changes later, you'll just need to run the instruction again.
This approach greatly reduces the risk of silent errors, especially when working with sparse fields (e.g., joining columns A, C, and F) or with large files where it's easy to miss a mistake visually.
Text cleaning and standardization with Flash Fill
Another typical situation where Flash Fill shines is cleaning up messy text strings. It's very common to import lists of names with extra spaces, mixed uppercase and lowercase letters, or strange characters at the beginning or end of the cell. With a couple of well-placed examples, you can force the rest to adopt a uniform format.
For example, you can manually type the name correctly in the first row, capitalizing only the first letter. When you run Flash Fill, Excel copies that style to all subsequent rows , removing extra spaces and normalizing capitalization according to your example.
The same applies to formats like phone numbers. If your data comes in as “678123456” and you want to transform it to “(678) 123-456”, you just need to type a number in the desired format in the adjacent column and run Flash Fill. If the pattern is consistent, you'll get all the phone numbers formatted at once.
However, it's crucial to double-check. When dealing with numbers, Excel can easily convert the result to text , which can cause problems later if you need to sum, filter by numerical values, or perform arithmetic operations with those cells.
If you notice that the number formatting has been lost, you can change the cell type again or rely on specific functions like TEXT() , although this already involves entering the realm of formulas.
AI for intensive data cleaning: advantages of Excelmatic
In more ambitious standardization projects, an AI-based solution shines even brighter. With Excelmatic, you can write very expressive instructions on what you consider "cleaning up" a column: removing double spaces, standardizing capitalization, correcting minor formatting variations, and so on.
An example of a command could be: “Clean the 'Name' column by removing extra spaces and capitalizing only the first letter of each word .” The tool interprets the intent and applies the transformations consistently, even when there are slight deviations in the original data.
You can also ask things like, “Reformat all numbers in the 'Phone' column to the format (XXX) XXX-XXXX,” without worrying about remembering the exact pattern structure or potential exceptions. The AI is usually able to handle edge cases where Flash Fill simply gives up or fills incorrectly.
Advanced transformations: reorder, hide, and generate data
Once you get the hang of Flash Fill, you'll realize it's also useful for more creative operations on your data. While it doesn't reach the level of complexity of a macro or an AI solution, it can get you out of many tight spots.
A typical example is the reorganization of product codes. If the identifier contains prefixes, suffixes, or blocks separated by hyphens , you can create a "clean" or reordered version in a new column, displaying the final code as you want it to appear in the first row. Enabling Flash Fill will replicate this logic for the rest of the code.
Another interesting use is masking sensitive information, such as document or card numbers. It's possible to convert "123456789" into something like "*6789" by displaying only the last four characters in the initial example, and letting the system mask the rest of the digits in all subsequent rows.
It's also very convenient for generating corporate emails from lists of names. You can use a format like "[email protected]", so the tool combines the first initial, the full last name, and the domain you define . Once you display a first example, the rest of the addresses will appear automatically.
This type of transformation works as long as the patterns are reasonably stable. If the list contains a mix of different structures or very specific exceptions , Quickfill may fail, and you'll have to correct it manually or use a more robust strategy.
Typical limitations of Fast Refill
Despite its usefulness, it's important to understand what Flash Fill doesn't do to avoid surprises. The first major limitation is that it generates static results : the cells it fills don't contain formulas or references, only plain values.
This means that if you change the original data later, the filled cells won't update automatically . You'll need to run Flash Fill again or repeat the process if you want the result to reflect the recent changes.
Another common problem involves inconsistent input data . If some rows include a middle name, others don't, and still others have unusual abbreviations, Flash Fill can easily get confused when trying to extract, for example, a surname. It will end up picking up incorrect words or leaving cells empty.
In addition to this, there are hidden characters. It's very common for non-printable spaces, line breaks, or invisible symbols to slip in when copying from the web or other programs. These symbols are unseen by the user but disrupt the pattern Excel is trying to learn. The result can be that the function skips rows or generates unexpected results.
In the realm of numbers, there's another key limitation: Flash Fill treats its output as text unless Excel clearly recognizes it as a number . For analysis, filtering, or subsequent calculations, this can cause some headaches if you were expecting to continue working with those values as pure numerical data.
Why an AI tool like Excelmatic overcomes these barriers
Many of these limitations are reduced when you move to an AI-powered workflow. Excelmatic doesn't just look at superficial patterns; it tries to understand semantic rules like "take the last word," "extract the 5-digit number," or "delete invisible characters."
If your "Full Name" column contains combinations with and without a middle name, you can literally request: "Create a 'Surname' column using the last word from each 'Full Name' cell ." The tool doesn't require identical structures because it focuses on the rule, not the specific form of each example.
Something similar happens with non-printable characters. Many AI systems incorporate internal cleaning steps that detect and remove these unusual symbols before applying transformations , so a cell is less likely to be left empty simply because it contains a hidden line break.
Regarding the static nature of the results, although the output is also generated as values, the ability to quickly relaunch your instructions at any time makes updating a renewed dataset a matter of seconds, instead of having to manually rebuild patterns from scratch.
What to do when Quick Refill isn't working
Sooner or later, you'll encounter a case where Flash Fill simply refuses to recognize the pattern you want to apply. Before you dismiss it completely, there are several steps you can try to get it working again.
The first step is to provide more examples. If Excel can't figure out what you're trying to do with just one row , enter the correct result for two or three consecutive records. The clearer the pattern (and the more consistent the data), the easier it will be for the internal engine.
The second option is to force it manually: select the cell or range where you want to apply the logic and use Ctrl+A (or the command on the Data tab). Sometimes the automatic triggering doesn't fire, but the function will work if you run it manually.
If you still have no luck, check the application settings. Make sure the Auto-Fill option is selected under File > Options > Advanced . If it's disabled, the system won't show suggestions as you type, even though the manual command will still be available.
Finally, you can also start from scratch: clean up the target column, define a much clearer example, and avoid test rows with complicated cases (such as unusual names or incomplete addresses) in your initial samples. If it still doesn't work, your data is most likely too irregular, and you need specific formulas or an AI approach like Excelmatic.
Mastering Flash Fill and understanding the capabilities of AI solutions puts you in a prime position to handle complex spreadsheets. Between Flash Fill's instant automations and the flexibility of tools like Excelmatic , you can go from spending hours cleaning data to letting the software do the heavy lifting while you focus on analyzing information and making decisions.