ICT Skills for Teachers (Office 2016 / Office 365)
Create a interactive mark book (Advanced level)
As an educator, you may have many classes and their continuous assessment marks will be saved on marksheets. An interactive mark book will assist you in identifying cases for special attention, flagging underachievers and star achievers, amongst other things. In this case the spreadsheet takes you one step further than simply calculating your totals and averages. Microsoft Excel will help you to achieve this.
By the end of this task you could achieve the following outcomes:
- Develop an advanced mark book
- Use absolute referencing to make formulas always refer to a specific cell
- Use nested IF statements to allow you to work with more than one condition
- Use conditional formatting to change the appearance of cells depending on what they contain
- Create combo boxes which can be used to look up information
- Use the VLOOKUP function retrieve specific information
Create your own dynamic markbook - one which you will find useful. Alternatively, follow this example. To do so you would typically work through the following steps:
| 1. | Start Microsoft Excel. See how » |
| 2. | Create a basic markbook: If you have not created a basic markbook you may like to follow that scenario first. Create a simple markbook » |
| 3. | Calculate percentage: Refer to a maximum total in one cell to perform your calculation. Using absolute referencing » Changing the number format » |
| 4. | Save your worksheet: Remember to save you work regularly. See how » |
| 5. | Copy the formula: Copy your formula to the other cells using Autofill. See how » |
| 6. | Automatically assign a grade based on a mark: Instead of calculating the grades, the spreadsheet can enter this information automatically if you set it up in that way. See how » |
| 7. | Conditional formatting: The use of colour in mark books is a good visual aid to see the performance of the whole class and give a snapshot impression of progress made. You can use conditional formatting to make cells stand out if they meet certain criteria. For example, you may want to colour all distinction grades as green and all fail grades as red. See how » |
| 8. | Insert a combo box: Create a drop down menu you can use to look up and enter information. See how » |
| 9. | Use the VLOOKUP function: In "looking up" information the combo box retrieves its drop down values from a range of cells. You can also use this combo box to "fetch" values from a different worksheet and display them. See how » |
| 10. | Save your worksheet. See how » |
Related scenarios
» Create a chart» Import charts into a word processor
» Draw up a budget
