Lesson 1 What is a spreadsheet?

Curriculum > KS2 > Unit > Lesson

Learners will collect and organise data in a format of their choice. They will then explore how data can be structured in a table. Finally they will input data into a spreadsheet.

Learning objectives

To create a data set in a spreadsheet

Package contents

Not registered yet?

Create an account and get access to over 500 hours of free teaching resources.

Help us make these resources better

Or email us at [email protected]

Key Stage 2 , Computing , Spreadsheets

What is a spreadsheet, modifying spreadsheets, what's the formula, calculate and duplicate, event planning, presenting data.

Spreadsheets Make Sense

This resource from SMILE contains 21 activities for using spreadsheets to solve mathematical problems. Teachers' notes give advice on appropriate ways of using the activity and identifying particular support students may need when using a spreadsheet.

The activities are:

Favourite colours  - carry out a survey and use ICT to display the data

Multiplication spreadsheet  - create a multiplication table

Pocket money  - perform repeated calculations quickly

Adding one  - investigate the effect of adding or subtracting 1 from the numerator or denominator of a fraction

Dividing investigation  - dividing integers by different numbers to categorise as terminating or recurring decimals

Planning a party  - create a simple model using a variety of operations

142857 multiplication table  - identify recurring decimals

Again and again  - generate a sequence using an iterative process leading to limits

Spreadsheet grids  - investigate the effect of the size of three or more numbers on their sum and product

Square roots investigation  - identify a limit using patterns and algebra

Squidge  - use brackets to create an algebraic expression to investigate sequences

A rich aunt  - appreciate the power of a spreadsheet to solve real-life problem

Averaging out  - use the mean to generate a sequence of numbers leading to a limit

Cuboids  - create an algebraic expression for the formulas for surface area, total edge length and volume

Differences  - between successive terms of sequences generated from linear, quadratic and cubic mappings

A problem of power  - introduce students to modulo function

Marbles - use algebra to model a real life situation

Strings  - numbers which are formed from the integer parts of terms of sequences

Converging sequences - the limit of a sequence formed from the ratios of corresponding terms

Geometry sequences  - investigate infinite sequences with finite sums

Optimising  - use algebraic and graphical methods to model and solve real life problems by manipulate mathematical formulas

Show health and safety information

Please be aware that resources have been published on the website in the form that they were originally supplied. This means that procedures reflect general practice and standards applicable at the time resources were produced and cannot be assumed to be acceptable today. Website users are fully responsible for ensuring that any activity, including practical work, which they carry out is in accordance with current regulations related to health and safety and that an appropriate risk assessment has been carried out.

Show downloads

Share this resource, did you like this resource, lists that tag this content, other sequences , posted by national stem centre, mathematics in the context of creative and digital technology , posted by.

Trending stories

Actor playing Lady Macbeth

Top results

spreadsheet lessons ks2

Spreadsheet Sweet Shop – Forget Data Input, Excel Is An Easy Way To Introduce Kids To Programming Basics

spreadsheet lessons ks2

"Looking at modelling and variables will provide a strong foundation for learning to code"

Terry Freedman

Spreadsheets are dead, right? Wrong! Not only are they a great tool for teaching programming concepts, they can easily be addressed under the ‘new’ computing programme of study.

If you’re not entirely convinced, the key thing to remember is that a spreadsheet is a modelling tool. In other words, it’s designed to answer the question, ‘What if?’. For example, what if the price of bread goes up by 50p a loaf? How will that affect a family’s budget?

The first thing to do is ensure pupils understand how a spreadsheet is set out, and what the correct terms are. It’s made up of columns going across from left to right (A, B etc) and rows going from top to bottom (1, 2 etc). The rectangles formed in this grid are called ‘cells’.

Pupils need to understand cells and cell references because this is where the idea of variables comes in. Unlike a calculator, where you would enter, say, 112 x 731 , in a spreadsheet you would enter a formula that multiplies the cells that contain those numbers. In this case, the formula might be =A2*B2. The cells A2 and B2 are called ‘variables’ because the numbers in them can be changed, ie they can vary.

