Juice Analytics

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.

Interesting idea, Rupesh. Centering those darn labels was the most time consuming part of this exercise, and they still don’t look all that great. The slight leftward bias in my labels also relates to charting internal margins and other Excel esoterica. Again, Grrrr.

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.

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.

Good question, Derek. I’m not a huge typography geek, but the NYT font looks like Helvetica Neue which is a really nice multi-weight version of Helvetica that’s available free on OS X, but not in Windows land. It’s a font we use a lot for presentations here in Juiceland.

Microsoft released a bunch of good new fonts with Windows Vista. They’re available as a free download for older versions of Office if you google for “Microsoft Office Compatibility Pack”. If you’re interested in typography and use Windows you should get these fonts.

I tried using these fonts on the graph and there is a slight improvement. See above for a version with improved fonts.

Thanks, Doug. Lazyweb, we thank thee!

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.

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.

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

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

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.

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

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?)

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.

Sesha, we have developed an approach for automatically updating PowerPoint slides (charts, text boxes, tables) from Excel spreadsheets. I’m not sure if that is exactly what you are referring to. We can discuss offline if it is.

my father died of lung cancer…hahahha jking

Nice charts, and comparisons within sex are easy. The problem with two-sided “tornado” charts like this, is it is very difficult to compare the two sides, male vs female. Obviously men have more prostate cancer, and women more ovarian and breast cancer. But I can’t tell the differences in pancreatic, colorectal, or non-hodgkins.

An option would be to put more space between the males bars, and insert the corresponding female bars.

What was the basis for sorting? It wasn’t by value, nor by alphabetical order.