Skip to content
 

Draw a circle in an Excel chart

By default, Excel has a limited number of charts. That does not mean that those are the only charts one can create. It turns out that with a little imagination and creativity, we can format and configure the default charts so that the effect is like many other kinds of charts.

One of the more versatile of charts is the XY Scatter chart. We can use it as the base for many data visualization tasks. Recently, for a client, I used one to create a radial org chart as in Figure 1. Such a chart is also called a Node-Link chart or a Reingold–Tilford Tree.


Figure 1 – Example of a radial org graph created in an Excel XY Scatter chart
after removal of all identifiable information and the obfuscation of data
necessary to protect the client’s confidentiality.

A lot of “out of the box” work into making this chart. One key element was the set of equidistant concentric circles that provide a visual reference for the small colored dots. This note demonstrates how to create concentric circles in an Excel XY Scatter chart.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0610%20draw%20circle.shtml

Tushar Mehta

2 Comments

  1. Joseph McDaid says:

    Chart looks fantastic, great work Tushar! I’ve bookmarked this for future reference – now I just need to figure out how to do the Reingold-Tilford tree part ;)

    • Tushar Mehta says:

      Hi Joe, Thanks for the compliment. I hope to post more about the tree part and even share the code but two things to consider:

      1) I did this as a crash project for a client and so the code is not as “clean” as I’d like it to be. That makes it difficult to share but I hope to do so.

      2) A few months after I completed the project I discovered D3 — open source, web-based graphics package. It might be worthwhile investing the resources to figure out how to transfer Excel data as JSON to a web page (automate IE via VBA) and use D3 in that page to draw the tree.

      The first time might take a lot of time because there are so many “moving parts,” but if it all comes together the benefits should be substantial. ;-)

Leave a Reply