Behind the scenes

This may sound a bit complicated, but you can introduce the idea in a very simple way, by constructing a sweets spreadsheet.

Enter the formula (=A2/A3) yourself, so that all the pupils have to do is enter the number of people there are and how many sweets have to be shared between them. Ask the pupils what they think is going on, and that will lead on to looking at the formula behind the scenes.

' width=

In fact, that ‘behind the scenes’ phrase is very appropriate: unlike in a programming environment like Scratch, a spreadsheet shows you the results but hides the code. With Scratch and other coding programs, you can see the code (or a representation of it), but not the results.

How can you help pupils to understand the concept of cell references? I’ve devised a very simple (and silly!) spreadsheet to help them do so. I enter a ‘secret’ message in a few cells, and format them to be the same colour as the background, ie white. The instruction to pupils is to select those cells in order to reveal the message. Obviously they have to understand what the cell references mean in order to be able to do so. Then they have to make their own secret message.

What if…

A good way of helping pupils understand the idea of variables and using a spreadsheet to answer ‘what if’ questions is to create a party planning spreadsheet. You may baulk at the idea of creating the spreadsheet yourself, but the point is that getting the kids to enter all the data themselves is a waste of time: the national curriculum doesn’t call for them to be skilled in data entry.

' width=

You might, of course, ask them to experiment with putting the formula in themselves once they have become used to experimenting with the spreadsheet.

As well as variables, spreadsheets can be used to get across the idea of conditional statements, ie IF-THEN-ELSE. There are two ways you can do this.

One way is to set an age checker spreadsheet. You can get the pupils to play with it for a bit, and then try to work out what is actually going on. With older children you could even get them to write down the steps in ordinary language (pseudocode) or as a flowchart.

' width=

The formula looks complicated, but all it’s saying is if the number entered in cell A13 is less than 10, then show the text ‘Sorry, you are too young’, otherwise show the text ‘Sorry, you are too old’.

It’s based on the general principle, IF (x,y,z), which means: check if the condition X is met. If it is, do Y. If it isn’t, do Z. In everyday life an example would be: IF it is raining, take an umbrella, otherwise do not take an umbrella.

Another option is to use conditional formatting. On the party planner spreadsheet, for example, you can ‘tell’ Excel to shade a cell red if the total amount of spending on any one item comes to more than £10. To do that, select the totals column, then select ‘Conditional formatting’ on the Home tab, then ‘Highlight cell rules’, then ‘Greater than’, and then change the number to 10.

Real-life scenarios

Digital skills

Although the computing programme of study is primarily concerned with, well, computing, it recognises the need for children to develop other digital skills.

For example, one of the statements for KS1 is, ‘Use technology purposefully to create, organise, store, manipulate and retrieve digital content.’ Getting children to create a spreadsheet and manipulate data through it is one way to address that requirement.

Another statement is, ‘Recognise common uses of information technology beyond school.’ Once the children have been shown what a spreadsheet can be used for, it’s not difficult to get them to imagine other uses – in the school itself, and the world beyond.

At KS2, pupils are required to work with variables. That’s precisely what spreadsheets were originally intended for, so again, spreadsheets are an excellent fit.

A KS2 statement reads, ‘Design and create a range of programs, systems and content that accomplish given goals, including collecting, analysing, evaluating and presenting data and information.’ Each of the skills suggested in the second half of this statement can be addressed through spreadsheets. In other words, as well as being good to teach as an end in themselves, spreadsheets represent what we might call ‘low-hanging fruit’: pupils can discover how to manipulate data before attempting to write any code with which to do so. Terry Freedman is an independent educational ICT and computing consultant. Find him at ictineducation.org and follow him on Twitter at @terryfreedman .

Sign up to our newsletter

You'll also receive regular updates from Teachwire with free lesson plans, great new teaching ideas, offers and more. (You can unsubscribe at any time.)

Which sectors are you interested in?

Early Years

Thank you for signing up to our emails!

