Office 365 Updates

With the release of Excel 2016, Microsoft began to release regular updates via Office 365. 

Users of Excel versions targeted at businesses default the semi-annual update channel meaning that each release remains stable for six months.  These updates can make significant changes to Excel, so we update our books and e-books every six months to include changes introduced in each semi-annual update.

Excel 2016 perpetual licence users (the “pay once and use forever license”) do not receive these updates so we carefully explain the differences wherever necessary in our courses.  If you pay a subscription for your Office license you’ll enjoy the new features available in each semi-annual release as described below:

This Version Has Not Yet Been Released

About this release

The January 2019 semi-annual Excel version 1808 release includes features originally made available in monthly updates 1804, 1805, 1806 and 1807. The next semi-annual release (expected to be 1902) will arrive in July 2019.

It seems logical that version 1808 will be very similar to Office 2019 (scheduled for first release in October 2018). Microsoft have recently stated that it will not be exactly the same as some Excel 365 features will not be included in Excel 2019:

“Office 2019 will include some, but not all, features that have been released through Office 365 since September 2015 (when Office 2016 was released).”

Power Pivot – a major new feature in the Version 1808 Semi-Annual release

On May 22, 2018 Microsoft’s Ashvini Sharma (PM Manager Excel) announced:

“A decision has been made to have Power Pivot be available in all Windows editions (“SKUs”) of Excel! This means the Business, Home, Standard, etc… all of these will have Power Pivot. As your comments have indicated, Power Pivot has moved from targeting a specialized audience to generally useful capability for working with data in spreadsheets. As such, it makes sense to make this as broadly available as possible.” We have started rolling this change out with the April Current Channel updates (Builds 9330 and beyond). I’m also happy to announce that these SKU changes will be reflected on the upcoming 2019 version as well.”

Power Pivot will probably not be available in most Excel 2016 (perpetual license) versions

We have a copy of Microsoft Office Home and Student 2016 (perpetual license) running Build 10730 and (in Sept 2018) it wasn’t possible to enable the Power Pivot plug in. From this it seems logical that Microsoft’s statement refers only to present and future versions of Excel and that Excel 2016 (perpetual license version) is regarded as being an older version that will not be enhanced with the new feature. This is entirely consistent with Microsoft’s policy of restricting new features to the subscription version of Excel 2016 (Excel 365).

Other new features in January 2019 semi-annual Excel version 1808

* Features marked with an asterisk are announced but may not actually make it into semi-annual version 1808 (or Excel 2019).  If not they are slated for release in the near future.

  • Stock Quote Data Type*: The concept of data types will be familiar to students who have completed the OLAP sessions in the Expert Skills book. The Stock Quote data type is rather different to a traditional data type as it provides a set of properties that (for a given stock) allow you to retrieve the latest stock price, change in price, and more. Microsoft refer to the new data types as: AI-infused data types. We are adding specific lessons to the Excel 2019/Excel 365 book to fully explain how you can make use of this new data type.
  • Geography Data Type*: This is the second of the new data types introduced in this version. We are adding specific lessons to the Excel 2019/Excel 365 book to fully explain how you can make use of this new data type.
  • Data Insights: With the help of Insights in Excel, you can get fast, automated, insightful analysis about your data. Simply click a cell in a data range, and then click the Insights button on the Insert Tab. Insights in Excel will analyse your data and return interesting visuals about it in a task pane.
  • Chat with co-authors while you edit: Collaborate more effectively by chatting with your co-authors without ever leaving Excel.
  • Improved cell and formula bar editing: You can now use CTRL+A to select text in a cell or the formula bar. There’s also improved support for emojis and other complex characters.
  • Improved Scalable Vector Graphic (SVG) support: You can insert SVG’s that have filters applied to them. This is a general Office feature and not Excel specific.
  • Translation: Translate words, phrases, or whole documents to another language with Microsoft Translator.
  • One-click fixes for accessibility issues: The Accessibility Checker is better than ever with updated support for international standards and handy recommendations to make your documents more accessible.

About this release

This is the second semi-annual Excel release and includes features originally made available in monthly updates 1709, 1710, 1711, 1712, 1801 and 1802. The next semi-annual release (1809) will arrive in January 2019 and will include all updates previously published to the monthly update channel in versions: 1804, 1805, 1806, 1807, and 1808.

