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.
Tips
How-to
News
Videos
Stories
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.
Chart Types
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.
Chart Layouts
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.
Comments: (26) Collapse
So let me summarize :
- no new chart type
- simple chart wizard replaced by multi-pronged separate type/layout/theme all aimed to "make it easier and quicker for the user to create good looking charts"
- no improvement on custom charts (the ability to mix two or more chart types is currently limited to 2D charts).
Color me unimpressed.
Mmmm...eye candy.
Give me something other than empty calories. Are there any other improvements?
Hi David,
So no new chart types - but has the charting object model changed in any way?
<>
Got news for you. In the business world charts are all about eye candy. Whether you're doing a business plan, marketing plan or other professional document, the visual appeal of good charts go a long way. For well over a decade, Office applications have lagged behind improvements in hardware display technology. Chart appeal in current versions of Excel & Powerpoint is pathetic. So the new visualizations and predefined layouts are definitely welcome. Thanks, David.
Some suggestions before it's too late:
- box and whiskers charts. Several authors out there have created add-ins and workflows. Why not (finally) build them in
- interactive turning off of points in scatter plots. Extremely helpful in statistical best-fit line drawing. Turn the point off by excluding from the series, but leave it on the plot by changing color & symbol.
We need flexibility to make variations, being able to add multiple types into the same chart. The current line & column is an inflexible nightmare, where you have to add an empty or dummy series to get in an extra line or column. Plain & simple also speaks volumes to our managers, as do labels and balloons, answering "What's this?". I also note there is a limit on the number of charts one can have in a workbook. Yes, we occasionally need to represent data on more than the 125 or so limit we can currently have. Instead we use combo boxes for the user to choose criteria for their dynamic chart. That is sub-optimal when you want to compare different sources silmultaneously without the clutter of having them on the same chart.
> For example, there’s a chart layout designed for making histograms - the third choice in the second row of the column chart layouts.
It looks like the histogram is the fourth (last) choice in the second row.
I applaud the new layouts.
I hope that the next version of Excel will include 3-D scatter plots.
It would also be nice if there were a quick way(s) to demarcate the forecast region of a time-series line chart from the historical data (e.g., using a vertical line and/or shading the forecast region) without using the drawing toolbar.
To extend Andy's point, I have been waiting for a way to assign element positions based on formulas somehow, so a text callout will follow the point or column it is pointing to or a historical/forecast line split will move as the chart is resized etc.
the biggest issue is usually when copying from excel into another application and the unpredictable resizing nature. but it would be super nice to be able to attach comments to certain elements somehow.
also, are the quick layouts customisable?
The extensibility of Excel's charts would be *greatly* enhanced if we were able to position Autoshapes (and controls) using the same coordinate system that is used to plot the data.
For instance, if a chart's Y axis went from 0 to 10, and it's X axis went from 0 to 100, we could use the following to place an autoshape at the center of the chart's plot area.
With ActiveChart
.Shapes(1).Top = .Axes(xlValue).AxisValueToPoints(5)
.Shapes(1).Left = .Axes(xlCategory).AxisValueToPoints(50)
End With
Such a function would provide a simple way to add specialized widgets that would respond dynamically to the data being plotted.
Looking at what users do is not necessarily the best way to think about what should be done... Just because everybody does it, doesn't mean it's good.
Please, consider talking to experts instead of polling the public (which is what you've effectively done). Read Edward Tufte's works, for example - start with "Visual Display of Quantitative Information", www.edwardtufte.com/.../books_vdqi.
The average user is far too easily charmed by visual effects that are actually misleading and detract from the quality of the chart. 3D charts are a good example - and the cone charts in Excel are an absolute abomination, because they distort data. Likewise the average user is likely to add noise to their charts in the shape of gridlines, borders, patterned lines, shadows etc.
Excel should guide its users towards good design, not towards common design mistakes.
The title of this post is "Charting I..." with the promise of more detailed discussion over the next week or two. Also, the very first post of this blog mentioned that huge improvements have been made to the charting engine. Should we therefore not wait for the details to unfold before drawing conclusions and passing judgment? Reading through the responses so far, one would think that the only thing added is "eye candy." The subsequent discourse should be interesting, given folks passion for their charts and the annoying shortcomings of the charting features in the current versions of Excel, which have suffered from a decade of neglect.
<<<<
"the promise of more detailed discussion over the next week or two. "
Hmmm. This post says it all about chart types. I am running the beta 1 and don't see anything related to chart types that is not covered in this post.
So what else should we expect related to chart types?
This release is a big bang release, a rare opportunity to shuffle a lot of things, including adding new chart types. You know that, because of the RGBA colors, 1 million rows per worksheet, ..., that many of those features won't be able to work in earlier Excel versions anyway (something the marketing brochure perhaps not tell you).
Well this appears to be a small step in the right direction however:
- as mentioned, when we prepare charts for our Board, combining multiple charts is quite common, and I still have to use hacks to produce a multibar stacked column chart which I find incredibly annoying (and difficult to share within the organization).
- it doesn't appear that charts can have broken axis. Not having this functionality, which has existed in many graphing programs for a decade, require numerous work arounds, which usually make the data harder to interpret by the audience
- simplifying the interface is great, for the "average" user, but many of us use Excel to prepare charts for Boards and sr. mgt. and adding functionality would elevate Excel from a merely adequate tool, requiring hacks and intimate knowledge of Excel to create charts that provide the desired visual message, to a tool that is superior and head and shoulders above the competition