Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel 2010

The official blog of the Microsoft Excel product team
A Bit More On Charting

Last week, in response to my post on charting, Harlan pointed at a few Economist charts as examples of professional charts.  Today, I spent a few minutes with Excel 2007 trying to create a similar chart.  I thought I would share the results.

The chart in question is located here.  It is a line chart with a title and some series labels.  Here is a screen shot.


Source: The Economist web site

To start with in Excel, I typed in the data (source: The Economist web site) and inserted a line chart …


(Click to enlarge)

Here is what you will see in the beta build when you insert a line chart.


(Click to enlarge)

As you can see, the Excel chart looks similar in composition with respect to good charting technique – there are no graphic effects, and the data is emphasized, axes, tick marks, and gridlines have been subdued to allow the data in the plot area to stand out more.  Next, I decided to fiddle around with a few settings to see how hard it would be to get things closer.  A few clicks to add a title, change where the vertical axis is, and resize the chart a bit and I had something looking very close.


(Click to enlarge)

The one major difference is that the legend is outside the chart, and in the chart plot area next to each series.  Again with a few more clicks, I removed the legend from the chart and added two text boxes (“OECD” and “Germany”) and positioned them near the appropriate data series.  With a few more clicks, I have something very close.


(Click to enlarge)

The point I am trying to get across here is that our default styles (and many of the other styles we include in Excel) are, I believe, what a lot of people are looking for with respect to “professional” and “effective”.  (I know there are features that would make the above easier, and like I said in the previous post, we are planning to continue to work in that area going forward.)  Here is what the same chart looks like when turned into a bar chart … again, the focus is on clarity of data.


(Click to enlarge)

So that’s it for today.  Download the beta, try the styles, and let us know.  As always, feedback welcome.

Posted: Monday, June 05, 2006 10:13 PM by David Gainer
Filed under:

Comments

Chris Nokleberg said:

Is it possible to shift the x-axis tick marks to be aligned with the data points (and the center of the labels) as in the Economist chart? That seems like the biggest remaining difference.
# June 6, 2006 1:46 AM

David Gainer said:

Chris - yes, it is.  Inside, Outside, Cross, and None are your choices for 2007.  I just missed that - my apologies.
# June 6, 2006 2:06 AM

Neil said:

What happens when the data changes - how easy would it be to have the text boxes change position when the data is updated?
# June 6, 2006 5:20 AM

sysmod said:


Re:
>>
sysmod - no change to extend list formats and formulas, nor any other error checking options (what others would you like to see?)
<<

Posting on that thread is disabled, so I'll put this here under charting.

I'd like to prevent negative numbers in pie charts being presented as positive. I'm not sure what would be a good way (folded back slices?) to make sure this misinterpretation is not overlooked.

Any ideas?

Patrick O'Beirne
# June 6, 2006 5:56 AM

Vitalie Ciobanu said:

In the last picture, you have a bar chart not column. Is this a mistake of "fast typing"?
# June 6, 2006 6:26 AM

Vitalie Ciobanu said:

I'm sorry, you have a column not bar chart. There was my mistake, sorry :)
# June 6, 2006 6:28 AM

Jon Peltier said:

Chris -

David missed your point. You can change the category axis scale parameters so that the Y axis does not cross between categories.

Neil -

