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.
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:
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.”
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.