Flash Fill Trick in Excel 2016

ExcelFlashFill

Here is a very common exercise that you might have or may come across – You have full names of employees in a column and you are expected to extract last names from these full names into a separate column. In versions prior to Excel 2013, you need to use formulas and functions to accomplish this task. Below screenshot shows an example of how this is achieved and the formula used. Cells A3 to A7 have full names (First name followed by last name), cells B3 to B7 have formulas to extract only last name from the full name.

Extracting Last Name from Full Name using Excel Formula

Starting from Excel 2013, you don’t have be an expert in formulas and function to achieve this task, instead you have magical built-in tool called Flash Fill at your rescue. In our example, in cell B3, instead of typing any formula, manually type the last name from the full name contained in cell A3. Since cell A3 has the text – “Balakrishna Sappa”, the last name from this full name is “Sappa”. Now in the next cell, i.e., cell B4, just type first letter of the last name from the corresponding full name. In this example, since cell A4 has the text – “John Short”, the first letter in the last name is “S”. Soon as you type “S” here, you will see Excel suggesting you rest of the letters to complete your work. In our example, Excel is suggesting the complete text as “Sappa”. i.e., Excel is looking at the values present in the cells immediately above the active cell and accordingly suggesting you. This feature in Excel is called “Auto Complete”.

ExcelAutoFill

But evidently, this doesn’t serve our purpose. So, in the above step, instead of accepting Excel’s suggestion, continue typing the second letter in the last name. i.e., you must type “Sh” in cell B4. Now, Excel rightly suggests the last names for all the rest of the full names including the one in the active cell. See the below screenshot. Just hit enter and you are done with the task. No knowledge of formulas and functions needed to complete the task. This feature of Excel is called Flash Fill.

FlashFill1

Note: An important point to remember to make use of Flash Fill is that you must enter the complete first value manually and at least first letter in the second row value. In our example, we have manually entered the complete last name value in cell B3 and first two letters in the last name of the second row. This is because, these are the steps that enable Excel’s Flash Fill to work its intelligence.

The above screenshots are from Excel 2016.

Hope that saved or will save you time by large when you are confronted with this or similar seemingly intimidating tasks..!!

Happy Learning..!! Do leave your comments and hit the likes if you find this useful.

Advertisements

A quick trick to create in-cell charts in Excel

Here I share a tricky way to create in-cell charts in Excel with an example. Below is the data of a few employees and the respective volume of requests completed by them.

Excel_InCellChart

Now enter the below formula in cell C2 and copy it till C8.

=REPT(“n”, B2/50)

 

 

 

 

 

 

Column C now looks as in the below figure:

Excel_InCellChart_3

Now, select the column C and then change the font face to “wingdings” (Home tab –> Font group –> Font face) and font color(Home tab –> Font group –> Font color) to a color of your choice (say blue).

 

 

That’s it..!! the in-cell chart now looks like the below in column C:

Excel_InCellChart_2

Explanation: The REPT function in Excel takes the 1st parameter to it and repeats it for required number of times indicated by the 2nd parameter to it. In our example, REPT takes the character ‘n’ and repeats it. The number of times it is repeated is indicated by the volume divided by a constant number of your choice. I am dividing the volumes by a constant number 50 to let the character ‘n’ appear for a small number of times but still retaining their proportionality to other volumes. This is required to make the chart appear compact.

Hope you enjoyed reading this post..!! Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right or simply click on the FOLLOW button at the bottom right of the window, if it appears in your browser.

Do leave your comments or questions, if any. Happy learning.. Smile