Updated November 2022: Stop getting error messages and slow down your system with our optimization tool. Get it now at this link
- Download and install the repair tool here.
- Let it scan your computer.
- The tool will then repair your computer.
Flash Fill is an amazing tool that has been added to Excel 2013 and will be available in all versions thereafter.
You can use Flash Fill in Microsoft Excel to facilitate data entry. It is an intelligent tool that tries to identify patterns from your data entry and does it for you.
Some simple examples of how Flash Fill could be used could be to find the first name from the full name, find the initials of the last name, format the phone numbers, etc.
How to Use Flash Fill in Excel
The best way to understand Flash Fill is to see it in action. The image contains a new data table with four columns. The first column already contains the full names of the customers (first, second and last). In the second, third and fourth columns, only the first name, middle name and last name need to be entered (so that parts of the clients’ names can be used in the welcome texts of e-mails and form letters such as “Hi Keith” or “Dear Mr Harper”).
Instead of manually entering the first name, middle name, or last name in the appropriate columns (or trying to copy the customer’s full name into column A and then hiding the unnecessary parts in the first name, middle name, and last name columns), you can use Flash Fill to get the job done quickly and efficiently. Here’s how to do it:
- Enter Keith in cell B2 and complete your entry with the down arrow key or Enter.
- When you complete this entry with the down arrow or Enter key on your keyboard, Excel moves the cell pointer to cell B3, where Flash Fill only asks you to type the first letter of the next name to get the image.
- In cell B3, enter only J, the first letter of Jonas, the first name of the second customer.
- Flash Fill immediately performs an AutoFill maneuver suggesting the rest of the second customer’s first name, Jonas, as text to enter in this cell. At the same time, Flash Fill suggests to enter in column B all the remaining first names among the full names in column A.
- Complete the entry of Jonas in cell B3 by clicking the Enter key or by pressing an arrow key.
- As soon as you have finished entering the data in cell B3, the First Name column is complete: Excel enters all the other first names in column B at the same time!
To complete this sample name table by entering the middle name and last name in columns C and D respectively, simply repeat these steps in these columns. You enter the first middle name, Austen, from cell A2 in cell C2 and then type W in cell C3. Complete the entry in cell C3, and the entries for the middle name in that column are completed. Similarly, enter the first name, Harper, from cell A2 in cell D2, and then type S in cell D3. Complete the entry in cell D3, and the surname entries for column D are completed, closing the entire data table.
When Should Flash Fill be Used in Excel?
Here are some examples of situations when Flash Fill should be used
- Concatenation of first and last name using Flash Fill.
- Separation of first name and last name.
- Extraction of initials from full names.
- Formatting phone numbers with Flash Fill.
- Extracting text before the @ symbol from e-mail addresses.
- Extracting names from e-mail addresses.
- Cleaning up data; removing extra spaces or punctuation marks in cells.
- Formatting numbers and text, such as upper and lower case or “correct” formatting.
- Advanced combinations such as reorganizing words and adding punctuation marks.
Limitations of Flash Fill in Excel
Although Flash Fill is an amazing tool, it is important to know its limitations before using it for large and voluminous datasets:
- The result of Flash Fill is static. This means that if you modify the original data set, the Flash Fill result will not change.
- Flash Fill ignores cells containing a space. If you use Flash Fill, make sure that the cells in which the result would occur do not contain spaces. If this is the case, Excel Flash Fill would simply ignore the result.
- In some cases, the Flash Fill can fluctuate in the model identification and give incorrect results. For example, if when extracting middle names, there are names that do not contain a middle name, the Flash Fill would give bad results.
Expert Tip: This repair tool scans the repositories and replaces corrupt or missing files if none of these methods have worked. It works well in most cases where the problem is due to system corruption. This tool will also optimize your system to maximize performance. It can be downloaded by Clicking Here
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.