Creating your own School/Class Tracking sheet and using it in Microsoft Live Pivot


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


Microsoft Live Pivot in the browser


Microsoft have not just release Microsoft Live Pivot as a downloadable application but also the ability view the data in your web browser.

Using Microsoft Silverlight, Pivot will open the data stored in an XML format in your browser the same way it would load it in the full version.

Here are a few examples

RoomSeekers takes their current database of hotels, locations, ratings and other information to turn into a Pivot collection.

They actually have 7 collections of their hotels based on different areas of the country.  I’ve selected an area I know well (Midlands) so I can see if I recognised any hotels.


The collection starts off being in Counties and from the bar chart view you can see that West Midlands has the highest number of hotels.


When selecting the West Midlands County and sorting the bar chart by price I can see that 1 hotel is available between £175 and £200.

Well in this example I’m looking to spend £80 to £100 so I have moved the selection bar to between this price and I also want to have Wi-Fi access so select Wi-Fi Available from the Hotel Features

image  image

I’m also going to sort the chart by star rating


I’m now able to select the hotel I want from the list and zoom in to see more information on that hotel



Now I like TweetPivot as I compare the people I follow and the people that follow me based on their information on twitter.

Go to the site and put in your twitter username.  If you don’t have one feel free to user mine, alex_pearce other the Learning Gateway User Group account , learninggateway.  You could also do it from celebrities as well if you want.


Tweetpivot can provide you with two collections, one based on your followers (also known as friends) and also on the people who follow you.  This account only has a few friends I’m going to select followers


Here is my own pivot collection based on my followers


If you want to play with Microsoft Live Pivot more got to the website here

What is Microsoft Live Pivot?


In the next set of blog posts I’m going to demonstrate what Microsoft Live Pivot is, what it can do and how we can create a great looking Excel tracking sheet that we can use to evaluate how students are doing in a course through out the year.

What is Microsoft Live Pivot?

I first saw Microsoft Live Pivot in January 2010 with a great demonstration at the BETT show on the Microsoft stand.  It’s a great visual tools that takes data and converts it into a very powerful, interactive tool.

The tools takes mountains of data and turns it into an easy to read, filtering system so you can drill down into the data to find more relevant information.

In one of the examples that are available with Live Pivot you get Concept cars.  In total there are over 300 concept cars dating from 1899 all the way up to 2010.  Other filters available are sort by Manufacturer and body style, you also filter by Engine Displacement, Engine type and transmission.


Down the left hand side is your filter.  We have a few different types of filters as well

One is your normal check box and you can select which ones you want to filter by


In the example below I have checked E85 and it has left me with the 3 cars available.  I can now click on one of those cars and it will give me more information such as length and width that aren’t a filter type.

As you check the different boxes the body of the video starts to filter out the cars that are no longer relevant.


The other filter type is by selecting from a range of numbers.  In this example we have the production year of the concept car.

image image

As you move the green bars the main body changes to only show the cars from 1921 – 1957.

You can have a play with this yourself by going to

Microsoft have a video that shows what I’m explaining so much better.