Its been a few days since I posted. In case you were wondering what I was up to – yours truly was busy coding a program to create treemaps in Excel. Treemaps are interesting creatures. You can pack a few thousand data points in a single treemap and utilize just about the same space as any of the traditional charts. The term ‘treemap’, as we see it being used nowadays, actually refers to the inward growing members of the treemap family (which have the advantage in that they use a finite amount of space and grow inwards rather than outwards, first conceptualized by Ben Shneiderman). The other set of treemaps are the traditional ‘tree-root’ shaped charts – growing from a parent node and outwards.
Coming back to the topic. So over the past few weekends I was busy developing an add-in for excel that one could use to create treemaps in excel. I pretty much finished testing yesterday and was quite satisfied with the results. I again spent a few hours yesterday night to rerun through the code and cut down the rendering time by about 23%. A couple of UDF’s and code optimization elsewhere helped improve performance. The program as it stands now, can plot reasonably large treemaps – in most cases the limitations are only those imposed by the version of the Excel in use and processing power available at hand. The only limitation that I ran into was with having more than 32,500 unique items at any one level – that may or may not be a concern depending upon the data set one is planning on using to create the treemap. Here is a snapshot from a random data set which had 6 levels in the hierarchy and about 384 rows (with 526 unique data points). The first treemap took 0.82 seconds on my new AMD X4 965 BE (more about it later) with Excel 2007.
Here’s the same data set with coloring at the last (6th level) and without names.
The number of items across levels and the number of levels themselves are limited only by the processing power. For treemaps with a few hundred items and a less than a dozen levels in the hierarchy, processing power should not really be too much of a concern. However larger treemaps will definitely need robust computational resources and take longer to plot. Its the nature of the beast itself – large treemaps are resource intensive – though I’ve tried to develop the code with the aim to keep rendering times as a liner function of the size of the data set.
I am also thinking that I could perhaps launch it as a commercial application and see what happens. There are a few other commercial and free treemap applications out there. I am hoping this is going to be one of the first few based completely on Excel (there is also a microsoft treemapper which I haven’t been able to get to work, yet).
Keep watching this space for more.
Update: You can try out the Treemap demo here and if you like it, you can buy the treemap utility here.
Thanks Ajay, can’t wait for giving it a try!
Merry Christmas
December 24th, 2009 at 4:54 amDitto, looks good!
December 24th, 2009 at 1:36 pmRoss
@Ross / John – Thanks. This ought to be out soon. Just giving final touches to the color palette.
December 24th, 2009 at 10:15 pmRegards,
Wow… this looks promising. I once attempted to create a treemap, but gave up mid way as mind couldnt get the math behind it. Waiting for it…
December 28th, 2009 at 5:46 amFirst thanks Ajay for your work, coincidentally, Fabrice, the coder of the sparklines for excel addin introduced a treemap in his incredible addin (http://sparklines-excel.blogspot.com/2009/12/treemap-switch-on-light.html).
Still if you ask me I would think on other charts cause still I haven’t found the need of a treemap to help clarify any business situation. For me they are just a pretty way to present data.
December 28th, 2009 at 10:37 pm@Oliver – I downloaded the workbook from Fabrice’s website and it looked really good – especially the color gradients were very well thought out. At first look, I thought that it works for data presented as a single column. Mine has taken a slightly different approach in that it works across data arranged as a hierarchically across columns. I would be releasing a demo version pretty soon.
@Chandoo – To be honest, it fried mine a couple of times too. The deeper you go, the more you drown 🙂
December 29th, 2009 at 1:18 amThanks! I’ve been craving to find a way to create a treemap from my data in Excel too.
I checked out that Wiki link at the bottom of your article and the first vendor I clicked on (HiveOnDemand) said it worked directly with Excel…via copy/paste, or saving as a .CSV file. (It even allows auto-updating from Google spreadsheet – which I now use!)
Follow-up Report: I’ve been using the free trial over the weekend and it works brilliantly. I highly recommend it…very professional…and it works quickly!
(Reference: http://www.hiveondemand.com)
December 29th, 2009 at 3:50 pmGreat ! looks really nice and fast !
Seems that we have been on similar lengthwave lately 😉
Did you code in VBA or .Net ?
Happy and successful 2010 to all !
January 1st, 2010 at 7:27 amI have some technical knowledge…but not enough to call myself a VBA or .NET programmer!
Happy New Year!
January 5th, 2010 at 1:32 pmStrange, I downloaded this TreeMap-demo prompts according to website, does not appear TreeMap Chart, only there’s an Excel pivot table. . .
January 28th, 2010 at 2:38 amEXCEL 2003 SP3
@leo – Can you please send me the workbook with the data. Let me take a look. You can please send it to databison[at]gmail.com. I’ve tested the treemap with a number of data sets in Excel 2003 and haven’t seen a problem.
January 28th, 2010 at 2:47 amRegards,
I just discover your site.
December 27th, 2016 at 7:36 pmIt’s wonderful.
Thanks
any version for excel 2013?
August 3rd, 2020 at 4:53 am