It’s the start of a new academic year and we are still looking at Microsoft Live Pivot but we are now going to look how we can create our own using Microsoft Excel and creating a tracking sheet that could be used in a class, subject or even the whole school.
There are many advantages as to why you could use Live Pivot to assess students. As well as being easy to use you can find all the students who have over 85% of marks across your year who many are gifted and talented in your subject. You can also see the students who are on the level of either just passing/failing and need that extra support to ensure they get a pass mark (example at the end).
Installing Excel Pivot Add-On
The Microsoft Live Pivot team have provided an Add-On for Microsoft Excel 2010 that allows you to create a new spreadsheet that you can then build your Pivot – download here. Unzip and install the setup.exe file.
Creating a new Pivot Collection
When you next open Excel you will find that there is now a new tab in the Ribbon called Pivot Collection.
Now select New Collection which will give you the default template for a Pivot Collection.
As stated of a new collection you need to set some of the Pivot properties. Fill this is based on your class or subject.
For this example I’m going to build a tracking sheet for the whole of Key Stage 3 in ICT. My demo school is called New Wave Academy so I’ve put that in there as well.
Switching back to the Collection Items sheet I’m first going to import all the images I want to use. Each file name is the name of the pupil to help me reference the learner.
In the Ribbon, select the Pivot Collection tab and select Import Images
Navigate to the folder you have all your images and click Open. This will import all images into the spreadsheet.
Note: If you want other users/staff to be able to view images in this spreadsheet you will need to import the images from a file share they can view.
Name is the only other field required that you need to enter. If you have named the images the same as the student names it will help otherwise enter their name here.
If you want to add a web link to the student you can do, this might point to the users MIS profile site or their e-portfolio on the Learning Platform. Also add a Description if you want.
Adding new Columns to Filter in Pivot
We can now start adding new columns where we will store information on that student. To add a new column select the next column after Description (cell reference 1I) and add a new Column Title. I’ve added Year Group. I’m also going to add Teacher and IT Group.
Throughout the year I may want to add more column based on the different assessments I have done.
I’ve added a few as an example and I’ve added the percentage marks from the first assessment we did at the start of term, a homework assessment we did on 7th September and also the fine assessment mark at the end of term. You can add as many as you want.
Conditional Formatting and Formulas
Normal tracking sheets in schools have a lot of conditional formatting and we can still do this within our Pivot spreadsheet. Select the column you want to format and select the format type you want from the Conditional Formatting icon in the ribbon.
We are also going to set a formula to a new column at the end based on the end of term assessment. Lets say 55% is a Pass, 65% is a Merit and 75% is a Distinction.
I’ve now added this formula to my cell and replicated it to all the cells in this column
You should now see that the new column has one of these scores in the cell.
When we create our Pivot you will find that it doesn’t look at the formula but at the results in the cell instead.
Publishing our Pivot
We now have our Pivot collection read to publish. This could be done any time throughout the year as you are filling in new columns.
We have 2 options
Quick Preview will collect all the data you have and publish this into a new collection but with out any images.
Publish Collection allows you to create your brand new collection to view with images. Select Publish Collection.
You will then get a status bar which is creating your collection.
When this is finished your pivot collection is completed and ready for you to view. At the bottom of this post you can view some screen shots.
You can download this collection, the spreadsheet and images from the links below
ICT Tracking Pivot Collection – Download
ICT Tracking Spreadsheet – Download
Images Used – Download
The Publishing Collection
Individual Student Data
Selecting the percentage span to assess students
All students currently around the pass/fail mark