Microsoft Excel 2016

Again, Microsoft Excel looks and functions just as it has previously, and there should be no surprises or learning curve when using Excel 2016. Power users will be pleased to know that Microsoft has listened intently to feedback and feature requests, and has implemented new functions, which we'll explore after this video.


New Excel Functions

Combine text strings using TEXTJOIN and CONCAT

A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting.

The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)

The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)

Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:

The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.

Filter and calculate data with MAXIFS and MINIFS

If you’re familiar with COUNTIFS, SUMIFS and AVERAGEIFS, then MAXIFS and MINIFS don’t need much explanation. The classic MAX and MIN functions calculate the maximum or minimum value in a range, but what if you need to apply conditions to filter your data? This is precisely what MAXIFS and MINIFS allow. You can specify one or more conditions that filter the data before calculating the max or min. The conditions can be applied to adjacent ranges or the range that contains the values. For example, let’s say a retailer has a table containing sales data for all their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales figure for a specified item in stores located in a specified region.

In the example shown below, MINIFS and MAXIFS are used to calculate the min and max sales figures from the table, but it only includes values from the Sales column if the value in the Retailer column is “BigMart,” the value in the Brand column is “Longlast” and the value in the Sales column is greater than zero.



For those who are keen to understand Excel 2016 more deeply, please explore the Excel 2016 training course provided by Microsoft. The course is interactive, and should only take about an hour to complete.


Take the Microsoft Excel 2016 Training Course

Continue to OneDrive for Business