New Functions Bring New UI

In the past two posts I talked about all of the improvements we have made to the function library in terms of accuracy and consistency. In this post I will describe the UI changes we have made to help users take advantage of these improvements in Excel 2010. In addition I will also talk about the backward compatibility experience for the new function library.

Function Improvements UI Changes

As a result of the changes we’ve made to improve the consistency of Excel’s function library, we have introduced a whole new set of functions. Often these new functions look very similar to functions that existed in Excel 2007 and earlier, except with more accurate and consistent names. For example, in Excel 2007 we have the FDIST function and in Excel 2010 we introduced the F.DIST.RT function which has equivalent functionality, but it has a more appropriate function name. We want to promote the use of the Excel 2010 version of the functions. With accurate and consistent names, spreadsheets will be easier to debug and it will also be easier to teach functions to new users. To help users distinguish between the new and old versions of a function, we created a new function category called compatibility functions. Compatibility functions can be used so that workbooks remain compatible with earlier versions of Excel.

The concept of compatibility functions comes into play in the Formula AutoComplete. Once a user starts typing =, and the name of the function, the AutoComplete list gives suggestions as to what function the user may want. Compatibility functions appear at the end of the AutoComplete list even if alphabetically they should be higher on the list, since we want to make it easier for users to access the new functions. Since we know that some users will still want to use the compatibility functions, we felt it was a good idea that they remain visible in AutoComplete.  There is also a new icon beside compatibility functions to differentiate them from other functions. If you are using the Technical Preview, you may notice that the icons are a little different than what is displayed below. We updated them after the Technical Preview build.


The compatibility functions category is also exposed in two other places in the UI. The first place is through the Insert Function dialog as shown below:


The second place is in the Ribbon on the Formulas tab:


Function Improvements Backward Compatibility

When a workbook with new Excel 2010 functions is opened and recalculated in Excel 2007 or earlier, the new functions will result in #NAME?. All existing functions will also be recalculated with the algorithms as they were implemented in the earlier versions. In contrast when a workbook created in Excel 2007 and earlier is opened in Excel 2010, all functions that were updated in Excel 2010 will recalculate with the more accurate algorithms.

To ensure that an Excel 2010 workbook does not have compatibility issues with an earlier version, users can run the Compatibility Checker. If a workbook contains new functions, the Compatibility Checker will report it. The Compatibility Checker will not check for function accuracy changes.