As part of the development of Excel 2007, Microsoft completely rebuilt the Excel charting engine. During overall development of the Office 2007 user interface, all of the charting dialogs were redesigned, and many features were added to the Chart Tools contextual tabs of the Ribbon. Office 2007 has been out now for well over a year, and I’ve been using it in some capacity since the beta kicked off over a year before its commercial release. I’ve decided it’s about time I gave a coherent review, rather than post bits and pieces in various forums. I hope that after this much time has gone by, my comments will be seen as the well-thought analysis I’ve tried to make it, and not a knee-jerk Luddite reflex.
Perhaps this assessment will help converts to Excel 2007 who are having difficulty with the changed charting environment, and hopefully any criticisms and suggestions will be taken to heart by developers of future releases of Excel. There is also a companion post reviewing general changes between Classic Excel and Excel 2007.
Creating Charts
The first thing that one notices when creating a chart is that the Chart Wizard is missing. Many people found the wizard confusing at first, but charting itself is a large feature, and the Chart Wizard was a relatively elegant way to put a lot of options into an orderly process. The Chart Options dialog, which was step 3 of the Wizard as well as a stand-alone dialog in its own right, is also gone. The lack of the Wizard and Chart Options means that many features are accomplished differently than before. This is not necessarily bad, as long as the features are still readily available and easy to use. For example, the selection of charts is available from the Insert tab of th Ribbon.
Chart Formatting
The second thing one notices is that Microsoft has modernized the default formats of Excel 2007’s charts.
Excel 2003 Default Chart Formats
Excel 2007 Default Chart Formats
The new defaults look much better, without the murky gray plot area background and the dull blue and pink series colors.
Speaking of formatting, most of the upgrade to the charting engine for Excel 2007 is a vast expansion in the formatting ability of the various chart elements. Text can be formatted as WordArt, and elements have many options for fancy formatting, including gradients, transparency, edge effects, shadows, glows, and more. For the most part this amounts to eye candy, because these formatting features do not add to the actual portrayal of the data in the chart. Of what use are bevel and surface texture effects if your chart has 4-pixel wide markers? I suspect that the slow performance in redrawing of Excel 2007 charts is partially due to an overworked graphics rendering engine having to check whether to apply so many more possible formats in charts with dozens of series and thousands of points.
Ironically, a popular formatting option since Excel 95 or earlier (and from much earlier programs on the Mac) has been removed from Excel 2007. One can no longer apply patterned fills to chart elements through the extensive user interface. Fortunately the capability remains in Excel 2007 VBA, and my clever colleague Andy Pope has written an Excel 2007 Pattern Fill add-in which allows the user to apply these fills to chart elements and shapes.
Pattern Fills: Lost, then Found
Chart Types
Many people looked expectantly at Excel 2007 to provide new chart types. Box Plots perhaps, or Waterfall Charts. But these expectations were met with disappointment. Excel 2007 includes the same roster of standard charts as Excel 2003. Fortunately the workarounds that many advanced users have developed to build custom charts are functional in Excel 2007.
Users will notice that the “built-in custom” (love that oxymoron) chart types from Excel 2003 are no longer available. This is not a great loss, as most of these types were either regular charts with different formatting, or simple combination charts. An intermediate user should be able to create combination charts readily in any version of Excel, so the built-in custom combos are not necessary. They were helpful, in that they showed the novice users that combinations were possible, although many users thought the only combination types available were those in the built-in custom dialog.
Excel 2007 has introduced a new technique for saving user-defined chart styles. Excel 2003’s system involved the user gallery, a hidden worksheet that contained samples of saved charts; this system was difficult to maintain (but then, I’m tough on my systems), and it was nearly impossible to distribute one’s custom types. Excel 2007’s system consists of individual chart template files saved in a templates directory. I have not tested this new system extensively, but it seems robust, and sharing custom types is as easy as exchanging files.
Chart Dialogs
A lot of people complain about the Office 2007 Ribbon, and claim it makes them slower than in Excel 2003. This point is partly valid: while charting, for example, there are three separate tabs for editing the chart, and the user often needs an extra click simply to activate the tab with the desired controls. As frustrating as that may be, it’s only a small irritation. What bothers me more than the Ribbon is the redesigned Excel 2007 charting dialogs. There are a number of problems with these dialogs that degrade their usability. I will not bring up every problem, but I will use a few examples to show the loss of usability.
Multiplicity of Tabs
In many cases while formatting an Excel 2007 chart, one has to click from one tab to another to accomplish what required only a single tab in Excel 2003. To illustrate, let’s look at the Format Series dialogs from the two Excels, 2003 on the left and 2007 on the right.
 |
 |
To format a line or XY series in Excel 2003, all the controls needed are found on one tab of the dialog. To do the same in Excel 2007, one must visit up to four Marker tabs and two Line tabs. I understand this was necessary to accommodate all of the new formatting options included in Excel 2007. But, such details: line gradients and line styles for the border of a marker? A marker that’s most likely no wider than 5 points?
I’d like the option to pop up a Chart Dialog Lite, that gives me a simple set of applicable and appropriate formatting tools: line color and thickness, line style for the connecting lines but not for the marker border, and marker shape, size, and fill color, without gradient fills or any edge, surface, or lighting effects. Put a button on the side that brings up the full-features form. This would allow me to make effective charts, and format their series as quickly as before.
Choice of Controls
In many cases, the new dialogs use controls which are not as efficient to use as their older counterparts. I’ll use the Format Axis dialogs from Excel 200 (left) and 2007 (right) to illustrate this problem.
 |
 |
The Excel 2007 dialog combines axis scale formatting with axis tick formatting, which is pretty nice. However, the choice of dropdowns for the axis tick controls is far inferior to the Excel 2003 layout, which uses option buttons. Here are these sections of the dialog in closer detail:
One uses option buttons, or alternately a listbox, in order to show all options to the user. One selects a dropdown in contrast to save space on a crowded dialog. My interface design philosophy is to show all options to the user. I’ve been creating Excel charts for twelve or more years, I’m intimately familiar with these dialogs, and I still like to see all options before I select one. In fact, Microsoft issued a best practices white paper for Vista programmers (wish I could find the link), which urged designers always to use option buttons or listboxes rather than dropdowns, for this very reason. Using the dropdowns has certainly used less space than the option buttons, but this dialog has room for the option buttons. The problem with a dropdown is that one has to move the mouse and click on the control to expand it and see the options. This takes a mouse click, and the expanded control covers one or two controls below it. Then to select an option, the user has to drag the mouse and release it, or click again. Two clicks or a click and drag, instead of one click, times dozens of charts a day. And I find that if the options are hidden in the unexpanded dropdown controls, I tend to click back and forth as I mull my options.
Tortuous User-Dialog Interactions
Many of the easy interactions which we took for granted in Excel 2003 have been replaced by inflexible awkward interactions in 2007. I will illustrate this issue with the axis scale formatting dialogs in Excel 2003 (left) and 2007 (right).
 |
