Users won’t see too much change from Excel 2007 to Excel 2010. But even though the majority of features are carried over, a few new twists have been added here and there. To give number crunchers an idea of what’s coming, this post will look at some of the features I have found while using Microsoft Office Excel 2010.

Note: This article is also available as a PDF download.

64-bit support

All the Office 2010 apps offer 64-bit support. I am including it here because it will particularly benefit Excel users, enabling them to create worksheets up to 4GB in size. In previous releases, such large workbooks were not the most reliable and tended to crash a lot. Excel 2010’s 64-bit support gets past this limit.

In my own use of Excel, I have yet to find a need for a spreadsheet that gets anywhere near the 4GB range. But for those who do most of their accounting or planning in Excel, this might be a top end feature.

Protected View for downloaded items

In business today, spreadsheets live on the network and very rarely on anyone’s desktop, which is good for disaster recovery planning and backups and things like that. However, depending on how clean and malware-free your environment is, some Excel macros could have viruses or other bad things in them. This is also true of items pulled in from the Internet. Microsoft added a feature to Office 2010 that prevents editing of downloaded content without your acknowledgement. To enable editing, you will have to click the button shown in Figure A.

Figure A

Protected View for downloaded content

Once you click the Enable Editing button, the Excel sheet is fully active (external data sources notwithstanding).

Ribbon customization

The Ribbon in all the Office 2010 applications is more customizable than in Office 2007, allowing you to move and add controls to suit your needs. The customization tools are accessible by right-clicking a tab on the Ribbon and choosing Customize The Ribbon. Figure B shows the Excel Options dialog with a new group added to the Home tab.

Figure B

Adding groups to the Ribbon tabs is easy in Office 2010.

Slicers and sparklines

Working with pivot tables, in my experience, seems to run the average Excel user off because they do not see the immediate benefit and frontline customization options a pivot table can provide. Excel 2010 has made things a bit easier by introducing a feature called the slicer, which brings a snapshot view of a pivot table to the current worksheet.

Slicers let you review the data without having to manipulate the table. This can be useful when creating high-level documents where only a section of the pivot table needs to be shown. The slicer retrieves pivot table information, if found within the workbook, and displays it in a chart-style box for placement elsewhere. Figure C shows an example of a slicer.

Figure C

A pivot table slicer

In this example, the pivot table was created on the same worksheet as the slicers. The selected information in the month slicer highlights corresponding information in the other slicer and changed the pivot table to show the values selected.

Excel 2010 also introduces a feature called sparklines — tiny charts that fit into a cell. This allows you to create a graphical representation right next to the data itself.

For example, suppose that in September I sold 50 widgets, but in October I sold 500 widgets. To bring this to the attention of a group reviewing the sales numbers, I could use a sparkline chart to highlight the data within a single cell. Figure D shows the number of widgets sold each month. The sparkline chart depicting September vs. October sales is highlighted in red.

Figure D

Sparkline charting in Excel 2010

Macro bug fix

Although many of the Excel 2010 enhancements involve new or improved features, a few problems have been ironed out as well. For instance, Microsoft has corrected a macro recording issue involving shapes. In Excel 2007, if you resize a shape while recording a macro, the macro won’t do anything when you run it. If you look at the code, you’ll see why: The entire macro is blank. This now works in 2010, so you can record macros to automatically manipulate shapes.

Top improvements?

These features are just some of the standouts in the upcoming release of Excel. There are certainly others I haven’t covered here and probably some I haven’t used yet. I am curious to know if you have a favorite new feature. Have any of the enhancements been on your wish list? Which ones will you find most useful?