You might also be interested in...

Teaching computing graphic

Why join Teachwire?

Get what you need to become a better teacher with unlimited access to exclusive free classroom resources and expert CPD downloads.

Exclusive classroom resource downloads

Free worksheets and lesson plans

CPD downloads, written by experts

Resource packs to supercharge your planning

Special web-only magazine editions

Educational podcasts & resources

Access to free literacy webinars

Newsletters and offers

Create free account

I would like to receive regular updates from Teachwire with free lesson plans, great new teaching ideas, offers and more. (You can unsubscribe at any time.)

By signing up you agree to our terms and conditions and privacy policy .

Already have an account? Log in here

Thanks, you're almost there

To help us show you teaching resources, downloads and more you’ll love, complete your profile below.

Welcome to Teachwire!

Set up your account.

Lorem ipsum dolor sit amet consectetur adipisicing elit. Commodi nulla quos inventore beatae tenetur.

Log in to Teachwire

Not registered with Teachwire? Sign up for free

Reset Password

Remembered your password? Login here


Simon Haughton's website

15 november 2013, year 6 spreadsheet lessons 2013.

Over the past five years, my theme park spreadsheet lessons for Year 6 have been one of my most downloaded resources and very popular in lots of schools across the UK based on the many emails that I've received from schools that have used them.

This year however, with the need to include more programming lessons into the curriculum (e.g. using my Python  guide ) and my personal desire for a bit of a change, I decided to condense my Year 6 spreadsheet work right down into just two lessons! This was a big risk but it really, really did work  very well - all the children were engaged, everyone improved their skills and many even described the activities as being "fun" which was great to hear.

As the two lessons seemed to work so successfully, I've decided to share them with you for you to adapt and use with your children - and as usual I would really appreciate any feedback on them.


Download 1. McDonald's spreadsheet (insructions)

Download 1. McDonald's spreadsheet (template)

Download McDonald's Prices

Download 2. Manchester attractions spreadsheet (instructions)

Download 2. Manchester attractions spreadsheet (template)

Download Manchester Attractions Prices

Examples of work produced:  Lesson 1   Lesson 2

15 October 2013

Introducing spreadsheets lessons 2013.

My tried and tested collection of resources for introducing spreadsheets to KS2 children:

Download LI for Introduction to Spreadsheets by Simon Haughton

Download 1. Wizard's Challenge

Download 2. The Gold Mine

Download 3. Times Tables

Download 4. Sweet Shop Problem

Download 5. Race Points

Download 6. Shopping Bills - Alternative

Download 6. Shopping Bills

Download 7. Game Points


Download 8. Conditional Formatting Quiz

11 December 2011

Introducing spreadsheets - planning, 16 november 2010, spreadsheet quiz.

Here's a nice activity idea I've just come up with to help consolidate children's understanding of basic formulae whilst at the same time teaching them how to use conditional formatting on cells .

spreadsheet lessons ks2

27 May 2010

Introducing spreadsheets - week 5.

For the final week of the spreadsheet topic, I decided to set the children two investigations to try and solve to reinforce the formula skills taught previously and to help them become more appreciative of the range of applications spreadsheets can be used for .

The first challenge required them to work out which method of getting pocket money over a ten week period and to then decide which one they would they would choose to have (i.e. they had to identify which one would give them the most money in total after ten weeks). Since the four possible methods were very similar it meant that mental methods couldn’t be used at all and that a structured table in a spreadsheet would be the most convenient way to find a solution. Whilst this only took the children about 15 minutes to complete they did seem to like doing it and clearly understood what they were required to do.


The second challenge required them to enter pretend data into an imaginary class register and to then use this to work out their overall attendance for the week as a percentage. This required them to enter a range of formulae: =SUM() to work out each child’s total attendance, =SUM() to work out the class’ overall attendance and a / and * to convert this to a percentage (no. sessions attended / total no. possible sessions * 100). To ensure that the task was achievable, I tried to keep it as simple as possible – so just 1s and 0s to represent attendance (no different codes for authorised/unauthorised absences!), only 11 children’s data to work with and the final percentage formula’s structure clearly explained so they knew how to work it out. Once done, I also opted to challenge the more confident children to try exploring the effects of changing data in the register to try and reach particular target attendance percentages (e.g. 80%, 95%, 100% etc.) Again, they all completed the investigation well and seemed interested in learning about how the school works out who wins the weekly attendance award given out each Friday in our achievement assembly.


