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.
Thank you, Robin. It's a pity you weren't around when they put all that bling into Excel 2007. Then perhaps there might be zero steps in making charts professional.
I echo dbb's comments. Excel is capable of producing excellent, professional charts; by default, however, it produces charts full of junk. This post is very useful, and it is good to see a post such as this finally appearing on a Microsoft blog. Maybe this is the beginning of a change in Microsoft - a recognition that bling does not equate to good visualisations.
Whilst defining the format for a 'professional layout' is always going to be an almost impossible task there are some things the default chart could be to help.
1. No legend when only a single series
2. Axis labels to Low with negative values
As you mention the selection of Designs is not exactly chart friendly, so being able to add our own designs to the Gallery would be a great feature. But as a minimum the removal of the bevel heavy choices to make bad design harder to achive.
look forward to more blogs from you Robin.
When you have a chart that has a lot of detail in say the smaller numbers where you would want to emphasize that, but also show that there are larger numbers in your chart - i have seen charts with a split in the middle, cutting out the mid section in order to detail the information on the high and low sides of the figures. How do you do that in Excel 2007?
One of the biggest disappointments in Excel 2007 was the charting. We heard how great it was going to be. Unfortunately, the only enhancements were nearly inconsequential improvements (a more flexible log scale is the one that comes to mind).
The drawbacks to Excel 2007 charting have been astounding. The Chart Wizard has been retired, and now the user is faced with a multitude of options for formatting, most of which add meaningless decorations to the charts. No new chart types were added, and no ineffective old types were retired.
The redesigned dialogs have greatly hurt productivity. For example, six tabs are now required to change the lines and markers in a series instead of one. The logical Chart Options dialog has been chopped up and the pieces strewn across several tabs, if you can find them.
Thanks for showing that it's possible to make a reasonable chart in Excel 2007, if you navigate around the treacherous formatting rocks in the stream. The only comment I'd make about your retooled chart is that you should use a lighter gray rather than a dotted line to de-emphasize the gridlines. Dotted lines use less ink but draw more attention than solid lines.
Robin - Refreshing...but this post amounts to heresy, considering that both Excel and the Chart Advisor break all the rules of "Professional Chart" design as per your definition. You're a brave soul walking the halls of Microsoft.
Susan - Note that the length of the bar encodes its value (only valid when the bar starts at 0), so cutting out the middle may not be the best thing to do. With widely ranging values, I'd recommend using a log scale.
Andy C. - I wouldn't get my hopes up. No doubt we'll see some new charts in Excel 14 (some good, some bad), but we're unlikely to see any change in default options, if the Chart Advisor philosophy is going to define the future.
Andy P. - I've often thought that having a user-defined gallery would be nice. For now, I create templates with the defaults I want to use.
Jon - Yeah, some things are still broken in Excel 2007's charting functionality. I'm holding my breath that SP2 will resolve most of the remaining issues.
I've come to terms with chart tools layout in Excel 2007. All of the Chart Options dialog box items (and more) are under the Layout tab (an utterly confusing name choice given that there's a group labeled "Chart Layouts" in the Design tab). For a single chart, it's more work making the trip to the Ribbon to change settings. However, if you have to make changes to multiple embedded charts, the Ribbon layout works better than the modal Chart Options dialog box, IMHO.
The key benefit to the modal dialogs was that you could visit a dialog, make numerous changes to an object (e.g., a legend or a series), then select the corresponding object in another chart, press F4, and apply every change to the selected object. Click - F4, repeat this two-second operation for each object you want to change.
In 2007, F4 has been eviscerated. At best, it repeats only the last minor individual adjustment you've made, not the whole sequence of changes made during the visit to the dialog.
This impediment is such a major change to working with Excel 2007 charts, that an SP will never fix it, and I fear that we'll not see it in a whole new release either.
I also doubt that SP2 can make more than a few minimal superficial changes.
Jon, I was looking at this from a very different angle, but you're right - reduced F4 functionality, together with sketchy macro recording, dialog box quirks and outstanding "bugs," do hamper chart productivity in Excel 2007. Because I spend a lot of time in Excel 2007, my senses have been "dulled" to the shortcomings.
Colin - I move back and forth, so every visit to 2007 is an exercise in frustration. "This was just working fine...!"
Lately I've been amusing myself with the misguided thought that I can actually design and build a custom charting interface for 2003 and 2007, combining the best features of each and the best features that both don't have. I'll be greeted with flowers and the cheers of the liberated.
I've built little pieces of this interface (an error bar function, and a half-baked replacement source data dialog), and I've assembled a broad array of nicely laid out but uncoded dialogs. I fear it would take months of full time effort, though, so I'll probably just build a little piece at a time, as I need a feature in one of my projects.
I was excited about this post and wanted to use it with a related link on how to use Outlook to Paste Special a Microsoft Office Excel Chart Object into a template but could not find this option in the Outlook Paste Special menu.
Does anyone know why this option is not available in my MS Office Small Business 2007? Also, since this is a link, does the original excel file need to be on a public server?
I have come across two more issues this morning.
In one case I was making a combination chart, and I needed the secondary category axis to be invisible and lined up with the primary category axis. In Excel 2003, this just happens without a lot of work. In 2007, some strange force is preventing the two axes from coexisting on the same line. The primary axis is pushed away from the plot area by the invisible secondary axis. I worked around it by hiding the primary axis line and fudging the secondary axis line and markers, while still showing the labels for the primary axis.
The other thing was in a VBA procedure I wrote to automate creation of this chart. In 2003, the procedure runs instantly, and the chart appears in finished form as the mouse is being released from the click that drove the procedure. In 2007, I had to switch the order of a few steps (which makes no difference in 2003), then the code took many seconds to run (30 seconds on a 1GB VM). The line that took 30 seconds (the rest of the procedure totaled about 0.06 sec):
Application.ScreenUpdating = True
The chart took 30 seconds to redraw when the procedure ended!
Thanks everybody for your comments!
Jon - I agree on the gray lines rather than dotted as a best practice. Also I feel your pain around performance issues in rendering your chart. We aware of this and actively investigating.
Kevin - Not sure about version specifics but the original excel file only needs to be in a location that is accessible by the person creating the email to update the chart. The person receiving would not need to have access.
Jon, The axis problem you mention is probably one of those "rationalized" behavior changes that made its way into Excel 2007. Nice work around. I notice that the primary and secondary axes labels flip position when you change the position of the primary axis in relation to the tick marks - on or between. Perhaps some use can be made of this for multi-level category axis labels...
Forgot to mention - In Excel 2007, the slow chart operations in VBA are really painful.
"Rationalized?" I think nobody on the team tested this kind of chart, so they didn't notice anything strange. There wasn't even a serviceable charting engine until RTM, so we couldn't try out all of our longstanding tricks. There are a zillion things like this that I bump into all the time.
Another: you can't add an XY series to a stock chart. Fortunately they didn't break the underlying line chart-up/down bars-high/low lines functionality, so you simply start with a line chart, not a stock chart.
There are so many things, that I hadn't noticed your trick with flipping the axis labels. Of course, if they came up with an axis object model with a decent hierarchy, then things like this would become irrelevant.