Juice Analytics

I like this technique and use it frequently. It’s better than a regular two-axis chart, because there’s no confusion about which data goes with which axis, and there’s no false correlation between data plotted on opposite axes. In fact, it’s a rudimentary panel chart, a technique I find increasingly useful.

What’s the advantage over simply stacking two charts, one atop the other?

Hadley,

One advantage is you have only a single X axis, so it can be a little easier to keep the graphs in sync horizontally. This gives you a more unified look, but you could definitely achieve the same result by stacking.

Chris

Looks very smart! I think the q1 2007 data is projected and not actual…right?

Such a simple idea, and yet, if I’m not mistaken, you can even work it on the otherwise very inflexible Pivot Charts, yes?

(On closer inspection, you probably can’t move the axis labels up and down like that in Pivot Charts)

Derek,

It’s sweet of you to continue to look for ways to redeem PivotCharts, but I’m afraid they are beyond redemption.

Nice technique, Chris!
I usually use a similar colour for the lines (bars) and for the corresponding axis title (and/or values) to show the connection (in your example: colour the % values in the same blue as the bars for instance). The advantage is that the chart takes less space then, but your technique is definitely superior and the info quicker to absorb.
Your screencast doesn’t show how the staged category axis is made, and your readers may be interested in that too (it can even show more than two levels!).

chris (lower-case chris, above):

The Q1 2007 numbers above should be considered final.

Sincerely,
The Accounting Departments of Enron, Lucent, et al.

Okay, I checked it out on pivot charts, and it does work, and the inflexibility of the axis labels is not such a problem on this example.

But I think we all know what happens to pivot tables and charts when you hit that “refresh” button. All the format tweaking gone with little to show for it, and all the work to do over again.

This is a nice method. I have taken to changing the colour of the y axis to match the line. I guess the trick with this method in makeing sure the 0/60% (in this example) match up.
Cheers
Ross

[...] Posted by barthox on February 6th, 2007 A nice little trick in Excel that might come in handy … from Juice Analytics  [...]

[...] PS: If you are interested in interesting ways to represent data, check out some recent postings from Juice Analytics (1,2) and Information Aesthetics blog. [...]

Cool, but would love to see how to get this format on the x-axis as well!

[...] One very common need in almost any industry is to show a given market, its size, the key participant and how all has evolved over the last few years. I have used since a few years a technique I like, and yesterday I read on Juice Analytics’ weblog using basically the same technique. By the way, these guys have an interesting blog. [...]

Very good post. Didn’t check the screencast, because streaming media is blocked at work, but will do soon. I have been using this technique for some time to describe a market and its key players. I posted a chart on my site http://jcandkimmita.info/jc/?p=73 that reinforces why the technique you presented is important. Some people may say in a 1-line, 1 bar situation “why bother”. However, if you have a situation where you have to show 5 or 6 lines at the same time, using the default combination chart layout would be very confusing.

Better than excellent ! Now No. 2 in my Excel tips&tricks file

FYI: Still my No. 1:
(Courtesy of http://www.j-walk.com/ss/excel/usertips/charttips.htm)

Sub SaveChartAsGIF()
Fname = ThisWorkbook.Path & “\” & ActiveChart.Name & “.gif”
ActiveChart.Export Filename:=Fname, FilterName:=”GIF”
End Sub

and another one I use a lot:
(Sorry! – Can’t remember where this came from)

Adding a picture (e.g. a *.GIF file) to a comment:
Insert\Comment
Edit\Comment
Format\Comment
Colors and Lines\Fill\Color\Fill Effects\Picture\Select Picture

For all you charters out there
(Courtesy http://www.Windmill.co.uk)

To quickly select an entire table of data in Excel:
1. Click inside the table
2. Press the Ctrl + * keys on the keypad
All contiguous cells will be selected.

(Insert rows and columns to make sure that the data is ‘isolated’)

Where would blogs be without Excel ?

Bula !

Derek -

In many cases you can do without a pivot chart:

Pivot Tables, Pivot Charts, and Real Charts
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

For some projects, I even have code that redraws all the charts when the PT is refreshed. The code appears to the untrained eye to work as quickly as redrawing of a native chart.

Greg -

You can do this to X axes in an XY chart. Line and Column charts use a non-numeric category axis rather than a value axis.

Adam -

I learned the picture-in-the-comment trick from Debra Dalgleish (http://contextures.com/tiptech.html).

- Jon
——-
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Awesome tip!

My favorite tip is still the in-cell graph
http://www.juiceanalytics.com/weblog/?p=239

Does anyone know if this doubling up chart will work with Excel 2007 Pivot Charts when the data is refreshed?

Hi, have the links to all the screencasts changed. This is I think the 3rd or the 4th screencast that I wanted to open, but get a page that says what I was looking for cannot be found.
Please let me know if there are any other URLs for the same.
Thanks.

DItto comment above. Trying to access screencast but get a page that says what I was looking for can not be found. Are these screencasts still available?

I tried sending someone to the screencast, but I see that the link is still broken. You guys checking comments?