Chart Table – When Chart meets Table




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

chart-table-with-chart-and-data

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.

chart-table-data

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:

  1. Use more than one color only if it adds value.
  2. Ensure that various data elements have proper separation both within the cell and from other data elements.
  3. Adjust (increase/decrease) the font-size so that each figure occupies the least amount of screen real-estate without hampering and user comfort.
  4. Lines and borders help define the structure. Make proper use of them.

Let’s look at a sample layout.

chart-table-layout

If you look at the layout shown above, you can observe the following:

  1. Lines demarcate the space occupied by table headers and data as well as reserve screen “real-estate” for the entire chart table.
  2. Values have been provided with proper formats (currency etc.)
  3. Column widths and row heights have been adjusted so that the least amount of space is occupied without hampering readability.
  4. Cell alignment has been changed so that values are vertically center-aligned which spaces out individual values evenly and provides proper separation.
  5. 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.

snap-to-grid

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.

chart-table-with-chart

You can download a sample worksheet containing steps on how to create a chart table

chart table

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. David Z wrote:

    This is awesome – I consider myself not-quite-expert excel user, but I’ve never seen this sort of thing done before. I work at a big market research firm and I’ve already mocked up some presentation charts based on this technique, they look amazingly more consistent than typical Excel graphics work!

  2. Ajay wrote:

    Hi David,

    My thoughts precisely. There’s so much potential here considering the existing low rate of adoption. The other part, as I said in the article and something that happens (and quantitative research is a good example as you’d know better) is that with the data at hand, a chart table packs in far greater authority and credibility that a standalone chart ever could.

    Regards,

  3. DaveG wrote:

    This is awesome but I was not able to duplicate the effect in Excel 2003. I downloaded your example which looks fine. When I try to duplicate it my numbers come in inverted (Tokyo at the bottom). This is one reason why I never use horizontal bar charts. I must be missing something simple.

  4. Ajay wrote:

    Dave you’ve got to fix the vertical chart axis. Double click the axis – the one that contains the category names (Tokyo, Paris etc) which open up the “Format Axis” box (in Excel 2003). Click the “Scale” tab and turn on the box which says “Categories in reverse order”. That’s should do it for you.

    Welcome to da TaB !!!
    Regards,

  1. Pareto Chart Table - A More Meaningful Analysis | Excel & VBA - da Tab Is On wrote:

    [...] first tabulate the data for the chart. The issues get listed in descending order of occurrence along with the frequency of occurrence and [...]

  2. Table Redo | Bob Droog wrote:

    [...] Source [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel