Excel Tool for Producing Sociograms

Author: Dr Rhyd Lewis, School of Mathematics, Cardiff University, Cardiff, Wales. www.rhydlewis.eu

Last Updated: Wednesday, 31 August 2022.


This page gives information on an easy-to-use Excel-based tool for producing sociograms of school children. The tool reproduces the sociogram tool of Robin Banjeree, albeit with a much simpler interface.


Instructions for Use

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 here.


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 matrix summarising the data and a visualisation of the sociogram:




Interpreting the Matrix and 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 italicised text, quoted verbatim from this webpage of Robin Banjeree, 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 group work.

·         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 cut-offs. 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.”


How is the Visualisation Produced?

When drawing the sociogram, this tool uses a variant of the simulated annealing algorithm to decide where to place each node on the plane. This approach is intended to make the network easier to interpret visually. Here, the cost function of this algorithm seeks to ensure a suitable balance between five criteria: (a) the arrows should not be too long; (b) pairs of arrows should not cross; (c) nodes should be spread out evenly; (d) nodes should not be too close to the border; and (e) arrows should not pass too closely to other nodes. The VBA source code of this implementation can be viewed by following the instructions given in the spreadsheet.