This article describes the new and improved features in Microsoft Excel 2010.
In this article
Access the right tools, at the right time
New and improved features can help you be more productive, but only if you can find them when you need them. Like the other Microsoft Office 2010 programs, Excel 2010 includes the Microsoft Office Fluent interface, which consists of a customizable visual system of tools and commands.
First introduced in Excel 2007, the ribbon makes it easy for you to find commands and features that were previously buried in complex menus and toolbars. Although you could customize the Quick Access Toolbar in Excel 2007, it wasn’t possible to add your own tabs or groups to the ribbon. In Excel 2010, however, you can create custom tabs and groups and rename or change the order of the built-in tabs and groups.
Microsoft Office Backstage view
Click the File tab to open Backstage view, where you create new files, open existing files, save, send, protect, preview, and print files, set options for Excel, and more.
Workbook management tools
Excel 2010 comes with tools that can help you manage, protect, and share your content.
Recover previous versions You can now recover versions of files that you closed without saving. This is helpful when you forget to manually save, when you save changes that you didn't mean to save, or when you just want to revert to an earlier version of your workbook. Read more about recovering your Office files.
Protected view Excel 2010 includes a Protected View, so you can make more informed decisions before exposing your computer to possible vulnerabilities. By default, documents that originate from an Internet source are opened in Protected View. When this happens, you see a warning on the Message bar, along with the option to enable editing. You can control which originating sources trigger Protected View. You can also set specific file types to open in Protected View regardless of where they originate. Read more about Protected View.
Trusted documents The trusted documents feature is designed to make it easier to open workbooks and other documents that contain active content, such as data connections or macros. Now, after you confirm that active content in a workbook is safe to enable, you don’t have to repeat yourself. Excel 2010 remembers the workbooks you trust so that you can avoid being prompted each time you open the workbook. Read more about trusted documents.
Access workbooks in new ways
You can now access and work with your files from anywhere you are—whether at work, at home, or on the go.
Microsoft Excel Online
Excel Online extends your Excel experience to the web browser, where you can work with workbooks directly on the site where the workbook is stored. Excel Online is part of Microsoft Office Online, and is available in OneDrive and in organizations that have configured Office Online on SharePoint 2010.
With Excel Online, you can:
View a workbook in the browser When you click on a workbook to open it in Excel Online, the workbook is displayed in view mode. You can sort and filter data in the workbook, expand PivotTables to see relationships and trends in the data, recalculate values, and view different worksheets.
Edit a workbook in the browser With Excel Online, all you need to access your workbooks is a browser. Your teammates can work with you, regardless of which version of Excel they have. When you click on an Excel workbook that is stored in a SharePoint site or in OneDrive, the workbook opens directly in your browser. Your workbooks look the same in the browser as they do in Excel. You can edit your worksheets in the browser, using the familiar look and feel of Excel. When you edit in the browser, you can change data, enter or edit formulas, and apply basic formatting within the spreadsheet. You can also work with others on the same workbook at the same time.
Excel Mobile 2010 for Windows Phone 7
If you have Windows Phone 7, you can use Microsoft Office Mobile 2010 to work with your files from anywhere—whether you’re at work, at home, or on the go. Excel Mobile 2010 is part of Office Mobile and already on your phone in the Office Hub, so you don't need to download or install anything else to get started.
You can use Excel Mobile to view and edit workbooks stored on your phone, sent to you as email attachments, or hosted on a SharePoint 2010 site through SharePoint Workspace Mobile 2010. When you edit a workbook via SharePoint Workspace Mobile, you can save your changes back to the SharePoint site when you’re online.
You can create, update, and instantly recalculate your spreadsheets using many of the same tools you already know and use in the desktop version of Excel:
Use the outline view to switch between worksheets or charts in a workbook.
Sort, filter, and manage your spreadsheets.
Add or edit text and numbers.
Read more about Office Mobile 2010 for Windows Phone 7.
Make fast, effective comparisons from lists of data
In Excel 2010, new features such as sparklines and slicers, and improvements to PivotTables and other existing features, can help you to discover patterns or trends in your data.
You can use sparklines—tiny charts that fit in a cell—to visually summarize trends alongside data. Because sparklines show trends in a small amount of space, they are especially useful for dashboards or other places where you need to show a snapshot of your business in an easy-to-understand visual format. In the following image, the sparklines that appear in the Trend column let you see at a glance how each department performed in May.
PivotTables are now easier to use and more responsive. Key improvements include:
Performance enhancements In Excel 2010, multi-threading helps speed up data retrieval, sorting, and filtering in PivotTables.
PivotTable labels It's now possible to fill down labels in a PivotTable. You can also repeat labels in PivotTables to display item captions of nested fields in all rows and columns.
Enhanced filtering You can use slicers to quickly filter data in a PivotTable with the click of a button and see which filters are applied without having to open additional menus. In addition, the filter interface includes a handy search box that can help you to find what you need among potentially thousands (or even millions) of items in your PivotTables.
Write-back support In Excel 2010, you can change values in the OLAP PivotTable Values area and have them written back to the Analysis Services cube on the OLAP server. You can use the write-back feature in what-if mode and then roll back the changes when you no longer need them, or you can save the changes. You can use the write-back feature with any OLAP provider that supports the UPDATE CUBE statement.
Show Values As feature The Show Values As feature includes a number of new, automatic calculations, such as % of Parent Row Total, % of Parent Column Total, % of Parent Total, % Running Total, Rank Smallest to Largest, and Rank Largest to Smallest.
PivotChart improvements It is now easier to interact with PivotChart reports. Specifically, it's easier to filter data directly in a PivotChart and to reorganize the layout of a PivotChart by adding and removing fields. Similarly, with a single click, you can hide all field buttons on the PivotChart report.
Slicers are visual controls that let you quickly filter data in a PivotTable in an interactive, intuitive way. If you insert a slicer, you can use buttons to quickly segment and filter the data to display just what you need. In addition, when you apply more than one filter to your PivotTable, you no longer have to open a list to see which filters are applied to the data. Instead, it is shown there on the screen in the slicer. You can make slicers match your workbook formatting and easily reuse them in other PivotTables, PivotCharts, and cube functions.
Improved conditional formatting
Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. Excel 2010 includes even greater formatting flexibility:
New icon sets First introduced in Office Excel 2007, icon sets let you display icons for different categories of data, based on whatever threshold you determine. For example, you can use a green up arrow to represent higher values, a yellow sideways arrow to represent middle values, and a red down arrow to represent lower values. In Excel 2010, you have access to more icon sets, including triangles, stars, and boxes. You can also mix and match icons from different sets and more easily hide icons from view—for example, you might choose to show icons only for high profit values and omit them for middle and lower values.
More options for data bars Excel 2010 comes with new formatting options for data bars. You can apply solid fills or borders to the data bar, or set the bar direction from right-to-left instead of left-to-right. In addition, data bars for negative values appear on the opposite side of an axis from positive values, as shown here.
Other improvements When specifying criteria for conditional or data validation rules, it's now possible to refer to values in other worksheets in your workbook.
Obtain powerful analysis from your desktop
Whether at work or home, you need to be able to manipulate and analyze your data in a way that gives you new insight or helps you make better decisions—and the faster you can finish your task the better. Excel 2010 provides new and improved analysis tools that enable you to do just that.
Power Pivot for Excel add-in
If you need to analyze large quantities of data, you can download the Microsoft SQL Server Power Pivot for Excel add-in, which adds a Power Pivot tab to the Excel ribbon.
With Power Pivot for Excel, you can import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions— all without requiring IT assistance.
Improved Solver add-in
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 on Solver from Frontline Systems, visit Solver Help at www.solver.com.
Improved function accuracy
In response to feedback from the academic, engineering, and scientific communities, Excel 2010 now includes a set of more accurate statistical and other functions. Certain existing functions have also been renamed to better describe what they do.
Accuracy improvements A number of functions have been optimized to improve accuracy. For example, Excel 2010 returns more accurate results for the beta and chi-squared distributions.
More consistent functions Certain statistical functions have been renamed so that they are more consistent with the function definitions of the scientific community and with other function names in Excel. The new function names also more accurately describe their functionality. Workbooks created in earlier versions of Excel will continue to work despite these name changes, because the original functions still exist in a Compatibility category.
Improved filter capabilities
In addition to slicers, which are described earlier in this article, Excel 2010 comes with new features that make it easier to sort and filter data.
New search filter When you filter data in Excel tables, PivotTables, and PivotCharts, you can use a new search box, which helps you to find what you need in long lists. For example, to find a specific product in a catalog that stocks over 100,000 items, start by typing your search term, and relevant items instantly appear in the list. You can narrow the results further by deselecting the items you don't want to see.
Filter and sort regardless of location In an Excel table, table headers replace regular worksheet headers at the top of columns when you scroll down in a long table. AutoFilter buttons now remain visible along with table headers in your table columns, so you can sort and filter data quickly without having to scroll all the way back up to the top of the table.
Excel 2010 is available in a 64-bit version, which means that power users and analysts can create bigger, more complex workbooks. By using a 64-bit version, you can address physical memory (RAM) that is above the 2-gigabyte (GB) limit that exists in the 32-bit version of Excel.
Performance improvements in Excel 2010 can help you to interact with your data more efficiently. Specific investments include:
General improvements In response to customer feedback, Excel 2010 improves performance in a number of areas. For example, Excel 2010 is more responsive when you move and resize charts, work in Page Layout view, and interact with shapes on the worksheet.
Support for large data sets Excel 2010 handles workbooks that contain massive amounts of data more efficiently. Specifically, it takes less time to perform activities commonly performed on large data sets, such as filtering and sorting the data, copy and pasting it from one worksheet to another, and using the Fill feature to copy formulas.
Multicore improvements Multithreading improvements in Excel 2010 help to speed up the process of retrieving, sorting, and filtering data in PivotTables and Excel tables. In addition, opening and saving large files is generally faster than before.
Faster calculation If workbooks are critical to key business processes in your organization, it’s important that calculation speed doesn’t become a bottleneck. To achieve faster calculation performance, Excel 2010 includes support for asynchronous user-defined functions, which can run simultaneously without using multiple Excel calculation threads. This is valuable when you are importing data into worksheets in custom ways, and in high-performance computing (HPC) scenarios.
Create workbooks with more visual impact
No matter how much data you work with, it's important to have tools at your disposal that let you explore and convey ideas with compelling visuals, such as charts, diagrams, pictures, or screenshots.
It's easier to work with charts in Excel 2010. Specific improvements include:
New charting limits In Excel 2010, the limitation on the number of data points that can be created on a chart has been removed. The number of data points is limited only by available memory. This enables people—particularly those of you in the scientific community—to more effectively visualize and analyze large sets of data.
Quick access to formatting options In Excel 2010, you can instantly access formatting options by double-clicking a chart element.
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. In Excel 2010, however, you can use the macro recorder to record formatting changes to charts and other objects.
Support for equations
You can use the new equation editing tools in Excel 2010 to insert common mathematical equations into your worksheets or to build up your own equations by using a library of math symbols. You can also insert new equations inside of text boxes and other shapes. To get started, on the Insert tab, in the Symbols group, click the arrow next to Equation.
In Excel 2010, there are more themes and styles than ever before. These elements can help you apply professional designs consistently across your workbooks and other Microsoft Office documents. Once you select a theme, Excel 2010 does the design work. Text, charts, graphics, tables, and drawing objects all change to reflect the theme you have selected, so that all elements in your workbook visually complement one another.
Paste with live preview
The paste with live preview feature enables you to save time when reusing content within Excel 2010 or across other programs. You can use it to preview various paste options, such as Keep Source Column Widths, No Borders, or Keep Source Formatting. The live preview enables you to visually determine how your pasted content will look before you actually paste it in the worksheet. When you move your pointer over Paste Options to preview results, you'll see a menu containing items that change contextually to best fit the content you are reusing. ScreenTips provide additional information to help you make the right decision.
Improved picture-editing tools
Communicating ideas in Excel 2010 isn't always about showing numbers or charts. If you want to use photos, drawings, or SmartArt to communicate visually, you can take advantage of the following features:
Screenshots Quickly take a screenshot and add it to your workbook, and then use the tools on the Picture Tools tab to edit and improve the screenshot. Read more about screenshots.
New SmartArt graphic layouts With new picture layouts, you can tell your story with photographs. For example, use the Captioned Picture layout to show pictures with nice-looking captions underneath. Read more about SmartArt graphics.
Picture corrections Fine tune the color of a picture, or adjust its brightness, contrast, or sharpness—all without having to use additional photo-editing software. Read more about correcting pictures.
New and improved artistic effects Apply different artistic effects to your picture to make it look more like a sketch, drawing, or painting. New artistic effects include Pencil Sketch, Line Drawing, Watercolor Sponge, Mosaic Bubbles, Glass, Pastels Smooth, Plastic Wrap, Photocopy, Paint Strokes, and many more. Read more about adding effects.
Better compression and cropping You now have better control of the image quality and compression tradeoffs, so that you can make the right choice for the medium (print, screen, e-mail) that your workbook will be used for. Read more about cropping and picture compression.
Collaborate on workbooks in new ways
Excel 2010 offers improved ways to publish, edit, and share workbooks with other people in your organization.
With Excel Online, which is part of Office Online, it’s now possible for different people to edit a workbook at the same time from different locations. If you’re in a small company or working on your own from home or school, all you need is a free Windows Live account to simultaneously author workbooks with others. Corporate users in companies running Microsoft SharePoint 2010 technology can also use this functionality within their firewall.
Improved Excel Services
If your organization previously used Excel Services to share Excel workbooks on SharePoint Server sites, take note of the following improvements:
Improved user experience Some of the more visible changes include the ability to refresh elements of a page, instead of having every change require a page refresh, and the addition of scroll bars, which let you easily scroll throughout the worksheet.
Better integration with SharePoint 2010 features In this version of Excel Services, you get better integration with important SharePoint 2010 features, including security, content management, version control, data connection management, and service administration features. In addition, Excel Services better integrates with the built-in business intelligence capabilities in SharePoint.
Improved support for workbook features Previously, if a workbook contained unsupported features, it couldn't be opened at all in the browser. Now, if a workbook contains an Excel feature that isn't supported, that workbook will, in most cases, open in the browser. In addition, more Excel features are supported in Excel Services, including new Excel 2010 features such as sparklines and slicers.
More support for developing applications Developers and non-developers alike can take advantage of new tools, such as a REST application programming interface, for building business applications.
The new Accessibility Checker tool in Excel 2010 enables you to find and fix issues that can make it difficult for people with disabilities to read or interact with your workbook. You can open the Accessibility Checker by clicking the File tab, clicking Check for Issues, and then clicking Check Accessibility. Errors and warnings will appear in a task pane. You can then review the issues and see which ones you need to fix.
In addition to the Accessibility Checker, you can add alternative text to more objects in your worksheet, including Excel tables and PivotTables. This information is useful to people with visual impairments who may be unable to easily or fully see the object.
Improved language tools
In the Excel Options dialog box, multilingual users can quickly set preferences for editing, display, ScreenTip, and Help languages. And, changing your language settings in Excel automatically changes them across all applicable Microsoft Office 2010 applications. If you don't have the software or keyboard layout installed that you need, you are notified, and links are provided to make it easier to quickly resolve such issues.
Extend workbooks in new ways
If you develop custom workbook solutions, you can take advantage of new ways to extend those solutions.
Improved programmability features
Improvements for developers include:
Changes to the XLL SDK The XLL Software Development Kit (SDK) now supports calling new worksheet functions, developing asynchronous user-defined functions, developing cluster-safe user-defined functions that can be offloaded to a compute cluster, and building 64-bit XLL add-ins.
VBA improvements Excel 2010 has a number of features that will enable you to migrate any remaining Excel 4.0 macros you may have to VBA. Improvements include better performance for print-related methods and chart properties not previously accessible with VBA.
Better user-interface extensibility If you develop custom workbook solutions, you have more options for programmatically customizing both the ribbon and the new Backstage view. For example, you can programmatically activate tabs on the ribbon, and make custom tabs behave similarly to built-in contextual tabs, where tabs only appear when specific events occur. In addition, you can make custom ribbon groups grow and shrink as the ribbon is resized and customize context menus with rich controls. You can also add custom UI and other elements to the Backstage view.
Changes to the Open XML SDK The Open XML SDK 2.0 now supports schema-level objects, in addition to the part-level support introduced in the Open XML SDK 1.0. This makes it easier to programmatically manipulate workbooks and other documents outside the Office 2010 desktop applications—for example, as part of a server-based solution.
Support for high-performance computing
Many organizations rely on high-performance computing (HPC) clusters to increase computational scale. For example, a financial firm might use compute clusters to speed up long-running, calculation-intensive financial models. Excel 2010 includes the ability to integrate with HPC clusters. When a supported cluster is available, users can instruct Excel to use that cluster by selecting a connector and configuring a name to use in the Advanced options of the Excel Options dialog box.