Excel 2016 Function Changes

Hi there! I know it’s been a while since my last post. I’ve been updating several books for the Office 2016 release. I thought I’d pop up and provide at least a glimmer of useful info. I’ll attempt to get back to regular blogging in the upcoming weeks.

Since I’m knee-deep in updating content on formulas, I’d like to share a few Function changes (as far as I can tell) introduced with Excel 2016.

New Excel Functions in 2016

As far as I can tell, only 5 new functions will be introduced in Excel 2016.

These forecasting functions are no doubt included as a necessary part of the new Forecast Sheet functionality.

You’ll find these functions in the Statistical formula category.

  • FORECAST.LINEAR: This function predicts a linear set of values using the existing values in your dataset.
  • FORECAST.ETS: You can use this function to apply exponential smoothing in order to get forecasted values.
  • FORECAST.ETS.SEASONALITY: This function helps in detecting seasonality patterns for a specified time series.
  • FORECAST.ETS.CONFINT: This function will return a confidence interval for the forecasted value.
  • FORECAST.ETS.STAT: I’m unclear on what this function is meant to do. The formula helper says this function returns the requested statistic for the forecast.


Demoted to Compatibility Status

These poor functions have been demoted to the dreaded Compatibility category; meaning because there are better functions for accomplishing their respective tasks, the functions listed below will be found under Compatibility on the Formulas tab.

CEILING: CEILING.MATH offers a more controlled method of rounding up to the nearest integer or multiple of significance

FLOOR: FLOOR.MATH offers a more controlled method of rounding down to the nearest integer or multiple of significance.

FORECAST: The new forecasting functions provide much more enhanced versions of this functions.


DATEDIF and Database functions are still Hidden.

What has not changed is that the most useful hidden function, DATEDIF, is still nowhere to be found in either the Excel 2016 Ribbon or the function helper. Disappointing. Also, the Database functions category is still the only category of functions not shown on the Formulas tab of the Excel Ribbon. I don’t use the database functions very much at all, but it’s still annoying that an entire category of functions is missing from the Ribbon.

Any other Function changes I missed in Excel 2016?

6 thoughts on “Excel 2016 Function Changes

  1. David Hager

    The DATEDIFF function is a legacy function added for compatibility with Lotus Notes. Cannot be legally acknowledged as an Excel function, and thus will always remain hidden.

  2. Doug Jenkins

    I must say that the Microsoft strategy on updating functions seems very strange to me.

    We get ever more esoteric stats functions, and even the odd esoteric engineering function, but absolutely nothing for generating splines, nothing for dealing with vectors, and nothing for solving even linear or quadratic equations, let alone higher order polynomials.

    Still, mustn’t complain I suppose. It keeps a steady stream of visitors coming to my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *