Changes in Excel 2010 (for IT pros)
Applies to: Office 2010
Topic Last Modified: 2012-04-05
IT Pros can learn about the new, changed, and deprecated features of Microsoft Excel 2010 and how these changes can impact migration plans. If you are migrating from Microsoft Office Excel 2003, we suggest that you review Changes in Excel 2007 (https://go.microsoft.com/fwlink/p/?LinkId=134563) and Migration considerations for Excel 2007 (https://go.microsoft.com/fwlink/p/?LinkId=164062).
Note
Are you looking for help using new features in Excel 2010? Visit Office.com to learn how to use new features (https://go.microsoft.com/fwlink/p/?LinkId=247994) and find out what features are discontinued or modified (https://go.microsoft.com/fwlink/p/?LinkId=182569).
In this article:
What’s new
What’s changed
What’s removed
Migration considerations
What’s new
This section highlights new features in Excel 2010 that might interest IT administrators. For more information about new features, see What’s New in Excel 2010 (https://go.microsoft.com/fwlink/p/?LinkId=200398).
In addressing user needs
This section highlights changes in Excel 2010 to address user needs, especially improvements in programmability.
High performance computing
In Excel 2010, High Performance Computing (HPC) solves computationally intensive problems by using multiple computers. When it runs on a desktop computer, Excel 2010 can offload the evaluation of certain kinds of user-defined function calculations to a compatible cluster, such as Microsoft Windows HPC Server 2008 R2. This offload enables Excel 2010 to continue calculating other parts of the workbook in parallel to the cluster that is calculating the user-defined functions. Complete workbooks can also be calculated on the cluster. For example, a workbook model can be recalculated many thousands of times by performing batches of calculations in parallel on the cluster. When a supported computer cluster is available, users can instruct Excel 2010 to use that cluster by selecting a cluster connector and configuring a specific cluster name to use in the Advanced options of the Excel Options dialog box.
Use this capability in Excel 2010 to solve mathematical analysis or data processing problems, or Monte Carlo simulations. Cluster-safe user-defined functions must be implemented in an XLL. You cannot create a cluster-safe user-defined function in VBA or in a COM Automation add-in. Also, cluster-safe user-defined functions cannot interact with Excel in any way except to return their value. Workbooks must be designed for the cluster. This means that existing workbooks will not necessarily work on the cluster without modification. This option is not supported for 32-bit and ia64-based, language-specific, Itanium platforms.
For more information, see HPC Services for Excel (https://go.microsoft.com/fwlink/p/?LinkId=193165).
Macro recording support for chart elements
In Microsoft Office Excel 2007, recording a macro while formatting a chart, or other object, did not produce any macro code. However, in Excel 2010, you can use the macro recorder to record formatting changes to charts and other objects and then reuse those changes repeatedly. Standardize formatting by distributing these macros to users.
Closing XLM/VBA gaps
Excel has a macro facility, known as Excel 4 macros (XLM for short), that was the primary macro language before the introduction of VBA in Excel 5.0. Most people have long ago migrated their Excel 4 macros to VBA. However, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.
In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. Excel 2010 does still enable the creation, editing and execution of Excel 4 macros. You can use Excel 2010 to migrate your macros.
User-defined functions run asynchronously
In Excel 2010, you can author these non-processor-intensive user-defined functions as asynchronous. This ability is supported in XLL add-ins, and the new Excel 2010 SDK has all that you need for writing asynchronous user-defined functions.
How it works
Break your user-defined function in two parts:
A synchronous function call, which sets up the asynchronous calculation, data request, external Web service call, and so on, and returns immediately.
An asynchronous part, which returns the result to Excel when it is ready.
Excel tracks uncompleted user-defined function calls and continues independent parts of the calculation. When the user-defined function call result becomes available to your XLL add-in, the add-in then calls back into Excel with the user-defined function result. For information about how to configure the XLL add-in, see Programmability Improvements in Excel 2010 (blog) (https://go.microsoft.com/fwlink/p/?LinkId=200472).
In Business Intelligence
Business Intelligence (BI) is a category of technologies that are used to support decision making.
Sparklines
Sparklines are a new kind of visualization in Excel 2010. They are small cell-sized graphics used to show trends in series of values by using line, column, or win/loss charts. Sparklines allow the viewer to see in a single cell information-dense graphics that greatly increase reader comprehension of the data. They demonstrate the “what” not the “why” of the data. For more information, see Sparklines in Excel (blog).
Slicers
Slicers make filtering and interpretation of data easier. They improve PivotTables and CUBE functions in a workbook. Slices filtered data interactively. They float above the grid and behave like report filters so you can hook them to PivotTables, PivotCharts, or CUBE functions to create interactive reports or dashboards. For more information, see Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers (blog), and Interacting with Slicers (blog), and Dressing up your Slicers (blog).
Microsoft SQL Server PowerPivot for Excel add-in
If you have to model and analyze very large amounts of data, you can download the PowerPivot for Excel add-in and work with that data inside your Excel workbooks. By using this add-in, you can quickly combine data from multiple sources that include corporate databases, worksheets, reports, and data feeds. You can then interactively explore, calculate, and summarize that data by using PivotTables, slicers, and other Excel features. As you interact with the data, you will notice that the response time is fast, whether you are working with hundreds of rows, or hundreds of millions of rows. If you have access to Excel Services in Microsoft SharePoint Server 2010, you can make your reports and analyses available on a SharePoint site so that other people in your organization can benefit from your work.
For more information about PowerPivot, see Introducing PowerPivot for Excel 2010 (https://go.microsoft.com/fwlink/p/?LinkId=186137).
Calculation feature improvements
This section provides updates to the Excel calculation engine.
New version of Solver
Excel 2010 includes a new version of the Solver add-in, which you can use to find optimal solutions in what-if analysis. Solver has an improved user interface, a new Evolutionary Solver, based on genetic algorithms, that handles models with any Excel functions, new global optimization options, better linear programming and nonlinear optimization methods, and new linearity and feasibility reports. In addition, the Solver add-in is now available in a 64-bit version. For more detailed help about Solver from Frontline Systems, see Solver Help at www.solver.com (https://go.microsoft.com/fwlink/p/?LinkId=164909).
New statistical functions
The function library in Excel improved in Excel 2010. Accuracy of functions improved, consistency improved with Excel's function names and definitions with 50 new functions, and a new functions user interface was added. New algorithms were implemented to improve the accuracy of our statistical, financial and math functions. For more information about improvements, see Function Improvements in Excel 2010 (blog).
In Excel Services
This section highlights changes in Excel Services. For more information, see Excel Services 2010 Overview (blog).
Excel Services improvements
Many organizations use Excel Services to share workbooks and data with other people, such as executives and other stakeholders in your organization. If you use Excel Services on a SharePoint site, you can take advantage of the following improvements:
Share workbooks from Backstage Before Excel 2010, it was previously possible to save and publish worksheet data to a SharePoint site. In Excel 2010, the options for doing this are now conveniently located together on the Share tab in the Microsoft Office Backstage view.
More support for Excel features Before Excel 2010, if a workbook contained unsupported features, it could not be opened in the browser. In Excel 2010, most workbooks with unsupported features will open. In addition, more Excel features are supported in Excel Services, including new Excel 2010 features such as Sparklines and Slicers.
Edit and collaborate on workbooks If you publish a workbook to a SharePoint site where Excel Services is installed, you can edit your workbook in a supported Web browser, in addition to viewing it. In addition, you and your colleagues can work on the same workbook at the same time. This means that you no longer have to e-mail a workbook around, or wait for someone to check it back in on the server before you can edit it. For example, imagine that you and your manager are currently viewing the same worksheet in different offices. If you make a change to the data, your manager will see that change on her screen. For more information about Excel Services, see What's new for Excel Services (SharePoint Server 2010) (https://go.microsoft.com/fwlink/p/?LinkId=168442).
Enhanced programmability This includes the following:
Improved Web Services API
New REST API
New JavaScript OM
Support of new XL features and visualizations These include slicers, sparklines, new conditional formatting, and others features.
Better Admin scriptability By using Windows PowerShell.
In Office suite changes
The new 64-bit version of Office allows the processing of larger data sets, which is especially important for Excel 2010. You use it with the new VBA 7.0. You might have to update VBA when you use 64-bit Excel 2010. For more information, see Excel 2010 – Now With More Bits! (blog).
Backstage view
The new Backstage view replaces the traditional File menu. All file management tasks are available in the Backstage view. Click the File tab to access the Backstage view. Although it was previously possible to save and publish worksheet data to a SharePoint site, in Excel 2010, the options for doing this are now conveniently located together on the Share tab in the Microsoft Office Backstage view. For more information, see Backstage (blog).
Excel 2010 64-bit advantages
Applications built with the 64-bit version can use more physical memory than ever, especially important for those who have to work with really large data sets. In Excel 2010 investments were made in our 64-bit architecture to optimize our memory consumption while keeping the cell table (and related operations) as fast as possible.
For example, create bigger workbooks using 64-bit Excel that can be too big for 32-bit Excel to open. But generally workbooks are interchangeable between 32-bit and 64-bit. For information about 64-bit support and code compatibility, see Programmability Improvements in Excel 2010 (blog).
What’s changed
This section summarizes the feature changes in Excel 2010 that might interest IT administrators. For more information about changed features, see Discontinued features and modified functionality in Excel 2010 (https://go.microsoft.com/fwlink/p/?LinkId=182569).
In addressing user needs
This section highlights changes in Excel 2010 to address user needs, especially improvements in programmability.
Compatibility mode tool
New features in Excel 2010 are disabled when you are opening previous versions of Excel in Excel 2010. For example, new 2010 Sparklines and Slicers features are disabled with opening earlier version of Excel. Workbooks created in the Excel 97-2003 file format (.xls) automatically open in compatibility mode. Workbooks created in Excel 2010, but destined for people who use earlier versions of Excel, should turn on compatibility mode to prevent accidental use of functions and features that are incompatible with the earlier versions of Excel. This is very important for users who plan to share workbooks with other users who have not yet migrated to Office Excel 2007 or a later version.
File loading performance
File loading (opening and saving) lends itself well to parallel processing and the multi-core functionality of Excel 2010. However, the structure and content of workbooks significantly affects the performance gains. For example, if there is only one very large sheet in a workbook, a proportionally large amount of file loading time will be spent loading the one sheet. But if you have two very large sheets, Excel can fetch the second sheet off disk while the first sheet is still being loaded into memory.
In Business Intelligence
Business Intelligence (BI) is a category of technologies that are used to support decision making.
PivotTable improvements
PivotTables are easier and faster to use in Excel 2010. Some of the key improvements include the following:
Improved performance In Excel 2010, multithreading helps speed up data retrieval, sorting, and filtering in PivotTables.
OLAP Write-back support (also known as PivotTable What-if Analysis) In Excel 2010, users can modify values in PivotTable cells, recalculate the PivotTable with the new values, and, if the results are satisfactory, publish the modified data to the Online Analytical Processing (OLAP) cube (or Analysis Services cube) so that the data is shared with other users. For more information, see Excel 2010 PivotTable What-If Analysis (Writeback).
Named sets Named sets are a tool that allows a re-usable group of items to be created for use in PivotTables. Combine items from different hierarchies (asymmetric reporting) in ways that otherwise wouldn’t be possible. Create PivotTables based on your own custom Multidimensional Expressions (MDX). Create PivotTables that dynamically change based on filters by using dynamic sets. For more information, see PivotTable Named Sets in Excel 2010 (blog).
Dynamic sets support When you work with the same set of items from data repeatedly, Excel 2010 provides the ability to easily create and reuse this logical grouping of items as a single object. For OLAP PivotTable users, dynamic rendering using the filter in the matrix is supported.
Filtering
For large worksheets filtering enables the quick location and display of specific data in tables and PivotTable views. Use new Search Filter capability to spend less time sifting through large data sets. For more information, see Excel 2010: New Search Filter (blog).
Conditional formatting
Conditional formatting lets references be made to different sheets on the workbook (cross-sheet conditional formatting). Use conditional formatting to discover and show important trends and highlight data exceptions. More styles, data bar options, and new icon sets made available. References to other worksheets enable in conditional formatting rules. Conditional formatting stores dependencies of the formulas that are used, so reevaluation of the entire conditional format is not necessary as often. Pivot tables or scrolling refresh more quickly so they can display faster. For more information, see More Conditional Formatting Features in Excel 2010 (blog).
Icon sets
Icon Sets are a new kind of conditional formatting. An icon is drawn in each cell representing the value of the cell relative to the other cells in the selected range. Icons sets are a great way to create groups of similar data as a part of your data analysis. For more information, see Icon Set Improvements in Excel 2010 (blog).
Data bars
Data bars now drawn proportionally according to their values. Negative values more clearly displayed and zero values are suppressed. For more information, see Data Bar Improvements in Excel 2010 (blog).
OfficeArt controls and objects
In Excel 2010, in addition to shape objects, the following controls and objects are converted to the new OfficeArt technology:
Form controls
Microsoft ActiveX objects
OLE objects
Camera tool objects
Shape objects drawn in earlier versions of Microsoft Excel that are not upgraded to Microsoft Excel 2010 SmartArt format cannot be grouped with shape objects that are created in or upgraded to Excel 2010 SmartArt format. Mixed shape objects are layered, with the earlier versions of shape objects drawn on top of all later versions. This also means that Excel 2010 charts cannot be shown on dialog sheets that were created in an earlier version of Excel. You cannot access the new shape objects by clicking Select Objects (Home tab, Editing group, Find & Select button). To select the newer shape objects, you must use the Select Multiple Objects command (File tab, Options, Customize Ribbon).
Pattern fills
Pattern fills removed in 2007 have been reintroduced in Excel 2010. Charts formatted with pattern fills in previous versions of Excel will keep and display the pattern fills when they are opened in Office Excel 2007. For more information, see Chart Pattern Fills (blog).
In strategic improvements
Charting improvements
It is easier to work with charts in Excel 2010. Specific improvements include the following:
New charting limits It is easier to work with charts in Microsoft Office Excel 2007. Specific improvements include new charting limits. In Microsoft Office Excel 2007, you could have up to 32,000 data points in a data series for 2-D charts. In Excel 2010, the number of data points in a data series is limited only by available memory. This enables users — especially those in the scientific community — to more effectively visualize and analyze large sets of data. Memory errors could result if large 64-bit worksheets are calculated on a 32-bit computer.
Macro recording for chart elements In Office Excel 2007, recording a macro while formatting a chart or other object did not produce any macro code. However, in Excel 2010, you can use the macro recorder to record formatting changes to charts and other objects.
Improvements in the chart UI Improvements include PivotChart Interactivity, Formatting improvements, Parity Improvements, and Limit Increases. Double click any chart element and the format dialog appears. The chart element selector is available in the right-click mini-bar. Pattern fills are available again. When formatting charts, you can now record a macro to use format and layout changes again. Parity Improvements were made in the areas of Axis Scaling, Layout, Visual Parity, and Object Model Parity. Data size limits on charts are removed or increased. Prior limit of 32,000 points per data series on 2D is removed. Machine memory is the current limit. The maximum of 256,000 data points is now removed. The only limit is the available memory and computer capacity. For more information, see More Charting Enhancements in Excel 2010 (blog).
What’s removed
This section highlights features that were available in previous versions of Microsoft Office that are removed from Excel 2010 and that might interest IT administrators. For more information about removed features, see Discontinued features and modified functionality in Excel 2010 (https://go.microsoft.com/fwlink/p/?LinkId=182569).
In addressing user needs
This section highlights changes in Excel 2010 to address user needs, especially improvements in programmability.
Clip Art task pane Search in Box
The Search in box is no longer available, which means that you can no longer limit your search to specific collections of content. To narrow your search, you can use multiple search terms in the Search for box.
ClipArt task pane Clip Organizer
Clip Organizer is a tool that arranges and catalogs clip art and other media files that are stored on your hard disk. Clip Organizer is no longer directly accesses the Clip Art task pane in Office programs. In addition, the following changes were made to Clip Organizer:
Although you can delete clips from Clip Organizer, you can no longer delete a clip from a specific collection.
The command that enabled you to find clips of a similar style is no longer available.
The command for sending a clip as an attachment in e-mail messages is no longer available.
The List and Details views are no longer available. Instead, all clips appear as thumbnails in the Clip Organizer window.
The Organize clips link is no longer available. To open Microsoft Clip Organizer from Windows 7, Windows Vista, or Windows XP, click the Windows Start button, click All Programs, click Microsoft Office, click Microsoft Office 2010 Tools, and then click Microsoft Clip Organizer.
The command for automatically finding media files on your computer and organizing them into collections no longer exists. However, you can still add clips manually to Clip Organizer or import them from a scanner or camera.
In calculation features
Conditional Sum Wizard
The Conditional Sum Wizard is replaced in Excel 2010 by a Function Wizard that includes SUMIF and SUMIFS functions. Formulas created in an earlier version that were generated by the Conditional Sum Wizard will continue to work and can be edited using other methods. The legacy Conditional Sum Wizard add-in is no longer available with Excel 2010.
Lookup Wizard
The Lookup Wizard is replaced in Excel 2010 by a Function Wizard that includes SUMIF and SUMIFS functions. Formulas created in an earlier version that were generated by the Lookup Wizard will continue to work and can be edited using other methods. The legacy Lookup Wizard add-in is no longer available with Excel 2010.
Updated statistical functions
The following table shows algorithms for calculating the statistical distribution functions that have been modified or redesigned completely for better accuracy.
| Description | Function |
|---|---|
Binomial distribution |
BINOMDIST, CRITBINOM |
Chi squared distribution |
CHIDIST, CHIINV |
Exponential distribution |
EXPONDIST |
F distribution |
FDIST, FINV |
Gamma distribution |
GAMMADIST, GAMMAINV |
Hypergeometric distribution |
HYPGEOMDIST |
Lognormal distribution |
LOGNORMDIST, LOGINV |
Negative Binomial distribution |
NEGBINOMDIST |
Normal distribution |
NORMDIST, NORMINV |
Standard Normal distribution |
NORMSDIST, NORMSINV |
Poisson distribution |
POISSON |
Student's t distribution |
TDIST,TINV |
Weibull distribution |
WEIBULL |
The following table shows additional functions for which the accuracy is improved.
| Description | Function |
|---|---|
Hyperbolic arcsine |
ASINH |
Ceiling function |
CEILING |
Convert function |
CONVERT |
Error function |
ERF |
Complementary error function |
ERFC |
Floor function |
FLOOR |
Natural logarithm of the gamma function |
GAMMALN |
Geometric mean |
GEOMEAN |
MOD function |
MOD |
Random number function |
RAND |
Sample standard deviation |
STDEVS |
Sample variation |
VARS |
As part of the accuracy improvements, Excel accepts a larger range of input values. As a result, it will return a wider range of results for certain functions. For example, the ERF and ERFC functions now take negative input values, and the MOD function can take larger input values. For more information about the improvements to the statistical functions, see Function Improvements in Microsoft Office Excel 2010 (https://go.microsoft.com/fwlink/p/?LinkId=186148).
In Office suite changes
Smart tags now “Additional actions”
Smart tags have been replaced by the shortcut menu option “Additional actions” in Excel 2010. Text is no longer automatically recognized by a smart tag recognizer and is no longer marked by a purple dotted underline of the active cell. Instead, users can trigger the data recognition function and view the custom actions that are associated with text by selecting the text and clicking the Additional actions item on the shortucut menu that is opened by right-clicking a selected cell.
Calendar control
Calendar control (mscal.ocx) was a Microsoft Access feature that could be used in Access worksheets. Calendar control is removed in Access 2010 and is not usable for Excel 2010. Instead, users can use Date Picker or their own custom calendar controls.
Migration considerations
When planning a migration to Excel 2010, review what is new, changed, and removed for Excel 2010. Because Excel 2010 has many similarities with Office Excel 2007, such as the same native file format, you can also review and use the existing migration documentation for Office Excel 2007 when you migrate to Excel 2010.
MSXML5
MSXML5 is not supported in Excel 2010. Users will receive a runtime error if they attempt to run an Excel extensibility solution created by using MSXML5. Migrate code to MSXML6 or to managed code that uses the .NET Framework.
VBA settings migration
In Office 2010, Visual Basic for Applications (VBA) 6.0 was updated to VBA 7.0. VBA 7.0 settings were reset to their defaults after migration instead of automatically repopulating. This occurred because the registry settings for VBA are in a different hive in Office 2010, as shown in the following table.
| Version | Registry subkey |
|---|---|
Office 2000 through Office 2007 |
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\6.0\Common |
Office 2010 |
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.0\Common |
To resolve this problem, copy the VBA 6.0 registry keys from the 6.0 hive to the 7.0 hive.
For more information, see User registry settings to migrate to Office 2010 and Compatibility Between the 32-bit and 64-bit Versions of Office 2010 (https://go.microsoft.com/fwlink/p/?LinkId=185841).