|
The Excel 2007 dialog above right shows an improvement to Excel’s logarithmic scale axes. In Excel 2003, a logarithmic axis could only use base 10 logs, and the axis min and max scale parameters had to be powers of ten, and tick labels would only appear at intermediate powers of ten. For example, an axis scale could span between 0.01 and 100, and have tick mark labels at 0.01, 0.1, 1.0, 10, and 100. Excel 2007 allows the user to select the base of the log scale, and the min and max of the axis need not be powers of that base. The labeling is still inflexible, spaced from the minimum scale by powers of the base.
In Excel 2003, people were confused by the Auto checkbox. Even though Auto generally means let the program do what it wants, people would set their desired values, then check the Auto box. When Excel ignored their entries, they would wonder why. Excel 2007 solved this problem by replacing the Auto checkbox with Auto and Fixed option buttons. The rest of this sub-dialog undoes that improvement. Here are closeups of the scale formatting sections of the dialogs:
In Excel 2003, the entry boxes are always enabled, even when the Auto boxes are checked. The user needs only one click, in the entry box, to change the value. Clicking on the Auto checkbox or its label moves focus to the entry box and selects the contents of the entry box, making it so quick and easy for the user to make adjustments. When the user changes a value, the Auto checkbox is cleared automatically.
In Excel 2007, an entry box is disabled until the Fixed option button is clicked. Clicking the option button does not move focus to the entry box, the user still has to click in the box for this. And because the click on the option button does not select the contents of the entry box, the user needs a click and drag or a double click, not just a simple click, to select the entire contents of the entry box.
As I mentioned at the outset, the Excel 2003 dialogs were elegant in their usefulness, and the redesigned dialogs ignore usability best practices.
Range Selection
The way ranges are selected in some dialogs has been changed. Let’s look at the Format Error Bars dialogs.
 |
 |
