New Function TEXTJOIN

This Function Distributed with Jan 2016 updates for Excel 2016. Please make sure you have installed latest version of excel and is updated. Otherwise, you can't use this function.

This function joins the texts with any delimiter we define. Syntax for the function is

TEXTJOIN(delimiter,ignore empty,text1,text2.........)

Delimiter - The delimiter between two texts. This can be space, any single charactor or many charactors. Should be enter within double quotes.
Ignore Empty - If we set 'True', Function will ignore any empty cell if referred.
Text1 - First text to be join
Text2, Text3.......... - Other texts to be join.

See below examples

	A	B	C	D
1	Desktop	Computor
2	HP	60	Black	Ink
3	Laptop		Comutor
4	HP	Scaner
5	8	GB	Flash	Drive
6	HP	Inject	Printer

=TEXTJOIN(" ",TRUE,A1:D1) will return Desktop Computer.

Blank cells ignored because ignore empty is True.

Space between two texts because Delimiter is Space

=TEXTJOIN("-",TRUE,B3:E3) will return HP-60-Black-Ink

Here "-" between two texts because it is the delimiter

=TEXTJOIN("-",FALSE,B4:E4) will return Laptop--Comutor-

Here additional "-" because ignore empty is False

If we set ignore empty to true, output will be Laptop-Comutor.

You can dowload example file here.

You can discover more about this function from Microsoft Ofice Help.

Funnel Charts

Funnel Charts are distributed with excel 2016 updates during Jan 2016. Please make sure you are using Excel 2016 & already installed Jan 2016 updates before use Funnel Charts.

This chart type can use to demonstrate data sets those have descending or ascending pattern. See some examples for this type of data sets.

• No of applications during various stages of interview process
• Cost of item during production process
• Grade 12 Mathematics Z Score
• etc

Download File With Sample Data

Follow below steps to generate

1. Sort the data set ascending or descending pattern. 
2. Select the dataset
3. Go to the Insert tab on the ribbon
4. Click on the 'Recomended Charts'
5. Select 'Funnel'
6. Press OK

See on You Tube

Flash Fill - New Feature

Flash Fill is a new feature in excel, which is looking like it automated text to column feature in excel. Flash Fill identifies the patterns on dataset & give us the extracted output. You can download this demonstration file from this link.

Let's consider that we have a list of emails, which are created in following pattern.

email=first name.last name@emailprovider

Email

[email protected]

[email protected]

[email protected]

[email protected]

[email protected]

We are going to extract first name, last name & email provider from this list. In traditional way we can use text to column option in data tab in the ribbon. Using Flash Fill we don't even think to change the tab in the ribbon. Assume that first email in the cell A2. Then we are going to enter first name in column B, Last name in Column C and email provider in column D.

Just start typing from Cell B2. Enter first name of the first email to cell B2 & then enter relevant first name to cell B3. While typing you can see excel shows all first names as flash popup. Just press enter to confirm it. That's it. All first names are extracted.

Try to extract last name and email provider from this list.

See on YouTube