I came across an article on Microsoft’s web site, What happened to my favorite Excel 2003 Chart feature? Normally I just roll my eyes at these articles, but this one caught my attention, and I decided to address some of the items from the article. The article begins:

The improvements to charts in Office Excel 2007 were dramatic. You can now create professional-looking charts with special effects, such as realistic 3-D, transparency, and soft shadows.

I’ll leave aside (for now) discussions of professional looks and realistic 3-D. But many of the other items deserve attention.

The article says, “Because of these changes, certain features that worked one way in Excel 2003, work differently in Excel 2007 or require alternative approaches to create similar results. Here’s an up-to-date summary of those Excel 2003 features that work differently in Excel 2007 and how to get those similar results.” The article tabulates these differences in a table of features, 97-2003 behavior, and 2007 behavior. I will use a similar format, using portions of the article text to describe features and behavior in 97-2003 and 2007, while adding my comments for each feature.

Feature: Simultaneous resizing of multiple charts

97-2003: You can change the size of multiple charts simultaneously.

2007: You must change the size of each chart individually.

Jon: The second item on the page, and it’s wrong. You can select multiple charts and resize them together, either with the mouse or with the Size group on the Drawing Tools > Format ribbon tab. In fact, this is actually easier in 2007, because the cursor changes when you mouse over the resizing handles. In 2003 the cursor doesn’t change, so you have to take it on faith that your actions will resize the charts.

What is confusing about Excel 2007 is that there is no difference in the appearance of a single chart which has been activated to format its elements and one or more charts which have been shift-click or ctrl-click selected for formatting of the parent shape that contains the chart. In 2003 and earlier, when the chart is activated, small black handles appear on the corners and edges of the chart. When the chart’s container object is selected, small white handles appear on its corners and edges. In 2007, no matter whether the chart is activated or the chart’s container is selected, the same awkward, thick border is displayed.

Feature: Using pattern fills in chart elements (for black-and-white printing and to improve readability for the visually-impaired)

97-2003: You can use pattern fills in chart elements.

2007: Instead of pattern fills in chart elements, you can use picture and texture fills. Charts with pattern fills that were created in an earlier version of Excel appear the same when they are opened in Excel 2007, but you cannot use the pattern fills in other chart elements.

Jon: Patterns are still available in VBA, and there are already at least two add-ins to add back this capability. Andy Pope has a nice Pattern Fill Add-In which applies fill patterns to chart elements and to shapes.

Feature: Sizing charts with the window

97-2003: You can use the Size with window command to automatically resize charts that are located on chart sheets when you change the size of the window.

2007: Instead of the Size with window command, you can use the Zoom to Selection command to achieve similar results.

Jon: Losing Size with Window removes a lot of critical functionality, including the ability to show full-window charts, and the ability to use mouse-position-based chart events on chart sheets. Zoom to Selection is a woefully inadequate substitute for Size with Window. I have had clients who could no longer use previously successful utilities because of this change to Excel 2007.

Feature: Copying charts to Microsoft Office Word 2007 and Microsoft Office PowerPoint 2007

97-2003: By default, a copied chart is pasted as a picture in an Office Word 2007 document or Office PowerPoint 2007 presentation.

2007: By default, a copied chart is pasted in an Office Word 2007 document or Office PowerPoint 2007 presentation as an object that is linked to the Excel chart. You can change the way that a copied chart is pasted by clicking the Paste Options button that is displayed when you paste the chart, and then pasting the chart as a picture or an entire workbook.

Jon: In Excel 2007, the pasted chart is inaccessible to VBA, regardless of the Paste Option used. You can regain the familiar OLE behavior and access the inserted Excel object in VBA if you insert the chart from a file.

Feature: Direct manipulation of data points on charts

97-2003: You can drag data points on a chart and change their source values on the worksheet.

2007: Dragging data points to change the source values on the worksheet is no longer supported.

Jon: I rarely used this feature, so I didn’t think it was a big deal to have it deprecated. I have to admit being surprised by how many users miss this feature.

Feature: Drag and drop data

97-2003: You can add data to a chart by selecting the data in the worksheet and dragging it onto the chart.

2007: You can no longer drag data from a worksheet to a chart. You can use other methods to add data to a chart.

Jon: This is no loss. It has always been more reliable to copy the data and use Paste Special to add the data to the chart. This still works fine in Excel 2007.

Feature: Built-in custom chart types

97-2003: Several built-in custom chart types are installed with Excel.

2007: There are no built-in custom chart types, however, you can easily create your own custom chart types, by saving your favorite charts as chart templates.

Jon: “Built-in custom”, one of my favorite oxymorons. The absence of these chart types is no big loss. In earlier versions of Excel, users discovered these types, and presumed that combination charts were limited to whatever they could find in this part of the Chart Type dialog. In general, it’s better in Excel 2003 to ignore these chart types, and make your own combination charts, because the custom built-in types may behave in unexpected ways when you change the chart’s source data range.

