Talent traffic chart with chord diagram in Excel

posted Nov 24, 2013, 2:30 PM by r   [ updated Jun 22, 2015, 4:28 AM by Krisztina Szabó ]

Harold: A visitor is all I ask for. A temporary companion just to help me pass a few short hours in my lonely life.
Monster: Mmm.
Harold: Thank you, Lord. Thank you.
Monster: Mmm.

by The FrankensTeam

The idea

The first inspiration to create a “circular cospmograph” came from Mike Bostock’s Uber rides by Neighborhood. Reading his article we learned the name of this visualization tool: chord diagram. We have seen lot of stimulating solutions on CIRCOS site.
We wanted to do something similar in Excel, although initially it seemd too difficult… but we are The FrankensTeam, we are able to resurrect the dead, so, for us, nothing is impossible. :-)
We started to build the first model using the technique developed for cosmographs. Only needed to modify the points of the lines using circles instead of vertical coordinate sets. After improving the formulas and clarifying the chart series, we managed to make the chart to be dynamic.
We developed a great way to position the bands without crossing each other, so the final visual result is more clear.

Talent traffic example

Surfing on the net we found a better data set to create a good example for this new Excel chart. Gene Lu’s post is a critique about a Talent Traffic chart. Our version uses similar logic as his "Talent war bar graph breakdown" as stacked column chart:

"Ratio is number of employees moving from Company A to Company B for every one employee going in the other direction."

You can see our version on the top picture. The color coding is the same as above, symbolizes where the talents came from. This way you can also see the ratio of employee left for example Yahoo (purple) because both the from and to side of the lines are visible on the same chart.

In the Excle file mouse rollover technique is used to highlight the companies one by one, while the rest of the lines are lighter. Here you can see Microsoft highlighted:

Dynamic versions

We created two simpler versions which are dynamic up to 10x10 data, so you can easily use the chart with your own data set.
On sheet MY_TABLE you have to write the category names to column A only, they will appear in the header row accordingly. Then fill in the table with your numbers, and... take a look at the chord diagram on sheet CHART!

You can download a rollover-based dynamic version.
Or you can choose a no-VBA version too, where you can select the data series to be highlighted from a validation list.
Update: we created a slicer-version of the chart also available under the download section.

Data and technique

The data table of the chart must be a square matrix where row and column headers contain the same categories. The rows and columns could represent from and to data. The same color is used for the numbers in rows, but you can easily change the coloring logic by transposing the data table.

Technically the chart is built from 3 chart types. A pie chart is used to add data labels, a doughnut ring to create the circle with the colors and xy scatter with smoothed line to draw the from-to chord lines in the inner part.

There are two points for each line within the circle to draw the curves. We added a curve parameter to change the distance between these two points. For me 0,4 gives the best visual result, but you have the chance to play with it. For example this is how 0,7 looks like:

For the base chart we use as many data series as the number of rows in the data table - it is necessary to have different colors for the rows, but it makes the highlight technique a bit challenging. The trick is that we use the same data series for the highlight mechanism, but we change the data range behind the series. When you choose a category to be highlighted, only those lines are visible which goes to or come from the given category. On sheet SUPPORT we grouped the line-block coordinates to support this highlight logic, so it was not difficult to build up the series using some OFFSET formulas.