We finished with a discussion about the benefits of using spreadsheets to solve such investigations compared to using mental methods or handwritten calculations (considering: accuracy, neatness and the concept of formulae results updating automatically).

Some people don’t always think that spreadsheets are an exciting topic to teach, but hopefully these last five posts of me sharing some of the activities I do with our children to introduce them to the basic (but fundamental) skills and key concepts have helped to inspire you and made you realise the enjoyment which can be had from learning how to work with them .

20 May 2010

Introducing spreadsheets - week 4.

For the fourth lesson I decided opt for a bit of an unusual spreadsheet activity to let the children consolidate their understanding of =SUM() formulae from last time and to also  teach them how it can be adapted to provided statistical information on a set of numbers .

spreadsheet lessons ks2

To begin, I let them all have a go at playing an online game called Starry Night where the aim was to keep the stars in the air by bouncing them on a moving bubble controlled with the mouse (you could easily use something different here but I picked this because it was: short , intuitive to play and had an outcome based on mouse control skills as opposed to curriculum knowledge). Having completed each game, I asked the children to go to a quick Google Form I’d made to type in their score. After playing for three goes – which took only a few minutes - they then had to submit their three scores so that they could be displayed on the main Google Spreadsheet I’d displayed on the IWB ( which is always greeted with a sense of wonder when it updates in real-time ). 

spreadsheet lessons ks2

Next, I asked them to load up a blank Excel sheet and to design a table to analyse the scores achieved by five of their friends. This only needed to be simple – one column for the child’s name, three for each of their scores and a third column for their total score. Once done, they then had to input the scores taken from the form results table they had opened in their web browser on their laptop (which by now contained everyone’s scores since Google only updates tables published as web pages every 5 minutes), before then entering =SUM() formulae to work out each child’s overall total score. I reminded them of the importance of checking formulae results seem reasonable here too as a way of checking for mistakes.

Following this, at the bottom of the table, I then asked the children to construct two additional formulae:  

This was quite straightforward since the formulae required all used a similar structure to the =SUM() formula . (Incidentally, choosing a game of skill rather than knowledge for them to play for this lesson avoided any unnecessary humiliation of them working out who scored the least and thus who is the least able academically.)

spreadsheet lessons ks2

Finally to finish, I let the children use the formatting tools to improve the appearance of their work – including encouraging them to add borders around the table cells and to correctly align the numbers to the right. I felt that this lesson was highly successfully since it managed to teach the children new skills in a fun and exciting way whilst at the same time also demonstrated to them the benefits of using an online collaboration tool to collect data for analysis . The only thing I would have added to extend it further if time had allowed would have been to let them try replacing the children used with other children’s scores so that they could see how the statistical information (i.e. the formulae results) would update automatically in such a model – but hey, I was quite impressed to fit game playing, Google forms and spreadsheet creation into an hour’s slot on such a hot day !

10 May 2010

Introducing spreadsheets - week 3.

Week three of the spreadsheets unit and time to introduce =SUM() formulae. In the past when I’ve shown children this, I’ve tended to make the mistake of making the initial usage a little too hard – so this year I opted to let them begin with a very simple task so the numbers involved wouldn’t be too difficult for them to interpret :

spreadsheet lessons ks2

Download Race Points Spreadsheet

This seemed to work well with a lot fewer children making mistakes this year (and asking for help when circular reference errors come up). I also created this slide to display on the IWB for them to refer to if needed, which also seemed to help:

spreadsheet lessons ks2

