You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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:
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.
Comments: (17) Collapse
Interesting. Has to be said though, some of those dialogs are looking old and tired. When are you guys moving to a vector based UI?
Those dialogs seem as small and clumsy as always. You design for high resolutions with the ribbon, but you don't change the dialogs. Perfect example: Data tab / text to columns / fixed width. I have a 1900*1200 monitor and (as usual in windows) Excel's window is maximied. Yet said dialog only uses 477*94 pixels (including both horizontal and vertical scrollbars) to display the relevant information of this modal dialog, THAT'S LESS THAN 2 PERCENT OF THE SCREEN AREA!!! Please, pay attention to dialog design!!!
Looks cool, will it work like vba? (god I hope this isn't a setting already) It'd be awesome if when the suggested function you could arrow down then hit enter to select the function you want.
JohnR: Formula AutoComplete is similar in function to IntelliSense in VBA and works as you described. The feature exists today in Excel 2007.
I guess that the compatibility checker will only inspect the use of functions in excel workbook cells and not in VBA code associated with that workbook?
I'd add the name of the NEW function to the tooltip, as it may be that the new function's name isn't visible in the current scrollarea of the intellisense dropdown.
Joseph Chirilov: In VBA when IntilliSsense displays the drop down, I can arrow down to highlight the item I want then hit enter, the system will then auto complete the item and let me continue typing to finish the statement. However (at least on my system) if I do the same with a function the system only enters the amount of text that I've typed so far and moves to the next cell. ex: In a sheet if I type =Su then arrow down to highlight Sum then hit enter what ends up in the formula bar is =Su
It'd be nice if after hitting enter the system entered =Sum( into the formula bar and left the cursor after the ( so I can complete the function.
JohnR: I'll pass your feedback along. You get the desired behavior if you press TAB instead of ENTER.
@Joseph,
Please post the local function names table as well. Thanks. If I am using a French version of Excel, does it follow the same scheme ? Are the function names localized ? If so, what is their names ?
anon - The function names will be localized for locales that currently support localized names (French is one of them). We do not have the final naming for the localized versions of the functions yet.
I profoundly hope that the new function names will NOT be localized (=translated) into foreign languages. I can tell you from experience (Dutch, French, German) that it is a source of utmost frustration. Some localized names don't even ressemble their original counterparts. It's guesswork all the time. To my knowledge, their is no standard dictionary included in excel which would help translating. On top of that, most interesting documentation is in English anyway. Please drop translated functions.
BEL8490:
I have a little tool that helps you find translations:
www.jkp-ads.com/download.asp#xlmenufundict
JKP, I'm very well aware of your excellent utility ( I use it since +/-2005). But with all due respect it is not part of standard Excel. Even if it was you still have to use the English names in VBA code and the Local names in your worksheets!
And admit that FLOOR is a lot simpler than AFRONDEN.BENEDEN (why the dot anyway?)
"...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"
How will this work with workbooks that are shared across users who are on both 2007 (or earlier) and 2010? For example, if we have a workbook on Sharepoint that was built in 2007, and a 2010 user opens it up causing the formulas to be "upgraded";
(1) Will there be a warning for the 2010 user when their results are different than the "original" results? In many cases the results may be close enough, but what happens when the two users base dependent calculations off those two different results (I'm thinking the Mars lander fiasco, although maybe they don't use Excel at NASA...). The results don't have to /be/ the same, but the user should know when Excel is returning different results for different users. Maybe one of those green triangle error warning icons, with a message something like "Excel 2010 uses a more accurate calculation method for this formula; Earlier versions of Excel (2007 and earlier) will return a different result than what you see here". Or if you really wanted to get fancy you could include an option for the user to click to recalculate using legacy formulas- (a) this cell only, or (b) all cells on this sheet, or (c) all cells in this workbook.
(2) When the 2010 user saves their changes, will the formulas revert to the original 'compatibility' formulas? If not, what happens when the next 2007 user accesses the workbook to try to do their work- will they get the #NAME error?
And on an unrelated note (maybe 2010 and 2007 are the same in this regard, but I don't use 2007)- on both the function dialogue box, and the ribbon examples above, I don't see a listing for UDFs... To keep things intuitive for the end user, shouldn't /all/ functions be accessible through the same menus/locations?
I like the autocomplete, although I recall having the same issue as JohnR - I expect a to select an entry... more consistent with standard interface design. How will UDFs be integrated into the autocomplete list? If I write UDFs for myself or others (which is quite often), will they be able to use it without perceiving it to be any different from any other formula, or will they have to find the UDF elsewhere to use it?
Perhaps include an Excel setting where users (or system admins who are rolling out a standard image) can select whether to automatically 'upgrade' formulas to the 2010 version, or always leave them in compatability mode for backward compatibility.
Thank you,
Keith
Comments: (loading) Collapse