Changes made to the main Office applications from Office 2016 up to May 2019
Part four of a seven-part series of blogs

To prepare the way for the start of a monthly Office update on this website, this long blog lists the changes made to Access, Excel, Outlook, PowerPoint and Word between the release of Office 2016 and May 2019.

  1. Office 365/Office 2019 updates up to May 2019
  2. General new features between Office 2016 and May 2019
  3. New features in Access between 2016 and May 2019
  4. New features in Excel between 2016 and May 2019 (this blog)
  5. New features in Outlook between 2016 and May 2019
  6. New features in PowerPoint between 2016 and May 2019
  7. New features in Word between 2016 and May 2019

Posted by Andy Brown on 10 May 2019

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.

New features in Excel between 2016 and May 2019

There are lots of new goodies in Excel - how many of the following have you picked up on?

New functions

There are a few useful new functions which have been added to Excel.  The TEXTJOIN function allows you to join all of the cell values in a range together:

TextJoin function

The TRUE in this case is telling Excel to ignore any blank cells in the range.

 

The above cell would return the following string of text, since I've used a comma as my delimiter:

African fish,Lesser spotted,Wise

The CONCAT function is just a shorter version of CONCATENATE, to save typing, but it also allows you to refer to cell references:

TEXTJOIN function Text delimiter not possible
Unlike for the TEXTJOIN function ... ... you can't specify a text delimiter.

The MAXIFS and MINIFS function join the SUMIFS, AVERAGEIFS and COUNTIFS stable:

MINIFS example

This formula is showing the minimum number of definite sightings for owls with the word spotted in their names.

Last (but definitely not least) there are two functions to make it easier to test multiple conditions - SWITCH and IFS.  The IFS function allows you to write nested IF functions like this one:

=IF(D5<10,5%,IF(D5<15,10%,15%))*D5

More elegantly:

=IFS(D5<10,5%,D5<15,10%,TRUE,15%)*D5

So the IFS function allows you to write nested IF functions more concisely, but of course you shouldn't really be nesting IF functions in the first place!

The SWITCH function is similar to a CASE WHEN statement in SQL, or the SWITCH function found in DAX, C# and SSRS.  Here's an example:

SWITCH function syntax

The syntax of the new Excel SWITCH function.

Suppose you want to show the ethnicity for someone using the old-fashioned police codes:

Ethnicity codes

You want to put a formula in the cell to show the ethnicity of someone with the given code, using the table.

You could use this formula in the cell selected above:

=SWITCH(B10,"IC1","White - North European","IC2","White - South European","IC9","Unknown","Other")

Again, though, this isn't the best way to do it - a VLOOKUP or INDEX/MATCH function would do the same thing much better!

Precision select

You can now hold down the Ctrl key and click and drag to remove cells from your current selection:

Hold Ctrl and click and drag ... ... to deselect cells.

Two new chart types - map and funnel charts

Excel now has a funnel chart:

Funnel chart

Typically you would use a funnel chart to show progress through a series of stages (for example, to show the number of enquiries lost at each stage of a sales process).

There's also a new map chart, which assumes that you have data in the right format:

Map chart data

This is a BIG assumption. Here I'm using Microsoft's recommended sample data, with simple country names.

 

Here's the map chart icon:

Map chart icon

The tab for creating map charts.

And here's the initial chart the data above would show:

Initial map shown

It all looks very pretty - but see the hint below.

Shape maps are great if you're using internationally recognised country names or US states, but what happens if you're using English parish council boundaries?  Or Swiss cantons?  Or Peruvian city boundaries?  For anything complicated, you'll need to get the underlying shape map data - no small task.

Changes to pivot tables

You can now set your preferred default pivot table layout in Excel.  Choose File => Options and click on this button:

Default layout

Click on the button shown above to change your default layout for pivot tables.

You can then set your preferences:

Pivot table layout dialog box

In researching this blog I think this was the most exciting new discovery I made (most of the rest of the new features I already knew about, but this was new to me!).

You can also now search for the pivot table fields you want:

Searching for fields

Here I've found all the fields containing the letters date.

 

You can make a slicer multi-select on a touch-screen device by default:

Multi-select slicer

Click on the icon shown to make it easier for touch-screen users to select more than one item at a time in a slicer.

 

Finally, if you drag a date field onto a section of a pivot table, Excel will automatically show a time hierarchy:

Time hierarchy Expandable years
The field list created ... ... when you add a date field.

I'm not 100% certain when this feature was added, but I think it was after the first release of Excel 2016.

PowerPivot improvements

PowerPivot has seen a number of behind-the-scenes improvements, and also now lets you save a database diagram as a picture:

Save as picture Database diagram
Choose this option ... ... to save a picture.

Publishing to Power BI 

You can now publish a workbook to the cloud, for use with Power BI:

Publishing to Power BI

Choose this option on the File menu to publish a workbook.

You used to be able to do this before, as it happens, but you needed to install an add-in and it was hideously clunky.

Improvements to Get & Transform (Power Query)

There have been a host of improvements to this much underused and underrated feature 

Get & Transform

The facility in Excel to extract, transform and load data from disparate data sources.

One of the most obvious changes is a new Queries & Connections pane:

Queries & Connections

This new pane combines (as you might expect from the name) both data connections and queries.

 

Here's what this looks like when used in anger:

Queries pane

You can either choose to show queries (as here) or connections.

 

This feature gets lots of love from Microsoft, as it's also part of the fast-changing Power BI reporting tool, so there are too many small improvements to mention.  Perhaps the transform which has changed the most is the Column from Examples tool:

Column from Examples

The artificial intelligence which guesses what new column you want to create is getting quite impressive.

 

Changes not specific to Excel

In addition to the changes listed above, don't forget the following new features which I listed in the general Office page at the start of this blog series:

Change Notes
Scalable Vector Graphics You can insert Scalable Vector Graphics (SVG) icons and other SVG files, and then resize and reformat them without losing resolution.
Microsoft Translator You can select any text (or an entire document) and translate it into a huge array of languages.
Animated graphics Insert a 3-dimensional model, then click and drag on it to make your picture come to life!
Inking You can draw on screen (with your finger or a mouse), format your inkings and change them to shapes or text.
Accessibility There is a huge array of new accessibility options to make it easier to use Office applications with a disability.
Mentions When reviewing a document you can automatically notify other people of comments by mentioning them.
This blog has 0 threads Add post