For the next task, I then asked them to do a common modelling activity to reinforce their understanding of the formula and to show them an example of a real-world application of a spreadsheet which included currency . The idea was easy – the children had to select 12 items of food/drink which they would buy for a week’s worth of lunch box meals and then work out how much change they would get from £10.

spreadsheet lessons ks2

Download Shopping Bills Spreadsheet

After copying each desired item’s name and price over from the prices sheet onto the receipt sheet, they then had to enter the appropriate formulae needed to calculate the total cost, thus helping them appreciate the benefits of using =SUM() over adding each figure together in one long formula. They also had to enter a simple subtraction formula then to work out the amount of change that would be received from £10 – which potentially might have required them to amend their earlier purchases if the result was a negative number (i.e. they had overspent).

This activity also worked nicely, with a ll the children seeming to enjoy beginning to use a spreadsheet for more meaningful/relevant purpose , and with a few even managing to go onto my extension task of pricing up the ingredients needed to bake a cake.

29 April 2010

Introducing spreadsheets - week 2.

Last week we looked at referencing cells and entering simple formulae, so to build on these skills this week, I spent a lesson teaching the children why we use cell references (and not the actual number values) in formulae.

I began by showing them a blank Excel sheet and asking them to copy the following table into it:


We then had a quick recap about how to work out a formulae before they then went and inputted twelve individual formulae themselves to work out the product of each pair of numbers (e.g. =A4*B4). Once done, I then told them that I now wanted them to show me the 8 times table (cue some unhappy faces) – by changing the number B values to the number 8 ( cue lots of happy faces when they realised the formulae results updated automatically ). This is always an interesting task because you get to see which children followed your instructions and which children decided to cheat earlier on and input the correct answers instead of the formulae – despite me telling them (though thankfully it was only a couple this year). 

Once they’d spent some time testing and experimenting with their spreadsheet by producing different times tables, I then let them spend 5 minutes changing its appearance to make it look more attractive. At this early level I simply let them experiment with different cell fill colours and border styles to get them used to the tools – discussing conventions like only using thicker borders for the outline will come later. Doing this sort of thing is always something children love so it’s no surprise that they easily managed to make their tables look really fancy.

spreadsheet lessons ks2

For the next task I then asked them to try completing a short spreadsheet model investigation about a sweet shop. It’s an idea I originally got from a secondary school but which I then modified a bit to make it more Year 5-friendly – notably by making the numbers easier to understand (so £5 instead of £500,000) and changing the context (from zoo animals to sweets in a shop). In the investigation, the children need to find the correct number of sweets Paul can buy so that he has 14 sweets that cost £10 altogether.


I first asked them to enter the formulae into the two blue cells to work out the totals for the quantity and price columns (thus helping them realise that a formula can contain multiple cell references). Once done, I then gave them about 10 minutes or so to use trial and error to solve the problem by entering numbers into the yellow cells. We managed to find two solutions today, but I’ve added a space for recording a third solution in the spreadsheet just in case they find one (and to keep them busy if there isn’t). I also encouraged them to try and spot any trends in the numbers (e.g. Can you buy more packets of jelly babies than cola bottles with £10? ).

spreadsheet lessons ks2

Once done, I then ended the lesson with a plenary discussing the advantages of using a spreadsheet to solve such problems compared to using a pencil and paper – notably that it lets you try out lots of different possibilities more quickly, neatly (no rubbings out) and more accurately.

22 April 2010

Introducing spreadsheets - week 1.

‘Spreadsheets’ isn’t the most creative of topics, so I try to make the way I introduce it in Year 5 as interesting as possible for the children .

Following a short talk about some key spreadsheet terminology – ‘row’, ‘column’ and ‘cell’, I begin with a simple activity to help the children learn how to reference cells correctly – with each cell automatically turning green when a child types in the correct cell reference.


Following this, I then teach them how to input simple formulae to make the spreadsheet quickly and accurately perform a calculation with two numbers. To do this, I ask the children to complete an activity I originally made three years ago now called ‘The Gold Mine’. This contains three levels (each on separate sheets) which each have pairs of numbers to perform calculations on and which turn gold if the correct formulae are entered to reach the correct answer:


