Excel Flash Fill
Flash Fill in Excel is used to automatically extract or combine data from a cell. Flash fill works perfectly when Excel can recognize a pattern. At the end of the post, we will also show when Flash fill does not provide expected results.
Extract numbers
1. Enter the value 200,000 in cell C3 to tell Excel what number to extract from cell B3.
2. On the Data tab, click “Flash Fill” (Keyboard shortcut: CTRL+e).
Result: Excel will automatically fill in the rest of the cells as shown below.
Combining information
Flash fill is going to combine information from two cells to create an email address.
1. Combine two cells’ information and create an email address manually in cell D3. Excel will then do the rest.
2. On the Data tab, click “Flash Fill” (Keyboard shortcut: CTRL+e).
Result: Excel will automatically fill in the rest of the cells as shown below.
Format numbers
Flash fill can also format numbers to create a telephone number.
1. Enter the value (876)-232-1543 in cell D3 to tell Excel what number to extract and format from cell C3.
2. On the Data tab, click “Flash Fill” (Keyboard shortcut: CTRL+e).
Result: Excel will automatically fill in the rest of the cells as shown below.
When Flash Fill does not work
Flash fill will not work if Excel fails to identify a pattern. Take a look at the example below.
1. Enter the value 200 in cell C3 to tell Excel what to extract.
2. Like last time, on the Data tab, click “Flash Fill” (Keyboard shortcut: CTRL+e). You will get the following result.
Note: Flash fill did not correctly fill C5 to C8 with decimal points due to the continuation of the pattern.
3. To solve this issue, help Excel by correcting the C5 value to 153.53. After entering the value, just press the down arrow on your keyboard. Excel will now show the correct information as below.
Finally, another limitation of Flash Fill is that, if you change any information in column B, Excel is not going to update information in column C.
| 3 of 12 finished! Recommending more on the Range: Next Example >> |
| << Previous Example | Skip to Next Chapter 03: Understanding Workbook |