In fact, the absence of these chart types is a positive thing. Microsoft has changed the way users can save custom chart formatting. The Excel 2003 approach stored all of a user’s custom charts in a hidden user gallery workbook. This approach makes it difficult to share custom charts among users, and I’ve found this gallery workbook to be prone to corruption. The Excel 2007 technique saves each custom chart as a separate chart template file. These are more reliable, and can be shared between users (and I believe between Office applications).

Feature: Grouping and selecting shapes

97-2003: Shapes are drawn in a format that differs from the shape format that is used in Excel 2007.

2007: Shapes that are drawn in earlier versions of Excel cannot be grouped with shapes that are drawn in Excel 2007, even when the shapes are upgraded to the current version. You cannot select shapes that are created in different versions of Excel at the same time. Shapes that are created in different versions of Excel are layered on top of current shapes. To select the current charts, use the chart element selection box (Chart Tools, Format tab, Current Selection group). To select hidden charts or shapes that were created in an earlier version of Excel, you must first add the Select Multiple Objects command to the Quick Access Toolbar.

JP: Not only are shapes from different generations of Excel treated differently for grouping, they are also placed in different Z order positions. Adding a shape or picture, followed by

Set MyShape = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
 

may return a different shape than expected, breaking a lot of legacy code.

Feature: Record chart shapes and shape formatting in a macro

97-2003: You can record changes to chart shapes and formatting in a macro.

2007: You cannot record changes to chart shapes and formatting in a macro.

Jon: This is a severe loss in functionality. PowerPoint 2007 has it even worse: it has lost its macro recorder altogether.

Microsoft knowledge base article 937620, You cannot record new shapes, shape formatting, and shape effects by using the macro recorder in Excel 2007, states that “This behavior is by design.” I suspect it’s more a matter of triage, redeploying scarce resources to prepare Office 2007 for Release to Manufacturing. I can’t believe anyone would consciously redesign a serious product in this way.

The article suggests, “To work around this behavior, use Microsoft Visual Basic for Applications (VBA) code to format the shapes. In Visual Basic Editor, click Object Browser on the View menu to find the correct objects, methods, and properties that are used in VBA code.”

Hello? Has anybody tried to decipher the new object model branch related to formatting of the new shapes and chart elements? At least Young and Champollion had the Rosetta Stone. I find this new content in the Object Browser inscrutible and the examples less than useless. In the past, when the Object Browser was hard to interpret, one could at least record a macro to help resolve the syntax behind the object model.

Extended Summary

There have been a lot of changes to the charting infrastructure in Excel 2007. The highly touted formatting features are not an improvement, but a distraction (see Steven Few’s review in Excel’s New Charting Engine — Preview of an Opportunity Missed). I believe that these gaudy formatting options are partly responsible for the order-of-magnitude increase in chart redraw times (see Excel 2007 Chart Performance - Revisited).

A few of the changes to Excel 2007 charts are really an improvement (the new custom chart template feature), or at worst, neutral (built-in custom chart types, drag and drop data, resizing of multiple charts). The over-antialiasing of Excel 2007, which I have covered elsewhere, gets mixed reviews. I give it a thumbs down, but some respected commentors like it. A related issue, which I have not covered in detail but the PowerPoint FAQ website and several online forums have, is that a metafile produced from an Excel 2007 chart cannot be broken down into its constituent shapes the way an Excel 2003 chart can be.

A few of the changes to Excel 2007’s charts are actually detrimental. The way new and old shapes are treated, and the way pasted charts are accommodated in PowerPoint and Word, cause problems. Many users are hobbled by the removal of the ability to drag points on a chart to change the underlying data. Removing UI access to pattern fills was a small decrease in capabilities.

Two features covered here are particularly unfortunate. Losing the ability to size a chart with the active window was a surprising change, and has broken a number of existing routines. The inability to record formatting actions in a macro ties the hands of programmers who need to understand the changes to the object model, and users who are just trying to make their lives easier.

Removal of the familiar menu/toolbar user interface in favor of a new ribbon/tab interface has drawn a great deal of criticism. In principle the ribbon interface should be an improvement, but experienced users have had to retrain themselves in tasks that shouldn’t have to be relearned. A detriment of the new interface is the much lower density of controls visible at any time, and the inability to change the interface through the interface. (Yeah, I know, we have the QAT. But seriously.) Worse than the confusing triad of contextual chart tabs is the redesigned set of chart-related dialogs, which I covered in some detail in Changes to Charting in Excel 2007. And I wonder why it was decided to no longer open an object’s formatting dialog when that object is double clicked on, breaking well over a decade of user interface convention. The unintended consequences of these UI changes have made me much less productive when working with charts in Excel 2007.

Possibly Related Posts:

• PTS Charting Classes
• A Belated Review of Excel 2007
• Forms Controls and ActiveX Controls in Excel
• How To: Assign a Macro to a Toolbar or Menu
• Changes to Charting in Excel 2007
• Error Bars in Excel 2007
• Simple Waterfall Chart with Up-Down Bars
• Weekend Fun: New iGoogle Interface Unpopular