Microsoft Excel

Thank you, Robin. It's a pity you weren't around when they put all that bling into Excel 2007. Then perhaps there might be zero steps in making charts professional.

I echo dbb's comments. Excel is capable of producing excellent, professional charts; by default, however, it produces charts full of junk. This post is very useful, and it is good to see a post such as this finally appearing on a Microsoft blog. Maybe this is the beginning of a change in Microsoft - a recognition that bling does not equate to good visualisations.

Whilst defining the format for a 'professional layout' is always going to be an almost impossible task there are some things the default chart could be to help.

1. No legend when only a single series

2. Axis labels to Low with negative values

As you mention the selection of Designs is not exactly chart friendly, so being able to add our own designs to the Gallery would be a great feature. But as a minimum the removal of the bevel heavy choices to make bad design harder to achive.

look forward to more blogs from you Robin.

When you have a chart that has a lot of detail in say the smaller numbers where you would want to emphasize that, but also show that there are larger numbers in your chart - i have seen charts with a split in the middle, cutting out the mid section in order to detail the information on the high and low sides of the figures.  How do you do that in Excel 2007?

One of the biggest disappointments in Excel 2007 was the charting. We heard how great it was going to be. Unfortunately, the only enhancements were nearly inconsequential improvements (a more flexible log scale is the one that comes to mind).

The drawbacks to Excel 2007 charting have been astounding. The Chart Wizard has been retired, and now the user is faced with a multitude of options for formatting, most of which add meaningless decorations to the charts. No new chart types were added, and no ineffective old types were retired.

The redesigned dialogs have greatly hurt productivity. For example, six tabs are now required to change the lines and markers in a series instead of one. The logical Chart Options dialog has been chopped up and the pieces strewn across several tabs, if you can find them.

Thanks for showing that it's possible to make a reasonable chart in Excel 2007, if you navigate around the treacherous formatting rocks in the stream. The only comment I'd make about your retooled chart is that you should use a lighter gray rather than a dotted line to de-emphasize the gridlines. Dotted lines use less ink but draw more attention than solid lines.

Robin - Refreshing...but this post amounts to heresy, considering that both Excel and the Chart Advisor break all the rules of "Professional Chart" design as per your definition. You're a brave soul walking the halls of Microsoft.

Susan - Note that the length of the bar encodes its value (only valid when the bar starts at 0), so cutting out the middle may not be the best thing to do. With widely ranging values, I'd recommend using a log scale.

Andy C. - I wouldn't get my hopes up. No doubt we'll see some new charts in Excel 14 (some good, some bad), but we're unlikely to see any change in default options, if the Chart Advisor philosophy is going to define the future.

Andy P. - I've often thought that having a user-defined gallery would be nice. For now, I create templates with the defaults I want to use.

Jon - Yeah, some things are still broken in Excel 2007's charting functionality.  I'm holding my breath that SP2 will resolve most of the remaining issues.  

I've come to terms with chart tools layout in Excel 2007. All of the Chart Options dialog box items (and more) are under the Layout tab (an utterly confusing name choice given that there's a group labeled "Chart Layouts" in the Design tab). For a single chart, it's more work making the trip to the Ribbon to change settings. However, if you have to make changes to multiple embedded charts, the Ribbon layout works better than the modal Chart Options dialog box, IMHO.

Colin -

The key benefit to the modal dialogs was that you could visit a dialog, make numerous changes to an object (e.g., a legend or a series), then select the corresponding object in another chart, press F4, and apply every change to the selected object. Click - F4, repeat this two-second operation for each object you want to change.

In 2007, F4 has been eviscerated. At best, it repeats only the last minor individual adjustment you've made, not the whole sequence of changes made during the visit to the dialog.

This impediment is such a major change to working with Excel 2007 charts, that an SP will never fix it, and I fear that we'll not see it in a whole new release either.

I also doubt that SP2 can make more than a few minimal superficial changes.

Jon, I was looking at this from a very different angle, but you're right - reduced F4 functionality, together with sketchy macro recording, dialog box quirks and outstanding "bugs," do hamper chart productivity in Excel 2007. Because I spend a lot of time in Excel 2007, my senses have been "dulled" to the shortcomings.

