WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 527 reviews for our classroom and online training
What's new in Microsoft Office 2016 (including Excel 2016 changes)
Part three of a five-part series of blogs

The latest incarnation of Microsoft Office (2016) should be released on 22nd September 2015 (Microsoft are always a bit ahead of themselves with versions). This blog gives a detailed guide to what's new, the biggest changes being in Excel and Outlook.

  1. The Wise Owl take on Microsoft Office 2016
  2. General changes for Microsoft Office 2016
  3. What's new in Excel 2016 (this blog)
  4. What's new in Outlook 2016
  5. What's new in Access, PowerPoint, Project, Visio and Word

Posted by Andy Brown on 07 September 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What's new in Excel 2016

This page summarises the main new features specific to Excel in Office 2016.

New Functions

Excel has several new functions:

Function What it does
IFS Makes it easier to write nested IF functions
SWITCH Provides an alternative to VLOOKUP for making choices
TEXTJOIN Joins all of the values in a range together, using a delimiter
CONCAT Replaces the CONCATENATE function

I've written a separate blog explaining these functions in more detail.

New chart types

There are 5 (!) new chart types:

5 new chart types

The five new chart types: doesn't it make you want to see what a Sunburst or Box & Whisker chart looks like?


Treemap charts show hierarchical data in a really nice way (like an infographic):

A treemap chart

A clever way to show that Mammals predominate for this data set.

A sunburst chart does the same thing (it's a bit like a stacked doughnut, although that sounds more like a Denny's dessert option):

A sunburst chart

The sunburst chart is another effective way to show which species has most transactions.


Out of the other charts, a waterfall chart should mean something to you if you work in finance, and histogram and box and whisker charts will make sense to statisticians (there's also apparently a pareto chart, although I can't find that).

Date/time fields in pivot tables

When you add a date field to a pivot table, you automatically get the option to summarise by month, quarter or year:

Summarising by date

There's just one date field in the underlying table of data for this pivot table, but Excel has added various summary fields for it.


Drill-down in pivot charts

A seriously clever feature: you can now drill-down in a pivot chart.  Here's an example:


You want to see the make-up of Mammal sales, so you right-click and choose this option.

You can then choose how you want to drill-down:

Drill-down part 2

We'll choose to drill down by product.


Here's what you get if you choose the option above:

Drilling down by product

Excel expands one bar to show all the "sub-bars".

Like I said: very clever!

Power Query is built in

Excel 2013 includes a fantastic add-in called Power Query, which allows you to extract, transform and load data from virtually any data source.  The problem is that no one knows about it!

However, Power Query is now built into Excel 2016.  For example: 

Inserting from web

Querying data from a website will automatically load Power Query.

PowerPivot user interface and other PowerPivot changes

This has had a big makeover, as any regular user of PowerPivot will instantly recognise:

PowerPivot makeover

Whether the new look is better is debatable ...


While we're talking about PowerPivot, calculated fields are now known as measures again, thank goodness:

Measures adding

Phew: we can stop telling people that calculated fields are measures by any other name!


Sadly, it looks like the editor for calculated fields - sorry, measures - is just the same ...

It's also much easier to enable all of the Power BI tools in the Excel Options dialog box (Advanced tab):

Enabling Power BI

No more messing about with COM add-ins!

One more nice feature: when you rename a table or field in PowerPivot, the field is retained in the Excel pivot table field list (previously the field was deleted and you had to add it back in).


Time now to look at the single application which has changed most in this new release of Office: Outlook 2016.

This blog has 0 threads Add post