Flash Fill in Excel 13

Flash a-ah

Savior of the Universe

Flash a-ah

He’ll save every one of us

The lyrics above are from the soundtrack Flash Gordon composed by Queen (awesome soundtrack) and is the first thing that came to me when I saw the Flash Fill function.

Picture this; We have all this data as shown in the image below, which is not in a workable format. The data should have been spread across columns but instead it is all in one column. So what do we do?

Do we use Left and Right formulas or any other sort of functions in order to reformat and rearrange the data? No!!!

This is where Flash Fill comes in. The new feature in Excel 13. This new feature is here to help us make most of the common task easier to accomplish.

Mixed Data

Let us see how Flash Fill works; In cell A2 we have all this mixed data “Philippos, Kassandra, 12400, Instructor” as show in the image below. In cell B2, B3, B4, B5, B6 and B7 under the column Name we want to extract the names of the persons.

Filled In Information

 

First of all we must type in cell B2 the name of the person that we want to, for my example “Philippos” and then press the Enter button to move on to cell B3. Then on the Home tab we navigate to the area of the ribbon named Editing and select the Fill command. Once the drop down menu of the Fill command is shown, we navigate to the end and select the command Flash Fill as shown in the image below.

Flash Fill Drop Down Menu

Once the Flash Fill command is selected all the names are filled in the appropriate cells as shown in the image below.

Filled In Information

Now in the cell C2, I have typed the name of the location that I want to extract and pressed the Enter button in order to move down to cell C3. Now I want by using Flash Fill, all the locations for the others to be extracted. A quick way to use Flash Fill is by pressing the key combinations Ctrl+E on our keyboard.

Flash Fill

Once again all the locations are extracted in their corresponding cells as shown in the image below.

Ctrl+E

The same procedure has been followed in order to fill in the rest of the information for the columns Amount and Description. I typed in cell D2 the first amount that I want to extract and pressed the Enter button to move on to cell D3 and then pressed the Ctrl+E keys and all the amounts were filled in automatically in their appropriate cells. The same was followed for the column Amount in their corresponding cells.

Flash Fill Drop Down Menu

Every time we use Flash Fill, at the right of the area the Flash Fill options button appears as shown in the image below. If we press the arrow at the right of it a shortcut menu appears with the options shown in the image below.

Flash Fill Shortcut Menu

You can also check out the video below in order to see how the Flash Fill command works in Excel 13 with many examples.

Flash Fill in Excel 13

About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on December 7, 2012, in Excel English 2013, Microsoft Office 2013 English and tagged , , , , , , , , . Bookmark the permalink. Comments Off on Flash Fill in Excel 13.

Comments are closed.