This usually takes the class about half-an-hour to complete and they always like doing it – not only because it is disguised well with lots of colours and clip art to distract them from what isn’t the most thrilling of tasks, but also because they seem to enjoy the challenge of working out what needs to be included in each formula and the solving of any problems which they might encounter (i.e. the many cases of referencing cells incorrectly or doing the wrong operation).

spreadsheet lessons ks2

Hopefully these couple of activities might be helpful to anyone else who teaches simple spreadsheet skills and if I feel in the mood I might share some of the other spreadsheet activities I do over the coming weeks too.

spreadsheet lessons ks2

Mr Haughton

The Typepad Team

Recent Comments

spreadsheet lessons ks2

Teaching Spreadsheets - A guide to introducing Spreadsheets to Primary Students

Understanding Spreadsheets is often an area of the curriculum that is missed. I would like to offer some ideas on how Spreadsheets can be taught in school from Year 3 to Year 6. I have worked with many secondary schools and observed many teachers and all to often I see that older secondary students don't know how to use spreadsheets. These are tools that most professions use in some capacity, so embedding this understanding at the Primary School level is essential, as many secondary schools make the assumption that these skills have been covered.

spreadsheet lessons ks2

One of the misconceptions when teaching spreadsheets is to teach Excel. This is wrong, as Excel is a brand of spreadsheet made by Microsoft. The way I describe this to students is to talk about the different brands out there and using Spreadsheet as the umbrella term. Under this we can put all the other brands, Google Sheets, Apple Numbers, OpenOffice Calc, Lotus 123 and I'm sure there are many more. So we must teach Spreadsheets as a generic term and ensure the students understand the different brands of spreadsheet out there.

So how can we teach spreadsheet effectively in Year 3, 4, 5 and 6? Giving the students a basic understanding of what they do and then ensuring they understand the power of a spreadsheet. I often tell the students that spreadsheets can often make mathematics much easier, as they do the calculations for you, therefore giving more time to understand the mathematical concepts.

Year 2 & 3

In Year 2 & 3 I have been teaching spreadsheets so they can achieve the following skills:

Recognise the elements of a spreadsheet (cell, row, column)

Move to a specific cell on a spreadsheet (eg C7)

Enter text or numbers & move among cells in a spreadsheet

Select & format a cell or range of cells

To start with we go on a Spreadsheet Treasure Hunt. This can be done in any type of spreadsheet, but the example below has been created in Google Sheets.

The Spreadsheet Treasure Hunt

The treasure is hidden behind one cell. It is a link to a game of Battleships, but you can change this to a link to anything.

NB: To convert this to either Excel, Numbers or other types of spreadsheet, open the spreadsheet in Google by clicking the link and copying, then download the spreadsheet as an xlsx (Excel) spreadsheet. You can then open this in many Spreadsheet applications.

The students will then need to start at the start position and go the cell instructed. Then each cell from then on, they use the Fill tool to change the cell colour. They will then see the new instruction.

Following on from this I would then ask them to replicate a spreadsheet that I display on the board. Similar to one below. In this second exercise, they need to insert text and use their formatting skills to change the cell colours.

The tally Chart Spreadsheet

To distribute both of these tasks I would use Google Classroom by creating a new assignment. The students would need to have Google accounts so they can access Google Classroom.

To understand how you can use Google Classroom, you can watch the video below which explains how to create an assignment.

Create an Assignment in Google Classroom

With Year fours we want to move the students on further with additional skills. New skills to consider would be:

Organise and present data in a data handling application

Use own collected data to create and format graphs (eg, bar chart, pie chart)

To start this we would revisit the Year 3 spreadsheet above and add a graph. So a starter challenge would be recreate the above spreadsheet, but in addition the students would need to find out how to add a graph to the spreadsheet. The students can experiment with different types of graph and then open up a class discussion to determine which graph is best to give an analysis of the data.

Students would then need to come up with three questions they need to ask about the data. Such as :

