golden rules

							PUPIL TRACKER – USERS GUIDE

SECONDARY SCHOOLS

The purpose of the Pupil Tracker is to

        Provide Key management information for SEF and Action Planning

        Enable staff to reflect on progress of pupils they are working with



The Pupil Tracker will provide the best source of evidence for good Self-evaluation.
It will provide information on the average levels that pupils are working at and the
average progress they are making towards targets. It is based on “live” data
rather than a reflection back on historic results.

This current levels (attainment) and progress being made can by shown by:

        Subject Area
        Pupil Group
        Intervention

Staff will also be able to select the group they are teaching or working with and
reflect on levels attained and progress being made.

The Pupil Tracker requires basic skills in Microsoft Excel which are explained
below. It is an interactive tool that enables you to investigate attainment and
progress for any group or subject you want.

You should first take a copy of the Pupil Tracker and save it in your own documents
so you can interrogate whatever you like. Within your subject area you might
choose to save a copy of the Pupil Tracker just for your subject. You can just save
the document and call it e.g. History Pupil Tracker and hide all the columns relating
to other subjects you don’t wish to see. (See instructions below).
KEY TIPS FOR INTERROGATING THE PUPIL TRACKER

To sort the data in order

Staff will want to sort pupils by those working at the highest to lowest levels or by
those making most or least progress.

In excel (2003 onwards) you can just click on the little arrow on the headings row
(filter) on any column and the first options it shows are to sort by Ascending or
Descending order. If you click on sort in descending order you will see that the
data is then sorted in that way. Do not worry about the rest of the data in other
columns as they will sort accordingly (as long as there are no blank columns present
in the worksheet).

If you sort by Descending Order in one of the columns showing the number of
points the pupil is working at (the columns with purple headings) then the pupils will
be sorted by highest level to lowest level. They will be sorted by points scored but
if you scroll back to the columns showing the actual levels (KS3) or Grades (KS4)
you will see they are now sorted in order. If you were to sort in the columns
showing the actual levels or Grades the sort may not work as excel does not
recognise the difference between e.g. 5C and 5A which is why you need to sort on
the number of points instead.

If you sort by Descending Order in one of the columns showing progress (the
columns with the pink headings) then the pupils will be sorted by those making most
progress towards targets to those making least progress. These will be colour
coded (Traffic lights) to show those making good progress (Green), those roughly
on track (Amber) and those making insufficient progress (Red).



To look at a group of pupils:

Staff may want to select just the pupils they are teaching – or say just pupils with
SEN or involved in a particular intervention.

The small arrows (filter) next to each heading allow you to select particular groups.
If you click on the arrow you will see a list of all entries that have been made in
that column e.g. if you click on gender you will have the option of selecting either M
for Males or F for Females. If you select on SEN you will see A for School Action,
P for School Action Plus or S for Statement. You can make a selection by just
clicking on the group you want – e.g. those on FSM or from a particular ethnic
group.

If you want to select pupils from different groups – you can use the option of
Blanks or Non Blanks. So if you want to see all SEN pupils you can choose Non
Blanks – and then you will see all the A, P & S pupils together. Conversely you can
select the Blanks and see those pupils not on the SEN register.

You may also want to use the option to Custom your selection. For example, if you
want to look at pupils within one sub-level of their targets in KS3 – you can use the
Custom option to then select pupils with progress scores greater than or equal to -
2. If you were looking at data on attendance you might want to use the Custom
option to select those with Attendance less than or equal to 90%.

Once you have made a selection the filter arrow will be coloured blue (in Excel
2003) - this means it is active. If you have selected by Ethnicity – say Black
African and then go to the Free School Meals (FSM) column and select Y - you will
have Black African pupils on FSM. If you want all FSM pupils you can click again on
Ethnicity and select “All” - which is the first option. Once you have selected “All”
the arrow will no longer be blue as it is no longer active. In this way you can select
any groups using as many different filters as you wish. So you could select White
UK Boys on SEN and FSM who are having support from the Learning Mentor. You
can drill down to look at any number of factors you wish – although, of course, the
numbers in the group will become smaller and the results may be less meaningful.

You can select groups of pupils or select on the intervention strategies that have
been completed or pupils at different levels e.g. all pupils who are working at above
Grade D in Art in Year 10 or those on the G&T register.

You might want to look at pupil progress towards targets by using the filter option.
You could sort your pupils by progress towards targets (in the pink headed
columns). Then you can select in the column showing the Targets – e.g. those with a
Target of Grade B at GCSE. Then you can look at the current Grades and see
those working above a B, on a B, working at a C or D etc.

It is important to remember that once you have made a selection you will need to
remember to go back to “ALL” in order to have the full list of pupils available again.
You can tell that you have all pupils showing if the row numbers in the far left hand
of the screen are in black & white and consecutive order. If they are blue and not
in order then you still have a filter selected.
Calculating averages for NC points working at or progress made:

The Pupil Tracker will automatically calculate the averages at the bottom of each
worksheet for you. When all pupils are showing – you will see the Average number
of Points the pupils are working at (underneath the columns with purple headings)
and the average progress being made (underneath the columns with pink headings).
This is key evidence for SEF.

When you filter e.g. just select a particular group - the averages will now re-fresh
at the bottom and show the averages just for that selected group. So you can look
at factors such as Ethnicity, EAL, SEN or particular intervention groups – and you
will see the averages just for that group.

If the values do not appear at the bottom and it is showing #N/A this will be
because the Tracker has used an assessment level that has not been recognised and
it has, therefore, not been converted into points. The tracker will only recognise
the levels as laid out in the worksheet called Explanation of points. E.g. it will not
recognise 5C/5B or 4A? or Absent. You will need to use a valid level or grade that
is accepted.

You can compare average progress made by subject area - or for particular groups
of pupils e.g. boys in English or the pupils you are teaching by using the filter to
select a group and then you will see the average progress they have made.

You could select any number of different groups and see the averages for them. In
order that you only need to do this once you have been provided with a Working Out
area to save the analysis you are doing. The boxes (Working out Grid) below are
for saving your workings.

Once you have filtered on a particular group the averages will appear as "live" in the
first boxes below the spreadsheet. You can then "copy" and then "paste special"
the "values" into the working out grid below. This will save a permanent record of
your analysis and can then be used for drawing charts. You can make a note to the
left or right of the Working out Grid of the group you are recording the results for
– e.g. SEN, EAL, FSM. You may find it easier to "UNHIDE" any hidden columns
when you work on your analysis so the results can be pasted for all subjects
together.
Hiding and Unhiding - for looking at your subject area only or for printing
sections

To hide columns - e.g. the columns with other subject results - or columns with
historic data that you don’t want to look at - just highlight the column(s) you want
to hide by clicking on the column Letter in the very top row and then click on
"FORMAT" - then "COLUMN" then "HIDE". To unhide - just click on "FORMAT" -
then "COLUMN" - then "UNHIDE". You can do the same for Rows.

You can also do this by using the Right Click. So if a Column or series of Columns
have been selected (by clicking on the letter in the very top row) then you can go
Right Click and Hide. Again you can go Right Click and Unhide to get the columns
back.

If you have forgotten which columns have been hidden – you can highlight the whole
worksheet by clicking in the very top left hand corner of the screen – above Row 1
and to the left of Column A – and then going FORMAT – COLUMN – UNHIDE.

If you want to print the results – say just for your subject – you might want to just
show the columns with the current grades and then with the progress. In this case
you can hide every other column and then highlight what is left – and print that
selection.



Inserting new Pupils (should be done by administrator)

If you need to insert new rows - you can highlight a whole blank row by clicking on
the row number on the far left hand side. Then go "COPY" and then click on a row
either side and go "INSERT COPIED CELLS". This will give you another empty row
to enter data into. You can copy as many blank rows as you want and it is best to
keep spare rows for any new arrivals. If you run out of blank rows you will need to
copy one with data and then delete the data (but not the purple or pink headed
columns with formula). If you want to insert new pupils you can just type in the
details in one of the spare rows. Don't worry about inserting them in alphabetical
order as you can enter them at the bottom and then just sort by order.

Deleting Pupils   (should be done by administrator)

When a pupil leaves you might want to delete them. You can do this by highlighting
the whole row in which that pupil appears and then going Right Click – Delete or Edit
and then Delete. However, you might want to retain pupils who have recently left
as they will provide a record of your pupil mobility which might be useful evidence
when assessing achievement. If you leave these pupils in the Pupil Tracker they
will not influence the overall averages as they use only the latest valid data.



Drawing Charts

To draw a chart you need to create a table of data. To create a chart Click on
Insert and then Click on Chart – or use the Chart Icon at the top of the screen.

The 1st option will be a column chart - which will usually be the option you want so
just select and Click Next. You will be asked to pick the data range (the
information you want to use to make the chart). You need to highlight the table
with the information. You might have a table with Rows headed SEN, Non SEN,
EAL, Non EAL, FSM, Non FSM and columns headed Average Points and Average
Progress. If you click on the top left hand cell and highlight everything including
the headings and the data. Then Click on Next. You will now be given options to
work through as to how you want the chart to look.

You might want to Click on Title and type in a title - then click on Legend and say
where you want it to be (or not be shown) and Data Labels will allow you to Click on
Values and it will show the values in the table. Once you have worked through the
options you want - just Click on Finish and the chart will be drawn for you. You can
drag the sides of the chart to move the position and change the size etc.

You can now colour the chart to make it look more professional - for example, if you
double click on the grey background you will get some options - click on Fill Effects
and then click on 2 colours - and then select each of the 2 colours then Click on OK
and then OK again to finish - and the chart will be coloured. You might find that
Colour 1 as White and then Colour 2 as one of the light pastel shades will work best.
You can change the colour of each bar by double clicking on the bar in the same way.



Your administrator should be able to offer advice on using the Pupil Tracker.




John Sinnott
EduData UK
John@edudatauk.co.uk
www.edudatauk.co.uk