Monday, May 5, 2008

Visualising Tabular Data - Rule Based Cell Highlighting

In Interactive Tables you saw how by making tables interactive it is possible to 'interrogate' the data contained within a table, such as filtering it by row according to the contents of cells contained within a particular column, or by sorting the table according to the value of cells in a particular column. By highlighting rows, columns or individual cells, certain data elements can be emphasised in order to communicate a particular point to the reader of presentation viewer, and so on.

Many spreadsheets also allow you to use rules that can automatically highlight particular cells too.

Highlighting Cell Values

In Google spreadsheets, this is achieved by selecting one or more cells (for example, selecting a column by clicking on the column label (A, B, C etc.)), right clicking on one of the selected cells to pop up a context sensitive menu, and then selecting the Change format with rules option:

change format with rules

The rules allow you to set the colour of the text contained within a cell, or its background, based the value of each cell's contents.

So for example, in the following diagram, I have defined a rule that will change the background colour of every cell in a particular column if the value of a cell is less than 100.

google spreadsheet - change colours on rules dialogue

Why might highlighting cells containing values that fall below - or above - a particular value be preferable to sorting a table and by eye glancing down a particular column to see which rows are sorted above or below the value of interest?

Charting Tables

In many situations, presenting data in tabular form can be just plain bewildering, even with interactive tables. With too much data in a table, the numbers may start to just swim before your eyes and lose all meaning.

Even with simple tables, it might be the case that a visualisation of the data 'explains' the data in a more useful way than just seeing the raw numbers (I'll talk about why in a later post).

So how can we visualise tabular data in a meaningful way? In common with many spreadsheet programmes, Google spreadsheet offers a set of predefined chart times that can be created using a 'wizard':

google spreadsheet charts

In the following posts, we'll look at how bar charts, pie charts, and histograms can be used to visualise tabular data, discussing the reasons behind why we might choose each particular chart type, before going on to consider some more elaborate animated visualisations that truly bring the data alive.

No comments: