This article describes the new and
improved features in Microsoft Excel 2010.
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.
more about customizing the ribbon.
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.
more or watch
a video about Backstage view in Excel 2010.
Workbook management tools
Excel 2010 comes with tools that can help you manage, protect, and share your
- 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 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
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 Web App
Excel Web App extends your Excel experience to the web browser, where you can
work with workbooks directly on the site where the workbook is stored. Excel Web
App is part of Microsoft Office Web Apps, and is available in Windows Live
SkyDrive and in organizations that have configured Office Web Apps on SharePoint
With Excel Web App, you can:
- View a workbook in the
browser When you click on a workbook to open it
in Excel Web App, 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 Web App, 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 SkyDrive, 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
more about Excel Web App.
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
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 can create, update, and instantly recalculate your spreadsheets using
many of the same tools you already know and use in the desktop version of
- Use the outline view to switch between worksheets or charts in a
- Sort, filter, and manage your spreadsheets.
- Add or edit text and numbers.
- Add comments.
Read more about Office
Mobile 2010 for Windows Phone 7.
If you have Windows Phone 7, get
step-by-step help using your phone.
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.
more or watch
a video about sparklines.
PivotTables are now easier to use and more responsive. Key improvements
- 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.
a video about repeating item labels.
- 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. Watch
a video about changes to the Show Values As feature.
- 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. Watch
a video about using interactive controls in a PivotChart report.
more about changes to PivotTables.
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
more or watch
a video about slicers.
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
- 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.
more about conditional formatting.
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.
PowerPivot for Excel add-in
If you need to analyze large quantities of data, you can download the
Microsoft SQL Server PowerPivot for Excel add-in, which adds a PowerPivot tab to
the Excel ribbon.
With PowerPivot 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.
more about PowerPivot for Excel.
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
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
- 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
more or watch
a video about changes made to functions.
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
- 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.
more or watch
a video about creating an Excel 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.
more about the 64-bit version of Office 2010.
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.
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)
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
- 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.
more about creating a chart.
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.
more about applying themes to Excel workbooks.
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.
a video about previewing content before you paste it.
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
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 Web App, which is part of Office Web Apps, 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.
a video about collaborating on a worksheet in the browser.
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.
more about Excel Services.
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.
more or watch
a video about the Accessibility Checker.
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.
more about setting language preferences.
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
- 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.
more about features for developers.
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.
more about Excel 2010 and high-performance computing.