Not bad for a v1 guys - I especially like the waterfall chart as a funnel visualization. The comparative column and bar charts seem a bit noisy though - might be as simple as using thicker bars and columns, then again might not.

very cool!

why did you exclude the basic line chart from the 'relationship' category but include the two-axis line-column chart?

Excellent tool, thanks guys! I've sent it round the office so now everyone can create clear, uncluttered charts. RIP grey background!

Awesome. It would be great if you add Numbers for ac format for uploading.

Guys, great tool much like the Chart Cleaner! I also understand that you aren't going to be able to please everyone.

The good: Excel downloads that show the table templates (specifically for the waterfall chart, which many people have trouble with), color schemes and formating are excellent, interactive selecting is great, inclusion of bullet charts and pushing less chartjunk is a big plus.

Opportunity: I see some downfalls with the options that are presented to me. Much like some of the dashboard tools, it gives me the option of what's available, but does not indicate what is optimal. For example, by selecting Composition, I get everything from bar charts to waterfall charts, to pie charts, to tables. It may be helpful to possibly rank which are the most effective. Why use a pie chart when a bar chart is a better choice. Or, use a stacked bar chart (I find very ineffective) when a line graph is probably better.

I think the objective here is to show what's possible and appeal to the masses. I just question your design when you have previously voiced that some of these break the fundamental rules of data visualization (pie charts).

Outstanding work!

This is awesome! Great time saver. Thanks for the great posts and tips.

Great tool! With Chart Cleaner you open mi mind to a whole new world of graphics. This is an excel-ent addition to my arsenal. Many thanks!

Great!
Using it right now for my scorecards!

I really appreciate your work... the charts are really cool... I am going to use them at work... thanks guys

I'm sure it's a great tool, but http://chartchooser.juiceanalytics.com/ makes Firefox on XP fall over.

Hmmm... is it only available for US users? Doesn't work from my place - Switzerland. Neither in Safari nor in Firefox. Just some kind of legal note appears...

thank you for this! it's going to be very, very useful for entrepreneurs and business owners.

Nice job!

The line chart uses a legend to identify the 4 data series in the example. Legends add an extra step for chart readers, they have to move their eye back and forth between the lines and the legend. This can interfere with quick, easy chart interpretation.

Why not use series labels to make it easier for the reader? I've modified your line chart file to add a procedure which adds series labels instead of the legend.

You can see it <ahref="http://processtrends.com/toc_chart_doctor.htm#Replace_Legend_with_Series_labels"> here</a>.

Kelly

Here's the link again. It didn't work when I used <a href="... <.a>

http://processtrends.com/toc_chart_doctor.htm#Replace_Legend_with_Series_labels

Kelly - Nice job! The link in your post above didn't work, so I just went to your site to find the example. I am a big fan of your changes. I would always opt for series labels versus a legend. They take up less space and make it more visually appealing.

Well that's one way to confuse their search engine, and fluster their business case.

Ever elusive
Juice mocks the google refrain
Categorize this!

Hi Chris,

the screencast is really amazing. I learned some very helpful tips. Please do others like this one.

Now, there is only one thing that you did not include in your graph (which is a long time doubt I have): the gridlines crossing OVER the bars. I have seen several graphs by Tufte and Phew that show white gridlines crossing over grey bars; I love that! Isn't it possible to do it on Excel?

As for the graph itself, there is one thing I did not like. IMHO, there should be one additional piece of information, something like "probability of death". Not sure if this one would make. My problem is that the graph might be used to say, for example, that prostate cancer has a worrying number of new cases, and deaths might increase. However, since we do not have information regarding old cases to compare to deaths, the simple design might lead us to think deaths will highly increase as a consequence of high number of new cases. Sounds reasonable?

Anyway, thank you very for the screencast.

Nice job Chris. I had to laugh a few times as you were getting some push-back from Excel. Sometimes the first cut is better, or more entertaining, than a polished one... Showing how to format the number so MEN and WOMEN are labeled at zero was very helpful. The NYT did a very nice job setting this chart up. Keep up the great work!

