Chart table – as the name suggests – is a beautiful fusion of a chart and a table giving rise to a visually appealing yet information rich presentation of data. Not that the concept itself is anything new – experts have openly and vociferously championed the cause of the chart table, its various facets have been discussed at length in numerous forums and the latest versions of excel come equipped with tools that further aid their creation (namely sparklines). However what is a bit surprising (and saddening) is the lack of widespread adoption by analysts and managers even though they have been around for a while now.
As you can see, packing charts and data together helps conserve space, brings a lot more visibility to the data than a chart ever would and provides the decision maker with much more information in a much smaller area. The advantages of a chart table are obvious but the question is – how often do you see something like this during the course of our day-to-day jobs? How many times would you find a senior executive put aside his regular cocktail of oversized pie, line and bar charts (which are inadvertently followed by a couple of slides filled with “analysis”) in favor of the chart table? How many times have you wished that the speaker knew that a gigantic bar chart that occupies half the screen does not convey any more information than it would have had it been 1/4th the size. And how many times have we seen potentially constructive arguments nipped in the bud because the presenter did not have the underlying data at hand and had to make the excuse/promise of sending it to the attendees post the meeting/presentation.
So well, here is my attempt at pushing the cause of the chart table further. Let’s begin my making one!
Insert Data into the Chart Table
The first step in creating a chart table is to feed it with the data that you want to use. For the purpose of this particular example, the data set consists of the list of top 10 cities with the highest number of Fortune 500 companies (Source: Wiki) alongwith their combined revenue figures.
Although some may prefer to prepare the layout before they feed it with data, having the data already present in the sheet can hasten and aid the process of creation of the proper layout. Personally I tend to go with the former but I ain’t gonna stop you if you want to do it the other way. Getting back to business – once you have the data neatly residing in a spreadsheet, it’s time to move onto preparing the layout.
Prepare the layout for the Chart Table
Assuming that one has identified the data points and the type of charts that need to be presented to the user, the next step is to prepare a layout for the chart table. There are no hard and fast rules regarding this though the following may be kept in mind:
- Use more than one color only if it adds value.
- Ensure that various data elements have proper separation both within the cell and from other data elements.
- Adjust (increase/decrease) the font-size so that each figure occupies the least amount of screen real-estate without hampering and user comfort.
- Lines and borders help define the structure. Make proper use of them.
Let’s look at a sample layout.
If you look at the layout shown above, you can observe the following:
- Lines demarcate the space occupied by table headers and data as well as reserve screen “real-estate” for the entire chart table.
- Values have been provided with proper formats (currency etc.)
- Column widths and row heights have been adjusted so that the least amount of space is occupied without hampering readability.
- Cell alignment has been changed so that values are vertically center-aligned which spaces out individual values evenly and provides proper separation.
- The more important data points have been formatted in bold which helps them stand out from the adjacent values.
With the layout in place, let’s move onto adding some charts to the chart table.
Adding the charts to the Chart Table
Having completed the first two steps of getting the data and then laying it out in the proper format, we focus our attention on adding the charts. Since we would like to compress the chart table into the most optimum amount of screen real-estate, we take advantage of the In-cell charts. In cell charts are essentially miniature version of the standard excel charts and sport only the most basic chart elements – essentially the bars, lines and the points that represent data and practically nothing else. To align the chart properly with the chart placeholder’s boundaries, you can enable the Snap to Grid option from the Drawing toolbar.
You can then adjust both the chart area and the plot area so that the charts fits snugly in the space appropriated to it. If you plan to show more than one in-cell chart in a chart table (and when they tend to present essentially similar/comparable data points), you may want to take a bit of care to ensure that they adhere to the same scales and axis. This point becomes important when you realize that owing to the much smaller size of the in-cell chart, the reader may not be able to easily grasp these subtleties and jump to the wrong conclusion. However in our case, both the charts tend to show mutually exclusive data points and hence this does not pose a problem.
(Note: Please bear in mind that the design and the choice of data points used in the creation of this chart table are primarily for illustration – they merely depict what can be done and may not necessarily represent the best form of analysis that can be carried out on the data used in the example.)
Once we are done with all these steps, here’s how our chart table is going to look like.
You can download a sample worksheet containing steps on how to create a chart table