Back
Excel

How to Create a Professional Chart using Excel 2007

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.

image

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.

image

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.

clip_image004

Chart Area

  • Set Border Color = No Line.

Vertical Value Axis

  • In order to keep your scale the same regardless of changes in the size of chart set the minimum, maximum and major unit options to fixed amounts.
    • Minimum = –.1
    • Maximum = .08
    • Major Unit = .02
  • Set Line Color = No Line

Vertical (Value) Axis Major Gridlines

  • Set Line Style = Dash type to deemphasize the gridlines as they are helpful in understanding the data but not the emphasis of your chart.

Horizontal Category Axis

  • Set Axis Labels = Low
  • Set Major Tick Mark = None
  • Label distance from axis = 0
  • Uncheck Multi-Level Category Labels

Series 1

  • Set Gap Width ≈ 34%
  • Fill = Solid Color and choose a red color to match the WSJ chart

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.

image

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.

image

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.

  • Office
  • Civic
  • Median
  • Paper
  • Technic
  • Trek
  • Urban
  • Origin

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.

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags