Anatomy of an Excel chart - Cosmograph dynamic version

posted Sep 25, 2013, 3:51 PM by Krisztina Szabó   [ updated Sep 25, 2013, 3:55 PM by roberto mensa ]
Dr. Frederick Frankenstein: For what we are about to see next, we must enter quietly into the realm of genius.

by The FrankensTeam


Now we will focus on an improved, dynamic version of cosmograph.
In the previous post we shared the first file, there you can read about the background data and you can find the posts and books inspired the base version.
In the past days we were playing with this chart and the data, and… we found a treasure. This chart is really versatile, eye-catching and a great tool to visualize complex data. We made lots of changes (a real evolution!) to make it easier to use.

Let's summarize the changes while we are going through the parts and the features of the chart:
(At the bottom of the page you can see an anatomy-styled picture where you can find the parts maked by letters.)
  • The most important is that you could visualize the details of the row and column totals of any data table. That is why we changed the structure of the underlying database, so this version works with 2D matrix data. You should add row and column headers, these will be the labels and the rollover selectors too. In our example the data is migration data by regions in row/column structure, but certainly you can use different data where the row and column split is not the same.
  • Take a look at the data table, then the chart and you will realize an imoportant thing. On the chart the categories appear in the same order as in the data table. So if you change the structure of the table, the chart will change accordingly. It makes possible different ways of data analyse: You will have really interesting result by sorting the rows largest to smallest, while columns smallest to largest. Another option if you have the same categories in rows and columns, you can use the same order, so the pairs will be clearly visible.
  • The chart is fully dynamic: You can add new rows and columns to the data table (on sheet "MY_TABLE") together with header. The data and the labels will immediately appear on the chart. The header text will automatically be part of the rollover-selectors.
  • The total data (e, g) on the left and right side of the chart are separated by “empty bands” (f).  You can change the size of these “bands” (in cell Q2, next to the chart, on sheet "END") - certainly you can set it to 0 if you would like to see the chart without separators.
  • For better understanding and better visibility, the labels are positioned to the middle of the intervals (c).
  • The highlighting method is different. If you compare the two versions, you can see now the parts are highlighted correctly, starting from the real position within the total, and not always from the top of the interval (a).
  • The title of the chart (b) is also dynamic, it shows the name of the selected data.
  • We separated the calculation steps into 3 worksheets for better understanding.
  • The calculation is faster because of the simplified data table and smaller number of data points.

Click here to download the Excel file.

We hope you find this chart useful and interesting. Please share your thoughts about it here.

Finally, as in great anatomy books, here is the "Anatomy of our Excel Cosmograph" picture:

Legend:

a) highlighted region
b) dynamic title
c) label centered
d) smoothed lines
e) non-highlighted data total
f) separator band
g) highlighted data total

Ĉ
roberto mensa,
Sep 25, 2013, 3:51 PM