Colin - I move back and forth, so every visit to 2007 is an exercise in frustration. "This was just working fine...!"

Lately I've been amusing myself with the misguided thought that I can actually design and build a custom charting interface for 2003 and 2007, combining the best features of each and the best features that both don't have. I'll be greeted with flowers and the cheers of the liberated.

I've built little pieces of this interface (an error bar function, and a half-baked replacement source data dialog), and I've assembled a broad array of nicely laid out but uncoded dialogs. I fear it would take months of full time effort, though, so I'll probably just build a little piece at a time, as I need a feature in one of my projects.

I was excited about this post and wanted to use it with a related link on how to use Outlook to Paste Special a Microsoft Office Excel Chart Object into a template but could not find this option in the Outlook Paste Special menu.

See:

http://blogs.msdn.com/outlook/archive/2008/11/17/living-in-outlook-advanced-message-templates.aspx

Does anyone know why this option is not available in my MS Office Small Business 2007? Also, since this is a link, does the original excel file need to be on a public server?

Kevin

Colin -

I have come across two more issues this morning.

In one case I was making a combination chart, and I needed the secondary category axis to be invisible and lined up with the primary category axis. In Excel 2003, this just happens without a lot of work. In 2007, some strange force is preventing the two axes from coexisting on the same line. The primary axis is pushed away from the plot area by the invisible secondary axis. I worked around it by hiding the primary axis line and fudging the secondary axis line and markers, while still showing the labels for the primary axis.

The other thing was in a VBA procedure I wrote to automate creation of this chart. In 2003, the procedure runs instantly, and the chart appears in finished form as the mouse is being released from the click that drove the procedure. In 2007, I had to switch the order of a few steps (which makes no difference in 2003), then the code took many seconds to run (30 seconds on a 1GB VM). The line that took 30 seconds (the rest of the procedure totaled about 0.06 sec):

Application.ScreenUpdating = True

The chart took 30 seconds to redraw when the procedure ended!

In het artikel " How to Create a Professional Chart using Excel 2007 " beschrijft Robin Wakefield, een programma manager voor Microsoft Excel, de stappen die nodig zijn om een standaard Excel grafiek aan te passen naar een professionele grafiek.

Thanks everybody for your comments!

Jon - I agree on the gray lines rather than dotted as a best practice.  Also I feel your pain around performance issues in rendering your chart. We aware of this and actively investigating.

Kevin - Not sure about version specifics but the original excel file only needs to be in a location that is accessible by the person creating the email to update the chart.  The person receiving would not need to have access.

Jon, The axis problem you mention is probably one of those "rationalized" behavior changes that made its way into Excel 2007.  Nice work around.  I notice that the primary and secondary axes labels flip position when you change the position of the primary axis in relation to the tick marks - on or between.  Perhaps some use can be made of this for multi-level category axis labels...

Forgot to mention - In Excel 2007, the slow chart operations in VBA are really painful.

Colin -

"Rationalized?" I think nobody on the team tested this kind of chart, so they didn't notice anything strange. There wasn't even a serviceable charting engine until RTM, so we couldn't try out all of our longstanding tricks. There are a zillion things like this that I bump into all the time.

Another: you can't add an XY series to a stock chart. Fortunately they didn't break the underlying line chart-up/down bars-high/low lines functionality, so you simply start with a line chart, not a stock chart.

There are so many things, that I hadn't noticed your trick with flipping the axis labels. Of course, if they came up with an axis object model with a decent hierarchy, then things like this would become irrelevant.

In excel 2003 you could change data labels using the same buttons that you use for formatting cells for such things as “%” “,” “$” add/remove decimal places. In 2007 this is no longer an option. With the change data labels now being stuck only inside the menus, even coding a macro for it could take a bit of work. Not sure where the object model is to apply what would be the equivalent of Selection.NumberFormat = "0%" etc. to labels in 07.

To expand on this issue, it would be useful to have this option when working in PowerPoint graphs as they are now excel based, but I haven’t been able to find any way to hit the PowerPoint facing portion of the charts in a macro. Being able to add to that interface would allow significant performance enhancements by adding back elements we had in 2003 such as the data label formats, along with potentially adding new options like entry boxes to set scale min/max/interval without having to go through the dialogue boxes.

Also, is there an area the defaults for charts can be modified? While I have set up crtx files and modified them to the desired formats. However, there is no way to have a template save beyond the active data range. i.e., a column chart has 3 colored segments, I want to add 2 additional segments so expand the range. The 2 new bars do not come in with the proper theme colors, and line borders drop off etc. I know this is not new to excel charts, but one of the few spots the older graph formats had more flexibility was in maintaining memory of the bar formats regardless if they were turned on or off.

1. The comments by Jon Peltier of February 24, 2009 are very nicely stated and very much to the point.

2. If I need to put a chart together in an expedient manner, I use Excel 2003, if I have an hour or so to mess around and maybe come up with nothing I use Excel 2007.

3. The hacky fix to put the shading below the x axis is done by gradient fill settings in the format plot area.  Funny I would have expected advice on how to effectively use that particular formatting option instead of it was too difficult.  

4. “we compiled a set of well defined heuristics such as when to use a line chart versus a column chart. These heuristics were incorporated into an advanced rules engine which scans your data and scores all of the relevant chart types.”  All of this is needed to decide when to use a line or column chart?  If the person preparing the chart does not know or have a good idea of how the data should be presented, this person is more than likely not the person who should be putting together the chart.  And, we pretty much had such a tool but without the heuristics, I believe it was called the chart wizard.

Many thanks Robin for the nice article. I agree with Brenda on simplicity of Excel 2003 charting. Many of us still use Excel 2003 for eleventh hour presentations.

@Brenda -

1. Thanks.

3. This bit of formatting is just eye candy. Just because the WSJ uses it, doesn't make it right. It's "unessential formatting", and I'm glad Robin left it out.

4. Are you speaking of the Chart Advisor? It was a nice idea, but it has at least two big weaknesses.

First, it only works with series data in columns. This is the better way to align your data, but it's often logistically preferable to use data in rows.

Second, the heuristics provide a score that includes not only best practices but common practices. I don't care if more people prefer a pie chart for a particular set of data, don't include the habits of the unsavvy in such a potentially influential tool.

I agree that the removal of the chart wizard was a great loss, in addition to taking all of the parts of the Chart Options dialog and dispersing them willy-nilly throughout the ribbon.

David -

I've found that for the most part Excel 2007 runs Excel 2003 code pretty much the same as 2003 does. Sometimes 2007 is crankier about the order of statements, so every procedure needs to be checked out.

The code that's problematic is shape-related: fills and borders, as well as color definitions. The old ColorIndex is followed, but the palette may not be what you expect, and it does not always match the default 2003 palette, despite documentation to the contrary. Speaking of documentation, the new object model introduced with 2007 shapes is labyrinthine, and the documentation is particularly uninformative.

And yes, the Excel-based charts which are now integral to PowerPoint are not accessible via VBA. I understand that a future service pack may address this, but I suspect it may not address it satisfactorily. Fortunately the tried and true OLE techniques of yesteryear still work in 2007, quite nicely.

Statements involving .NumberFormat and the like are still operational.

Hallo!

I have a question regarding excel chart:

is it possible to write, for example in vertical axis the letter instead of number.

when i will one rating chart: in horizontal axis are years, vertical axis are rating (AAA, AA+, AA, ...) how can I make that?

thanks in advance.

David - Excel based charts will be accessible through VBA in both Word and PPT with Excel 2007 SP2, keep on the lookout and please let me know if you see issues with it.

Thanks for the update, I look forward to testing it out once SP2 rolls out.

From the Excel blog: How to Create a Professional Chart using Excel 2007. It’s really too bad it takes 17 modifications of the default (not counting the clicks/entering of input) to make a “professional chart” (read: roughly Tufte-approved),

Hello All:

This is a great resource.  Thanks for all the postings here.  For anyone who is interested, it is possible to add the shaded area below the category axis.  See my site above for a step by step explanation to add the shaded area to the chart in Excel 2007.  For the first time, it is now possible to apply a transparency to a data series in an Excel chart.  The chart is relatively easy to create, however, we have discovered numerous printing issues with color print drivers and transparencies in Office 2007.  One workaround is to SAVE AS to a .PDF or Microsoft .XPS document before printing.  The .PDF/.XPS files print out fine.

Mike -

Not for the first time. John Walkenbach showed us how to do this a number of years ago, at least as early as Excel 97. Here's a recent revision of the article:

http://spreadsheetpage.com/index.php/tip/creating_a_transparent_chart_series/

And it prints fine on my HP deskjet printer.

On the x-axis, how did you format the dates so that the year (i.e., '08 and '09) is only shown for January and the first month and not the rest of the year?

Asbjorn -

This axis is a category (text) axis, not a date/time axis. The first chart, with the blue bars, has a two-column category axis range, with the 2-digit years in the first column and the one-character months in the second. I show this technique in <a href="http://peltiertech.com/WordPress/chart-with-a-dual-category-axis/" title="Chart with a Dual Category Axis">Chart with a Dual Category Axis</a>.

This centers the year under all of the associated months. To align the year with the first month of the year, you could (1) insert a space character into the previously blank cells of the first column of labels, or (2) revert to a one-column label range, where you put the year and month into special cells and just the month in others.

Avoiding unessential formatting is a good suggestion if one wants to create professional charts, as using some of that formatting can actually make the chart INCORRECT. Here's an example:

1. Enter the following in Sheet1:

Item Value

A 2

B 1

C 0

D 1

E 2

2. Select the range (A1:B6) and create a pie chart.

3. Select on the resulting pie chart and then click Style 10, Style 18, or Style 26 from the Chart Styles box on the Chart Tools/Design tab. Notice how dramatically the slices on the pie change. It's a brand new, totally WRONG chart!

C H Art:

Ha, as if a pie chart isn't bad enough, Excel can't even get the slices right! A few more of the styles are messed up too.

Is there any way to display tool tips for all the series of data in a chart all at once and make it constantly visible (and changing) as one moves the mouse over the chart e.g. say I am plotting a line chart with five series of different data hence five line appears with time as the horizontal axis (same for all the series), now instead for the viewer to eyeball the value for any particular data point and try to compare it with other series won't it be nice if there are floating tool tips where the Y and X axis value are displayed for all the series in their respective colors when one slides the mouse left to right.

Regarding the pie charts, it appears that Excel does give the correct slices, it's the fill colour that goes wrong. Where you have a 0 sized slice the colour from that slice, which shouldn't show at all, spans across any prior slices back to the origin (12 o'clock position). If your 0 slice is last in the series then you lose all visibilty of slices! This obviously shouldn't be happening, but until it is fixed:

Ways to avoid this:

Stick with a style that works;

Order the series so that 0 values are 1st;

Add a tiny value to them (0.00001) but keep the displayed values to fewer places of accuracy;

0 values in pie charts are not always so easy for a viewer to notice (or notice the lack of) so in order to show that something is 0, where possible choose a different chart type.

If you really want to see this going wrong in glorious style, use the exploded pie charts!

It's a bit concerning. I think for the time being I will be on a strict diet and avoid pies altogether.

Sean -

If you watch it while it redraws, you see that the 0 value slice is really a 360° slice. The slices before it are under this full circle, and those after it are above it.

The best way to prevent this problem is not to use pie charts. Everyone "knows" they're good for showing proportion of a whole, but in fact, they are really pretty bad at it, unless you have only two, or at most three, slices, and the values are easily distinguishable from each other.

Pie charts are often prone to error, as I showed in http://peltiertech.com/WordPress/2008/03/01/pie-chart-rounding-in-excel/, and as C.H. Art showed in hiw comment.

Today’s authors, Amit Velingkar, a Program Manager on the Excel team, and Bob Silverstein, a User Experience