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.
Today's author, Robin Wakefield, a Program Manager on the Excel team, discusses charts.
As a new program manager in Excel one of my first tasks was to understand what problems users encounter when building professional charts in Excel. I defined professional as following the principles of Edward Tufte by reducing the amount of non-data ink on a chart. On an earlier post, there were a lot of comments about this particular aspect and I agree we could do better in helping users accomplish this goal. I also looked at blogs and publications which were known to produce professional looking charts to determine what the salient aspects were. As I researched this further I found there were a lot of remarks on the web that pertained to Excel 2003 charting so I thought it would be worthwhile to share what I found; specifically what changes you can make to a default chart in Excel 2007 to make it more professional looking. Your feedback on this would be greatly appreciated as we are considering how we can build this into the product going forward.
In order to illustrate this let’s try to recreate a chart I found in the Wall Street Journal.
I manually created the data so it may be a little off but when I insert a column chart Excel creates the following default chart for me.
In order to make this chart look more professional like the Wall Street Journal you can make the following modifications.
Step 1: Update Formatting Settings
With the chart selected click the Formatting tab on the Ribbon. In the left corner you can choose the chart object you would like to format and click "Format Selection" to get the formatting dialog for the object.
Vertical Value Axis
Vertical (Value) Axis Major Gridlines
Horizontal Category Axis
Step 2: Update Font Size
Select the chart and on your home tab select a font size which is large enough that you can still read the words but they are not the emphasis of your chart. In this example I set the font size to 8.
Step 3: Remove Legend
Given the example only has one data series the legend is unnecessary in this case.
Step 4: Add Title
Add a title to the chart to tell your reader what the chart represents. In order to mimic the Wall Street Journal chart you need to move your title to the upper left hand corner and set the alignment to left. You also need to format the text so the title is in a larger font and bold whereas the subtitle is in a smaller font.
Step 5: Add captions
In order to add the citation you need to manually add a text box with this information to your chart.
Step 6: Add a Vertical Axis Title
In order to display the units of the vertical axis without showing a % next to each number on the axis the best way to do this is to add a vertical axis title and enter %. You can then move this title to line up with first number on the vertical axis.
As you can see I was able to get pretty close to the original chart by changing formatting settings and adding some additional text. The thing I was not able to accomplish without a hacky workaround was to fill only the bottom portion of the plot area with a gray color.
Avoid Unessential Formatting
Throughout this article I have focused on what changes you can make to the default chart but I would like to take a moment to discuss the principle of avoiding unessential formatting. As part of Office 2007 the concept of Themes was introduced. This was an Office wide effort and is shared across Word, PowerPoint, and Excel. For more information about themes and how they work in Excel see this post.
Within each Theme there are multiple Chart Styles available which is described here. The way this works is the chart styles are predefined to map to accent colors, fills, and effects etc which are defined in the Theme. When a new theme is added the chart styles are automatically generated based on the Theme definition. Although this was a great way to produce many choices for users it had an unfortunate side effect in that some of the effects/fills are not optimized for charting as they are also used on shapes, smart art etc.
See the styles matrix below for an example of the styles that are created based on the Office Theme definition.
In order to reduce the amount of non-data ink I would recommend sticking to the first row of styles as they do not have any effects applied to them. As you move down the rows, effects get more intense and in some themes the effects make the chart unusable such as the Metro theme which adds a glow around the columns.
Within one row we provide a variety of color options as well. The first option is always a grayscale and this will be your best choice to reduce the amount of non-data ink. The second option is all 6 accent colors in the Theme and options 3-8 produce a monochrome chart based on one of the accent colors.
If you would like to add color to your chart the following themes are good options for color variety where the colors are differentiated but no one color stands out from the others.
Another great resource to help choose the right chart type for your data and programmatically determine when some of the above changes should be applied is the Chart Advisor. Check out the post here.
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.
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.
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.
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.
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.
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:
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?
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="peltiertech.com/.../" 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:
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.