Cool Tools For Ed Tech Leaders: Spreadsheets

No, wait! Don't stop reading just yet! I know that spreadsheets sound boring, but they really aren't. Used properly, they can be essential tools in your planning toolbox, because they have three brilliant features.

Brilliant feature #1: The sort facility

Using this, you can re-order the spreadsheet by deadline, to see what's coming up, or by  person, to see who is meant to be doing what, or by area of work, to see if everything is being covered. Using the sort feature is easy, as long as you have designed the spreadsheet sensibly. That means, having a separate cell for each attribute of each task, ie date for completion, area, person responsible, and so on.

One tip: format the dates as yyyy-mm-dd (or, in USA, yyyy-dd-mm). Why? Because that's the only way you can make sure everything is listed in chronological order, if that's what you need.

Brilliant feature #2: Sumif

This is a great feature that's available in Excel, Google Spreadsheet and in OpenOffice's Calc. What it lets you do very easily is to perform the following kind of calculation:

If this item comes into category A, add it to the total, otherwise don't.

You can use Sumif to find out what you're spending money on, or where your team's income is coming from. For example, you may have categories like software, hardware, printing, and so on. Using Sumif, I once determined that 60% of my department's spending was going on photocopying worksheets. I asked my team to print off multiple copies instead (if they needed print-outs at all), which resulted in savings of hundreds of pounds over the year.

Brilliant feature #3: Conditional formatting

Use this to create the traffic light system: green for 'yes, done that', amber for 'we're getting there', and red for 'there's been a glitch'. The traffic light system gives you an instant visual summary of how you're doing as far as meeting targets is concerned.

Conditional formatting can work on either numerical values or text, or a formula. For example, you could have a column called Progress, and set up the conditional formatting to turn a cell red if it contains the word 'no', green if it contains the word ';yes' or amber if it contains the word 'partly'. Or you could set it up based on a formula 'today's date minus target date'. If the answer is less than zero, the cell goes red, and so on.

The spreadsheet was one of the first applications developed for the personal computer, and it's more than just a glorified calculator. Pretty it ain't, but boy is it useful!