Dynamic Markbook - scenario

Create a dynamic markbook

Level of difficulty: Advanced

As an educator, you may have many classes and their continuous assessment marks will be saved on marksheets. A dynamic markbook 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.

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:

	Description	Resources
1	Start Microsoft Excel	How to start Microsoft Excel
2	Create a basic markbook: If you have not created a basic markbook you may like to follow that scenario first.	How to create a simple marksheet
3	Calculate percentage: Refer to a maximum total in one cell to perform your calculation.	How to use absolute referencing to calculate a percentage How to change the number format
4	Save your worksheet: Remember to save you work regularly.	How to save a file
5	Copy the formula: Copy your formula to the other cells using Autofill.	How to use autofill
6	Automatically assign a grade based on a mark: Instead of calculating the grades, the spreadhseet can enter this information automatically if you set it up in that way.	How to use a nested IF function How to autofill
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.	How to select cells How to use conditional formatting
8	Insert a combo box: Create a drop down menu you can use to look up and enter information	How to create a combo box See an example markbook with combo box
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.	How to use VLOOKUP
10	Save your worksheet	How to save a file

Related scenarios Create a class database Create a chart Import charts into a word processor

Home \| Contents