Hi. I want to share a trick I added to your repertoire. Besides separating the dimension from the metrics, I added some columns of labels built by concatenating two or more dimensions of interest. For example, I could have a "year_month" column with data such like "2008_12". Then I can do a sumif(year_month column, "2008_12", metrics column) and sumarize on one cell all metrics for December.

I use basically the same construct, but I use the GETPIVOTDATA function with data validation dropdowns and INDEX-MATCH lookups.

I think GETPIVOTDATA can be slightly more flexible, identifies errors more readily, and does not require macros to recalculate automatically. Also, showing all items for all dimensions is not very practical for larger data sets with many dimesions.

I have developed a similar approach to my Excel report applications, especially when I have multiple data sources, but with the following adaptations:

If I'm not allowed to customize the source data report, I tend to do some row-level transform formulas on the data worksheet to the right of the data itself. It could be as simple as parsing month, date, year from a text field, and calculating the quarter. Or I may be comparing multiple columns using nested IF statements and providing some preliminary "decision" about that data at the row level. When I paste in the updated source data, I just have to copy down that block of cells with formulas to match the length of the data. Dynamic ranges and pivot tables do the rest.

Even if I've done some Row-level Transforming on the Data worksheet(s), I still create a Table-level Transform worksheet with a pivot table to establish the dimensions for my data and perform consolidation -- often that's just subtotaling rows at some meaningful detail level.

Finally, I use a combination of data filters and vlookups/etc to pull the Transform layer onto the Present worksheet instead or macros. I avoid macros because most of my financial report users panic when they open an Excel file and the macro Enable/Disable warning pops up!

On a more philosophical level......

If it is a imperfect tool why waste the time learning it. And if experts are having problems staying off the underbrush the novices/intermediate users have no chance.

I personally feel excel is getting a bad name because users who have not been fully trained try to do too much - I have seen the same thing with SAP users and even developers.

I believe that what excel needs is structured training - intermediate users can do their own analysis but any formal reports should be done by power users and only these reports should be used in the business.

Incidentally if you have minimised the use of excel what are you using instead. What are your views on excel friendly olaps?

If you trade a dynamic range for Excel 2007's Tables, or write a procedure to resize a static range, you'll get much better performance.

Offset is a volatile function, and using it in a dynamic range makes all dependent formulas (but not those sourced from a PivotTable result, like GETPIVOTDATA) recalculate constantly. With a large workbook, it can get pretty slow.

Hi there,

Are there members of juiceanalytics on Twitter?

Regards.
Sandro.

Long time listener, first time caller. I must say, you guys have created quite a wealth of information around the face of business intelligence. THANK YOU for sharing all of this great information and example templates. This might be a bit of a nitnoid question as you guys are tool agnostic but have you run across Excel Services in your travels. I think you have some really cool charts but they don't travel well to Excel Services and was wondering if you had aligned some of your graphs and graphics to take advantage of that tool set for end user reporting.

waaooo, what a great thing it is !

It's far from a full solution, but the XY Chart Labeler might help you get part of the way there: http://www.appspro.com/Utilities/ChartLabeler.htm - I find it indispensable for my bubble and scatter charts.

Chris, it's easy enough to mimic the NYT chart with your data in Excel. A couple of custom series to represent labels (e.g. site name and terms), some custom formatting and you've got a decent representation. The problem of labels in this case I think is the relative smoothness of the data. the NYT data ranges from 1 to 21 which compared to your sample data which runs 0-100% is a relatively tight grouping so the bubble scales are much more similar. In other words, it was a lot easier for the NYT team to fit labels on their bubbles than it is to do on your data. I'll post a graphic later on my blog but, I think the acceptable solution is to create a size legend series...

Scott, in my attempt, I did use x/y chart labeler (a must-have for excel) but that was for custom row and column labels, I don't think it will really solve for getting the labels onto the buttons.

Hey Chris,
I was about half way through writing the blog post and encountered a lovely BSOD. Too tired to start the post over tonight so here is the URL to the screenshot of my solution:
<img src="http://blog.instantcognition.com/wp-content/uploads/2009/01/juice_concentrate_bubble.png" width="350px" height="287px" border="0" alt="Clint's Excel Bubble Chart Solution" title="Clint's Excel Bubble Chart Solution"/>

Wow, didn't think that would actually work.
In any case, to follow up on my original comment about the smoothness of the data...
The area of the bubbles in the NYT graphic scale from 1 to 21 which means the diameter has to scale 21x from smallest to largest. For the concentrate data the diameter scales from .5% to 100% - a 200x increase so it's much harder to make data labels fit into/onto the bubbles and be readable and preserve the integrity of the graphic itself

one last note ... don't do math when you're tired. As the above shows (still true, math is just wrong)

Just wanting to throw SAS' name in the hat, as another software that can do this kind of "bubble table" graph (with a bit of programming).

Here's an example of the output. I'm using gif here, which everyone should be able to easily view. SAS can also output other formats such as svg and 1200-dpi tiff, which make the bubbles look smoother (more "publication quality").

http://robslink.com/SAS/democd33/death_odds.gif

I just posted an Excel version of the chart based on the UDF I created.
Here : http://sparklines-excel.blogspot.com/

I put this together in Tableau:
http://tinyurl.com/9skr3h

Wasn't too sure what your colour was measuring, so I based it on size.

I tried it with text labels, but the text was too big relative to the bubbles. And I couldn't force the size variance to be any bigger. I would have liked to be able to make the biggest bubble bigger.

However, I think it's okay for 5 minute's work!

Andy,
yes, it's much simpler in Tableau and while I like your color as value indicator, you could just as easily have the values displayed next to the bubbles instead of 'on' them and Tableau keeps it clean.

That wordtree report visualization is absolutely brilliant, you guys rock!

This is very exciting! I agree that keyword categorization is definitely the right way to think. I'm testing out the demo version as we speak. Keep up the great work. -Jared