Analysing Data with Custom Conditional Formatting

28 Nov 2017
article 10 minute read

How to use conditional formatting to visually analyse data, including non-numeric data such as grades, levels and categories.

Visual data analysis is a potent tool for quickly understanding patterns, trends and areas for action. In an educational context, this can be used to track performance over time, or as a comparative tool to highlight achievement in different areas (e.g. subjects or skills).

Example Layout

The example google sheet above (populated with random generated test data) is also a generic template that you can use right now to analyse your own data.

Populating with Data

While you might be lucky enough to have purely numerical (continuous) data to analyse, frequently it may be fully discrete (such levels, categories or grades) or a mixture of both types. Building upon the techniques explored in our tutorial on handling grades, this template sheet can handle all data types (discrete, textual and numerical).

Conversion Magic!

Sadly not, but close! If you are analysing non-numerical data like UK 1-9 GCSE Grades or standard A-E grading scales, you need to tell the sheet a little about the type of data you are using. This is done by creating a scale of all possible values in the column, delineated by ‘|’ characters.


This is done by putting these scales in a hidden row at the top of the template sheet. Any column without a scale at the top will be interpreted as a column containing numerical data. If you have data in a column with a scale, the scale must contain all the potential values in the column, otherwise the calculations will fail (e.g. if you are using the grade scale above, don’t include a value such as ‘Z’ that isn’t included in your scale).

You can use the example scales (in the config tab of the template spreadsheet) or customise your own. In order for the scale to work reliably, make sure it ends with a ‘|’ character but doesn’t start with one (e.g. D|C|B|A|). You may also choose to use words / phrases in your scale too, which will work perfectly as well.

Needs ImprovementMeetsExceedsOutstanding 

The various formulas in the template sheet will convert the cell values into numbers before performing calculations on them (such as average, median and stdevp), without you needing to do anything else!

Paste Your Data

There are five main areas of the template sheet, which are listed below. To ‘clear’ the template sheet for first use, select all the cells under the grey and black headed columns, and press delete on your keyboard. All the data will be removed, and calculations will be reset to blanks.

When you paste your own data into the sheet, you should make sure you only paste values (not formats) by using the ‘Paste special -> Paste values only’ menu command, or press ctrl-shift-v. By doing this, you won’t override any of the existing formats or conditional formats.

What else does it do?

Once you have finished pasting your data in the template sheet (into the columns with black, grey or green headers), it will generate summary figures for all your data. At the top of each column will be the aggregated statistics for each column, including:

Each row (person) will also have a set of summary figures in the columns with dark red headers. This will include similar an average and standard deviation, which are both normalised and coloured in the same way as the column summaries above. There are also:

Conditional Formatting

The ability to change the appearance of cells according to rules is a powerful tool for quick data analysis. It is important to use it properly though. Simply colouring cells according to their values is often not the best way to proceed (you’ll end up with a sea of green or red!). The key is to use format to communicate something that isn’t already conveyed by the value.

In the case of our template sheet, indicating whether a value is high or low with colour (over the whole cohort) isn’t much use, as it will merely highlight lower-performing individuals (which is already done by the average column and the rank). It is duplicate information and a waste of our precious bandwidth!

We need something we can visualise and then action, so the template sheet highlights (in red/green) especially low or high values for each row. Our approach immediately shows skills or subjects need attention for each individual.

The template achieves this by comparing the value in each cell to the average for each individual and highlighting it if it is a certain number of standard deviations away from that average. By default, any value that is more than 1.4 standard deviations from the average will be highlighted (although this can be altered as required).


The template sheet will work straight away, for a large number of rows and columns, so you shouldn’t need to do any further customisation work! If you need to add additional rows, you will need to copy down the formulas in the dark red columns. Add extra columns to the right of the last data column (e.g. before the blank end column). Conditional formats should automatically adjust to include the extra cells.

Rounding Precision

By default, the output summary values are rounded to two decimal places of precision. This can easily be adjusted on the ‘config’ tab, although it is worth considering how precise your source data is before setting this too high.

Trigger Levels

These are set in the last hidden row at the top of the template sheet and labelled high (this is for green) and low (this is for red). If you find you have too many value cells (in the black headed columns) being formatted in a particular colour, slightly increase the appropriate value here. If the opposite is true (too few cells being highlighted) then gradually reduce it until the right number are coloured! The ‘right number’ is up to you, and specific to what you want to achieve. Most people will only want to have a few cells highlighted in each row so that they can target the most important weaknesses or celebrate the clearest strengths. It’s not an absolute; it’s just an indicator of where best to target your efforts, in other words, it is the product of analysis.

Tagged Sheet, Google, Training, Education, Markbook, Example

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)