Excel Unplugged

Excel vs. Excel 365

Whatever side of the Cloud vs. On Premise debate you take, it’s a fact that at this point there are two Excel 2016 versions out there. First there’s Excel 2016 with constant updates but mostly those updates are bug fixes and security patches. But then there’s Excel 2016 that comes as part of Office 365 and this one is now Excel 2016 and a half. At this point, those that are Office 365 subscribers get a better version of Excel or to be politically correct a newer version of Excel. Why? It gets functionality updates and at the point that the next version of Excel will be available, those running Excel as part of Office 365 subscription will basically already have one! I’m not going to list all the differences between the two versions, you can get a more or less complete list of those here. I’ll only give you a scenario where two new functions in the 365 version in my humble opinion make a world of difference.

The scenario is simple. Let’s say we wish to join the words in cells A1 to A11…

Excel vs. Excel 365

If you know the Fill/Justify trick or VBA, you’re ok, but otherwise, this is a tedious process of endless clicking. You can either use the CONCATENATE function or write a formula using the & symbol to join strings. It would look like this

=CONCATENATE(A1,” “,A2,” “,A3,” “,A4,” “,A5,” “,A6,” “,A7,” “,A8,” “,A9,” “,A10,” “,A11)

Excel vs. Excel 365

TEXTJOIN

This is a function that we (Excel users) have been waiting for! Monumental upgrade of the CONCATENATE function. It not only combines the chosen strings, but allows you to select an entire area of values to combine. It then let’s you choose, what should be the delimiter (so there’s no need to input the delimiter n times to join n+1 strings, you only need to give it once!) and whether empty cells are to be included.

The syntax is

=TEXTJOIN(delimiter,include blanks,range1,range2,…)

and here’s  TEXTJOIN applied to our scenario

=TEXTJOIN(” “,TRUE,A1:A11)

Excel vs. Excel 365

And just to make the difference clearer, I used the FORMULATEXT and LEN functions in Excel

Excel vs. Excel 365

That’s 88 characters vs. 26… That’s less than one third of characters and less than one third of time… The main difference is derived from the ability to select the entire range instead of one cell at the time.

CONCAT

CONCAT function is less glamorous than TEXTJOIN, but it can still be considered a solid upgrade of the CONCATENATE function but with one big limitation.

Whereas it is great that you can select the whole area instead of picking out individual cells, but there’s no argument that would let you define a delimiter. Nevertheless, it’s still nine clicks less than the CONCATENATE function.

Conclusion

If anyone was wondering, if a file with these new functions was to be opened in a non-Office 365 Excel, those functions would translate into

Excel vs. Excel 365

would still yield results but could not be edited…

In my opinion these two functions are very helpful and every data professional would benefit from using them. If you peeked at all the differences, you are more or less aware that there are two versions of Excel out there. Microsoft has succeeded in making the version that comes as part of Office 365 subscription very appealing to those that intend to use Excel on any level.