That Blue Square Thing

WJEC Applied ICT A Level Unit 2 - eSkills

Note: this page applies to the legacy WJEC Applied ICT A level specification which has now been withdrawn (last resit opportunity in 2019). A new syllabus is available to schools in Wales that seems similar.
I no longer teach A Level Applied ICT but I'll leave these pages up as an archive which might still be helpful for someone, somewhere.

Part 2 - Spreadsheet Design

You need to produce detailed designs for your spreadsheet system before you implement it.

Yes, before you implement it. I know that's difficult and that you'll really want to go and build it and then come back to the designs and do them afterwards. But...

Really - design first...

Look, I'm not saying don't experiment with some prototype ideas to check that what you think will work will work. Just bear in mind that they really, really, really want you design first (and will take marks off if you don't)


Your designs need to specify what's going to happen on each worksheet in your workbook. So you'll probably need a separate design for each worksheet. That's fine.

In particular you need to think about:

Note that it's OK to do your designs by hand or it's OK to do them using a computer if you prefer. Just don't do them in Excel...

You need to think about your designs in terms of layout, content and presentation. They should specify what's going to go where on the sheet and what it's going to do.

The general rule is one piece of paper per sheet. For some sheets we've had success in the past using two sheets - one for the layout and one for the formulae and functions. You won't need this for every worksheet, but if you have a complex worksheet with lots going on on it then it might be worth using two design sheets.

Input and Output Designs

It's particularly important to think really carefully about how users will input data and what outputs will look like.

There are specific marks for this.

In input terms it means using elements such as comboboxes (dropdown boxes), tickboxes, radar buttons, spinners and so on to make data entry more efficient. It also means using sensible input messages (tooltips which appear when a user clicks in a cell perhaps, or even messages on the sheet itself). All of this should make data entry easier and more efficient for the user.

This is an excellent place to draw a prototype sketch and show it people to get their ideas. You can then record their responses and write these up - as well as your responses to them. There are specific marks for this in Part 6 - Testing

For outputs you need to think about how you can structure a really good looking output in a spreadsheet. Using methods such as concatenation would be a good idea. It's also important to test the general look of your outputs. Would them meet the demands of the organisation? Do they look professional and do all the sorts of things the user would want?

It can be a good idea to look at real examples of inputs and outputs and draw on them as a source of ideas. I'll include some output ideas at the foot of this page.

Again, sketch these out and test them. More marks...

Accessibility and Usability

You have to specifically show that you've considered both accessibility and usability in your designs. This should probably be annotated on to the designs, but you might want to produce a summary as well to help remind the examiner you've done this.

Accessibility is the extent to which an application is usable by a person with a disability. So, for example, text should be large enough to be read clearly and text colours should contrast effectively with background colours. Care should be taken to avoid colours likely to cause issues with colour blind users. It might be possible to make all navigation possible using only a mouse etc... (e.g. tabbing between boxes or using hot keys to fire macros)

Usability is how easy a user interface is to use. This should include factors such as how easy the system is to learn, how safe the system is (the user shouldn't be able to delete dara by accident) and how efficient the system is. Efficiency is a particularly key feature to consider as it crops up in other places in the markgrid.

For example, you might use cell protection to ensure that a user can only click in cells they need to be able to select. This would avoid them being able to delete key data and would also allow effective use of tabbing to move from cell to cell.

Macro Designs

You need to specify your macro designs. These probably won't fit on the sheet designs, so will need their own section. They get marked on their own as well...

Remember, the aim of macros is to make the user interface more efficient by automating routine processes. This could be navigation from sheet to sheet, but you should really aim to go beyond this and automate processes such as:

You don't have to get into writing macro code at this point. They key words from the markgrid are that you have "identified and described a range of automated routines..." (aka macros).

The Markgrid

Markgrid for Unit 2 Part 2

PDF iconSpreadsheet design markgrid - PDF version of markgrid

Note: this markscheme is a copy of one made available freely By WJEC as part of their CPD programme. It is copyright WJEC (probably) and reproduced here simply to make access easier for students. No attempt to claim copyright is being made, although I could have copied the text into my own interpretation...