Matrix bubble chart with Excel

posted Oct 18, 2013, 9:18 AM by r   [ updated Sep 24, 2014, 3:22 AM by Krisztina Szabó ]
If you're blue and you don't know
Where to go to, why don't you go
Where fashion sits?
Monster:
Puttin' on the Ritz
[...]
 

Introduction

In the past we alredy have tried to represent all the data of a table in a single chart (check the Stacking Cubes Charts with Excel article). 
The purpose was to give an overview, a total picture without focusing on anything in particular. This is just the beginning then you can change the formatting and customize the chart, with color and highlighting what interests you most or use in combination with others to create a dashboard.

Download the files

 

Step to step the creation of the Excel chart

We start by defining a dynamic range, for example, in this way:

rng=OFFSET($B$2,,,COUNTA(OFFSET($B$2,,,100)),COUNTA(OFFSET($B$2,,,,100)))

 


In the picture below
you can see what we want to realize.
 

To achieve this we must add 3 series, one is to show the bubbles, the other two are used to show the column and row labels.
The chart could easily be replicated based on your own data. In a nutshell: For the bubble positions, we use fixed x and y coordinates, the bubble size is the original data. 
 

A little trick with the label formatting helps us hiding the lower values (<50 in this case) which, as is shown above, would lead to confusion.
 
 
On the below pictures you can see the defined names and the three series used on the chart, so you can easily follow the construction steps.
 
 
matrix_data serie:
arr_h=--rng
arr_x=(COLUMN(rng)-MIN(COLUMN(rng))+1)*(rng=rng)
arr_y=(ROW(rng)-MIN(ROW(rng))+1)*(rng=rng)
 

axis_s serie:
asse_x_val_h=asse_x_val_y+1
asse_x_val_x=OFFSET(rng,-1,,1)
asse_x_val_y=COLUMN(rng)*0
 
 
You need to notice that in the serie "axis_x", x values are the column headers of the table (text value, if you look the file will see that the values ​​in the cells are '2008, '2009 ... '2013) and that both series "matrix data" and "axis_x" are backed on the primary axis. Finally the bubble size is the original data, but to avoid overlapping circles, set the Scale of the bubble size to 50, so the diameter of the largest circle will always be 1.
 

To build a y-axis that displays the row headers define the following names and the "axis_y" serie:
asse_y_val_h=ROW(rng)^0
asse_y_val_x=OFFSET(rng,,-1,,1)
asse_y_val_y=ROWS(rng)-INDEX(arr_y,,1)+1
 
 
But be careful! in this case we associate the series to the secondary axis ...
 
 
The last step is to set the maximum value of the axis to a very large number:
 
 
If you add new data (new columns or new rows), the chart will resize . In the picture below we have added the row with the totals of each year.
 

Dynamic version based on the activecell

For a dynamic version based on the activecell use this code:
 
'in the Worksheet class module
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static b As Boolean

If Not Application.Intersect(Target, Range("Rng")) Is Nothing Then
ActiveSheet.Calculate
b = True
Else
    If b Then
        ActiveSheet.Calculate
        b = False
    End If
End If
End Sub
 
We have to add a new serie to represent the current value (a single bubble with black outline).
 

Use these new names:
check_=AND(CELL("row")=MODE(ROW(rng),CELL("row")),CELL("col")=MODE(COLUMN(rng),CELL("col")))
active_h=IF(ISERROR(check_),0,INDEX(rng,CELL("row")-MIN(ROW(rng))+1,CELL("col")-MIN(COLUMN(rng))+1))
active_x=IF(ISERROR(check_),1,CELL("col")-MIN(COLUMN(rng))+1)
active_y=IF(ISERROR(check_),1,CELL("row")-MIN(ROW(rng))+1)
 
 
In the picture the result:
 

The value corresponding to the active cell is highlighted and a label describes the detail of the data. When the active cell is outside of the table, it will disappear.

If you plan to use this chart in your workbook, here you will find how to copy it from our example file to your own file.

If you like this chart, you should take a look at the version we created for a user with conditionally formatted bubbles.

Ĉ
r,
Oct 18, 2013, 9:18 AM
Ĉ
r,
Oct 18, 2013, 9:18 AM
Comments