Creating Better Graphs

[mezr]

I'm getting frustrated (and bored) with the limitation of options and ultimate quality of graphs produced in Excel. I know that I could import into Xcelsius, use Rich Chart Builder or something else to make them look more professional, but I'd prefer not to bring another piece of software into the mix.

I'm wondering if anyone has come across any good Excel add-ins that enhance the look or add variety to the normal graph options.

 

[Jon Peltier]

What makes the charts more professional? Surely not fancy 3D effects, shading, and transparencies. How about clarity? Clean up the blah default formats, make backgrounds white, gridlines light gray, axis lines and plot area borders light gray or invisible. Remove unnecessary lines and labels. Use colors sparingly. Keep it simple.

 

[almagg]

check out:

Excel Charts
John Walkenbach

lot of good ideas.

 

[mezr]

Thank you both.

Jon, I agree with your comments. I too have never felt that "pizzazz" makes a "better" chart. I've always leaned toward the "less is more" mentality, and prefer an overall clean look. Still, there have been some components that I wish could look more polished (marker options are limited, the use of shadows to make certain items subtly stand out is not available, etc). Also, like I said in my post, part of it is just that I make so many charts that I sometimes just wish there was more variety ... just to deal with my own boredom (I hate to say that, but some of the subject matter is pretty dry).

almagg, I've never seen Walkenbach's book on Excel Charts. Thanks for the tip, I just looked it up on his web site. I'll have to get it. I use his "JWalk Chart Tools" addi-in.

I just got done doing the free "test drive" of Excel 2007 on the Microsoft website. I like what I see with the additional chart options, and look forward to eventually making the switch from 2003. Has anyone been able to work with charts in the new version yet?

 

[Jon Peltier]

Mezr -

You can go beyond the built-in data marker options:

http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html

You can easily highlight a particular point by selecting it (two single clicks on the point) and formatting it, perhaps making the marker largre or a different color. To do this dynamically, you could make a conditional chart:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

J-Walk's book, his web site, and my web site all have ideas that could help make your charts more interesting. Other sites: Andy Pope's (http://andypope.info) and Tushar Mehta's (http://tushar-mehta.com).

Incidentally, I was technical editor on John's book.

 

[Jon Peltier]

Oh yeah, I forgot to mention Excel 2007.

There are no new chart types in 2007, but the default chart formats are perhaps more appealing. There are more opportunities to create garish charts with excessive 3D effects and color gradients and the like, but if you keep it simple, you can get nicer looking charts more easily. However, and maybe it's my unfamiliarity with the new interface, I feel that it's harder to format individual chart elements in Excel 2007; the dialogs have more tabs with fewer items on each, so you must tab around a lot more, and the F4 repeat and Ctrl-Z undo operations are not implemented as well as in earlier versions. Some aspects of charting work differently than in 2003, and using VBA with charts in 2007 has led me on some unexpected adventures.

I am not planning to upgrade to 2007 soon for routine work, and I've advised my clients to test all of their existing Excel workbook carefully before making the transition. But this is the same caution that is dusted off every time a major application has a major new release.

 

[mezr]

Thank you for getting back to me again, Jon.

From your web site ...

"... Tired of the same, lame choices for chart series markers? This page shows how to use any custom shape as markers in an Excel chart.

That's just what I was feeling .. and needing! GREAT site. I appreciate your taking the time to steer me there. Very useful. I just got into a rut and needed someone to wake me up to the possibilities. I really had no idea that some of these things could be done.

Also, I agree with your assessment of Excel 2007. Ultimately, I got to where I wanted to be. But I too felt like I had to "tab around" a bit more.

I'm pretty good with a spreadsheet, but I'm no MVP ... by any stretch. That's why I love coming to MrExcel. In my own circles everyone looks to me as the guy that "knows Excel". I come here to be humbled and remind myself that I'm barely scratching the surface of the program's potential, and that there's so much to learn. What's more is that I'm offered the assistance of some of the best minds in this niche. And it all comes so willingly from people like you.

Thanks again, Jon.

 

[Jon Peltier]

Please, you're making me cry .