Guide

What is question-level analysis (QLA)?

A practical guide for primary teachers: what QLA is, how a QLA spreadsheet is usually set up, why entering the marks takes so long, and ways to make it faster.

What question-level analysis is

Question-level analysis (usually shortened to QLA) means recording each pupil's mark for every question on a test, not just their total score. Once the marks are in, you can look across a question or a group of questions to see which content most of the class found hard, which pupils share the same gaps, and what to reteach.

A total score tells you how a pupil did. Question-level marks tell you where they gained and lost marks. That's why many schools carry out QLA after practice tests and assessments, particularly in Year 6.

How a QLA spreadsheet is usually set up

Most QLA spreadsheets, whether a school's own or a downloaded template, follow the same pattern:

• One row per pupil and one column per question.
• A row near the top holding each question's maximum mark (often 1, sometimes 2 or 3).
• Formulas that total each pupil's marks and each question's marks, often grouped by topic or reading domain.
• Conditional formatting that colours marks green or red, so gaps stand out.

The analysis is automatic once the marks are there. Getting them there is the slow part.

How many marks are there to enter?

It depends on the papers, and on whether you record every question. As a rough guide, a full set of Key Stage 2 SATs-style papers can look like this:

For a class of 30, that's roughly 4,800–5,000 individual marks, each one found on a paper and typed into the right cell. Question counts vary from year to year, so check your own papers. If you only record some questions, the number will be lower.

Ways to speed up QLA data entry in Excel

These work whether or not you use QLA Voice.

Make Enter move the right way

If you enter one question for the whole class, you want Enter to move down. If you enter one pupil's paper at a time, you want it to move right. In Excel for Windows: File › Options › Advanced › After pressing Enter, move selection. In Excel for Mac: Excel › Settings › Edit › After pressing Return, move selection (called Preferences in older versions).

Match the order you enter to the order of the papers

Going through the pile one paper at a time? Enter across each pupil's row. Marking one question across all papers? Enter down the column. Switching between the two is where places get lost.

Stop impossible marks at the door

Excel's Data › Data Validation can limit a range of cells to whole numbers between 0 and the question's maximum mark. A typo like 11 instead of 1 is then refused straight away instead of quietly skewing the totals.

Read and type in pairs

If you have help, one person reads the marks and the other types. It's quicker because the reader never looks up and the typist never looks down.

Say the marks instead of typing them

This is what QLA Voice does. Keep your eyes on the papers, read each mark aloud, and it goes into the selected cell, which then moves on. It only accepts numbers from zero to ten, checks each one against the question's maximum, and ignores ordinary conversation. See how it works and how it checks each mark.

Checking your data before you rely on it

• Use conditional formatting to highlight blank cells in the marks area, so missed questions stand out.
• Compare a few pupils' totals in the spreadsheet with the totals written on their papers.
• Look out for a row that's shifted by one (every mark one cell along). It usually means one mark was skipped or entered twice.

Whatever method you use, typing or voice, a quick check before you act on the analysis is worth it.