Skip to main content

Excel tricks that every maths teacher should know

This maths teacher shares the Excel spreadsheet formulae that have transformed his lesson planning and saved him time when creating resources

News article image

This maths teacher shares the Excel spreadsheet formulae that have transformed his lesson planning and saved him time when creating resources

I love creating resources. There’s very little I enjoy more in teaching than using something I have created specifically with a class or pupil in mind and seeing that resource hit the mark. I like animating PowerPoint presentations, finding interesting pictures and videos for hooks, or thinking of fun ways to twist an activity.

However, the one part of making resources that’s never fun is creating the question bank. This part is plain old hard work. There are two sorts of questions: unique, challenging, higher-order thinking skills questions and basic practice questions. The practice questions are time-consuming and dull to write – and working out the answers can be even worse.

Over time, I found many shortcuts, but recently I’ve found the holy grail: Microsoft Excel. Excel is a game changer. It allows you to create sets of questions at a speed that would put Barry (The Flash) Allen to shame.

Using Excel to write questions means you only need to write one or two questions, then through the magic of random number generators you can have thousands in a second. Here’s a quick example:

  • Type in the formula:  =randbetween(2,500) in cell A1
  • Type in the formula: = “Find the area of a circle with radius” & A1& “cm” in B1

Note: The quotation marks are for text, while the ‘&’ links text and the random numbers that appear.

  • In cell C1 type a formula to calculate the answer: =pi()*A1*A1  or: =round(pi()*A1*A1,2) would round the answer to two decimal places
  • Now you can select the three cells and drag them down to give as many questions as you need, with answers calculated instantly.
  • Copy these the questions and paste them into a document or presentation, selecting the “copy as text” option.

Differentiated questions 

Using the example above, if I copied one of these questions and pasted it into a column I could quickly create a set of differentiated questions. Change “area” to “circumference” or “circle” to “semi-circle”. You can make three columns, each with a different level of difficulty.

Now, before you get the impression that every lesson I teach is based around hundreds of very similar questions, I’d like to point out Excel isn’t only useful when planning lessons where pupils need lots of practice. I’ve created board games, Top Trumps-style tasks and number search quizzes, all based on a simple template for generating lists of random questions.

Here are two more top Excel tips:

  • Use the “if” function to give a choice between questions: if(randbetween(1,2)=1, QUESTION 1, QUESTION 2). This can offer some variety for pupils.
  • If you are projecting straight from the board, hide the answers by making the text in the answer column the same colour as the background. Then, change the text colour to reveal the answers when it’s time for pupils to mark their work.

Undoubtedly, some practice is needed to master the formulae, but this is time well spent. Next time the printer packs up and someone’s borrowed my textbooks without telling me, I’ll have a hundred questions up on the board before the pupils have even unpacked their bags.

Ben Cooper is assistant SENDCO and teacher of mathematics at the Burton Borough School in Newport. @bcoops_online​

Log in or register for FREE to continue reading.

It only takes a moment and you'll get access to more news, plus courses, jobs and teaching resources tailored to you