The tutorial explains the basics of the Flash Fill functionality and provides examples of using Flash Fill in Excel.
Flash Fill is one of the most amazing features of Excel. It grabs a tedious task that would take hours to be performed manually and executes it automatically in a flash (hence the name). And it does so quickly and simply without you having to do a thing, but only provide an example of what you want.
The following tips and tricks reference functionality in Excel 2016, however most should be applicable to earlier versions of Excel for Mac and PC. Excel 2013 will present Flash Fill when it recognizes a pattern and works best when your. In any Microsoft Office 2013 program, you can right click on any tool in the toolbar to.
What is Flash Fill in Excel?
Excel Flash Fill is a special tool that analyzes the information you are entering and automatically fills data when it identifies a pattern.
The Flash Fill feature was introduced in Excel 2013 and is available in all later versions of Excel 2016, Excel 2019, and Excel for Office 365.
Started in December 2009 as an attempt of Sumit Gulwani, a senior researcher at Microsoft, to help a businesswoman he accidentally met at the airport with her merging challenge, a few years later it has evolved into a powerful ability to automate many Excel chores.
Flash Fill easily copes with dozens of different tasks that otherwise would require complex formulas or even VBA code such as splitting and combining text strings, cleaning data and correcting inconsistencies, formatting text and numbers, converting dates to the desired format, and a lot more.
Each time, Flash Fill combines millions of small programs that might accomplish the task, then sorts those code snippets using machine-learning techniques and finds the one that suits best for the job. All this is done in milliseconds in the background, and the user sees the results almost immediately!
Where is Flash Fill in Excel?
In Excel 2019, Excel 2016, and Excel 2013, the Flash Fill tool resides on the Data tab, in the Data tools group:
Excel Flash Fill shortcut
Those of you who prefer working from a keyboard most of the time, can run Flash Fill with this key combination: Ctrl + E
How to use Flash Fill in Excel
Usually Flash Fill starts automatically, and you only need to provide a pattern. Here's how:
- Insert a new column adjacent to the column with your source data.
- In the first cell of a newly added column, type the desired value.
- Start typing in the next cell, and if Excel senses a pattern, it will show a preview of data to be auto-filled in the below cells.
- Press the Enter key to accept the preview. Done!
- If you are unhappy with the Flash Fill results, you can undo them by pressing Ctrl + Z or via the Flash Fill options menu.
- If Flash Fill does not start automatically, try these simple troubleshooting techniques.
How to Flash Fill in Excel with a button click or shortcut
In most situations, Flash Fill kicks in automatically as soon as Excel establishes a pattern in the data you are entering. If a preview does not show up, you can activate Flash Fill manually in this way:
- Fill in the first cell and press Enter.
- Click the Flash Fill button on the Data tab or press the Ctrl + E shortcut.
Excel Flash Fill options
When using Flash Fill in Excel to automate data entry, the Flash Fill Options button appears near the auto-filled cells. Clicking this button opens the menu that lets you do the following:
- Undo the Flash Fill results.
- Select blank cells that Excel has failed to populate.
- Select the changed cells, for example, to format them all at once.
Excel Flash Fill examples
As already mentioned, Flash Fill is a very versatile tool. The below examples demonstrate some of its capabilities, but there is much more to it!
Extract text from cell (split columns)
Before Flash Fill came into existence, splitting the contents of one cell into several cells required the use of the Text to Columns feature or Excel Text functions. With Flash Fill, you can get the results instantly without intricate text manipulations.
Supposing you have a column of addresses and you want to extract zip codes into a separate column. Indicate your goal by typing the zip code in the first cell. As soon as Excel understands what you are trying to do, it fills in all the rows below the example with the extracted zip codes. You only need to hit Enter to accept them all.
Formulas to split cells and extract text:
- Extract substring - formulas to extract text of a specific length or get a substring before or after a given character.
- Extract number from string - formulas to extract numbers from alphanumeric strings.
- Split names in Excel - formulas to extract first, last and middle names.
Extracting and splitting tools:
- Text Toolkit for Excel - 25 tools to perform various text manipulations including splitting a cell by any character such as comma, space, line break; extracting text and numbers.
- Split Names tool - fast and easy way to separate names in Excel.
Combine data from several cells (merge columns)
If you have an opposite task to perform, no problem, Flash Fill can concatenate cells too. Moreover, it can separate the combined values with a space, comma, semicolon or any other character - you just need to show Excel the required punctuation in the first cell:
This method is especially useful for combining various name parts into a single cell as shown in How to merge first and last name with Flash Fill.
Formulas to join cell values:
- CONCATENATE function in Excel - formulas to combine text strings, cells and columns.
Merging tools:
- Merge Tables Wizard - quick way to combine two tables by common columns.
- Merge Duplicates Wizard - Combine similar rows into one by key columns.
Clean data
If some data entries in your worksheet begin with a leading space, Flash Fill can get rid of them in a blink. Type the first value without a preceding space, and all extra spaces in other cells are gone too:
Formulas to clean data:
- Excel TRIM function - formulas to remove excess spaces in Excel.
Data cleaning tools:
- Text Toolkit for Excel - trim all leading, trailing and in-between spaces but a single space character between words.
Format text, numbers and dates
Quite often the data in your spreadsheets is formatted in one way while you want it in another. Just start typing the values exactly as you want them to appear, and Flash Fill will do the rest.
Perhaps you have a column of first and last names in lowercase. You wish the last and first names to be in proper case, separated with a comma. A piece of cake for Flash Fill :)
Maybe you are working with a column of numbers that need to be formatted as phone numbers. The task can be accomplished by using a predefined Special format or creating a custom number format. Or you can do it an easy way with Flash Fill:
To re-format the dates to your liking, you can apply the corresponding Date format or type a properly formatted date into the first cell. Oops, no suggestions have appeared… What if we press the Flash Fill shortcut (Ctrl + E) or click its button on the ribbon? Yep, it works beautifully!
Replace part of cell contents
Replacing part of a string with some other text is a very common operation in Excel, which Flash Fill can also automate.
Let's say, you have a column of social security numbers and you want to censor this sensitive information by replacing the last 4 digits with XXXX.
To have it done, either use the REPLACE function or type the desired value in the first cell and let Flash Fill auto fill the remaining cells:
Advanced combinations
Flash Fill in Excel can accomplish not only straightforward tasks like demonstrated in the above examples but also perform more sophisticated data re-arrangements.
As an example, let's combine different pieces of information from 3 columns and add a few custom characters to the result.
Supposing, you have first names in column A, last names in column B, and domain names in column C. Based on this information, you want to generate email addresses in this format: initial.surname@domain.com.
For experienced Excel users, there is no problem to extract the initial with the LEFT function, convert all the characters to lowercase with the LOWER function and concatenate all the pieces by using the concatenation operator:
=LOWER(LEFT(B2,1))&'.'&LOWER(A2)&'@'&LOWER(C2)&'.com'
But can Excel Flash Fill create these email addresses for us automatically? Sure thing!
Excel Flash Fill limitations and caveats
Flash Fill is an awesome tool, but it does have a couple of limitations that you should be aware of before you start using this feature on your real data sets.
1. Flash Fill results do not update automatically
Unlike formulas, the results of Flash Fill are static. If you make any changes to the original data, they won't be reflected in the Flash Fill results.
2. May fail to identify a pattern
In some situations, especially when your original data are arranged or formatted differently, Flash Fill may stumble and produce incorrect results.
For example, if you use Flash Fill to extract middle names from the list where some entries contain only the First and Last names, the results for those cells will be wrong. So, it is wise to always review the Flash Fill output.
3. Ignores cells with non-printable characters
If some of the cells to be auto-filled contain spaces or other non-printable characters, Flash Fill will skip such cells.
So, if any of the resulting cells are blank, clear those cells (Home tab >Formats group > Clear > Clear All) and run Flash Fill again.
4. May convert numbers to strings
When using Flash Fill for reformatting numbers, please be aware that it may convert your numbers to alphanumeric strings. If you prefer to keep the numbers, use the capabilities of Excel format that changes only the visual representation, but not the underlying values.
How to turn Flash Fill on and off
Flash Fill in Excel is turned on by default. If you do not want any suggestions or automatic changes in your worksheets, you can disable Flash Fill in this way:
- In your Excel, go to File> Options.
- On the left panel, click Advanced.
- Under Editing options, clear the Automatically Flash Fill box.
- Click OK to save the changes.
To re-enable Flash Fill, simply select this box again.
Excel Flash Fill not working
In most cases, Flash Fill works without a hitch. When it falters, the below error may show up, and the following tips will help you get it fixed.
1. Provide more examples
Flash Fill learns by example. If it is unable to recognize a pattern in your data, fill in a couple more cells manually, so that Excel could try out different patterns and find the one best suited for your needs.
2. Force it to run
If Flash Fill suggestions do not appear automatically as you type, try to run it manually.
3. Make sure Flash Fill is enabled
If it does not start either automatically or manually, check if the Flash Fill functionality is turned on in your Excel.
4. Flash Fill error persists
If none of the above suggestions has worked and Excel Flash Fill still throws an error, there is nothing else you can do but enter the data manually or with formulas.
That's how you use Flash Fill in Excel. I thank you for reading and hope to see you on our blog next week!
You may also be interested in:
Just as you can use Excel 2016’s AutoFill to fill out a series with increments different from one unit, you can also get it to fill out custom lists of your own design.
For example, suppose that you often have to enter a standard series of city locations as the column or row headings in new spreadsheets that you build. Instead of copying the list of cities from one workbook to another, you can create a custom list containing all the cities in the order in which they normally appear in your spreadsheets.
After you create a custom list in Excel, you can then enter all or part of the entries in the series simply by entering the first item in a cell and then using the Fill handle to extend out the series either down a column or across a row.
To create a custom series, you can either enter the list of entries in the custom series in successive cells of a worksheet before you open the Custom Lists dialog box, or you can type the sequence of entries for the custom series in the List Entries list box located on the right side of the Custom Lists tab in this dialog box, as shown.
If you already have the data series for your custom list entered in a range of cells somewhere in a worksheet, follow these steps to create the custom list:
Click the cell with the first entry in the custom series and then drag the mouse or Touch pointer through the range until all the cells with entries are selected.
The expanded cell cursor should now include all the cells with entries for the custom list.
Select File→Options→Advanced (Alt+FTA) and then scroll down and click the Edit Custom Lists button located in the General section.
The Custom Lists dialog box opens with its Custom Lists tab, where you now should check the accuracy of the cell range listed in the Import List from Cells text box. (The range in this box lists the first cell and last cell in the current selected range separated by a colon — you can ignore the dollar signs following each part of the cell address.) To check that the cell range listed in the Import List from Cells text box includes all the entries for the custom list, click the Collapse Dialog Box button, located to the right of the Import List from Cells text box. When you click this button, Excel collapses the Custom Lists dialog box down to the Import List from Cells text box and puts a marquee (the so-called marching ants) around the cell range.
If this marquee includes all the entries for your custom list, you can expand the Custom Lists dialog box by clicking the Expand Dialog box button (which replaces the Collapse Dialog Box button) and proceed to Step 3. If this marquee doesn’t include all the entries, click the cell with the first entry and then drag through until all the other cells are enclosed in the marquee. Then, click the Expand Dialog box button and go to Step 3.
Click the Import button to add the entries in the selected cell range to the List Entries box on the right and to the Custom Lists box on the left side of the Custom Lists tab.
As soon as you click the Import button, Excel adds the data entries in the selected cell range to both the List Entries and the Custom Lists boxes.
X force Industry Specific Features:Furthermore, now includes advance industry-specific features in its new tool kit. Fab 3000 v6 keygen crack mac free.
Select the OK button twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box.
If you don’t have the entries for your custom list entered anywhere in the worksheet, you have to follow the second and third steps listed previously and then take these three additional steps instead:
Click the List Entries box and then type each of the entries for the custom list in the order in which they are to be entered in successive cells of a worksheet.
Press the Enter key after typing each entry for the custom list so that each entry appears on its own line in the List Entries box, or separate each entry with a comma.
Click the Add button to add the entries that you’ve typed into the List Entries box on the right to the Custom Lists box, located on the left side of the Custom Lists tab.
Note that when Excel adds the custom list that you just typed to the Custom Lists box, it automatically adds commas between each entry in the list — even if you pressed the Enter key after making each entry. It also automatically separates each entry on a separate line in the List Entries box — even if you separated them with commas instead of carriage returns.
Click the OK button twice to close both the Custom Lists box and Excel Options dialog box.
After you’ve created a custom list by using one of these two methods, you can fill in the entire data series by entering the first entry of the list in a cell and then dragging the Fill handle to fill in the rest of the entries. If you ever decide that you no longer need a custom list that you’ve created, you can delete it by clicking the list in the Custom Lists box in the Custom Lists dialog box and then clicking the Delete button. Excel then displays an alert box indicating that the list will be permanently deleted when you click OK. Note that you can’t delete any of the built-in lists that appear in this list box when you first open the Custom Lists dialog box.
Keep in mind that you can also fill in any part of the series by simply entering any one of the entries in the custom list and then dragging the Fill handle in the appropriate direction (down and to the right to enter succeeding entries in the list or up and to the left to enter preceding entries).