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.

Yes, allowing this to be automated is definitely in our future plans. We just wanted to get something out quickly for people to start playing with so we could see how well it worked in the wild first.

Minor nitpick: I'm not the sole author of this service, everyone on the Dabble DB team worked on it to some degree.

Looks great, but I'm not sure about the prospect of sharing all of my data. Both from the perspective of sharing it with Dabble as a data processor, but also the need to transmit it over the internet.. less good!

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.