Office solution: Improving Excel's charting defaults

Here's a few improvements to last week's default chart; what changes would you make to improve this chart?

Last week, I presented an Excel chart and asked you to improve it, in any way. Today, I'll present a few of my improvements - please feel free to continue the conversation.

It's my opinion that Excel charts benefit from a minimalist strategy. Keeping display objects and properties to a minimum will improve most Excel charts. Here's what I did to improve the original chart:

  • I deleted the gray background. It's distracting and serves no purpose. Right-click the background, choose Format Plot Area, select No Fill, and click OK.
  • Sometimes you can eliminate the gridlines, but in this case, toning them down a bit is all that's required. They shouldn't compete with the data series lines. Right-click a gridline and choose Format Gridlines. In the Line Color section, choose Gradient Line and adjust if necessary (it probably won't be necessary). In the Line Style section, I chose a dash style. You can also reduce the line's width and more. Remember, less is better.
  • I changed the yellow data series to a dark green. That yellow simply doesn't show well. You might want to change the color for all the data series. Right-click the series and choose Format Data Series. Select a new color from the Line Color section. You'll also want to change the marker color(s),  most likely.
  • I deleted the legend and replaced it with a single data label for each series-it's a convoluted fix, but it's worth the effort. Right-click the data series and choose Format Data Labels. In the Label Options section, check Series Name. Make sure you don't check Values (unless you specifically mean to). This option will display the series name at each marker. You can leave it as is, or remove all but one identifying marker, as I've done. In this case, I've identified the highest value for each series. To remove individual series name markers, click once to select all. Then, click a second time (not quite as fast as a double-click) and Excel will remove all but the selected label from the selection. Press Delete. I also applied Bold and a larger font to help the labels stand out just a bit.
  • The axis values add a bit of unnecessary noise because they're so long. You want to retain the values of course, but using a custom format, you can reduce the number of digits. Right-click the axis, and choose Format Axis. In the Number section, select Custom and enter the following format: $#,##0,," M";.
  • Finally, I removed the border above and to the right of the plot area. They distract from the two axes. Right-click the plot area and choose Format Plot Area. in the Border Color section, choose No Line.

These are changes I'd make if the chart were really a line chart. If you look closely, a line chart isn't really appropriate for the data! In this case, a column or bar chart would be a much better choice for representing this particular data.

Sandy took a stab at this week's challenge and did a great job. Her chart displays the data with the chart and she added a secondary axis of commissions. She made several formatting tweaks, including a white on black theme, which can be great for viewing on screen or in a presentation. She also created a dynamic chart, in Excel dashboard fashion. Most of Sandy's changes support the dashboard look. Mine were aimed more at printing. So, as you can see, a lot goes into creating a meaningful and effective chart.

I'll be writing about this particular technique soon - using check boxes to update a chart. Thanks Sandy - you did a great job!

Fledis agrees with me on two points: the data isn't represented well with a line chart and removing the gray background. I removed the background because it's distracting, but Fledis also mentioned that it's a poor print attribute. Fledis would also use thicker lines - a great suggestion. I noticed that Sandy did that in her chart. Thanks Fledis!


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox