Creating a data sheet of assessment data

Creating a data sheet of assessment data.

The tutorial covers setting up a data sheet using student names and ID information downloaded from an SMS or an assessment tool, and covers conversion from csv. to Excel, formatting, adding data, and adding data validation drop down lists.

Once you have downloaded and saved the csv. file from your SMS or assessment tool, open it in Excel.

Begin by converting it to an Excel file using the Save As option in the file menu.

Saving it in Excel will allow you to format the sheet and report on the data in the way of graphs and other types of presentation.

Delete any columns that are not relevant, but remember, for the accumulation of data over time, it is essential to have enough information by which you can identify tests and students. Suggestions are, date of the assessment, type of the assessment, student name, student number, year level, gender, ethnicity, assessment result.

In this example we will delete the date of birth column as this is not relevant to assessment results.

You now have the basis of your assessment data sheet to which you can add further columns of assessment data if you wish. For example, you may wish to add OTJ data next to the assessment result.

To do so, add the heading in the first blank column, for example, OTJ, and enter the data for each student.

To format the data sheet, use the standard Microsoft formatting options such as bold, font size, background colour, and so on. For example, you may wish to make the headings stand out by making them bold and giving them a background colour.

Alternatively, you may wish to use the predefined styles to apply to the whole table.

To do this, highlight all the data including the headings and select the Format As Table option to try a different style.

Where you have a column with repetitive data and where drop down lists of options would be useful, you can use the data validation feature to create a drop down list.

To do this, first, type the list of values somewhere in the spreadsheet away from the main table.

To create the drop down list for the column in which you want to enter data, select the column header and select Data Validation from the data menu.

Then select List from the Allow Drop Down list and click on the Source icon.

You can now select your list values by highlighting the list you created with your cursor and clicking on the Source icon again. This will select the cells which contain your list values.

Click on OK to complete the data validation set up.

You will now see that your column has a drop down list for each cell.

You are now ready to start adding more assessment data as you collect it.

Compiling data in this way allows you to have a centralised repository that you can add to as the data becomes available.

Remember to save frequently and to backup the spreadsheet on a regular basis.