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.
TV says
“Feature: Drag and drop data
…
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.”
I just wish Paste Special was a keyboard shortcut or on the right click menu. I suppose I could add that functionality with VBA, but it’d be nice if it were default.
Jon Peltier says
TV –
My custom toolbar in Excel 2003 has a Paste Special button front and center, right next to the ore specific ones, Paste Formulas, Paste Transposed Values, Paste Formats, and the Format Painter.
I suppose you could add Paste Special to your QAT. I’m reluctant to add anything to my QAT, because once I do, I’ll want to add everything, and the QAT will be four monitors wide. I know I should pick the 27 or so most important items to put on the QAT, but those 27 iteems change from day to day.
Perhaps the biggest mistake Microsoft made with Excel 2007 was violating the maxim: Don’t ever reduce functionality and flexibility with an “upgrade”.
Tim Mayes says
Jon, thanks for this. The bit on drag and drop was one thing that I was missing. It never occurred to me to try paste special. How dumb am I? (That is a rhetorical question. :-) ) This is a big help. Much easier than going through Select Data.
Mike Alexander says
TV:
“I just wish Paste Special was a keyboard shortcut or on the right click menu. ”
Check out this posting on DailyDose. It talks about a technique that lets you get to PasteSpecial Values with a right-mouse-click.
http://www.dailydoseofexcel.com/archives/2005/10/15/mouse-shortcuts/
Jon Peltier says
Mike –
There are many tricks we’ve learned in Classic versions of Excel that still work in Excel 2007. I guess we’d classify them as things that were not changed, for better or worse, in Excel 2007.
osenkov says
Direct manipulation of data points on charts (and change their source values on the worksheet) was a very useful feature in earlier versions of Excel. I would wish this feature be restored in new Excel versions.
I (and my co-workers) have used this feature in many different occasions. These are:
(1) shifting a whole graph along Y-axis to a certain position by dragging only one data point (when the data points are interdependent);
(2) quick elimination of large spikes in a chart containing millions of data points;
(3) connecting several Y(x) spectra obtained in different X-regions and having different baselines (quick baseline removal);
(4) quick determination of a slop value at a certain location of the graph (by drawing and manual shifing a two-point straight line);
and many-many other uses.
I really missed this feature in Excel 2007.
sam says
“Perhaps the biggest mistake Microsoft made with Excel 2007 was violating the maxim: Don’t ever reduce functionality and flexibility with an “upgrade”. ”
The maxim that they violated was “they tried to fix something that was not broken (the UI) ” thanks to the overwhelming feedback they received from “real” users
diti says
I wonder if Microsoft has by now created some add-in to enable the
dragging of individual points on a Excel 2007 graph ??
I just can’t understand why this VERY useful capability was removed from
Excel 2007 !
I still keep Excel 2003 in my home PC for that reason alone !
Very often one has to extract data from plots in published scientific papers where numerical data is not directly available.
I have often used the “GrabIt” add-in for first readings, but a more accurate approach to the original plot will usually require further refinement. The capability of dragging the points on the plots is then most useful to achieve this refinement, see ?
Sometimes I even skip the “GrabIt” step :
I do create a a straight line with points at regular intervals, and then just drag the points onto the original curve from the paper!
Thank you for any information !
Teresa
Jon Peltier says
Hi Teresa –
I saw your post in the newsgroup, and what I said was:
“Microsoft decided for whatever reason that this capability was not important, so it was removed. It is therefore unlikely that Microsoft would issue an add-in to recreate this capability, and in fact, they have issued no such add-in. Perhaps they have heard the outcry and will revive the feature for the next version of Excel.”
In the meantime, I’m working on a utility that simulates this functionality. I’m only in the earliest stages of development, and it’s a nights-and-weekends kind of effort. So I won’t have anything ready too soon.
Wes says
I heartily agree with your comment about the lost macro recording capability. That was such a quick and easy way to find needed objects and properties.
I miss the ability to use shortcut keys (cut, paste, home, end) in many of the text boxes in the chart dialogs.
How can I get the address of the range of values in a chart? If I use series.values and series.xvalues, I get the numerical values, not the range address.
Jon Peltier says
John Walkenbach has developed a class module that can be used to extract this information from the chart series formula:
A Class Module To Manipulate A Chart Series
Graham says
Like several other posters, I’m lamenting the demise of the feature allowing direct manipulation of data points.
Microsoft appear to view this as a “seldom used feature” but they clearly did not consult with one of the world’s largest oil companies who use the feature extensively in a range of critical spreadsheets for determining officially-reported oil and gas reserves. The feature allows production decline curves to be easily manipulated (by shifting the end points of the curves) so that the curves fit through historic plotted production vs time data. When the curves are adjusted all of the associated reserves calculations are automatically updated.
I, and other colleagues, regularly write spreadsheets using this data fitting technique to solve a range of related technical engineering problems and we’re going to be lost without it. The company hasn’t moved to Office 2007 yet but when they do we’ll be crying out for the utility you’ve mentioned that you have started to work on. I hope you finish it.
Great website by the way. Always my first port of call for Excel-related queries.
Jon Peltier says
Graham –
I don’t know what led to many of the design changes in 2007. I suspect a lot was having bitten off more than they could chew, and leaving half eaten features in the release.
I haven’t had time to work on the Goal Seek replacement feature for a while, but I’m planning a week or two of non-consulting, so I can fix up the blog and build some more utilities. I have much less time than ideas.
Jeffrey Weir says
Building on Wes’ comment above, magine for a moment that you’re COMPLETELY NEW to VBA, and that you want to find out something simple, like how to move a simple connector .
You start up the recorder, select the connector, and drag an end somewhere new, to see how the ‘TOP’ property works. You don’t yet know that it’s called the ‘TOP’ property…that’s why you are firing up the macro recorder in the first place.
Then you go look at the blank macro you just recorded, and scratch your head. You do this several times. You record a change to a range instead of a shape, just to make sure the macro recorder is working (it is). You restart your PC to see if that makes a difference (it doesn’t).
You waste another half an hour on the help system…maybe because you dont know how to USE the help system effectively yet; maybe because the help system SUCKS. But you don’t know which ‘maybe’ is the major factor in your time wasting, because you don’t know what you don’t know. Although you suspect the latter.
You give up trying to learn by doing, and go surf the haystack of needles that is the internet, until you FINALLY find a great resource like this one that tells you “You sure picked a tough time to learn how to manipulate shapes boy, cause MS just FIRED THE TEACHER.
This is outrageous. I bought a PC mainly because Mac didnt have VBA, and this is my reward? Crap!
Yes, most VBA questions can be answered on the internet given a little time and good question phrasing. Therein lies the damn problem…
damselfish says
I am still (happily) using 2003, but I sure wish I could find the multiple chart resize functionality you mentioned. I have googled and googled to no avail, pored over my Que book, and I can’t find anything that tells me how it was done, only that it’s gone in 2007.
If you read this, can you take pity on a 2003 user and tell me how to do it?
Thanks!
Jon Peltier says
You mean select multiple charts and resize by dragging on the handles of one of them? It works in 2007 and in classic Excel. You don’t think it’s going to work, because the cursor doesn’t change to indicate it’s working, but keep clicking and dragging.
damselfish says
Well, I thought that’s what I was doing, but your comment is helpful in that now I know for sure I shouldn’t be hunting for some exotic command hidden in the menus.
I will endeavor to persevere & no doubt I will get it done.
Thank you!
Anna says
Hi Jon
Can you tell me how (or if) I can put my chart templates into a toolbar or the ribbon in Excel 2007? Prefarable text not the unrecognisable chart symbols used in templates.
I have about 20 customised charts that I use extensively and the route to these is tortuous – Insert, Other charts, templates, then hover over each unhelpful symbol of a chart to find the one I want. When you have a report with a hundred charts in it to produce to a tight deadline anything that can speed things up is highly desired.
I recently moved from 2003 and am finding it a real pain. I seem to be one of the few people that used customised charts a lot.
Any suggestions would be gratefully received.
Jon Peltier says
Anna –
Off the top of my head, I can suggest you use template names which are as descriptive as possible. I can’t think of an easy way to keep your favorite templates anywhere in the ribbon. Now, if you want to learn some RibbonXML, you could put together something useful.
Anna says
Thanks Jon. I was afraid you would say something like that. I am seriously considering reverting to Excel 2003. If I ever have any time where I don’t have imminent deadlines I will consider looking at Ribbonxml.
Why on earth did MS remove so many good things from 2003! I have lots of bugbears with it, and one that is causing me a lot of time at the moment is making sure that the chart I design (which looks wonderful on the screen) looks somewhere near respectable and acceptable when it is printed out on a basic colour laser printer which cannot cope with all the shading and fancy stuff. For instance, brilliant orange looks muddy brown. Also, with the drive for economy, more and more of my clients print in mono so any design has to work for that as well.
Andreas says
Direct manipulation of data points on charts – a great functionality. Removing it – not so great.
And I can only agree with you Jon: “Perhaps the biggest mistake Microsoft made with Excel 2007 was violating the maxim: Don’t ever reduce functionality and flexibility with an “upgrade”.”
Now I think that a bunch of open source alternatives have the edge, even if one has to do a little programming before up and running…
Harry says
Direct manipulation of datapoints is a function that engineers and scientists need for calibrating and smoothen data, adjust models and optimise systems.
I’m pretty sure that ANY engineering company in the world, using excel, is significantly affected by removing this feature. Most must be wondering what got into their heads at microsoft.
For an engineer there is no really useful new feature in excel 2007. There is STILL no real 3D-chart function that graphs data correctly respecting axis spacings that are irregular…and still no decent 2D or 3D table interpolation function…
Probably they only asked the financial world for input to get to what 2007 is…I must use matlab now to do my work…
Hope that they release a special engineering version for the next release that would be a big step forward!
Roger says
I would like to resize a chart to fill up a printed page by decreasing the page margins and adjusting scaling. Am I missing something? Why isn’t this an option. Is there a work around?
Jon Peltier says
Roger –
I used to do this routinely in Excel 2003, but it doesn’t work the same in 2007, and I haven’t had time (or motivation, really) to figure it out.
Grzegorz Piotrowski says
I also struggle with the XY Scalar charts created in E2003 that on opening them in E2010 change their size. The dots in chart were replaced with autoshapes (arrows). Now some of them have original size in 2010 and some not. What is most frustrating about it is lack of consequence. I tried everything to no avail. Looks like the plot area in 2010 changes sometimes for no reason. In E2003 everything all arrows are identical and in 2010 they differ.
Has anyone come across similar issue?
ChemEngr Mike says
If Microsoft reckons that the ability to directly manipulate of data points is a rarely used feature they cannot have consulted many engineers. I too use this feature a lot for turning all sorts of graphical data into numerical so I can use the trend line equation for further calculations. I’ll be lost when my company decides to “upgrade” from 2003.
John says
I have reviewed several HELP sites and not found a command to get spreadsheet data onto a chart (other than the data SELECTED). I used to be able to click on a vacant point on a chart and type “=” followed by clicking on a cell in a spreadsheet; the data in the cell (text or number) would show up on the chart. That doesn’t work in Excel 2010. Have I missed an optional way to do this?
Jon Peltier says
John –
Do you mean you want to add a label linked to a cell? In Excel 2003 and earlier you just had to select the chart and start typing, but too many people had random things typed onto their charts without knowing why. In Excel 2007, it became necessary to insert a text box first. Select the text box (so the cursor is not blinking in it), type =, then click on the cell. This works with the chart title, axis titles, and individual data labels, as well. See Link Chart Text to a Cell for a tutorial on this topic.
John says
Thanks a LOT; just a few different strokes, but it works!
don barrett says
Excellent article.
Very old post, but I’m wondering. Did MS ever replace the missing “Resize” in any of their later versions.
I have been sticking to 2003 for so many years because of things like this.
I use VBA to make all kinds of chart changes. Ranges, Sizes, Add/remove sets, Magnify,,,
Please tell me VBA isn’t going away as I hear!
Thanks
Don
Jon Peltier says
Hi Don –
Remind me what “Resize” is.
And no, VBA isn’t going anywhere. The global economy is driven by billions of lines of VBA. As much as Microsoft would like us all to move to newer platforms, they couldn’t afford to drop VBA support.
Don Barrett says
No Worries.
This is from your article: Which listed all the lost 2003 functionality.
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.
I agree:
“woefully inadequate substitute for Size with Window”
A short followup article of the replaced ones over the many years/versions since would be great.
Thanks for the quick response and all the help you have provided so many users over the years!
Don