Using Excel to create a matching pairs activity

I am a big fan of Excel, and have discovered over the years how to use it as a very effective teaching and learning tool. In the coming weeks (and months depending on how busy I get) I plan to create a few tutorials to show how different types of activity can be created.

My first attempt is going to look at creating a simple matching pairs activity, and to do this we will learn about

  • IF statements
  • Data validation
  • Inserting a hyperlink to move from 1 sheet to another
  • Sorting a list
  • Combining contents of cells to create a sentence
  • Unlocking cells
  • Protecting sheets
  • Hiding gridlines, sheet tabs, formula bar and row + column headers

This tutorial will consist of 4 screencasts.

The first shows the end product, and the reasons behind some of the choices


In the second screen cast we will look at how we create the sheet that the student will enter the answers into


The way that this resource works, is the student enters some answers, then clicks on a ‘check answers’ button which shows how many they have right. This looks really clever, and some people think I have used code to create it, but all I have done is create a second sheet in the workbook, that is laid out identically (so looks the same) which checks the answers. When the student clicks on the button to ‘check answers’ or the equivalent one to return to test, all they are doing is flicking between 2 separate sheets.

The third screencast looks at how to duplicate the entry sheet (to create the check sheet) and change it to check how many the student has got correct.


In the 4th tutorial, we will look at the final stages of tidying up the resource and making it work neatly. We will during this process hide the rows of the table containing the answers. The key to a resource like this, is once you have created something like this once, you can then just change the data in this table and you have a new resource.


There are a few more improvements that you could make – for example, on the check sheet, at the top of the page we could add a formula to the title, to copy it through from the ‘test’ sheet – this way if you change the title on the first sheet, it will automatically change the the heading on the second page. Also if the students don’t answer all the questions, when they check their answers they will get a ‘0’ displayed. This could easily be removed by adding another IF statement.

I hope that this has been useful, and look out for future tutorials on this topic.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: