Create a Grid for Multiple Charts - step by step guide

Over the past few months, I have created numerous multiple charts, which are small charts within a only one Graph. I did this using just one Excel chart.

Below is an example of a multi-pie chart which I made for a Polish friend.

Creating a dynamic grid is the first step for more complex charts where not all cells will necessarily be occupied and not talking about only pie or donut charts.

For me it was a new frontier. Until a few years ago it would have been impossible to create a multi-chart in only one Excel chart. Today, thanks to the new functions and features that Excel has made available, all this [I quote Gene Wilder] "can be done!"

I hope I have sparked your curiosity, so the first step in our design is to create a grid. Each cell will be one mini chart area.

Dynamic Base

We must already think of the grid is dynamic based on some parameters:

• Number of rows and their height
• Number of columns and their width
• Distance from the grid origin
• Space between the chart area and the 4 vertices that will delimit the plot area

These 4 points could ideally also be the points where to place the labels

So what I want to get is something like this:

Imput Parameters

Now we can define our parameters, The image shows the name used and the description. The sheet name is Setting.

Input parameter names:

n_r_g =Setting!$C$2

n_c_g =Setting!$C$3

h_r_g =Setting!$C$4

w_c_g =Setting!$C$5

it =Setting!$C$6

s_r =Setting!$C$7

Name with formula:

max_g =Setting!$B$9

The formula I used in Setting!$B$9:

=MAX(n_r_g*h_r_g+s_r;n_c_g*w_c_g+s_r)

Square and Max Serie

It is very important that the plot area is perfectly square and that the horizontal and vertical axis have the same measurement. To get it we will use two tricks.

The first is to add a Pie type series with value {0} this will make the Plot Area a perfect square.

Add che Square Serie:

The second will be to add a series where x and y have the same value which will be = at the largest of all x and all y values (the orange dot that you see at position 13,13 and which we will then hide by removing the indicator).

Add the Max Serie:

We transform the graph into Combined and assign the xy scatter type to the Max series and the Pie Chart type to the Square series:

Series of Centers and Vertices

The next step is to create the matrices of the x and y values of the central points of the grid.

In I2 we write this formula for the x:

=(MOD(SEQUENCE(n_c_g*n_r_g,,0),n_c_g)+1)*w_c_g-w_c_g/2+s_r

and we define name

x_c =--Setting!$I$2#

In J2 we write this formula for the y:

=(INT(SEQUENCE(n_c_g*n_r_g,,0)/n_c_g)+1)*h_r_g-h_r_g/2+s_r

and we define name

y_c =--Setting!$J$2#

Add the Centers Serie:

for the other 4 points we will use the x and y calculated for the centers as a basis.

We will need an x further to the right and an x further to the left,

x_v_1 =x_c-w_c_g/2+it

x_v_2 =x_c+w_c_g/2-it

and a higher y and a lower y

y_v_1 =y_c-h_r_g/2+it

y_v_2 =y_c+h_r_g/2-it

We can add the 4 series:

Grid Serie

for the grid you will need a number of vertical lines equal to the number of columns +1 and a number of horizontal lines equal to the number of rows +1

We will use only one formula for the x's and one for the y's

x formula in F2:

=TOCOL(EXPAND(VSTACK(SEQUENCE(n_c_g+1,,0)*SEQUENCE(,2,1,0)*w_c_g+s_r,(SEQUENCE(n_r_g+1,,0,0)+SEQUENCE(,2,0)*n_c_g*w_c_g)+s_r),,3))

define the name

x_g =Setting!$F$2#

y formua in G2:

=TOCOL(EXPAND(VSTACK((SEQUENCE(n_c_g+1,,0,0)+SEQUENCE(,2,0)*n_r_g*h_r_g)+s_r,SEQUENCE(n_r_g+1,,0)*SEQUENCE(,2,1,0)*h_r_g+s_r),,3))

define the name

y_g =Setting!$G$2#

The last step is to add the labels (in our example case we will add the position number. The formula in L2 is:

=SEQUENCE(n_r_g*n_c_g)

define the name

label =Setting!$L$2#

we add the labels to the Center series and use the name label to assign the values to a range

In conclusion we can change the number of rows and columns, their size, the internal dimension and the position from the origin by playing with the parameter values

you can download the file by clicking here or by visiting my website E90E50charts