Which colour car passed the school gate the most?

Did how many more or less yellow cars were there than the red cars?

Further lessons would then follow by linking to a science experiment where the students create a spreadsheet based on the results of the experiment.

In Year 5 we start to look at simple formulas and how the students can use them. The skills we look are :

Enter & use simple formulae (eg A2+B2)

Use basic functions (eg, SUM, AVERAGE etc)

Use drag and copy to apply functions & formulae to a range of cells

Open the spreadsheet below so students can start working through how they can add formulas to a spreadsheet. The first tab is a Starter challenge. The students would need to work out how to do the final cell, as this may have not been taught. They would need to know that all formulas in a spreadsheet start with an "=" sign.

Spreadsheet Formulas

In this task students use international traffic cams to record data. Students to work in two or threes. Assign each group to a Traffic cam from around the world.

Traffic webcams around the world

Copacabana Beach - Rio

Hellenic Parliament - Athens

Piazza Venezia, Altare della Patria - Rome

Milan - Piazza San Babila

Brighton - East Sussex

Rome - Via del Corso

Madrid - Metropolis Building

Verona - Castelvecchio

Milan - Train Station

Antoine de Paule Square - Paola

Starter Activity

Show the different traffic cams around the world. Decide on how much time you will have to count the traffic when you watch the traffic cam.

Traffic Cam investigation

Students to watch the traffic cams at exactly the same time for the exact same duration. The should record the number of vehicles using a tally chart on a piece of paper.

Once the time limit is up, students to transfer their data onto the spreadsheet.

Ask students to start asking questions about the data they have gathered.

With Year 6 we want them to use many of the skills that they have used since Year 3. So I devised a set of tutorials where students create their own Times Table Quiz using a spreadsheet. See the info video opposite. These tutorials tackle all the following skills:

The use of cells, columns and rows

Copying down of cells

Central aligning

Using single quote to enter special characters as text

Basic formula

Using the Dollar sign to fix a formula position

Number sentence formula

The asterisk as a multiplication symbol

Conditions using the IF Function

Conditional Formatting

Hiding columns

Embedded functions with other functions

Formating cells with the fill option

Colouring cells based on conditions

This project has many extensions and can be linked with teacher Algebra. The video below outlines the project and what the students we do to create their own Times Table Quiz.

This lesson will look at how to achieve the following:

Basic formula Using the

Dollar sign to fix a formula position

Times table Quiz using Spreadsheets Lesson 1 - alignment, basic formula, dollar sign, asterisk, copy

In this lesson you will use the following to turn this spreadsheet into a quiz.

Times table Quiz using Spreadsheets Lesson 2 - If then else conditions, hiding columns, Google sheet

During this lesson you will embed two functions with the same formula. Both these functions will be the IF statement. You will understand more fully how the IF statement works.

Times table Quiz using Spreadsheets Lesson 3 - embed functions, IF THEN ELSE condition, Google Sheet

This lesson will focus on formatting. You will find out how to format the spreadsheet with different colours. This is a good teaching strategy for teaching younger children.

This lesson will also expand on the conditions used from previous lessons by looking at conditional formatting. This changes the colour of a cell depending on a condition.

Times table Quiz using Spreadsheets Lesson 4 - Conditional Formatting, Colour, Color, Google Sheet

Using this timestable method within a spreadsheet you can teach some concepts of algebraic equations by using the spreadsheet to substitute. You could make these as complex as possible getting students to develop a number sentence in the spreadsheet based on the equation.

Times table Quiz using Spreadsheets Lesson 5 - Algebraic Equation Algebra Substitution Google Sheet

The Spreadsheet Challenges

The following links are Spreadsheet Challenges for student in secondary or student in upper primary years that want to take spreadsheets to the next level.

Featured Posts

spreadsheet lessons ks2

Should young people of today need to learn about World War II?

spreadsheet lessons ks2

Breaking down the Coding Barriers for our Teachers

The Trackpad vs The Mouse

spreadsheet lessons ks2

Using Twitter with Primary or Elementary Students

