Short guide to create a Coxcomb Chart in Excel

#CoxcombChart#Excel #Dataviz

Creating a Coxcomb Chart in Excel is actually very easy if you use a regular xy scatter chart.

Below I will explain the few necessary steps.

Let's imagine we have a table like this:

Just for convenience, let's define two names:

n_categories=COUNTA(Tabella1[Categories])

n_angles=360

this second name, will be a parameter that we will subsequently vary and establishes how many lines we will use to color the shape, we must use multiples of 360, in the example I used 3600

The concept is that we will use radiating lines to color the shape, varying in radius based on the table values.

Next we will draw the axes, borders and reference line for the average value.

we need an array of the angles (in radians) and one for the index which will be used to recover the value.

Let's proceed:

in O2

=INT(SEQUENCE(n_angles,,0)/n_angles*n_categories)+1

the result is a matrix of 360 (n_angles) values that go from 1 to 8 (categories)

in P2

=RADIANS(SEQUENCE(n_angles,,,360/n_angles))

The matrix of angles in radians.

To draw 360 radiant lines we must have a matrix of 360 rows and 3 columns:

Column 1: center point

Column 2: end in radial position

Column 3: NA

in Q2

=P2#*0

in R2

=INDEX(Tabella1[Value],O2#)*SIN(P2#)

let's remember (basic trigonometry notions) that this is x=radius*sine of the angle https://en.wikipedia.org/wiki/Unit_circle

in S2

=R2#*NA()

These will be our x's, so let's assign the name x_1 as follows

x_1 =--OFFSET(Foglio1!$Q$2#,,,,3)

now, in T2

=Q2#

in U2

=INDEX(Tabella1[Value],O2#)*COS(P2#)

in V2

=S2#

and let's assign the name y_1 as follows

y_1=--OFFSET(Foglio1!$T$2#,,,,3)

we can add the series to a scatter plot using the names x_1 and y_1

we also add a new series and give it the name square, a value y ={1} is added, we change it to ={0}

we will need this series to have a perfectly square work area... how?

This is a trick I use in all my scatter plots where I need to have the right work area proportions.

We need to transform our chart into a combo chart. The series with value 0 will become a Pie chart series.

Attention, first bring all the series as xy dispersion, then at the end the square series as Pie chart

Pie in italian is Torta!

Adding the borders is very easy, we already have all the necessary data.

We therefore define only 2 names which we will then add to the border series:

x_c=Foglio1!$R$2#

y_c=Foglio1!$U$2#

we're almost done

Let's draw the axes (easy!)

in G2 the 8 angles (in radians)

=INDEX(P2#,MATCH(SEQUENCE(n_categories),O2#))

in H2 the x coordinates of the borders

=MAX(Tabella1[Value])*SIN(G2#)

in I2 the x coordinates of the center

=H2#*0

in K2 the y coordinates of the borders

=MAX(Tabella1[Value])*COS(G2#)

in L2 the y coordinates of the center

=I2#

we assign names:

X_GRD=--OFFSET(Foglio1!$H$2#,,,,2)

y_GRID=--OFFSET(Foglio1!$K$2#,,,,2)

and add them to the chart

only the average value ring is missing (very easy!!)

in W2

=SIN(P2#)*AVERAGE(Tabella1[Value])

in X2

=COS(P2#)*AVERAGE(Tabella1[Value])

add names

x_a=Foglio1!$W$2#

y_a=Foglio1!$X$2#

and we add the series using the names we just defined

using 360 segments the fill is not full, so we bring the number of angles to 3600 by changing n_angles name

this is the final result

the concept should be clear now, to add new external rings it will be enough to create new series where the radius will take on the additional values (value1+value2 ... value n)

That's all!

Update! New feature, variable arc size

Let's add a field to the table that we will call Value_arc.

In Z2

=MMULT(--(ROW(Tabella1[Value_arc])>TRANSPOSE(ROW(Tabella1[Value_arc]))),Tabella1[Value_arc])

Returns the progressive sum of the Value_arc values

Let's replace the formula in O2 with this:

=MATCH(SEQUENCE(n_angles)*F5/n_angles,Z2#)

Finally we need to add two series to keep the axes the same size.

One must have the maximum value used in the graph as x and y, we use this formula to find it

=MAX(R2#,U2#,W2#,X2#,I2#,L2#)

the other must have the values of x and y (with the minimum value used in the graph) pointing to the cell containing this formula

=MIN(R2#,U2#,W2#,X2#,I2#,L2#)

Below is the result