I would not have used text boxes. I would have placed a data label on a single data point in each series. I prefer the last point of the series, but you can do any. (In 2007 B1TR this is not yet working, I haven't gotten around to trying it in Beta 2.) When data changes, the label stays near the point, though a little human interaction may be needed to select a new point or different label alignment.

Patrick -

Another great reason not to use a pie chart. Use a simple column chart, where a negative number is obvious, and it's easier to judge the relative heights of the bars than angular widths of pie wedges.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com
# June 6, 2006 7:07 AM

technochrista said:

I have been reading the articles about the charts in the new Excel. I was surprised at how many people complained about the styles of the charts, and their lack of professionalism. I thought the charts were fun looking.

I have a slightly different perspective however than those people working in the "business" world. I work for an education company. We use Office programs with K-12 students. Our company has students start to use Excel when they are in grade 3 or eight years old. The new Chart Wizard is simpler to use. As well, the styles of charts are fun and colorful. I know this will appeal to many young users who will find the new options exciting.

I understand that the majority of Office users are in the "business" world. However, when selecting styles please don't forget the education industry. Excel can be fun! Little things like great looking charts are one way to get kids to enjoy using spreadsheets.
# June 6, 2006 8:54 AM

Harlan Grove said:

technochrista,

For some of us (probably many more than you'd believe), 'fun' is ipso facto nonprofessional. Use in elementary schools is ipso facto nonprofessional. However, I fully agree that Office 2007 seems better suited to children than business users.
# June 6, 2006 9:37 AM

Harlan Grove said:

Picky: the Economist chart starts with the bottommost gridline at 1.2 y-value with the x-axis implicitly at 0 y-value with the little symbol underneath the 1.2 denoting a discontinuity in the y-axis. You'd need to use another text box to hide the 1.0 in Excel, and yet another one for the footnote for 06 and 07 years.
# June 6, 2006 12:05 PM

A User said:

The line chart is mostly ok. But rather than supplement with a bar chart on the same numbers, a chart of comulateve compound price growth could better facilitate interpreting the data.

(Tick marks are superfluous in bar charts with uniform gaps.)
# June 6, 2006 1:08 PM

Darren Crewes said:

I use to use excel 2003 to manage our data that we used in our charts and I would save the files in a dbf format because the original program that makes the files needs this format.  However I don't seem to be able to save as a dbf file anymore is this correct?
# June 6, 2006 1:21 PM

Ola Sandstrom said:

I need to plot in True 3D: X, Y, Z
When do you think this will be possible?
# June 6, 2006 4:30 PM

David Gainer said:

Jon, thanks for answering Neil, Patrick, and Chris’ questions.

Technochrista, thanks for the feedback.

Harlan, A User, you are correct with your additional points.

Darren, we have removed support for writing DBF from Excel 2007 due to extremely low usage.  Every feature in the product has some amount of overhead, and over time we periodically depricate features that are mostly unused.  A full list will be posted soon.

Ola, it is in our list of things to consider for next time.  Please feel free to send me an email with specific requests.
# June 7, 2006 12:15 AM

sysmod said:



At 05:20 07/06/2006, "MSDN Blogs - Automated Email" <admin@blogs.msdn.com> wrote:
Jon, thanks for answering Neil, Patrick, and Chris’ questions.

Just to be clear on this, he did not answer my question. He pointed out what people *should* do (bar chart rather than pie) which is fine, but  my concern is with what people actually do, and how they can be protected from mistakes that they might not see immediately.

My question was in the context of your request of what should be done in Excel to improve error checking. So I pointed out the source of one kind of error, in charts, and asked what people thought would be good ways of making it obvious that this is an error.

Let me repeat the question, so that it is still on the table:

In reply to:
David Gainer : "any other error checking options (what others would you like to see?) "

I'd like to prevent negative numbers in pie charts being presented as positive. I'm not sure what would be a good way (folded back slices?) to make sure this misinterpretation is not overlooked.

Another way might be to add shading, or explode the slice, or add a tooltip, or some other non-interrupting way of doing it. IOW, I'm not expecting an alert message.

TIA

Patrick
# June 7, 2006 9:11 AM

Chris Gemignani said:

David,

I think this is a great exercise for the Excel team to go through and the final output really looks quite good.

I'd like to back up Jon Peltier's point about labeling the series by data labeling specific points. This is what I do too when line charting a limited number of series. Tufte makes some good arguments that labeling series rather than supplying a legend is more effective. Could you add a feature to label the series--automatically determining optimal label placement--rather than having a legend.

Thanks.
# June 7, 2006 9:35 AM

Rebecca said:

Will the new version allow use of a formula or cell reference for the chart title instead of allowing text only?
# June 7, 2006 9:41 AM

Richard Black said:

Nice work - have you also been though Edward Tufte's work to see how chart should be done?   R.
# June 8, 2006 5:13 AM

Microsoft Excel 2007 (nee Excel 12) said:

Today we have a guest post from Sander Viegers, a user experience designer who worked on many aspects...
# July 12, 2006 6:12 PM

opslaan als dbf | hilpers said:

PingBack from http://www.hilpers.nl/434095-opslaan-als-dbf

# January 23, 2009 7:39 AM

Microsoft Excel A Bit More On Charting | Wood TV Stand said:

PingBack from http://woodtvstand.info/story.php?id=3820

# June 1, 2009 2:29 PM

Microsoft Excel A Bit More On Charting | bar stools said:

PingBack from http://barstoolsite.info/story.php?id=7751

# June 19, 2009 2:44 AM
New Comments to this post are disabled
Page view tracker