Recent Posts

spreadsheet lessons ks2

UK School Closures - Are you prepared? Advice, Suggestions and Ideas on how to get prepared.

Should young people of today need to learn about World War II?

The Edtech Files: Episode 3 - Using iMovie on the iPad

The Edtech Files: Episode 2 - Creating a Magazine Cover

The Edtech Files: Episode 2 - Creating a Magazine Cover

spreadsheet lessons ks2

The Edtech File: Episode 1 - Teaching Copy & Paste

Philosophy on Education

Search By Tags

Join our email newsletter to receive free updates!

Search for Ideas and Resources

More English

Popular Books

Popular Authors

spreadsheet lessons ks2

More Number

spreadsheet lessons ks2

More Science

spreadsheet lessons ks2

More Computing


spreadsheet lessons ks2

Popular Artists

spreadsheet lessons ks2

Follow us on...

spreadsheet lessons ks2

Religious Education

spreadsheet lessons ks2

Classroom Management


Other Topics

spreadsheet lessons ks2

spreadsheet lessons ks2

Help your children to learn about and use spreadsheets effectively with our teaching ideas and resources.

Filter by age

Filter by type.

( Select all / Select none )

Spreadsheets Banner

Spreadsheets Banner

Questions and Answers

Questions and Answers

Spreadsheet Modelling Display

Spreadsheet Modelling Display

spreadsheet lessons ks2

© Teaching Ideas 1998-2020 Teaching Ideas Ltd. Registered in England - No: 09178280 VAT No: 199650845

spreadsheet lessons ks2


  1. Spreadsheet Activities Ks2

    spreadsheet lessons ks2

  2. Spreadsheets Unit 1

    spreadsheet lessons ks2

  3. KS2 SATS Results Spreadsheet

    spreadsheet lessons ks2

  4. Spreadsheets Unit 1

    spreadsheet lessons ks2

  5. KS2-4 Analysis tool

    spreadsheet lessons ks2

  6. KS1 and KS2 Art and Design Assessment Spreadsheet

    spreadsheet lessons ks2



  2. Let's Learn Spreadsheet (Microsoft Excel)


  4. Excel 5 grade Everyday English 2d

  5. Spreadsheet Grade 6

  6. Digital citizenship for kids [4 steps to digital citizenship]


  1. How to Teach KS2 Spreadsheets

    In this video, Twinkl Teacher Arthur showcases Darshna's brilliant lesson series which teaches children how to use spreadsheets:

  2. Lesson 1 What is a spreadsheet?

    Learners will collect and organise data in a format of their choice. They will then explore how data can be structured in a table.

  3. KS2 (Ages 7-11) Computing: Spreadsheets Video Lesson 1

    If you're finding learning on KS2 Computing to be a bit of a struggle, this fun and useful lesson on Spreadsheets could be just what you and your children

  4. Spreadsheets

    Free online lessons for students across a variety of UK school curriculum subjects. ... What is a spreadsheet? 7m video · Lesson 2

  5. Spreadsheets Make Sense

    This resource from SMILE contains 21 activities for using spreadsheets to solve mathematical problems. Teachers' notes give advice on appropriate ways of

  6. Spreadsheet Sweet Shop

    KS1 computing – IT around us lesson plan. Download Now Download Now. PrimaryComputing. Spreadsheets are dead, right? Wrong! Not only are they a great tool

  7. Simon Haughton's website: Introducing Spreadsheets

    Over the past five years, my theme park spreadsheet lessons for Year 6 have ... of resources for introducing spreadsheets to KS2 children:

  8. A guide to introducing Spreadsheets to Primary Students

    So how can we teach spreadsheet effectively in Year 3, 4, 5 and 6? Giving the students a basic understanding of what they do and then ensuring they understand

  9. ICT: Spreadsheets & Databases

    Primary Resources - free worksheets, lesson plans and teaching ideas for primary and elementary teachers.

  10. Spreadsheets

    Help your children to learn about and use spreadsheets effectively with our teaching ideas and resources.