Mark book tools in spreadsheets

Why this exists

A mark book is easier to read when status is a choice from a list and marks are shaded by colour. These three tools add drop-down lists, tick boxes and colour scales to any spreadsheet in a few steps. Tutors can see at a glance who is behind and who is doing well.

Three tools turn a spreadsheet into a quick mark book.

Drop-down list

1. Open the Data menu and choose Drop-down list.
2. Type the cells to change, like C2:C30.
3. Type the choices, one a line (for example Not started, In progress, Done), or point at cells that hold them.
4. Choose Add drop-down.

Tick boxes

1. Data > Checkboxes.
2. Type the cells and choose Add checkboxes. A ticked cell reads Yes, an empty one No.

Colour scale

1. Data > Colour scale.
2. Type the cells, such as a column of marks, and choose Add colour scale.

Low marks shade one colour and high marks another, with the middle colour at the middle mark.

To take any of these off, use Remove drop-downs and checkboxes or Remove colour scale in the same box.

Good to know

• All three survive Download as Excel (.xlsx) and come back when you import the file again. Excel has no tick box of its own, so a tick box becomes a two-item list there.
• Other Excel rules, such as data bars and icon sets, are not kept, and the app warns you when it drops one.

Related

• Spreadsheets
• Sharing a document