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!

Hi Derek. I too use Excel 2003, and so I guess I don't have the bar transparency feature that Shawn proposed to make the gridline appear to float over the bar. Still, Shawn's idea would work to make the gridline float over the bar but appear transparent on the background. He simply needs to change the dotted gridline color to white. The white will show briefly through the transparency (may appear off-white) but will be indistinguishable against the backround. I'm guessing that for the NY Times graph, they did a rough mockup in excel using ugly colors and ugly fonts, and then a designer traced it (to preserve the scale) in Illustrator and beautified it with color and fonts.

Great work. Keep posting to benefit many like me.
Can you also help me in constructing graphs on a mckinsey chart that we use at our office. My problem is to edit the text boxes and graphs every time i need to update the data

Ughh. This square pie is horrid. Possibly a good example of some folks trying too hard to "spice" up data visualization. I absolutely agree with your feedback regarding this being awful. Kind of like, A for effort, but F for results. I am still debating whether this is better than a regular pie chart. My conclusion is no. A regular pie would have at least shown this a little bit better. The best way would have probably been a bar chart.

Good analysis. You are right about the color problem. I am (mildly, I believe) red-green color blind and I basically can't tell the difference between the "return to earth in a different form" and "live in a different place" sections without looking for the thick grid line in between. Even with the line it took me about 5 seconds to find the break.

Excellent article. It seems that CBS is well aware of the online fans yet they continue to depend on Nielsen's. In an article yesterday Leslie Moonves is quoted as saying he wants Jericho viewers in front of the TV because that's how CBS gets paid. This man needs to get a clue. We are in front of the TV and we've gathered many new viewers so CBS needs to make sure they're counted. They won't be as long as this reliance upon Nielsen continues.
Our Jericho board even got nominated for an Emmy. We're the best free advertising CBS could get.

Very interesting article and the extra lenghts you went to in order to get the WHOLE picture is greatly appreciated. I totally agree that all viewers need to be counted.

Skeet Ulrich was the reason I tuned in to the show. He is a phenomenal actor, and when he first came on the scene 12 years ago, he was touted as a virtuoso. He has matured and grown as an actor even more so than his contemporaries and deserves a freaking emmy, especially for the dramatic season finale. Old stereotypes are the refuge for the ill-informed.

Thank you so much, your article gives me hope that the networks might eventually realize having viewers sitting in front of a tv viewing there show live is not the be all end all it once was. This is not the 1950's or 1960's we have all this new technology that allows us to view shows at times that are more convenient for us. I would certainly think that someone at some network will finally come up with a way to count all their viewers. I would love to know what the criteria are for being a neilson family. Father works 9 to 5 Mom stays home and fixes dinner to be served by 5:30 so we can all sit down in front of our tv's in time for prime time television. No taping, no dvr, no Tivo, no ON Demand, just you in your chair in front of the tv. Thankfully, Nina Tassler seems to realize there is a bigger picture. Unfortunatly, Mr. Moonves, is her boss. That would be "CLUE"LES Moonves.

I sure hope Les Moonves and Nina Tassler read this because I LOVE JERICHO!!!
I don't watch online and I am not a Nielsen family, so I never get counted. NOT FAIR!!
I never missed an episode of JERICHO yet!

JERICHO ROCKS!! WATCH IT! You'll LOVE it!!
I PROMISE!!

Thanks for the article - I have been fascinated by this story (although I should admit, I've also been a participant). One thing to also take into consideration is that your numbers take into account those internet views that can be monetized by CBS - but because you are only looking at the 'official' cbs.com site, you aren't really seeing the full impact of the online audience. For example, over 100,000 fans signed a petition, and the self-proclaimed 'Jericho Rangers' have created countless sites to fuel the fan movement. Some of the most popular (no, I don't run any of these) are jericholives.com and jerichorallypoint.com.

Nice post Zach. I agree that the Nielsen ratings system is broken, and getting more broken. However, it's not AS broken as most people think. It exists to measure investment in TV advertisements - not the popularity of TV programs. These things used to be one in the same, these days they are diverging - as you point out. My guess is the divergence hasn't yet reached a point whereby the monetization of the non-TV audience comes close to making up for the shortfall in TV audience ratings revenue. Why is that? Interested to hear your thoughts (if you agree).

Completely agree. But it's a bit of a conundrum for the networks. The real value in a fanatical fan is that they consume (or are prepared to pay to consume) more of the content. But as long as the networks try and monetize shows based on advertised product consumption (ultimately), they never tap into that real value.

Let's see... 100,000 fanatical Jericho fans each paying $1.50 for one of a 10 episode season... $1.5M. Hmmm... if they paid Skeet what he is actually worth - $10/show - they should be able to break even :)

I believe the issue here is much more fundamental than network programming decisions and Neilsen ratings. The “broadcasting” technology and business model is a mass market medium which places value on aggregating large, demographically identifiable audiences.

The ultimate users of Neilsen data are the media buyers, and by extension the product advertisers. They are usually much more interested in how the ratings break down at the gender/age level. For example, when Chevrolet wants to advertise the Silverado pickup truck, they are probably going to want to target Males aged 18-34. They might concentrate their media buy on “Monday Night Football,” for instance, rather than spreading it out over many fragments of their target audience, even if those fragments are equally loyal and reliable viewers of other respective programs.

Compare this to the Google model, where the smallest advertiser can pinpoint a potential base of customers for their customers in a precise and predictable way by using contextual ad placement.

The other fundamental problem for broadcasters is that loyalty alone is almost impossible to monetize unless it is highly popular and can be franchised (“Law and Order” for example). The ancillary revenue that networks receive from web sites and merchandise are miniscule compared to the dollars that advertisers will pay to reach a large television audience.

In the first table, the second column is labelled "Website audience / TV audience", but the values in the columns are percents. This doesn't make sense to me- does 5.5% mean there were 5.5 times as many web viewers as tv viewers, or only 5% of the number of tv viewers used were website viewers? It's a big difference!

A scatterplot of web audience vs tv audience would also be useful, especially if supplemented with some reference lines (eg. 2x 5x 10x)

Well thought out, illustrated and extremely relevant. I encounter to many quarterback rankings that not only are too essoterica to be relevant but miss the mark of being actionable. I am lucky to be part of an organization that is quick to call BS on metrics that miss the mark. I appreciate this perspective and will be referencing often :)

That is an extremely well written article. Thanks a lot for sharing

Jeff, what's a "quarterback ranking"?

As a former McKinsey consultant, and now a COO of an e-commerce company, I appreciate the value of good metrics. I have to say tho that I have never seen anybody distill the essence of a good metric nearly as effectively as you have in this article. Thank you very much for your article. My former employer, Forrester Research, could charge $000s for such insight - and rightly so!

Finding the right metrics (or KPIs) to measure performance or to identify problem areas for an organisation is THE challenge, indeed. On the highest level, they are usually too abstract to be meaningful (actionable), and drilling down may easily let you get lost in a sea of details, losing to see the forest for the trees. This article nicely summarizes the problem and points into the right direction for analysis. Well done, Zach. We need you!

If you are interested in this article, you may want to take a look at Andrew Jaquith's book, "Security Metrics: Replacing Fear, Uncertainty, and Doubt". It was recommended to me but I've only just started reading it so haven't drawn my own conclusion yet.

Your blog is great, but your navigation links don't work (Firefox, Windows XP). Diminishes your credibility, which rests on being uber-cool tufte-style usability geniuses.

I've got FF & WXP here, along with the rest of my office. Links work fine.