BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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 |
| MAXIFS, MINIFS | Join the SUMIFS, AVERAGESIFS and COUNTIFS family |
I've written a separate blog explaining these functions in more detail.
New chart types
There are 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 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):
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:
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:
We'll choose to drill down by product.
Here's what you get if you choose the option above:
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:
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:
Whether the new look is better is debatable ...
While we're talking about PowerPivot, calculated fields are now known as measures again, thank goodness:
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):
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.