Software optimize

Microsoft Excel 2010 introduces some solid enhancements

Like the other Office 2010 apps, Excel doesn't offer any radical changes. But you'll find welcome improvements to existing features and a few cool new tools.

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?

About

Derek Schauland has been tinkering with Windows systems since 1997. He has supported Windows NT 4, worked phone support for an ISP, and is currently the IT Manager for a manufacturing company in Wisconsin.

7 comments
pmclornan
pmclornan

I agree that 4GB spreadsheets have limited value. But - the fact that Excel will be running a 64 bit process will be invaluable to those running heavy libraries in XLLs (or UDFs). We very quickly hit a ceiling when running MC simulations with our XLL because of the current 32 bit process limitation.

simonmb
simonmb

good day i have macro on excel 2007 i have installed office 2010 beta and windows 7 64 bit . i have encountred problem to run the macro how can i solve this problem please answer to bensimon.moshe@gmail.com

jolyaa
jolyaa

hi i be very happy if you call me for job

cthach
cthach

Will there be a fix for the too many styles bug? We have users who copy some data from one workbook to another and it adds a bunch of duplicate styles. After a few days the number of styles gets close to or over the 4000 styles limit and ALL the formatting on the whole workbook is lost!

TheProfessorDan
TheProfessorDan

I teach Office 2007 at a local community college and to be honest, I like it. I think Microsoft's decision to essentially tweak Office is wise. It is amazing how often a company is doing something right and makes the decision to change for the sake of change and creates all new unneccasary problems.

godzhesas
godzhesas

Indeed the enhancements looks worthwhile! Slicers and sparklines i believe should have a major success. Also i am currently using a flex based pivot table - http://www.flexmonster.com

lightft
lightft

I like what is mentioned in the article, and I am sure a new version is a great MS sales tool, but getting what we already have to work right is a needed business tool. At least since Office XP I and others have been unable to get past occasionally unavailable links between Word and an embedded XL spreadsheet pane. I say occasionally because we get messages varying from "cannot perform "x" because Excel is not installed" (even while it is running fine in another window) to "auto-recover has been disabled for this session of Excel" to an Excel-headered message window stating(while entering data in the spreadsheet) "the file could not be accessed... " and telling me the path name is bad. I also love having data I just entered disappear when I click outside the spreadsheet pane but re-appear when I click back inside the pane. The best one is that if I get a message that the link to Excel is broken because Excel is not installed on the system, and I just ignore it and get a cup of coffee, when I come back everything is fixed and it works fine again. However, if I am foolish enough to enter anything into the document while or soon after the warning is displayed, the spreadsheet changes to an un-editable picture. All the usual tricks - running with /a, safe mode, changing normal.dot, doing a repair, deleting the appropriate registry keys, have no effect. Multiple machines, Office XP, 2003 and 2007; Win XP, 7 Beta and 7 RTM. This is NOT a rant, but a plea in hopes that someone hears or knows of a solution. I have tried lots of boards, and often hear from fellow sufferers, but so far no answers that work. Since this always happens while I am editing the Excel sheet from within Word, and never when I am working outside the Excel link, I figured that hoping it might be addressed in Excel 2010 was worth a shot.