Excel Tool for Producing Sociograms
This page gives information on an easy-to-use Excel-based tool for producing sociograms of school children. The tool reproduces the , albeit with a much simpler interface.
The Excel program can be downloaded here. Once downloaded, all relevant information should be input into the Data worksheet as illustrated in the screenshot below. To collect this data, a survey should be conducted within the classroom. In this survey each child should first be asked to nominate up to three students that they most like (ML) and up to three students that they least like (LL). Further information on how to do this in a safe and valid way can be found .
The collected information should then be typed into the spreadsheet. Specifically, each child should be allocated an ID number, and the ID numbers for their ML and LL nominations should be listed alongside, as the following screenshot demonstrates.
Once the data has been typed in, the sociogram program is executed by clicking on the button at the top-right of the spreadsheet. This will produce (a) a matrix summarising the data, and (b) a visualisation of the sociogram:
The matrix reported by this program gives a summary of the data. A table is included at the bottom of the sheet that lists, for each student, how often they have been named as “Most Liked” (ML) and “Least Liked” (LL) by the other students.
The Sociogram produced by the Excel program gives a visual representation of the data. The following text, quoted verbatim from , describes how to interpret this network.
Each node represents a pupil, and the arrows indicate ML nominations. Single-headed black arrows indicate unreciprocated ML nominations and double-headed blue arrows indicate reciprocated ML nominations. If you included ML and LL nominations, then some pupils may be allocated to one of the following colour-coded peer status categories on the basis of the numbers of ML and LL nominations received:
· Popular (coloured green): received high numbers of ML nominations and low numbers of LL nominations. The majority of pupils with this classification tend to be prosocial, showing socially competent behaviour.
· Rejected (coloured grey): received low numbers of ML nominations and high numbers of LL nominations. This is considered to be the most 'at-risk' peer status classification, as it is often associated with externalising and/or internalising problems. This also tends to be the most stable peer status classification, with some pupils experiencing multiple years of peer rejection. Further assessment of these pupils, measuring their feelings and their patterns of thinking, will be important for supporting these pupils' development in targeted work. In addition, the links in the sociogram will provide valuable information for supporting and managing these pupils in groupwork.
· Controversial (coloured orange): received high numbers of ML nominations and high numbers of LL nominations. This classification tends to be less stable, but pupils falling into this category can be highly influential. They are often dominant characters who are perceived to be highly popular, yet they may be aggressive, disruptive, and even intimidating. Because of their influence, work with these pupils can have a substantial impact on the classroom ethos.
· Neglected (coloured pink): received low numbers of ML nominations and low numbers of LL nominations. This classification is generally seen as less problematic than the Rejected category. Research often finds that these pupils -- despite having low social impact -- are well-adjusted, although they may have a reputation for shyness.
Bear in mind that the peer status allocations are based on statistical cutoffs. There may be other pupils in the class/group who fall just short of the threshold for one of these peer status categories. Please examine your Excel spreadsheet to see the exact numbers of ML and LL nominations received by each pupil.
Please direct any questions on this Excel program to Rhyd Lewis: , .
Last Updated Friday, 24 August 2018