5 excel functions every teacher needs to know

Whether you’re on the SLT or it’s your first year in the classroom, some of your time will be spent analysing pupil data. Taking a bit of time to learn the following excel functions will work wonders to speed up and enhance that analysis

Conditional formatting

Conditional formatting can give you an instant visual impression from your data. There are some basic built in options straight on your menu bar, but spending a few minutes here learning how to set up your own rules will turn a page of numbers into a useful informative document quickly. Take a 20 minute lesson on conditional formatting here to master the rules!

Examples:

1 - using basic data bars from the built-in conditional formatting toolbar

2 - colour coding pupil performance on individual questions

Pivot tables

How many pupils in that class are below target? How many in year 9 are working at level 6 or above? There’s no need to count or copy and paste columns if you know how to use a pivot table. Learn the basics here. 

Examples:

calculating level percentages

VLOOKUP

This command comes in very helpful for generating automatic comments from a numerical result. It can also save valuable time by looking at the level boundaries for you and selecting the correct level. Basics here. 30 minute lesson here.

Examples:

1 -  generating automatic comments from a numerical result.

2 - level boundaries used automatically

IF

This function is useful for identifying how many pupils are on target by quickly comparing two (or more) cells. Find out what the IF function is here. 20 minute lesson here

Example:

identify pupils who meet their target level

countif

Why count the number of pupils who meet a certain condition when excel can do the counting for you? Check out the COUNTIF function here.

Example:
 

count the number of pupils at different levels

This probably seems like the most complicated of the posts on this website, and that’s probably the thing that puts most people off learning excel functions. But please, trust me, a small investment of time to learn these functions will save hours in the long run. It’s worth it!