The controls for selecting custom error bar values in Excel 2003 are right on the same dialog. The equivalent controls for Excel 2007 are not located on the form, although there is room for them. Instead the user must click on the Specify Value button, which pops up a small form with the range selection controls.
The selection boxes are rather narrow, so it’s particularly difficult to read the reference. in addition, this small dialog is modal, so the larger dialog is locked in place while this dialog is present. If your desired range was obscured by the larger dialog, you have to close this small range selector, move th larger dialog, and call the range selection dialog back.
Options Removed from Dialogs
Several favorite items have been removed from the dialogs. Let’s revisit the Format Series dialogs from above.
|
|
Error Bars and Data Labels can be added and edited from the Format Series dialog in Excel 2003, but not in 2007. The Options tab contains other goodies, depending on chart type: High-Low Lines, Up-Down Bars, Drop Lines, Series Lines. These features are all available on the Excel 2007 Ribbon, but not in the dialogs. We may get used to this change, but not having these items available on the formatting dialog disrupts previously smooth thought patterns.
Dialog Behavior
There are two aspects of dialog behavior that have changed for the worse in Excel 2007. The first of these is a minor inconvenience, but still annoying. Since the first time I’ve used a mouse (remember MacDraw on the old Mac Plus?), I’ve been trained by dozens of applications to point at an object, then double click, and a dialog would appear to help me format that object. For some inscrutable reason, all of office 2007 has lost this double-click-to-format behavior. Users frequently ask whether they need to reinstall Excel 2007, because the double click isn’t working right. I always agree that it isn’t working right, but that’s by design.
The more egregious change at first seems like a good idea. The formatting dialogs have become non-modal, so that you can interact with the objects under the dialogs while they remain open. This appears to make the dialogs more flexible, but the consequences are brutal. Let me explain.
In Excel 2003 you can format an object, say, a chart axis. You can visit every tab of the formatting dialog, then dismiss the dialog, select another object of the same type, another chart’s axis in this example, then press the F4 function key, and repeat all of the formatting that was accomplished during the previous visit to the formatting dialog. One click of the F4 key repeated dozens of actions. Alternatively, pressing Ctrl-Z would undo everything that was done during the visit to the dialog.
In 2007, with the non-modal dialogs, items are added to the undo/repeat queue one action at a time, not one dialog visit at a time. To undo a series of items from one dialog visit, you need to press Ctrl-Z many times. But the killer is that the F4 key (or Ctrl-Y) only repeats the absolute last action taken in the dialog. One click of the F4 key now repeats one action. One of a user’s most powerful productivity tools, the F4-Repeat Last Action command, has been eviscerated.
Summary
This analysis has been written after over two and a half years of working with Excel 2007, starting with the last Excel 12 alpha version, through four betas, the ultimate release version, and SP1. It is a hard thought, critical assessment of charting in my favorite charting platform, and how charting has changed with the introduction of Excel 2007.
Charting in Microsoft Excel has undergone a complete restructuring in Excel 2007. Most of the changes are cosmetic, confined to formatting of the charts, and the default Excel charts look much nicer than in earlier versions. There is a lot of eye candy, that is, fancy effects that others may (and do) call chart junk. While no new chart types were introduced in Excel 2007, the techniques developed to simulate unsupported chart types are still functional in Excel 2007.
The Office 2007 Ribbon has received a great deal of criticism for dumbing down the interface and making it less efficient for use by power users. I have found the inefficiencies of the Ribbon to be minor, dwarfed by major inefficiencies in chart formatting dialogs introduced by disregard of best usability practices. Because of these deficiencies, I find myself often struggling to create charts in Excel 2007 which in Excel 2003 would practically draw themselves. I write this review partly in the hope that the points I’ve raised will help guide development of an improved charting experience in future versions of Excel.
Zach says
Jon,
I consider myself very good with computers in general, and pretty good with Excel as well. Perhaps it’s because I don’t spend TOO much time charting, but I’ve always found the interface for charting in Excel 2003 very confusing. I was never able to figure out the logic for why many options were placed in their respective locations, and therefore find myself checking a few places when looking for an option. I haven’t made the switch to 2007 yet, but I’m hoping there’s a more logical organization scheme.
Jon Peltier says
Zach –
The organization of the charting features in Excel 2003 isn’t completely logical, but it seemed easy to find what I needed. I may have placed certain things in different locations, but mostly I would have left it alone. The Excel 2007 interface seems to have taken so much away from the dialogs and placed it onto the Ribbon, which makes them seem further removed from the chart.
I’m in the process of designing a charting interface for Excel 2000 through 2007 with custom menu/toolbar or Ribbon elements, as well as dialogs of my own design, based about 60% on Classic Excel, 20% on Excel 2007, and 20% on my own personal wish list and usability observations. It’s a nights and weekends project, so it won’t be available next week, but hopefully over the summer.
John Dawson says
One point that I think you missed was that it is now more difficult to interact with chart data. In “old” Excel, you could pick up and drag chart objects to change inputs in a “what if” spreadsheet. I know at least three businesses who used this technique and are now cursing not being able to do the same.
I also find 2007 laborious when constructing charts. Yes they ultimately look better but maybe just a simple colour pallet change was all that was needed.
Jon Peltier says
Hi John –
Microsoft was surprised to see how many people used the Goal Seek (point dragging) feature, and I must admit, so was I. Many people miss this.
Another feature that was removed was the ability to size a chart sheet to fit the window. Without this feature, macros that are based on the user’s mouse motions NO LONGER WORK on chart sheets in Excel 2007. In addition, the ability to let a chart adjust to fit any user’s screen regardless of aspect ratio or resolution was very nice.
For colors in charts, you could check out an earlier blog post:
Using Colors in Excel Charts
This is more helpful for Excel 2003 and earlier, of course.
And of course there are more differences in Excel 2007’s charting (like the changes needed to add custom error bars using VBA without crashing). I suppose if people comment with their experiences, we can compile a list of differences that may help out other users.
Jon Peltier says
Tony –
To me, many of these issues seem minor, and seemed minor when I first reported them during beta testing. There were two things working against changing these issues. First, any change made to a program the size of Excel has to be carefully tested, because you never know what kind of dependencies you may be altering by an apparently simple change. For me to design a VBA utility that sits on top of Excel and provides custom dialogs is a minor undertaking compared to changing a few dialogs which are integral to the application. I have already designed some of the dialogs, by hte way.
Second, and I am making a statement about things I have no direct knowledge of, I suspect MS did not have time to complete a more finished version of the UI (the dialogs etc.) within the time that Office 2007 was scheduled to ship. I do not expect any changes to items I’ve mentioned here to be implemented to any service release of Office 2007. I am hoping MS will give these issues some attention in their design of Office 14.
Tony Rose says
Some of this feedback seems to be easily fixed with Excel 2007 (but I’m no MS programmer). You would think that a quick release or service pack would do the trick. Maybe they just haven’t gotten enough feedback. Or, it could be that they are well into the development of Office 14…
Jon Peltier says
At first, the adoption of 2007 was pretty slow. I expected this version to be another forgotten version, like 2002. But lately it seems to be moving along. While I still have a few clients on Excel 2000 and 2002, most are on 2003 and 2007. The thing is, new computers now come bundled with Vista and 2007, so that’s what people use. Even if they preferred the previous versions, they don’t have the licenses or the installation media to allow the older versions on their newer machines.
A client called me up two weeks ago and ranted for a half hour because he didn’t like his first taste of Excel 2007. He said on a whim he went out and bought it, thinking it would solve all the problems with his earlier installation, Excel 2000. He opened it up, couldn’t find anything, and was angered that such a widely used and familiar interface would have been tossed aside just like that.
Tony Rose says
I just wish they would do a little bit more work and due dilligence with industry experts on the new office versions (planning and not just beta testing) instead of trying to churn out a new one every 3 years.
I wonder why the adoption rate is for Office 2007? I know many companies are still using 2003. Maybe a lot of people are holding out for Office 14.
Perhaps you just can’t please everyone and 95% is good enough…
Tim says
Jon,
Nice post, and I can’t disagree with your points. I second John Dawson’s point about the Goal Seek feature. I used that quite often to get data exactly right for class presentations.
The two things that bug me the most about the new charting engine are that it is slow, and that I can’t seem to use defined names in the Series function like I used to. Some defined names seem to work, while others fail. For example, I can’t get it to accept a name that uses a dynamic range with the Offset function. Any ideas about that?
Jon Peltier says
Tim –
Defined names work pretty much as before in charts, with one notable exception. If the name begins with “chart”, it fails. There are a couple other glitches with names in charts, too. But I’ve used plenty of dynamic names in Excel 2007 charts.
I think that Excel 2007 is more strict about proper referencing of names than 2003 was; I don’t know for sure, because I’m usually compulsive about referencing things like that.
Tim says
Jon,
Well, that fixes that! I was, as I commonly did in the past, using the name ChartData. I guess I should never use obvious names. :-)
Thanks.
Jon Peltier says
Colin – Thanks for your thoughtful comments.
I would have reduced tab counts on the dialog by combining like tabs. All four marker tabs would be combined, and I’d use buttons to pop up child dialogs for special features. Ditto for lines. Putting everything onto separate tabs is just plain poor usability.
I didn’t talk about the galleries, probably because I never use them. I probably looked at them a few times, then moved away from them. I do almost all formatting from scratch, which makes the loss of F4 functionality a true handicap.
If it were up to me, I’d tear apart the existing chart contextual tabs and put the useful stuff onto the first tab (“Charts”), and maybe some of the other stuff on the second (“Chart Misc.”). I’d also add things back to dialogs.
I realized last night that the modeless dialogs may be to blame not only for the loss of F4, but also for the removal of range selection controls to separate modal child dialogs. Is the modelessness of these dialogs worth losing so much functionality and usability? I vote no.
Re the cell sized charts: I have another half-finished sparkline tool that uses regular Excel charts, and the only thing I need to do for Classic Excel is to make the chart object overhang the cell boundaries a few pixels, so the plot area can be as large as the underlying cell. Once I stepped out of the ChartArea=CellSize box, it was easy. Aligning and grouing charts has never been an issue. Hold ALT while dragging and resizing to make the chart snap to cell boundaries. Consistent sizing of the interior plot area is no better in 2007 than before.
Colin Banfield says
Jon, another superb post. A few comments:
I find it amazing that, despite all of the usability testing, Microsoft has made some stuff more onerous than they need to be.
When I opened the Excel 12 beta for the first time and looked at the charting feature, the immediate problem I had was with the nomenclature on the contextual chart tabs. On the Design tab, there’s a group called Chart Layouts. Next to the Design tab, there’s a tab called Layout. Huh??? I pointed out this naming confusion to Dave Gainer during the Excel 12 beta but I don’t recall his response. The fact that I can’t remember suggests that I wasn’t convinced by his answer.
I think that getting rid of pattern fills was a good idea. They add an enormous amount visual clutter to bars and columns. If you’re printing in black and white, then using different shades of gray is a far better option. Honestly, I think Excel MVPs should be make bettering use of their time other than regurgitating an outdated concept (Tusher’s Rader Chart add-in is another example of time that could be have been better spent on something more useful). In contrast, you’re done a far better job with truly useful chart add-ins, such as the box and whisker plot and the dot-plot add-ins. Getting back to patterns, you would also recall the discussion on the Excel blog here: https://blogs.office.com/2007/11/16/chart-pattern-fills/
The multiplicity of tabs is a real usability drawback. There’s also some unintuitive naming. For example, “Series Options.” Aren’t markers and lines series options? Better would be to name the tab “General Options”. Then “Line Options” would consolidate all line formatting in one dialog and “Marker Options” would consolidate all marker formatting in one dialog. The same logic would apply to other chart object dialog boxes.
The lack of setting focus in the “Fixed” text boxes in Axis Options after you click the option button is an obvious oversight which should have been fixed in SP1. The old Auto setting check box was confusing for users so the change is otherwise welcome. However, the “Auto” problem still exists in in the PivotTable Group dialog box. Why wasn’t a similar change made there??? I’ve never understood the naming convention of “high” and “low” as applied to a vertical axis location. I was hoping that these names would change to something more meaninful in Excel 2007.
One major advantage of the Ribbon that’s often overlooked is the galleries. This is certainly a concept that doesn’t fit the old menu and toolbar paradigm. In theory, the “results oriented approach” of choosing a finished “product” and modifying the “product” (if necessary) to get exactly what you want as opposed from starting from scratch is a great idea, especially for the time constrained user. However, the results oriented design approach doesn’t work well for constructing charts in Excel 2007. This is so because the gallery options are based mostly on arbitrary choices rather than data visualization principles. Stephen Few wrote an article on choosing thoughtful defaults in charts. There isn’t a single gallery option that embraces his suggestions. As a matter of disclosure, all of my charts are formatted from scratch.
On the positive side, there some bright spots. The color options are much better with the use of thoughtful hues rather than the ugly fully saturated hues of the past. And the theme colors are complementary. Using the new drawing engine allows charts to be as small as a cell – great for creating sparklines. Also, it’s easy to align and group multiple charts – great for dashboards.
Colin Banfield says
Jon, agreed.
Colin Banfield says
Forgot to ask in my previous entry, have you tried this free utility?
http://www.spreadsheetml.com/products.html
Jon Peltier says
Colin – Thanks, something else to try in my free time.
Tim says
I was reminded by the discussion of the “Fixed” text boxes that one feature I have always wanted was the ability to set the min and max scale for the axes with a cell reference. This seems to me that it would be a no-brainer and simple to add, but it never has been added. I’m sure I could probably do this with VBA, but it would be easier if it was built in. This would make zooming and panning in charts very easy.
Jon Peltier says
Tim –
Good point. That’s a feature that’s been often requested, but they didn’t get it into Excel 2007. You can in fact do this with VBA: I outline the process on my main site in Link Chart Axis Scale Parameters to Values in Cells, and Tushar Mehta has constructed an add-in, the Chart Manager, that does this automatically.
Another cell linkage we’d like to see: links of a series to a set of labels in cells. There are add-ins that do this for you, notably Rob Bovey’s Chart Labeler and John Walkenbach’s Chart Tools, but these link individual labels to individual cells, so if the series endpoints change, the labels are linked to the wrong points, and new points on the end don’t have labels.
And how about the linkage of custom error bar values to cells? It works pretty well, I guess, but it’s only partially visible in VBA: you can assign a range to the error bar values, but you can’t read which range is already assigned. It’s a write-only property.
Colin Banfield says
It comes down to this: Make every entry box in the chart dialogs a RefEdit control. As currently implemented, the control choices that Excel uses for setting values (some text boxes, some RefEdit controls) are pretty varied and arbitrary.
Jon Peltier says
Colin – That would be nice. There’s a lot of infrastructure that would need to be added to the chart model to make this work. I’d gladly trade all the shadows and gradient fills for this, but it’s not my choice.
Jon Peltier says
I could have talked about the Select Data Source dialog in 2007, and I may in a future post. There are some improvements, such as combining the Data Range and Series tabs from 2003 into a single view, and including a link to hidden and empty cells, which used to be accessed through the Tools > Options menu. But the ranges for the X and Y values of the series are not visible in the main Select Data Source dialog; one must click on an Edit button, and view these on a child dialog.
And the thing that’s bugging me right now is that although there is a nice Switch Row/Column button, so you can redraw the chart with respect to the opposite data alignment in the sheet, there is no indication whether the current data is plotted by row or by column. So I have to waste a trip to the Edit Series child dialog before I can confidently make use of the Switch button.
Sanford says
Is it possible to assign different numerical values to a portion of the Y axis in an Excel chart? I have a scale range from 20 to 65 with intervals of 1.5. But because of the distribution of the data, I want the intervals from 20 to 25 in .5 intervals.
Jon Peltier says
Sanford –
It’s not a built-in feature of Excel, but it’s possible to hack your way through it. I have a tutorial on my site (with links to other similar techniques):
Broken Y Axis
Daniel Dare says
I use a widescreen display, and the inability to choose “scale chart to fit window” is a real loss.
It means you always have wasted space at left and right of your screen in chart sheets. Also, now I find every time I open a file, the charts are different sizes to what it was before.
I’ve dragged a button onto my Quick Access toolbar to at least allow me to “Zoom to Selection” with a single click, but that still leaves a space at left and right. And I still have to click every time I open a chartsheet – Some of my files have dozens of chartsheets. I guess I will automate this with a macro eventually.
Daniel Dare says
OK I’ve found a partial work-around for the widescreen chart problem:
Charts which are intended only for onscreen use can be set up to use a “Legal” paper size. That is 21.59cm by 35.56cm or an aspect ratio of 1.647:1 – That fits much better on the widescreen display.
“Tabloid” isn’t bad either at 1.545:1
“A4”, “A3” etc are only 1.414:1 – hence the unused space at left and right.
Jon Peltier says
Daniel – That’s clever, and fixes part of the problem with an unsizable to window chart sheet. However, the reason I need the chart sheet to size to window is so I can use mouse events (up, down, and move) in the chart. These are unworkable in a chart sheet that isn’t sized to the window.
Yohay says
Hi there,
Any chance someone knows how to make the axes start at the point (0,0) rather then at the right end of the x-axis? (I’m talking about the simplest scatter plot, just need both axes to start at (0,0)).
Many thanx & best wishes,
Yohay.
Jon Peltier says
Yohay –
Right click on one axis, choose Format XXX, change the “Axis Crosses At” setting to zero. Repeat for the other axis.
Ken Stern says
Error bars are giving me a headache – any ideas here?
I have a series of graphs making up a dashboard, and on each one I want to add a single line across (to show which months the actual performance exceeds the service level, or something like that). Normally I’d just add a second series consisting of one point (the service level), plot it as XY-scatter on the secondary axis which is pegged to the scale of the primary, remove the marker and give it an X error bar and play with the custom values so it stretches across the width of the graph.
This works fine in Excel 2003, but in 2007 the error bar length appears to bear no resemblance to the values I enter. I entered 10, and it got a little longer. 15, shorter. 20, a tiny bit longer. 1000, no change. I finally opened it in Excel 2003 and put in the error bars, which worked fine, and then re-opened in 2007 where they’re variable again. Argh! Any remaining hair I have will thank you for your help. (Sorry if this is not a good forum for this.)
Jon Peltier says
A. If you experience misbehavior with Excel 2007 charts, install Service Pack 1. It does not fix all of the issues, but it helps with many of them.
B. Microsoft has made it much, much harder to use error bars in Excel 2007. The buttons on the Chart Tools > Layout tab are not at all useful, and it’s not clear how to get what you want unless you’ve fought with it a number of times.
Here is the protocol I’ve used most recently:
1. Select the series you want to add error bars to.
2. Click the Error Bars button on Chart Tools > Layout tab > Analysis group.
3. Click More Error Bars Options at the bottom. This adds vertical error bars (and horizontal for an XY series) and opens the Format Error Bars dialog.
4. If you selected an XY series, it seems impossible to get to the Horizontal Error Bars dialog, but it’s not impossible, just convoluted. You can reach around the dialog and select the horizontal error bars, and the dialog will change to Horizontal Error Bars.
5. Set the values you want, the same as in 2003, although there’s some goofy behavior when adding custom error bar values.
6. If you only want horizontal error bars, select the vertical error bars and press Delete.
The goofy custom error bar value behavior includes:
1. The Format dialog stays where it is, and a new dialog pops up for you te enter/select a range. If the Format dialog is in the way, you cannot move it. I don’t know why they didn’t put these selection boxes on the main dialog as in 2003, or why they didn’t temporarily hide the format dialog when this child dialog pops up. Anyway, you have to cancel the small dialog, move the big one, and try again.
2. If you only specify one value, and clear the other value, the one value you enter is forgotten, and both values retain their original settings. Why it won’t accept a blank to indicate no error bar is a mystery. If you enter 0, it is converted to ={0} and you get an error bar of zero length. That means, if the error bars have the little end cap (the “T” at the end), the zero length error bar leaves an end cap on the data point.
3. If you set the values programmatically, you cannot rely on optional arguments for the values being optional. If you set a positive custom error bar, you need to specify a zero value for the corresponding negative error bar. Most VBA works as well in 2007 as it did in 2003, but this is one command I spent hours trying to make work. Silly me, I assumed “optional” meant you didn’t need to include it.
Rajnish Mistry says
I used MS Excel 2003 and now I am Using MS Excel 2007. In 2003 I can easily Change the value of data by drag the tradeline on direct from chart of XY ( Scatter) type Chart. and set the curve of trade line shape.
But In MS 2007 Excel I can not drag the tradeline. So let me know is threre any option for drag the trade line and change the data.
I am waiting for your reply.
Rajnish Mistry
Jon Peltier says
Hi Rajnish –
In What happened to my favorite Excel 2003 Chart feature? I revisited some of the ways that Excel 2007 charting has changed.
One of the ways is the feature you mention: the ability to drag a data point and change the underlying data is no longer a feature of Excel in version 2007. I can think of a workaround, which requires a bunch of VBA coding, including the chart events which no longer work in Excel 2007 on a chart sheet. If you work with charts which are embedded in sheets, then the chart events work as expected, and a program can be written to handle this feature. Actually, I can think of a way to accomplish this even on a chart sheet, but it’s not as elegant. But in any case, it’s a substantial amount of coding.
elen says
Hi! I am looking for an answer to my problem. In the “Select Data Source” for chart, I need to separately click on “Edit Series” instead of just having it in the same box and modify it right there as with 2003. not 2 more clicks but directly in the “select data source” box.
Any ideas? is there an addin that would have the old feature?
Jon Peltier says
Hi Elen –
This explosion of child windows for editing chart features is a giant step backwards as far as I care. I know of no add-in that repairs this interface, other than the one in my mind which has yet to build itself.
You might like to try a taste of it with the Error Bar utility I described today in Error Bars in Excel 2007.
Gillian says
I cant seem to select the x and y axes in Exel 2007.
Example:
I want the X axis to be “Risk” and the Y axis to be the Return……
In office 2003 you could select the data…
Please can someone help
x y
Country Return Risk
US 0.009910366 0.064825981
FRANCE 0.012431884 0.061552698
GERMANY 0.006859464 0.070327555
UK 0.012764617 0.064368472
Jon Peltier says
Gillian –
You can’t select the X and Y axes in the chart, or you can’t select the X and Y data? I suspect you’re asking about the data. It’s much more convoluted than in the past.
When making a chart, put X in the first column and Y in the second. But a label above the Y data, and keep the cell above the X data completely clear. Now when you make the chart, Excel will know how to define X and Y.
In an existing chart, right click on the chart, choose Select Data. In the dialog, select a series, click Edit. In an XY chart, you get places to select X and Y data. In other charts you only get to choose Y data here, but you can click the Edit button above the Categories part of the dialog.
Enrique says
Ohhh… The Goal Seak feature (as it appears to be called), or the lack of is killing me! I used that feature all the time.
I part of my work in Real-Time Simulation, I often have to convert an old X-Y paper plot into digital points so I can model the object the plot represents (specifically, pump curves). Well my approach, which works out great, has always been to scan the paper plot and then trim the resulting jpg image such that the new image only has the X-Y Area of the original plot. Finally, I backfill the Excel Chart with this image. With this setup, I can drag the Excel points such that they “perfectly” overlay the underlying graph. I LOOOOVVED that feature!
I’ve never been much of a MS basher, but I must admit that this sort of stuff kills me! I.e. to remove perfectly good features from previously available versions. Also, I think that since 2005 or so, MS has redesigned/recoded entire applications with the introduction of many visually appealing tools, but also introduced a number of bugs on previously perfectly working features. This is becoming increasingly irritating.
Jon Peltier says
One gets the idea that the designers and programmers of the Office applications are not themselves heavy users of their applications, do not understand how users use their applications, are unaware of many existing but not widely documented features, and do not understand usability.
Enrique says
Here’s another actual example of a perfectly working feature under previous versions that is now broken under current versions. This time, it’s about the Custom Build Steps in MS Studio. Again, as part of our Simulation Environment, we use a lot of them. This feature worked absolutely flawlessly for our setup under “any” versions of Studio prior to 2005. Custom Builds, which were reprogrammed entirely for Studio 2005 (from a thread I once read), were originally loaded with bugs! And at least one still exists. I had even created a very minimal compilable project that would reproduce the problem 100% of the time. I sent that project to MS Support and got the answer that they were consciously choosing not to fix the problem, at that time anyway. This is EXTREMELY frustrating to have had a perfectly working feature that now fails, and to be told on top of it that they don’t care to fix it right now. And I’m not even gonna address the new Search Engine under Vista…
This all sucks because I’m usually the type of guy that likes to move along with the latest versions of software, always installing the Beta versions of whatever comes along… But for the first time, I’m faced with a real dilemma about Office 2007. I really really don’t like the new interfaces, although visually appealing. I use pretty much all of the programs (including Visio, which hasn’t been butchered yet). However, I use them as tools, not as my main every day programs. Now, I find myself wasting so much time browsing around trying to find the most basic features, and often having to resort to the web to find where the damn thing is… I have jerked around for some time to see how the Goal Seek was implemented under 2007 or where it was hidden, only to find out that it just doesn’t exist anymore.
I hope some of these MS guys read your excellent post. En passant, est-ce que tu parles français avec un nom comme Jean Pelletier?
Jon Peltier says
Enrique –
Thanks for your further comment. It illustrates a trend that goes beyond simply our first reactions to the new Office interface.
Regrettably, I don’t speak French. My father’s ancestors migrated slowly from what has become the province of Quebec through northern New England, settling in Rhode Island. I vaguely recall my great-grandmother who spoke only French, or what passed for French in small New England mill towns. My grandparents spoke English, in fact, I’m not sure my grandfather spoke much French at all. I took a couple years in high school, but that’s long gone.
Mohan Kumar Karunakaran says
Hi Jon,
It is wonderful to see your research on the new charting engine. I am into MS Office development for quite a while. I am dealing exensively with Excel charts and I am wondering if there is a way to modify the standard colors (10) using VBA. It will solve most of my problems.
Thanks,
Mohan
Jon Peltier says
Hi Mohan –
As far as I know, the standard colors are carved in stone.
Gordon says
Even Microsoft have had to come clean over the dip in functionality and have listed the list of differences in a remarkably candid appraisal of 2007’s “improvements” over previous versions:
What happened to my favorite charting features?
I commented on this article to the effect that it helped me – helped me avoid moving to Excel 2007 that is!
Jon Peltier says
I commented on the Microsoft article in What happened to my favorite Excel 2003 Chart feature?
jeff weir says
One thing I hate about the 2007 dialog box is that if you right click on a chart and select ‘format gridlines’ (which can be a mission in itself because the mouse pointer has to be practicaly sitting over them) then you can’t select the ‘No Line’ option under ‘Line Colour’. In other words, you can format them here, but you can’t turn them off here. Just stupid.
In your comments above, you say “I think that Excel 2007 is more strict about proper referencing of names than 2003 was”. I get grumpy when Excel 2007 insists that I must prefix a dynamic chart range name with the workbook name. Why make me type that in? Just stupid.
Jon Peltier says
Jeff – Both of your points are applicable to “Classic” Excel as well.
The Format Gridlines does not have a No Line option in 2003. To get no line, select and delete the gridline. (A gridline with “no line” is undefined.)
On rare occasions in Excel 2003, you didn’t need to prefix the name with a sheet or workbook, but it has always been wiser not to rely on this.
Barbara Cotton says
I have been using your excellent technique for a broken y axis for several years, but now that I have switched to Excel 2007 it no longer works (or not in exactly the same way). It’s the same up to the point of adding the new series for markers, but when the new series is changed to XY Scatter, I get a completely different result. I would be very interested to know whether you have found a way around this.
Jon Peltier says
In Excel 2007 SP2, using the same data as in my example page, the step you are having problems with works the same as in all Classic Excel versions. I remember a problem with earlier editions of Excel 2007, which did not apply the intended X values to the XY series. Check the series formula of this series (if you can’t select it, select the last column series and press the up arrow to select the XY series. The series formula in my original example is:
=SERIES(Sheet1!$G$1,Sheet1!$F$2:$F$13,Sheet1!$G$2:$G$13,4)
The rest of the protocol is about the same as in Excel 2003. Soon I’ll post an updated protocol that is applicable to both versions.
Barbara Cotton says
Thank you for your comments about my broken y axis. I found that some charts were OK and one was not, so I came to the conclusion that there must be something about the way the chart was originally built that was causing my problem. I rebuilt the chart from scratch, and it now works fine.
shameka says
I absolutely hate the fact that they took the combination chart options out. for my job I use the column/trend combo all the time. The Trend analysis on 2007 version doesnt quite fit in to what I try to reflect in my charts. I did however save the column/trend combo i used in previous version to templates on my computer so in future I can still create what I want and just change the name or title of each graph.
I wish they would add it in. If others were confused, they dont have to use that one
Jon Peltier says
Microsoft changed the way that custom chart types were stored, moving from a user gallery consisting of a workbook containing a chart sheet for each chart type, to a template system in which each stored chart is a standalone file. The new system is more robust than the old, but I think they didn’t get around to converting their built-in custom chart types to templates.
Daniel Ferguson says
Jon, you hit the head on the nail regarding usability; MS has nuked my efficiency. It’s seems they got too focused on increasing accessibility for “surface” users, trying to put every single option on the menu bar. I can’t fathom WHY they split up all the formatting menus. Everything I do now requires an extra two or three clicks. Excel 2007 is going to give me an RSI.
No double-click?! Gotta be kidding me!!!
Well, it feels like MS drove a wedge between normal and advanced users. I’m forced to rely more on VBA for tasks that used to be quickly accomplished by having lots of options in one place.
Has anyone considered creating new formatting forms in VBA? After only one day I’m ready to make one with my most frequently used tasks. If someone has already coded a nice form I’d be interested.
Jon, thanks for your work, I get inspiration from your site all the time.
Jon Peltier says
Daniel –
Thanks for the note. On the backburner I have a set of charting dialogs under development, which will combine the best of Classic Excel, the best of Excel 2007 (yes, there were a few improvements among the mistakes), and some improvements. I cannot predict when it will be completed.
constanza says
Hi I was kind of hoping you help me out with a problem. I want to make a graph with smooth lines but by some reason the minimum of the function appears to be in a different value for x when checking the box for this option. Is there another way to smooth the chart lines or a way to fix this once the graph is made?
Jeff Weir says
Constanza – perhaps this is because the function can also give a misleading match at each end of the series, because it has less data points with which to perform a regression on points in a moving range around the X value than it does everywhere else (and it might be substituting zero values for any missing data). I just wrote about something similar on another of Jons pages at https://peltiertech.com/loess-utility-for-excel/#comment-19427#ixzz0Rmsoa8Qk – this might help answer your question.
Jon Peltier says
When you use the smooth lines option, you run the risk of plotting incorrect or misleading lines, as described in Smooth Talking Lies.
Jon Peltier says
Jeff –
Constanza’s referring to the line smoothing formatting feature, which actually uses a Bezier algorithm to compute the smoothed curve. Because of this, it may make a nice looking smooth line, but the line may extend well beyond any sensible values.
Here’s a set of alternating pairs of 0 and 1 values plotted using the smoothed lines option:
The LOESS bombs out with only 4 points, but with 5 and 6 points, it doesn’t fit the data, and the two LOESS fits diverge:
Yohay says
Hi Jon, How are you?
I’ve written an expression in CONCATENATE that combines both days & hours.
I want to show the days with 0 decimals and the hours with 1 or 2 decimals. It’s all under the same statement. How can I do such a thing? it’s kinda like Casting in C language, right? Do I need VB for that or I can plug it in the statement line? If VB what should write?
Many thanks & Best wishes,
Yohay.
Jon Peltier says
Worksheet function or VBA? In either case, don’t use concatenate, put the value (date and time) in the cell, and display it with a custom number format. To show days and a space and hours with two decimal places, use a format like
d h.00
constanza says
Thanks for all your answers!
Ken Morison says
Great summary. Shame I didn’t find it a year ago. I thought I’d get around it with a custom VBA interface, but the documentation on that and macro recording is terrible. Now I can’t find such simple syntax as the colour of the border on a series marker.
Any news on Excel 2010 yet?
Jon Peltier says
Ken –
I have a handful of posts relating to Excel 2010. The interface is pretty much the same mess as in 2007, but chart redrawing performance has improved significantly.
Jay says
Jon,
Have always apprecaited your site and found it useful. For charting in 2007, one small snag I have found. You have an excellent article on creating vertical bars on charts (https://peltiertech.com/Excel/Charts/AddLineVertSeries.html) that I have used multiple times in 2003. Works like a charm. However, when I try the same thing in 2007, the dates never come out right. Was wondering if you have seen this as well and have any tips?
Example:
Status
11/02/09 0
11/02/09 1
The vertical line ends up way over to the right.
Saw this same behaviour in 2003, but when you deleted the secondary x axis, the line shirted to the correct date.
Jon Peltier says
Jay –
There are issues with line-XY combination charts where the X axis is a date scale axis. I’ve written updates of some of my tutorials, but there are just too many. Thanks for bringing this to my attention. I’ll check out all of the Add-A-Line pages.
dan welch says
Hi, I have just started using Excel 2007. I have a lot of experience with using other versions of Excel and it’s driving me up the wall at the moment. I need to create a graph – simple line graph with seven sets of variables. In the past I created a custom graph type with all the formatting I like – basically, “auto scale” off for all axes, axes labels, and legend. All of the graphs are printed out and form part of my factory’s production records and so I don’t really want them printing with huge sized text that’s been auto scaled up when it’s been printed, as this leaves the graph itself too small to be of any use at all. I cannot find out how to do this with 2007. It’s easy to do in 2003. Do you know if it’s possible in 2007 – and if so, how?
Many thanks!
Jon Peltier says
Dan –
It’s all possible, you just have to find where the buttons were hidden and figure out what things have to be done in a different order. The text shouldn’t be a problem, because in 2007 font autoscaling is off.
Yohah says
Hi Jon,
How are you?
I’m experiencing trouble with my excel 2007 regading the prompt “Selection is too large”. Any idea how can I overcome it? All I is copy-paste from another tab in the same sheet…
Thanks, Good day,
Yohay.
Jon Peltier says
Yohah – How large is the range which is “too large”? Does it work with a smaller range?
Yohay says
Hi Jon,
(now back from the weekend…)
It’s about 26,000 lines. We tried implimenting it on smaller ranges (say 500-1,000 at a time) and it worked on several iterations and after several more it just kicked us out with that prompt (“Selection is too large”).
Its suppose to be a simple drag-down, that’s why it’s so weird… nothing too sophisitcated..
Any idea?
Thanks,
Yohay.
Jon Peltier says
Break it down into a step size that doesn’t fail. Excel 2007 has required a lot of provisions like this.
Bill says
I wish I had never changed from 2003 to Office 2007. avoid it at all costs.
Adrian says
Jon,
First of all, thank you for an excellent site.
My company has just upgraded us all from Excel 2003 to Excel 2007, and despite some searching I still can’t find a way to select a chart and then “adjust to fit page” before printing. Am I being particularly dumb, or has this feature been removed from Excel 2007?
If it has been removed, do you know of a some kind of macro or add-in that could recover this functionality, please? It’s a right pain to have to keep hopping between the worksheet and print preview in order to adjust the chart to fit the print.
Many thanks for any advice.
Adrian
Jon Peltier says
Adrian –
This functionality has been removed. Thanks, Microsoft.
There is no readily available VBA alternative. The actual size-to-fit functionality is not possible, but code could be written to make the chart the right size to fit, without actually locking the chart edges to the window edges.
Charles says
Hi Jon,
I have written a fairly complex program in office 2003 involving charts.
The prtoblem I have is not being able to drag the data labels to move them when I open it up in office 2007.
Do you now if this can be done?
Many thanks for any advice,
Charles
Jon Peltier says
Charles –
You have to single click twice on a label, first to select the series of labels, then to select the individual label. Then you should be able to move the label.
You can also use Rob Bovey’s Chart Labeler to move labels, either a whole series at once or one at a time.
Peter says
Jon, Thanks for your excellent post. One year after switching to Excel 2007 I am still hugely frustrated by the new charting dialog and functions. So frustrated that after using Excel for 15 years I am thinking of switching to Open Office.
You mentioned many issues but I feel there are so many other irritations that slow production, e.g.:
– chart titles and axis label boxes that do not automatically expand or center,
– when creating a new chart from scratch, the need to laboriously create label boxes for each axis,
– incompatibility with marker and bar styles when adding a new series to an old chart.
Most disappointing, as you mention is the lack of any new useful functionality and chart types.
One has to wonder what was Microsoft thinking and did they do any testing with experienced users – amazing! A pity they did not keep a compatibility mode using the old charting code!
Bob says
My beef is the malfunctions in area charts. If any blank cells are in the range, Excel 2007 adds a TREND LINE! The effect is bizzare in the extreme. The line bisects the area, with fill and void areas reversing below the line. Utterly useless chart results. Also, narrow peaks vanish in Excel 2007. And auto extends dates beyond the ends of the actual date data range – my September date range begins with August 31st! Which is a ZERO, since that data isn’t in September, and that causes the trend line to appear! All of this appears normal in Excel 2003. What in the world did they do?
Vicky Davis says
I would like to know if I could use Excel and make a form that has our company logo on a blank graphing page(these pads can be purchased and called Planning Pads and are quad ruled 4 sq inch). This type paper is not available for printers anymore so they say and I thought if I could make this form I could give to them to make some 1/2 sheet pads. I have figured out how to fix each cell etc but when you go to print with no data in the cells shows nothing to print? This may be a simple question but I have used Excel for years but always had data in the cells as opposed to trying to make up a form etc. Thank you for any input!
Jon Peltier says
Vicky –
You need to set the print area to the renge you want printed. Or simply type a space in the bottom right cell.
Tech Helper says
Regarding Peter’s comment I think that Open Office does just as good a job as MS Office. For free software I dont think you can go wrong even of you are used to Excel.
Lisa says
I was working on a chart in Excel 2007 and the colors look washed out. I am using the office theme and the colors are not vibrant. I wanted to know did I turn something off or on without knowing it. Can this be fixed? Please advise
Hashim says
One of the option in Excel 2003 to have separate colour for ‘Major’ and ‘Minor’ gridline seems to have been removed from Excel 2007. Or is there a way to achieve thsi formating option.
Jon Peltier says
Hashim –
You may have to add the minor gridlines first. Once you do you can format each independently. Select the one you want, either by clicking on it or by choosing it from the dropdown in the top left corner of the Chart Tools > Layout or Format tabs.
Hashim says
That was very helpful. Thanks very much. I did this by Layout\Gridlines\More gridline options
Though the first approach e.g., selecting gridlines seems less efficient. Because you don’t know which gridlines are selected; Major or Minor until you go into the dialogue and even so the colour you select is applied across both gridlines.
Jon Peltier says
The dropdown in the top left of the Chart Tools > Layout or Format tab shows what is selected, and you can use it to select hard to click items.
Hervé says
Hi Jon,
I have a chart in a sheet using excel 2003.
When opening this sheet in excel 2007, I am not able to add an extra curve to the chart. More precisely, excel allows me to add the curve but the curve never appears (despite changing the color of the plot area).
Is this a know bug ?
Is there anything to do but re-creating my chart in excel 2007 ?
Best regards
Hervé
Jon Peltier says
Hi Hervé –
There are a lot of little incompatibilities between 2003 and 2007. To minimize these, make sure you’ve applied the latest service pack to Office 2007. But if the chart is too complicated, it may be a lost cause.
Hervé says
Hi Jon,
thanks for the answer. What do you mean by a “too complicated chart” ?
Jon Peltier says
Hi Hervé –
This means not too complicated for you to build, but more complicated for Excel 2007’s ability to convert. I have no firm definition, but you’ll know it when you encounter it.
Liz Heath says
Having recently (and belatedly) moved to 2007, I’m increasingly frustrated at many of the changes which require additional steps. But I’m absolutely flummoxed at trying to figure out how to change the default location of charts when they are created. I do NOT want it on the worksheet. I cannot think of an instance in 15 years where I have wanted to put a chart on the worksheet. Is there anyway to change that default location so I don’t have to do the additional steps of moving it each time?
Jon Peltier says
Hi Liz –
As far as I can tell, the default to create a chart as an embedded chart in the active sheet cannot be changed. FWIW, I almost always (>99%) want charts embedded in the worksheet. In my experience, most people want embedded charts most of the time, and I think Microsoft must have measured this with their Customer Feedback Program.
This tendency not to make chart sheets is especially true since Excel 2007 broke the ability for chart sheets to resize to fit the Excel window.
Of course, if you select your data and click the F11 function key, Excel will insert a new chart sheet with the default chart type. So you could change the default to whatever chart type you generally use.
Bob Norris says
I have spreadsheet developed in Excel 2003 where I look at monthly comparison of data through graphs (there are 36 of them on the same sheet), and I like each graph to have the same vertical scale for visual comparison of range as well as pattern (or shape). When I import new source data , the vertical scale of the old graphs is always inappropriate to the new data. In Excel 2003 I manually updated the scale in one graph to suit the new data, I then selected (in sequence) the scale of the next graph, a quick CtrlY and the scale update was repeated. This process took a couple of minutes to update all 36 charts. It is very frustrating that the repeat last command function does not work on chart axes in Excel 2007.
The process is very laborious and tedious in changing the scale of every chart through the dailogue box.
Is there a way in which a cell value can be used to set the maximum scale of an axis?
Hervé says
Hi Bob,
I am not an expert but I am controling the maximum scale of my charts by using the following sub :
Sub change_charts()
‘ X axis
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlCategory) _
.MinimumScale = ActiveSheet.Range(“B85”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlCategory) _
.MaximumScale = ActiveSheet.Range(“B86”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlCategory) _
.MajorUnit = ActiveSheet.Range(“B87”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlCategory) _
.MinorUnit = ActiveSheet.Range(“B88”).Value
‘ Y Axis – chart_head
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlValue) _
.MinimumScale = ActiveSheet.Range(“C85”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlValue) _
.MaximumScale = ActiveSheet.Range(“C86”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlValue) _
.MajorUnit = ActiveSheet.Range(“C88”).Value
ActiveSheet.ChartObjects(“your_chart_name”).Chart.Axes(xlValue) _
.MinorUnit = ActiveSheet.Range(“C88”).Value
End Sub
Ian Bridge says
I am lost with charting in the Excel now. I used to be able to develop charts in the various versions of excel (going back to very early versions working in Windows 3.1). I cannot seem to get the chart function working now no matter how I select the data. Is there any way I can manually force excel to chart the data I want rather than having it decide what it thinks I should use?
Jon Peltier says
Ian –
If possible, arrange your data in one contiguous block. The first row and column should contain series names and x-axis labels or values. The top left cell should be blank.
With this arrangement, Excel should guess correctly, and the only thing you may have to do is switch rows and columns.