Microsoft Excel Flash Fill
Table of Contents
![how to use excel flash fill - Mouse Training London Ltd - Mouse Training London Ltd Microsoft excel flash fill](https://www.mousetraining.london/wp-content/uploads/2020/12/how-to-use-excel-flash-fill.png)
Excel – Flash Fill
![flash fill control - Mouse Training London Ltd - Mouse Training London Ltd flash fill control](https://www.mousetraining.london/wp-content/uploads/2020/12/flash-fill-control.png)
Sometimes, half the problem is getting data into Excel so that you can work on it. You run a query on a database and get a mishmash of poorly formatted data. Hundreds of rows full of data you don’t want mixed with the few odd bits that you do. Been there? This is where you need Excel 2013’s Flash Fill to come to your rescue.
To make sense of your data, type in a few row’s as an example of what you need and then click Flash Fill to do the rest of the work for you. Excel applies the lessons learnt from your examples to the rest of the data, with all the columns below filling with the correctly formatted data. No complicated formulas and no macros required. And no retyping!
You can extract simple patterns from your data, for example extract the first name from a full name or you can extract multiple patterns. For example, you can get the date, the business name and the amount from a credit card statement. All by typing a couple of examples for Excel to use as a template.
The Microsoft Excel Flash Fill control is on the Home tab of the ribbon in the Fill group. It is also found in the Data Tools group on the Data tab. And, of course, you can add it to your Quick Access Toolbar if you find yourself using it frequently.
![flash fill in data tab - Mouse Training London Ltd - Mouse Training London Ltd flash fill in data tab](https://www.mousetraining.london/wp-content/uploads/2020/12/flash-fill-in-data-tab-300x243.png)
Flash Fill Example
![join first and last names - Mouse Training London Ltd - Mouse Training London Ltd join first and last names](https://www.mousetraining.london/wp-content/uploads/2020/12/join-first-and-last-names.png)
Here’s a simple exercise; joining first and last names together in the same cell. Type the example “John Smith” into column C and then click Flash Fill to do the rest.
That’s an easy one. If you know Excel formulas you could do that as
=A1&B1 or =CONCATENATE(A1, B1)
![type a few examples - Mouse Training London Ltd - Mouse Training London Ltd type a few examples](https://www.mousetraining.london/wp-content/uploads/2020/12/type-a-few-examples.png)
![parsed data - Mouse Training London Ltd - Mouse Training London Ltd parsed data](https://www.mousetraining.london/wp-content/uploads/2020/12/parsed-data.png)
Begone with your complicated formulas and Text-to-Columns routines. Type-in a couple of examples and let Flash Fill take over. The results are seriously impressive.
Try doing that with a formula. I’ve still got that headache.
![replace the PR with UK - Mouse Training London Ltd - Mouse Training London Ltd replace the PR with UK](https://www.mousetraining.london/wp-content/uploads/2020/12/replace-the-PR-with-UK.png)
![click flash fill to complete - Mouse Training London Ltd - Mouse Training London Ltd click flash fill to complete](https://www.mousetraining.london/wp-content/uploads/2020/12/click-flash-fill-to-complete.png)
Related Courses
View Excel Training Courses – Link