What’s New in Office 2010: Excel

By now most of you have installed a copy of Office 2010 and if you were previously using Office XP or Office 2003 as many were then you are in for a real treat. There are so many changes from those versions that it’s not possible to list them. So what I’m going to do is just post what’s brand new and then point to some quick and easy training courses at the end.

Here’s what brand new for Excel:

Backstage view -The new Backstage view replaces the traditional File menu. All file management tasks are available in Backstage. 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 Microsoft Office Backstage.

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. Click here to see what they look like.

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. Click here to see what they look like.

Excel 2010 64-bit advantages - Applications built with the 64-bit version simply can use more physical memory than ever, especially important for those who need 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.

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 others 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 simply 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.

PivotTable enhancements - PivotTables are easier and faster to use in Excel 2010. The description of those changes are too lengthy to recount here.

Microsoft SQL Server PowerPivot for Excel add-in - If you need 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, including corporate databases, worksheets, reports, and data feeds.

Calculation Improvements – new version of solver, 50 new statistical functions

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.

Excel Services enhancements - Many organizations use Excel Services to share workbooks and data with other people.. 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.

Conditional formatting - Conditional formatting allows references to 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 allow in conditional formatting rules. Conditional formatting stores dependencies of the formulas 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.

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.

Data bars -Data bars now drawn proportionally according to their values. Negative values more clearly displayed and zero values are suppressed.

Pattern fills - Pattern fills removed in 2007 have been reintroduced in Excel 2010. Charts formatted with pattern fills in previous versions of Excel will retain and display the pattern fills when they are opened in Office Excel 2007.

Charting - 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

High Performance Computing Clusters – which in english means if you’re doing a huge amount of computing you can assign a group of computers to process the data and get it done faster.

Macro recording support for chart elements – even more places to automate Excel repetitive activities

So now that you’re overwhelmed with just the list of what’s new, never mind what’s changed, how about a little training? Microsoft has free bite-sized training bits available online. Here are two that you should make the time for right away.

Here’s a 30 minute short course on getting up to speed on the new version of Excel. http://office.microsoft.com/en-us/excel-help/make-the-switch-to-excel-2010-RZ101809963.aspx

A 15 minute course on new security features in Office 2010. You’re going to need to know how to use these for all parts of Office. http://office.microsoft.com/en-us/excel-help/office-2010-security-protecting-your-files-RZ101665538.aspx