Easier workaround that space guesswork (to centrally align the category labels), Chris, is to add another column where you can put in the number of spaces required; and use the formula =CONCATENATE(REPT(" ",no. of spaces,category label). Some hit and trials will give the result.For a long list / to start with something scientific before hit and trial, here is the solution:
- Figure out the length of longest label(LEN function).
- For each label, use the formula =(length of longest label- length of current label)/2
- Logically this should put in the correct number of spaces in front of each label.. however due to some reason, (may be the width of space character), the alignment seems slightly leftwards. So I add "1" to the formula above.
- In most cases, this would render center alignment. In the skewed cases, one can overwrite the formula with a higher/lower number and achieve desired result.

Wonderful screencast.

Small correction - Apologies.
Read the formula =CONCATENATE(REPT(" ",no. of spaces,category label) as
=CONCATENATE(REPT(" ",no. of spaces),category label)

Great screencast! Thanks for sharing it. I've been using Excel for awhile now but I've never had much of an opportunity to incorporate graphs. When I do, its often a frustrating experience.

Nicely done. Excel graphing masters have their own special kung-fu.

Side note: Assuming you're using a Mac notebook (sounds like it), try using the "fn" key in conjunction with "delete". This should make Windows delete as you expect.

Gridlines over the top is easy if you roll your own using a Line or XY (Scatter) series.

There's a limit to the different types of series that can be combined, but quickly glancing at the design, it doesn't immediately seem to me that you've reached it yet.

A question for you typography experts: what widely-available free font most closely mimics the lettering in New Yorks Times graphics?

Very nice graph, learnt a couple of neat tricks there.

Presuming that we'd be creating this graph to be printed out, or exported as a graphic, I'd be tempted to use either a text box or do something with the camera object here for the series labels.

It might require some altering of cell heights, but I'm sure something could be achieved.

I've only just started making screencasts professionally, but this is one amazing example. Something to look up and aspire to.

For the headline face, NYT is using Franklin Gothic (Bitstream, not ITC); for the body text, Helvetica. Their online features (at nytimes.com) seem to be using Helvetica for all text.

Great screencast.

Nice work Chris. I thought this chart looked sharp when I saw it on reddit the other day. There have been a number of nice graphics and interactive "infographics" coming out of the NYT lately. Any idea what they use to produce these?

Can we look forward to a script/screencast showing how to reproduce this in matplotlib/python?

-Pete

Thanks Chris and Doug. I had a Franklin-a-like already, and I've downloaded the Powerpoint 2007 Viewer to get the new Vista fonts, and used Calibri for a Helvetica substitute.

This is a great tutorial, I learned a lot on this. I've always been turned off by Excel's charts but never spent the time to learn how to make them better.

Wrote it up on my blog too: http://richardsona.squarespace.com

Well done, guys.
I was somewhat intrigued by Daniel Waisberg's wish to have the gridlines OVER the bars. Dereks' suggestion to make a combichart with your own gridlines is one possible solution but I don't think so easy as he said for most of us. I see two possible ways to get these gridlines on top of the bars (although I would like to add that I don't think it's very necessary to do so). I share both ideas here, for discussion purposes.
1. You can use a stacked bar chart with a thin "white" lining around the fill. This requires a bit juggling with the spreadsheet with conditional numbers (for the length of the bars; if it exceeds the default value between the grid values, it needs to be cut off).
Note: I fear this is not easier than derek's suggestion. It also requires some forward thinking about the grid, the default value taken as a variable.
2. An overlay chart. Essentially you split the chart in (a) the bars and (b) the value and category axis, including axes values and grid lines. Make sure the plot area is transparent, and that the dimensions are EXACTLY equal. Now position (b) on top of (a) (Select chart (b) and use ALT+mouse to snap it into position over (a) accurately).
Note: this method is a bit inflexible in the sense that resizing is elaborative. Moreover, in this particular example of a combined chart it may not be so easy to do.

I hope this makes sense.

Well, I meant for someone with the skills to reproduce Chris's graph. At that level of play, the drawing of arbitrary lines over the top usng a XY (Scatter) graph range should be peanuts.

Unfortunately I can't get the Quicktime screencast to work, so I don't know in enough detail how the trick was done to advise how to take the next steps. It would be something like this: create a range of cells with the following values:

<pre>X-value Y-value
-150,000 0
-150,000 12

-100,000 0
-100,000 12</pre>

...and so on. Fix the secondary x and y axis scales to the appropriate values (if they're not already being used for something else--if they are, have a think aboubt designing your way around the problem). Format the line so it is the same as the background colour (white). Now they will only be visible when they cut across the bars.

Build-your-own axis scales (which is very similar to this) is also a highly useful technique; I would go so far as to say it's the most powerful single piece of Excel hackery there is. Both are described in the Juice article "<a href="http://www.juiceanalytics.com/writing/2006/08/tufte-charts-in-excel/">Tufte Charts in Excel</a>" by Zach.

Make sure you install those Windows updates that they were bothering you with during the screencast....

I bet that is a very good illustration of how to produce a good presentation, particularly in a business point of view.

All marketing managers should learn to produce these types of presentations!

<i>Make sure you install those Windows updates that they were bothering you with during the screencast....</i>

They're not bothering me with any updates, either in Opera or Internet Explorer. Both browsers, after taking a long time to download the very large screencast, report:

"QuickTime is missing software required to perform this operation.
Unfortunately, it is not available on the QuickTime server."

I don't see any suggested action arising from that message.

I am not an Excel guru but I did manage to get the grid lines to show through the bars in the graph.

Below is a link to a screen shot of the effect using Excel 2007.

<a href="http://photobucket.com" target="_blank"><img src="http://i159.photobucket.com/albums/t152/robertshawnmitchell/transparent_bar_graph.jpg" border="0" alt="Photo Sharing and Video Hosting at Photobucket"></a>

Here is a close up of the chart with both data series using transparency.

<a href="http://photobucket.com" target="_blank"><img src="http://i159.photobucket.com/albums/t152/robertshawnmitchell/closeup.jpg" border="0" alt="Photo Sharing and Video Hosting at Photobucket"></a>

Shawn, you're using a feature that is new in Excel 2007, but you've misunderstood the effect that is being sought. It is not to have a black gridline visible under a translucent bar (though there are ways to acheive that effect pre-2007 also).

It is to have a white gridline visible *over* a solid bar, but invisible on the white background. That is acheived by a custom line series, but not, I believe, by built-in gridlines even in XL2007. They're still underneath the data series.

Cool... my recollection of font usage:

AIC Franklin Gothic -heds & subheads, 12pt. (1 col.), 14pts. (2cols.), etc... also used for smaller uppercase (7pts.)

Helvetica & Helvetica-Light: body text (9pts.)
Helvetica-Bold (8-9pts.): bolded upper & lower case text.

Helvetica Light-Oblique: source line (7 or 8pts?)

AIC Imperial: credit (5.8pts?)

I can't get my second graph to flip and display in reverse. It does one of two things:
1. the order of the Y axis values sorts in the opposite order;
2. only one of the data series moves to the opposite side. the one on the secondary axis stays put.

Any suggestions?? Thanks!