This page contains some simple instructions to make a basic PivotTable. Every school will have different requirements and it is definitely worth having a good play with the data to see what sort of format you might want to use.

Some top tips on creating PivotTables:

• Know what information you want to see before you start! It's a lot easier to manipulate data when you know what you're trying to achieve.
• Start small! Try your tables with a smaller subset of data. When you know how you want to set up your table you can add in more data/subjects/perods etc.
• Click things! Don't be afraid to try different buttons or combinations to see what happens. You can always pull out new data if something goes wrong.

1

Run the Learning Objective Export Report from your FrogProgress platform making sure to only export latest judgements).

Open the downloaded report in Excel.

2

This step is optional but it will really help you get to grips with the labels in the pivot table. 

Create a new column in your spreadsheet (this will be Row R) called Name.

Use the following formula to create a field that shows a child's forename and surname together.

=CONCATENATE(A2,B2)

It is also possible to use the formula below if you would like a space between the forename and surname:

=A2&" "&B2

Note: Remember that you will need to add this formula into cell R2 and use the drag option to fill the rest of column R with the formula which will allow Excel to automatically add in the correct cell number for that row (e.g. for row 3, the formula will be =CONCATENATE(A3,B3).

3

Now we're going to create the PivotTable itself. The first step is to create a new sheet within your Excel workbook. 

When you have created your new sheet, select the columns from your export. The easiest way to do this is to click and hold on the "A" at the top of the first column and drag your mouse to the "R" column:

Select Insert from the top menu and choose PivotTable which should be the first option on the left.

The Create PivotTable dialogue will appear and you should be able to see that "Select a table or range" is already filed in. If not, then use the to select your data.

For the Location field, choose Existing Worksheet and use the to choose the new sheet that you created at the start of this step. Select a cell on the new worksheet that you want the table to appear in. Click the  again to return to the Create PivotTable window. 

You should see that this field will become populated with the name of your new sheet in inverted commas, followed by a ! and the cell name you have chosen.

Click OK to create your table!

4

You should now see a screen that looks similar to this:

We now need to tell the table what data we want to display, and we do this using the PivotTable Fields options.

For this tutorial we will make a simple table that resembles the Student Tracker in FrogProgress. To do this we are going to click and drag the following fields around:

Columns: Name

Rows: LO/KPI Name

Values: Count of Detail

Filters: Event Type

As you start to drag fields around, you will notice that the table will start to build itself. This is really handy because you can see how the different options affect your table as you go. 

You should end up with something that looks like this:

You now have a table which will give you a sum of any judgement made against a student and will show you any gaps. This table is currently very plain so we will now talk about some rules that you could choose to make gaps visible at a glance.

5

Colouring within a PivotTable is controlled by conditional formatting within the spreadsheet. To set conditional formatting, click into the sheet with your PivotTable and choose Conditional Formatting from the top menu. 

Select Manage Rules. 

To set up a page that looks like Student Tracker which shows Where You Should Be, Not Where You Should Be and Not Taught, you will need three rules along the lines of these rules below: 

A rule set up like this will give you the following table:

It is possible to add many different types of rules to do different things and we definitely recommend having a play around to get the kind of display you would like! If you are struggling with the formatting or the rules, please see our example spreadsheet on the Using the Example page.