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.
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.
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 peltiertech.com/.../pie-chart-rounding-in-excel, and as C.H. Art showed in hiw comment.