It has been incorrectly speculated (though it seems logical) that version 1803 will be identical to a new perpetual licenced version of Office named Office 2019.  Microsoft have recently stated that this won’t be the case as some Excel 365 features will not be included in Excel 2019.

Microsoft have stated in a support document:

“Office 2019 will include some, but not all, features that have been released through Office 365 since September 2015 (when Office 2016 was released).”

Though it is not yet clear which version Office 2019 will be based upon, the technology preview suggests that it may be version 1808 (Version 1809, that will be one update ahead of the potential Office 2019 release, will be released to the semi-annual channel in January 2019).

  • Display empty cells, null (#N/A) values, and hidden worksheet data in a chart.
    By default, data that is hidden in rows and columns in the worksheet is not displayed in a chart, and empty cells or null values are displayed as gaps. For most chart types, you can display the hidden data in a chart.For line, scatter, and radar chart types, you can also change the way that empty cells, and cells that display the #N/A error are displayed in the chart. Instead of displaying empty cells as gaps, you can display empty cells as zero values, or you can span the gaps with a line. For #N/A values, you can choose to display those as an empty cell or connect data points with a line.
  • Digital Pencil.
    Support for digital pencils added. You can now write or sketch out ideas with the new pencil texture. Simply tilt to do shading with supported digital pens.
  • LinkedIn Integration.
    Following Microsoft’s acquisition of LinkedIn, they have integrated LinkedIn profiles with Office. These will only appear if your Microsoft account has been linked to your LinkedIn account, and only in areas where user information is displayed (such as when sharing files between multiple users).
  • LinkedIn features setting.
    Go to File > Options > General to control whether LinkedIn features are shown in your Office applications.
  • Quickly access your sites and groups.This feature refers to Microsoft SharePoint; if you are not using SharePoint it will not affect your copy of Excel. If you are using SharePoint, new options will appear in the Open menu that enable you to access items from SharePoint ‘sites’ and ‘groups’.
  • Convert SVG icons to shapes.
    Support added for Scalable Vector Graphics files (SVG). SVG files can now be inserted into Excel workbooks just like any other image file. You are also able to transform all SVG pictures and icons into Office shapes so you can change their color, size, or texture.
  • Deselect cells.
    Sometimes when you’re selecting multiple cells or ranges in Excel, you accidentally select one or more that you didn’t intend. You can deselect any cells within the selected range with the Deselect Tool. Pressing the Ctrl key, you can click, or click-and-drag to deselect any cells or ranges within a selection. If you need to reselect any of those cells, continue holding the Ctrl key and reselect those cells (for Mac, use the Cmd key).
  • 3D models.
    Use 3D to increase the visual and creative impact of your workbooks. Easily insert a 3D model, then you can rotate it through 360 degrees.
  • Microsoft Translator. 
    A new Translate option has been added to the Review tab on the Ribbon. This allows you to quickly translate words, phrases or sentences between languages using Microsoft Translator..
  • New ink effects. 
    Express your ideas with flair using metallic pens and ink effects like rainbow, galaxy, lava, ocean, gold, silver and more.
  • Sharing files UI.
    For OneDrive for Business or SharePoint files, clicking the Share button in the upper right-hand corner of the ribbon or going to File > Share launches a simplified and improved Share dialog. For new or locally-saved files, the UI allows users to easily upload their files to OneDrive to start collaborating.
  • Block dangerous extensions.
    Extensions that are considered to be high risk, and are embedded as OLE package objects, are blocked, by default, from activating. For example, .exe, .vbs, and .js. Learn more
  • Safe links.
    When a user clicks on a link, Office 365 Advanced Threat Protection (ATP) inspects the link to see if it’s malicious. If the link is deemed malicious, the user is redirected to a warning page instead of the original target URL.
  • Helpful sounds improve accessibility. 
    Turn on audio cues to guide you as you work. Find it in File > Options > Ease of Access. No add-in needed. Learn more
  • File locations by account. 
    When opening or saving a file, the list of places is organized by the account associated with them.
  • Pen customization. 
    Choose a personal set of pens and highlighters for inking. Your customized set is available on all your Windows PCs.

Released: January 2018, Version 1708

About this release

This is the first semi-annual Excel release and includes features originally made available in monthly updates 1706 and 1707.  From this point onward, a new semi-annual version of Excel will be released in January and July each year.  This version is fully supported in the current  edition of our books/e-books.

The next semi-annual release will be on July 10th 2018 and will include all updates previously published to the monthly update channel in versions: 1709, 1710, 1711, 1712, 1801 and 1802.  It has been speculated (and seems logical) that this version will also be identical to a new perpetual licenced version of Office named Office 2019.

  • 3D Models: A new option in the Illustrations Ribbon group that allows rotatable 3d models to be inserted.
  • “Add Column from Examples” enhancements: Supports more Date/Time, Math, and Index Column transformations.
  • Performance improvements: Excel opens complex workbooks with multiple sheets faster, so you can crunch formulas with large ranges, filter lots of rows, or copy and paste quicker.
  • Collaborative editing: Work with others at the same time in your workbook.
  • Insert online pictures: New landing page for selecting images and attribution information is automatically inserted with the image.
  • Azure Data Lake Store connector: Users can now import data from Azure Data Lake Store.
  • “Add column from Examples” enhancements: Supports suggestions, more Date/Time operations, and additional transformations.
  • Data tab: Ribbon buttons on the Data tab have been rearranged into two new groups: Get & Transform Data and Queries & Connections.
  • Share queries: Export any query definition into an Office Database Connection (ODC) file, and then share it across workbooks or with others.
  • Load data: Load data from a query directly into PivotTables or PivotCharts without having to save the data into the Data Model.
  • Shared file activity: Choose the Activity button in the upper right corner of the file to see when a file shared in OneDrive for Business or SharePoint was shared, edited, renamed, or restored.
  • Safe links: When a user clicks on a link, Office 365 Advanced Threat Protection (ATP) inspects the link to see if it’s malicious. If the link is deemed malicious, the user is redirected to a warning page instead of the original target URL. 
  • Enhanced data import functionality: Easily import and shape data from various sources. Manage workbook queries and connections with the Queries & Connection side pane, and share queries with others via ODC files. 
  • Changes in shared files: View who has made changes in shared workbooks, and restore earlier versions. 
  • Lasso Select with a pen button: Use supported digital pen buttons to Lasso Select ink without visiting the ribbon.
  • Autosave when using OneDrive:  If you save an Excel file to a OneDrive it will automatically save your changes every few seconds as you work.  there is no longer any need to explicitly save your work (when a file resides upon a OneDrive).

Released to Current Channel on Jun 7, 2017

Retain Copy fix

Copy and  Paste has been improved.  Previously if you copied and then did other tasks (like typing or inserting cells) the copy was lost and the clipboard cleared.  This has now been fixed.

Data tab re-designed

The data tab now has two new groups: Get & Transform Data and Queries & Connections.

Get & Transform Queries can now be shared

You can now export a query into an Office Database Connection (ODC) file and then share it across workbooks or with others.

Get & Transform Load Data command

Load Data can now send query data directly into Pivot Tables or Pivot Charts without having to first save the data into the Data Model.

Get & Transform improvement

In the Query Editor, if you create a new column by providing sample values,  as you type, Excel detects the required transformations and shows a preview of the new column.

Queries can now be shared

You can now export a query into an Office Database Connection (ODC) file and then share it across workbooks or with others.

Released to Current Channel on May 18, 2017

Recent hyperlinks

The hyperlink interface has been overhauled, adding a new Links drop-down to the Insert > Links area on the Ribbon. This contains a repository of recently-used hyperlinks, allowing you to quickly apply hyperlinks that you use often.

Default PivotTable layout

The default layout of PivotTables can now be set using the File > Options menu, making it easier to create standardized PivotTables without needing to reconfigure them every time.

Get & Transform changes

The Data Ribbon tab has been completely rearranged in this version, completely removing the Get External Data and Connections groups and adding new Get & Transform Data and Queries & Connections groups. This removes the old system of accessing external data completely and moves the Get & Transform tool to the forefront as Excel’s means of accessing external data sources.

This version also includes some new improvements to Get & Transform:

  • Columns can now be created by example
  • Table columns can now be split into rows
  • SAP HANA parameters and grouping have been improved
  • Queries can now be saved as ODC files

Add-in deployment

Excel add-ins can now be deployed to entire user groups via Office 365. This allows you to ensure that all of your users have access to the same add-ins.

Quick Access Toolbar improvements

This version allows the Superscript and Subscript options to be added to the Quick Access Toolbar.

Released to Current Channel on April 21, 2017

Shared file activity

A new Activity tab was introduced in January 2017. This version adds additional information to the Activity tab, showing when a shared file was shared, edited, renamed or restored.

Dubai font

The new Dubai font family has been added to Excel in this version. Dubai supports major languages that use Arabic script.

Link security

Excel hyperlinks are now inspected to determine if they are malicious. Users are warned if Excel detects that a hyperlink may go to a malicious site.

Get & Transform improvements

This version includes several new improvements to Get & Transform, including:

  • Automatic detection of delimiters when splitting columns using the Query Editor
  • When connecting to DB2 data sources you can now specify the package collection to connect to

Released to Current Channel on March 9, 2017

Background removal

This version adds a new Remove Background tool, available from the Picture Tools tab when a picture is selected. This tool allows backgrounds to be easily removed from pictures using a free-hand drawing tool.

Get & Transform improvements

This version includes several improvements to Get & Transform, including:

  • ‘Select Related Tables’ option available for ODBC and OLEDB data sources.
  • Multiple files can now be combined via the folder Data Preview dialog
  • A new context menu option has been added to the Query Editor window, allowing new steps to be inserted into existing queries

Released to Current Channel on February 23, 2017

Get & Transform improvements

This version allows you to convert a list into a delimited text column using Get & Transform.

Additionally, this version includes the ability to specify a failover option when connecting to a SQL data source.

Released to Current Channel on January 25, 2017

Activity pane

Documents shared via OneDrive and Sharepoint automatically save historical versions as they are worked on. This version of Excel includes a new Activity pane that allows you to view and restore historical versions.

Digital pen support

This version improves support for digital pens, including using the pen’s buttons to use Excel features directly without needing to find them on the Ribbon.

Get & Transform improvements

This version includes several improvements to Get & Transform, including:

  • Support for the Percentage data type
  • Improvements to binary combining functions
  • Support for OLEDB data sources, allowing custom connection strings and SQL statements

Released to Current Channel on December 6, 2016

Map charts

This version includes a new chart type – the Filled Map. This allows you to chart geographic data on a 2-dimensional map of the world (as opposed to 3D maps).

SVG images and icon library

SVG images are now supported by Excel and can be imported into Excel workbooks. This version also includes a new library of SVG icons that can be inserted using the Insert tab.

Save to recent folders

The File > Save As menu now includes a Recent category that allows you to quickly save files to recently-used folders.

Accessibility improvements

This version includes many accessibility improvements, making Excel easier to use on different devices and more accessible to people with disabilities.

These include:

  • Improved screen reader integration, allowing much more information to be reported by screen readers
  • Improved screen reader navigation
  • Alt text support for images, tables, PivotTables and PivotCharts
  • Accessibility checker tool available from the Review tab
  • Improved accessibility features in PDFs created via Excel
  • Improved chart navigation using the keyboard
  • Improved text formatting using the keyboard
  • Row height and column width can now be specified via the Ribbon

Released to Current Channel on November 10, 2016

Ink Replays

Ink Replays allow you to replay handwriting using the new Draw tab (added in July 2016). This allows you to use freehand drawing to create presentations within Excel, illustrating your thought processes in a step-by-step way.

This feature is primarily intended to be used on tablet devices, but it can also be enabled in desktop versions of Excel.

CSV UTF-8 support

CSV files can now be saved using UTF-8 encoding from Excel.

Get & Transform Improvements

Several improvements to Get & Transform are included in this version:

  • Related tables can be automatically imported when connecting to an OData source.
  • Columns can now be added to queries that invoke a custom function defined within the file.
  • A new Query Dependencies view is available that shows a diagram of all of the queries defined in the workbook and how they relate to each other.

Shared With Me

A new Shared With Me option is available from the File > Open menu, allowing you to quickly access files that have been shared with you by other users.

Released to Current Channel on October 4, 2016

Get & Transform improvements

Several improvements to Get & Transform are included in this version:

  • Parameters can now be added to queries, allowing a query to obtain parameters from another query or a list of values instead of only fixed values.
  • New queries can now be created that are based on existing Merge or Append operations.
  • Web pages can now be previewed when importing data from the web using Get & Transform.

Macro recording for new chart types

Excel’s macro recorder now supports the new Treemap, Histogram, Pareto, Waterfall, and Box & Whisker chart types.

New Tell Me help options

Tell Me help now has the ability to understand color changes. For example, entering Make my font red into Tell Me help will present the option to instantly set the font color to red. This new feature allows you to set font, highlight, shading, page, shape border and shape fill colors.

Released to Current Channel on September 21, 2016

Power Query import improvements

Data imported from Power Query via Get & Transform will now retain any formats and calculated columns that were applied to it in Power Query.

Released to Current Channel on August 16, 2016

Get & Transform Improvements

Several minor improvements were made to Get & Transform with this version:

  • New Date/Time and Text transformations make it easier to migrate Excel workbooks to different environments
  • Row filters can now be grouped
  • Transformations can now be given a description, allowing you to document and clarify the purpose of your transformations.

Power BI publishing

Excel files can now be published directly to Power BI if you have a Power BI subscription.

Released to Current Channel on July 5, 2016

Draw Tab and Shape Recognition

A new Draw tab option is now available for the Ribbon (it must be enabled via the Ribbon customization options). This tab allows expanded drawing options with shape recognition that allows crudely-drawn shapes to be automatically converted into perfect squares, triangles, circles, etc.

The Draw tab is primarily intended to make working with shapes easier on tablet devices, but it can also be enabled on desktop versions of Excel.

New external data source options

This version adds several new options when connecting to external data sources, allowing Excel to connect to Sharepoint Online, Microsoft Exchange and SAP HANA.

Get & Transform improvements

This version also adds several minor improvements to Get & Transform:

  • Duration values can now be converted to years
  • White space and line feeds are now properly displayed in the Query Editor preview
  • Queries can now be renamed directly from the Queries pane
  • Jagged CSV files are now automatically detected
  • A Microsoft account can now be used when connecting to Microsoft Exchange
  • URLs are now validated to prevent incorrect URLs from being entered when connecting to Sharepoint

OLAP performance improvements

Creating, editing and refreshing OLAP pivot tables should have improved performance with this update.

Released to Current Channel on June 6, 2016

Get & Transform Improvements

Several Get & Transform improvements were included in this release and the previous version, but Microsoft did not document them separately. Due to this, all of the Get & Transform improvements can be found in our post for the previous version (click Version 1604 below).

Bug Fixes

This update fixes several bugs:

  • Drop-down lists becoming corrupted and duplicating themselves.
  • File associations for Excel documents being lost after uninstalling an earlier version of Excel.
  • Temporary files not being cleared correctly.
  • Crashes caused by VBA code errors.

Released to Current Channel on May 4, 2016

New connection options

Get & Transform is now capable of extracting data from JSON and Azure SQL Data Warehouse sources. Extracting data with Get & Transform is fully explained in our Excel 2016 Expert Skills book.

Improved memory management

The main difference between the 32-bit and 64-bit versions of Excel is that the 32-bit version is only able to work with up to 2 gigabytes of memory, which can be a problem when working with large datasets. This update dramatically increases this limit to as much as 4Gb, although this may vary depending upon the computer’s specification.

The 64-bit version of Excel 2016 does not have any limit on the amount of memory it can use, so this update has no effect on users of the 64-bit version.

Appending more than two tables using Get & Transform

Prior to this update, only two tables could be appended at once (although it was possible to append more tables by manually altering PQFL code). This update allows you to append three or more tables in a single operation.

Choose Columns sorting options

When transforming data using Get & Transform, you may be prompted with a Choose Columns dialog that lists all of the columns in the data. By default the columns are shown in the order in which they appear in the table, but this update allows you to choose to sort them alphabetically if you wish.

Improved transformation performance

The algorithms used to carry out transformations using Get & Transform have been improved, meaning that they will be significantly faster when working with large data sets.

Percentage transformation

A new transformation option has been added to the Get & Transform Query Editor, under Add Column > From Number > Standard > Percentage. This allows you to very quickly create a new column as a percentage of an existing column. You could do this using the Multiply option prior to this update.

Jagged CSV support

‘Jagged’ CSV files are comma-separated values files that have different numbers of columns on each row. This update enables Get & Transform to automatically detect and process a jagged CSV file if you import data from one.

Improved integration with Exchange and SharePoint

If you are using Excel with a Microsoft Exchange sever, you can now log in using a Microsoft Account. If you are using a Microsoft SharePoint server, you will find that some new validation rules have been added to prevent incorrect SharePoint URLs from being entered.

Web data source credentials

When connecting to a website to download data, you may need to provide credentials. Prior to this update you had to specify separate credentials for every online resource that you connected to. This update allows you to specify credentials for an entire website (or any part of a website), making it much easier to work with multiple data sources that reside on a single website.

Limits removed from Query Editor preview

Prior to this update, the Get & Transform Query Editor could only display a maximum of 3000 rows and 100 columns in its preview pane. This update removes these restrictions so there is no limit on the amount of data the Query Editor can display.

New data load options to reduce bandwidth usage

If you are using Get & Transform queries in your workbook, Excel will often download data in the background to allow it to be quickly previewed without having to perform a full refresh. Some users may wish to disable this feature, especially if they have limited bandwidth. This update allows you to disable background data loading using the command: Get & Transform > New Query > Query Options > Current Workbook > Data Load > Allow data preview to download in the background.

Released to Current Channel on April 4, 2016

Get & Transform is a new feature in Excel 2016 that is fully explained in our Excel 2016 Expert Skills book. Get & Transform brings features that were previously part of PowerPivot and integrates them into Excel.

This update makes several improvements to Excel 2016’s Get & Transform features.

New filter options

This update adds several new filter options to the Get & Transform Query Editor. These include:

  • Is Not Earliest (date)
  • Is Not Latest (date)
  • In the Previous (X hours/minutes/seconds)

Copy and paste queries from Power BI

If you also use the standalone Power BI product, you can now copy and paste queries directly into the Queries task pane within Excel’s Get & Transform Query Editor.

Splitting delimited columns using special characters

It’s a common requirement to split one column into several columns by searching for a delimiter character (such as a comma). This update enables you to split columns using special characters, including tabs, carriage returns and line feeds.

Refresh button in Merge Queries dialog

A new refresh button has been added to the Get & Transform Merge Queries dialog, enabling you to refresh the displayed data without closing the dialog. This could be useful if your data changes rapidly.

Monospaced option in Query Editor

The Get & Transform Query Editor now has a new View > Font > Monospaced option. This will display all of the values shown in the preview pane using a monospaced font, meaning all numbers and letters will be the same width and line up with each other. This makes it easier to compare values on different rows.

Database credentials options

If connecting to an external database, you often need to provide a valid username and password. Previously, it was necessary to do this for every database that you connected to. This update allows you to specify a username and password to use for every database on the same server, making it easier to work with multiple databases that reside on a single server.

Add Prefix and Add Suffix options

Two new commands have been added to the Query Editor: Transform > Add Column > Format > Add Prefix and Transform > Add Column > Format > Add Suffix. These enable you to quickly add text to the beginning or end of any text column.

Released to Current Channel on March 17, 2016

Publish to Power BI feature

Power BI is a Microsoft product that creates online dashboards and reports. This update allows you to send your workbooks directly to Power BI via a new option in BackStage View (File > Publish >  Publish to Power BI). You will need a Power BI subscription in order to make use of this feature.

Improved small object selection

This update increases the size of selection handles when working with very small resizable options (such as shapes, charts or text boxes). This is particularly useful when working with touchscreen devices.

This feature applies to Excel, Word, PowerPoint and Outlook.

Released to Current Channel on February 16, 2016

IMPORTANT

Using these new features may cause your workbooks to become unusable by users who are not Excel 365 subscribers. Only Excel 2016 users who are Excel 365 subscribers will have access to these features.

Funnel charts

The Funnel Chart is a new chart type that is intended to work well with values that steadily increase or decrease over time, such as running totals. It can be accessed in the same way as all other chart types, as shown in our Essential Skills course.

Improved Formula AutoComplete

Excel automatically displays a list of possible functions as you type a formula, as shown in our Essential Skills course. For example, typing =SUM displays the possible functions SUM, SUMIF, SUMIFS, SUMPRODUCT, etc. This is known as Formula AutoComplete.

Prior to this update, Formula AutoComplete only displayed functions that began with the text that you entered. This update enables it to find the text you type in any part of a functions name, so now typing =SUM also displays the DSUM, IMSUM and SERIESSUM functions. This will make it easier to find the function you need even when you can’t remember its exact name.

New Send As options

This update adds new sharing options, enabling you to choose to send your workbook as an attachment or as a PDF. Prior to this update, the sharing options only allowed you to save your workbook to the Cloud and send links to other users to enable them to access it online.

You can see how Excel 2016’s online sharing works in our Essential Skills course.

CONCAT function

The CONCAT function is not really a new function; it’s actually exactly just the CONCATENATE function renamed, presumably because CONCATENATE is a very long function name compared to most other Excel functions. CONCATENATE has not been removed so you can still use it to maintain compatibility with earlier versions.

You can see how to use the CONCATENATE function in our Expert Skills course.

Only Office 365 users will have access to the CONCAT function, so you should continue to use CONCATENATE instead if your workbook may need to be used by users of earlier versions or users who are not Office 365 subscribers.

TEXTJOIN function

The TEXTJOIN function is a brand new function that is designed to make it easy to create delimited text from a range of cells. For example, if cells A1:A3 contained the names Tom, Dick and Harry, you could convert them into the text Tom;Dick,Harry with the following formula: =TEXTJOIN(“;”,TRUE,A1:A3).

The TEXTJOIN function can be typed like any other function or accessed via the function library, as shown in our Expert Skills course.

IFS function

The IFS function is a new function that is intended to make it easier to create IF functions that have multiple conditions without having to ‘nest’ them inside each other.

The IF function =IF(A1=1,”A”,IF(A1=2,”B”,”C”)) can be written more elegantly using the IFS function =IFS(A1=1,”A”,A1=2,”B”,A1>2,”C”).

Aside from the more elegant syntax, IFS works identically to a set of nested IF functions, which you can learn about in our Expert Skills course.

SWITCH function

The SWITCH function is another way of simplifying complicated IF functions. Instead of allowing multiple completely different logical tests like the IFS function, the SWITCH function allows you to test a single cell for multiple different values.

For example =IF(A1=1,”A”,IF(A1=2,”B”,”C”)) can be written more elegantly using the SWITCH function =SWITCH(A1,1,”A”,2,”B”,”C”).

Everything that the SWITCH function can do can also be done using the IF function, as shown in our Expert Skills course.

MAXIFS and MINIFS functions

These new functions work in the same way as the existing SUMIFS and COUNTIFS functions, but return the maximum or minimum value instead of the sum or count.

SUMIFS and COUNTIFS are almost identical to SUMIF and COUNTIF, but they allow multiple logical conditions. You can see how to use SUMIF and COUNTIF in our Expert Skills course.

Black Office theme

Office Themes allow you to customize the color scheme used by Excel’s interface. This update adds a new Black theme, which offers a darker color scheme with higher contrast, which may help to reduce eye strain.

Themes apply to all Office applications, not just Excel.

You can see how to select a theme in our Essential Skills course.

Ink annotations

This update adds a new Draw tab to the ribbon, which allows you to write and draw directly onto the screen when working with touchscreen devices or a stylus. This tab will not appear unless your computer has either a touchscreen or stylus available.

This feature extends to all of Office, not just Excel.

Sorting options in Open dialog

When browsing files using the File > Open dialog, you can now sort files by Name or Date modified. These features are also available in the Save As dialog.

This feature extends to all other Office applications except for Outlook.

Navigation options in Save As dialog

When saving a file using the File > Save As dialog, you can now navigate freely between folders without needing to use the Browse option.

This feature applies to Excel, Word and PowerPoint.

Released to Current Channel on December 11, 2015

New templates

This version adds three new templates to Excel’s template library, named: Calendar Insights, Stock Analysis and My Cashflow. These templates are intended to provide a practical demonstration of Excel 2016’s new Business Intelligence and OLAP tools. The OLAP and BI tools are fully explained in our Excel 2016 Expert Skills book.

Improved JavaScript API

Microsoft Office contains a JavaScript API that allows programmers to create add-ins for Office. This update adds new features to the API to enable it to insert pictures into Excel workbooks.

This update also applies to Word and PowerPoint.

Leave a Comment

Your email address will not be published.