You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
A few posts ago when I described the work we did in the area of “great looking documents”, I mentioned charting. I am going to spend the next week or two covering charting in detail. For this first two posts, I want to cover how we have used the ribbon to make it possible, with no more than 3-4 clicks, to create a wide variety of professional-looking charts.
When talking to customers about charting in Excel, one of the big pieces of feedback we hear is how hard it is to make a chart that looks ready to publish. Generally, users aren’t graphic designers or experts in information visualization, yet they still want a result that looks professional and communicates their information effectively. With advent of the ribbon, we have a new UI design approach – results-oriented design (which is discussed in a blog post here). In a nutshell, the idea is to give users a couple of reasonable choices for professional designs, then allow them to mix and match those choices into a professional document. While we’ll still give users access to the detailed features that make their documents look good, they should be able to get close to a final result in just a few clicks. To that end, chart creation in Office 2007 can be as simple as making four straightforward choices that give users access to a vast range of possibilities. The four choices are: what type of chart they want, how they want the elements on that chart laid out, how they want their chart formatted, and what document theme they would like to use. I’ll talk about two of these choices in this post, and the other two choices in the next post.
In current versions of Excel, when a user creates a chart, the first thing they need to do is select the type of chart - column, line, scatter, pie, surface, and etc. In Excel 2007, we’ve made the variety of chart types available a lot more visible, and we have offered help for users to choose between them. To insert a chart, a user would start with the Insert tab. Excel 2007 has an insert chart type “group” (7 related controls) on the ribbon’s insert tab. This makes it easy to pick a chart type, with large icons and tooltips that describe when to use a particular type.
(Click to enlarge)
Note that we have provided galleries for the most common chart types – column, line, pie, bar, area, and scatter – with the remaining chart types surfaced in a 7th gallery. For those who want to browse through the full list of chart types, or change the type of an existing chart, the Create Chart dialog makes it easier to explore the list of chart types and pick the one you want.
And for those who love shortcut keys, Alt+F1 will now create a chart object with the default chart type, while F11 still creates a chart sheet with the default chart type.
Although we do hear many customer requests for new chart types, unfortunately we weren’t able to add any in Office 2007. We’re not yet planning the next version of Office in depth, but no doubt that will be considered again, as this is another area where we hear a lot of feedback.
Once a user has chosen a chart type, there are a variety of charting features that can help the user communicate their data effectively. In previous versions of Excel, these are scattered around through a variety of dialog boxes, making it hard for all but the most diligent users to take advantage of the settings. For Office 2007, we studied a massive number of charts that we found in publications, books, and Excel spreadsheets to determine the most common combinations of chart elements such as titles, legend, data table, etc. From this, we created a gallery of predefined chart layouts (e.g. combinations of chart elements) that can be applied with one click. Here is what the gallery looks like for a bar chart
And here is what the galleries look like for a few other chart types. Note that each chart type has its own unique set of chart layouts.
Line Quick Layouts
Pie Quick Layouts
Scatter Quick Layouts
I have not included the pictures of the chart layouts for all the chart types, but I’ve tried to include those for most of the more popular chart types. We have tried to included layouts for the sorts of charts we are commonly asked about. For example, there’s a chart layout designed for making histograms - the third choice in the second row of the column chart layouts. There’s a chart layout designed for making sparklines – the first choice in the third row of the line chart layouts. There’s a chart layout designed for making pie charts with labelled slices instead of a legend. Each chart layout is a useful high-quality chart which presents data in a different way. We are still refining the set of chart layouts and their icons, so expect some changes before Office 2007 is done.
Re comments about "average" users, I recall a pizza metaphor a while back. Live with this fact: if you REALLY want anchovies, you ain't gonna get 'em here.
It'd be nice if (1) text values in chart series were treated like blank cells rather than like numeric zeros, and (2) Excel wouldn't ***, whine and moan every time I have nonpositive values in ranges charted against logarithmic axes (it'd be nice if there were a second button or check box in the error dialog Excel displays that allowed me to tell Excel to ignore this error at least for the rest of the session).
Charting is all about data visualisation. The new looks are definitely a leap forward to produce them professional and "eye candy", now supporting >>56 colours, transparancy, glow. I personnaly like what Stephen Few is saying about this (www.perceptualedge.com) and no doubt there are many others who understand the business of data visualisation.
It wd be great if Excel cld provide assistance in the choice of colours (e.g. suggesting colours with optimal contrast) - but in the end Excel is just a tool. It's the user who decides waht and how to proesent information.
However, I fully agree with Helen that these stunning looks may be counter-productive when the user is offered (too) many great-looking options without any guidance in how to bring across the message effectively. Maybe Microsoft could provide help here in white papers alike help files for the less talented among us? Anyway, I can hardly wait when Excel 2007 will be available.
I, like Helen, try to adhere to most of the tenents of Edward Tufte when creating charts.
I want my charts to be as effective as possible at **communicating**, not at looking pretty.
I don't mind all of the bells and whistles - I just won't use them - but please try to incorporate as much as possible to help us 'Tuftians'!
Help us increase the information-to-ink ratio!
One example, axis labels at the values of the data points, not at predetermined every-n increments.
Hi everyone – thanks for the comments. This isn’t going to be the only post about charts, so bear with me.
Graham, The core object model is largely unchanged. Solutions that used the Excel 2003 chart object model will still work. We have added new object model for new features such as applying templates, chart layouts, styles, and OfficeArt formatting for chart elements; e.g. glow, shadow, etc.
Dave, Justin, Andy, Vic, Nathan, Harlan, HEK, Greg, everyone else, thanks for the suggestions. Please take a look at the public beta when it comes out shortly and give us your specific feedback.
Andy, no 3D scatterplots. I like your idea about historical vs forecast – it is something we have talked about, but not something we had time for this version.
Paul, I think your copy/paste scenario will improve a lot in Office 2007. More on that in a later post. This version, the quick layouts are not customizable.
Helen, we looked at a wide variety of sources, including business publications, textbooks, scientific journals, etc. The goal is to provide a wide variety and allow the user to select the style most appropriate to their situation. Again, interested in your specific feedback when the public beta is available.
Has the limit on the number of cells allowed for charting been increased over Excel 2003? I wanted to chart US Census data by Census Tract (~64,000) but it failed. I was force to take a 1 to 5 sampling in order to chart the data.
What are charts really all about? Eye candy? For those really interested in this issue I suggest looking over any or all of the books by Edward R. Tufte on this question.
Box & Whisker charts can already be created in Excel, in fact many variations. Create one and then save it as a template chart, (I assume we can do that in 2007 although I haven't tested yet).
Justin - its pretty easy already:
1. Create a chart from all of your data
2. Select one of the series you want displayed as a different type and choose Change Chart Type from the Design ribbon.
Similar technique works in earlier version of Excel.
Jamie - It looks to me as if the new version support the same number of data points as the old version - 32,000.
it looks so different!
and maybe hard too
if not ,it's just name changed to 2007,it works the same and looks more beautiful,just so?
Will the bar chart have the capability of adding data elements (in the datasheet) as labels e.g. bar chart that shows growth in revenue for different product group over 5 years and then over each year revenue, I want to show the product revenue % relative to the corresponding year (while keeping the chart scaled by revenue).
Jamie - no change there unfortunately.
Karim - this is possible for some types (i.e. Pie), but not bar, at least not without a workaround (like putting shapes on the chart).