Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

Improved ribbon

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 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 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 2010. 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 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.
  • Add comments.

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.

Improved PivotTables

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. Watch 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.


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.

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.

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

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.

64-bit Excel

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 enhancements

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.

Improved charting

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. Read 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.

More themes

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.

  • 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.

  • 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.

  • 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.

  • 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.

Collaborate on workbooks in new ways

Excel 2010 offers improved ways to publish, edit, and share workbooks with other people in your organization.

Co-authoring workbooks

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.

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.

Accessibility Checker

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.

4673 questions
13 answers

Easiest way to open CSV with commas in Excel

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this: It's probably because when Excel thinks "comma-separated values", it actually searches for some other delimiter (I…
Borek Bernard
  • 14,048
  • 32
  • 81
  • 93
14 answers

Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

I'm trying to process some data in Excel. The data includes numeric account numbers and other long numeric strings (like cell phone MEIDs). I am not doing math operations on these strings, so I want Excel to treat them as plain text. Here is what is…
Michael Levy
  • 1,930
  • 6
  • 17
  • 22
9 answers

Excel - Prevent cell text from overflowing into next (empty) cell

When you have text in an Excel cell that is too long to be shown in the visible area of that cell, and the next cell on the right is empty, Excel lets the text be displayed in that next cell (and the next, and the next, as needed). I want to change…
  • 1,685
  • 3
  • 16
  • 22
16 answers

How to make Excel's "Auto Fit Row Height" feature actually auto fit the row height?

For every generation of Excel I can remember (including 2010, which I'm using now), Excel's "Auto Size Row" features sometimes fails to actually auto size a row when the cell contains wrapped text. When it works properly, all the text is revealed…
  • 3,951
  • 6
  • 31
  • 33
8 answers

How to remove the print preview lines in Excel 2010?

Somehow or other I have managed to switch on the print margin lines in Excel 2010. I cannot seem to turn them off. I went to Page Layout->Page Setup->Print Area->Clear Print Area but the lines remain on my screen. Any ideas to get rid of them?
Mark Allison
  • 1,275
  • 2
  • 15
  • 23
3 answers
9 answers

How to force Excel to open CSV files with data arranged in columns

I generate a CSV file with an extension .csv in which every piece of data in one line is separated with a comma: 1,2,3,4 1,2,3,4 The file is sent via email and I want that when a customer opens it she sees data arranged into columns. Is such thing…
  • 1,311
  • 3
  • 12
  • 18
2 answers

Excel - can I set a cell to equal a certain value, no matter what is typed in it?

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (call it A1) to to show a certain name (call it…
22 answers

Unable to get Excel to recognise date in column

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what. I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even…
  • 1,488
  • 4
  • 16
  • 28
3 answers

Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

Whenever you copy a cell or a range of cells in Excel (my version is 2010 on Windows 7), and then do something else like insert new sheet row or simply edit another cell, Excel removes the clipboard data. For me this seems to me so utterly stupid…
  • 694
  • 1
  • 5
  • 8
17 answers

Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel: How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over. I want to…
  • 2,029
  • 10
  • 32
  • 46
2 answers

Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries). How can I make Excel group all the distinct values together and then display them relative to each other? Say that there are 100…
6 answers

Ignore multiple "number stored as text" errors at once

I have a spreadsheet where a certain column has a lot of data which generates "number stored as text" errors. I want the numbers to be stored as text in this column for certain formatting purposes. Is there a way for me to quickly dismiss all…
  • 13,585
  • 44
  • 113
  • 181
10 answers

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller…
  • 1,719
  • 13
  • 30
  • 43
4 answers

Disable navigate to end of list when double-clicking on border of selected cell

I'm not sure whether this is a feature but it's very irritating. When you double click on a border of a selected cell, you go to the end of the list depending on which side of the selected cell you double clicked on. How do you disable this?
  • 559
  • 1
  • 5
  • 10
2 3
99 100