Square chart and Treemap in Excel

posted Apr 26, 2013, 8:37 AM by r   [ updated Jan 15, 2015, 1:42 PM by Krisztina Szabó ]
[...] Freddy: Of course, of course, I'm sure we'll get along splendidly. Oh, I uh, you know I don't mean to embarrass you, but I'm rather a brilliant surgeon. Perhaps I can help you with that hump.
Igor: What hump? [...]


The Idea

A few days ago I tumbled on the Washington Post site about President Obama's 2014 budget proposal.
I found the visualization really interesting - the squares represent the most important parts of the budget, and I can see the details of each part using the same square structure. Amazing and dynamic - I immediately wanted to create it in Excel and i thought «It - could - work!» :-)

The first question is: how the squares are placed?
The base idea is that the squares ordered from largest to smallest are placed in the corner closest to the origin of the axeses. The methodology is described here - it is an interesting problem solved with an algorithm.

I am sure that this view is suitable to illustrate a few data from 2-3 to a maximum of 10-12 (In my final file according to the original article shows up to 17 values). If the maximum number of squares is known and the squares are ordered from largest to smallest it is possible to simplify the positioning problem by assigning fixed positions to the squares. So I assigned the positions to all squares in addition to the 8 you can see in the above picture.



Square chart with Excel chart

Based on the positions, I had to compute the coordinates of the squares. It is easy using the length of the edges, and the edges are the square root of the data value according to the original article where they use the area af the square to represent the data.
As I thought about how to draw squares using coordinates, an old chart made with error bars come into my mind.
This way it is rather easy to create the chart - this is the base idea:
So only the right-bottom and top-left coordinates are needed. Right-bottom coordinates are determined by the fixed position and can easily be calculated by adding the edges of the appropriate squares. I name these coordinates as (x, y) - you can see marked with red circles on the below picture.
The top-left coordinates (x1, y1) can also be calculated easily:
x1 = x - edge
y1 = y + edge
Marked with green circles on the below picture.
To draw the sides of the squares, I will use the error bars with the value of the edge - only the sign and the direction is important:
- Starting from the right-bottom corner we need the vertical-positive and the horizontal-negative bars.
- Starting from the left-top corner we need the vertical-negative and the horizontal-positive bars.
As you see the whole structure is very simple, the only operation used is SQRT to calculate the edges and adding/substracting them. The most important part of the drawing is done by the Error bars.

(Kris was amazed by this tiny thing - Error bar was totally new for her. Later on she was inspired to create some funny charts using it... maybe we will publish soon. :-))

To make the visual result more readable, we can create a third series to display the labels in the center of the square using these coordinates:
x2 = x - edge / 2
y2 = y + edge / 2
Unfortunately to color the squares a workaround is needed. I filled the them with random lines using xy scatter chart. After making some fun with it.... ummm.... mainly Kris :-) I threw out the idea.
I really missed the effect of changing the selection dynamically and show the details as moving the pointer on the graph. It is pretty easy to do with js and forms but not in Excel. The solution I wanted to use is the mouse rollover technique. It requires the cells below the chart area to be sized according to the elements of the chart, so the chart must be static. If I fix the size of the squares I will lose the possibility of changing the data. Dead end. :-(


Square Chart with conditional formatting

So I tried another way to visualize filled squares: Conditional formatting of cells. The logic is very similar, but instead of drawing the squares on a chart I colored them directly into the spreadsheet using conditional formatting on cells resized to small squares.
I use 100x100 small cells to map the structure - in a support sheet I compute indexes - so where a square should appear, I have numbers >0, and I have 0s for the white space around and between the squares.
I place the “fake chart” on a new sheet because this way it is easy to use the mouse rollover technique to make the whole construct dynamic. As you move the mouse above the squares, a dynamic text shows the name and amount of the actual square or lists all the budget parts if you position the mouse outside the squares.

On the right-hand side a smaller chart appears illustrating the breaking down of spendings by category.

To have a full picture, I created the Revenue part too, so you can choose between
spendings and revenue from a list.

To obtain all these dynamism, in the file we use only 6 (six!) lines of code: two VBA functions used within the HYPERLINK formula.

I think and I am increasingly convinced that the rollover technique discovered by Jordan has revolutionized the way in which the users interact with Excel dashboards making them looking and working similar as graphics made with js. All the rest of the file is made with formulas and tools available to the average user.
To be honest I have not spent a lot of time optimizing all the gears ... despite this, it turns like a good Swiss watch :-)

I have also prepared a simplified version to make it easier to adapt to your data. In this file we used color scale in the conditional formatting and added a dynamic label based on the colors.
Dowload the file:
President_Obama_s_2014_budget_proposal.xlsm
Square_chart_with_cf.xlsm

Treemap with conditional formatting

Another way (a “usual” way) to visualize this kind of data is the well-known treemap chart. You can read about it here and you can see my old files here.
For this project I built up the treemap using conditional formatting and the dynamic rollover technique:


I made the legend dynamic too, so when you move your mouse over the color labels, you can see the square highlighted on the “chart”.
The files are quite big and contain resource-consuming formulas - to enjoy it you need a pc say "modern". :-) I created the files with Excel 2010 and tested in 2007. To use it with Excel 2003 it will definitely need some modifications.

Dowload the file:
TreeMap_with_cf.xlsm

Leave a comment



Ĉ
r,
Apr 26, 2013, 8:37 AM
Ĉ
Krisztina Szabó,
Jan 15, 2015, 1:38 PM
Ĉ
r,
Apr 26, 2013, 8:37 AM
Ĉ
r,
Apr 26, 2013, 8:37 AM
Ĉ
r,
Apr 26, 2013, 8:37 AM
Comments