An introduction to

Spreadsheets and Databases

Spreadsheets and Databases both allow the user to organise, store and analyse data in table forms.

A Spreadsheet allows the user to input data into cells, which can be manipulated to allow the user to organise the data they need, they can also have formulas added to them to complete calculations.

A database is similar to a spreadsheet but allows the user to lock the type of data which can be added to a cell. They also allow the user to relate different data sets for simpler organisation of large data sets.

Spreadsheets

The data stored in a spreadsheet is organised in Cells, by Rows and Columns.

A Cell

A Row

A Column

These can be used to store and organise the data they hold in different ways.

By naming the Columns data can then be collected in rows below.

TIP. Rows and Columns can be 'Locked/Frozen' meaning that as the data is scrolled through the titles of the columns will remain in place.

The data that is within the rows or columns can have formulas applied to them.

In this example, the sum and average formulas have been used to add the data to generate the 'Total' and 'Average' columns.

The data in a spreadsheet can also be used to generate charts to create a visual representation of the data.

Databases

A Database allows the user to set parameters for the data that can inputted.

In the example here Just2Easy has been used to create a simple Database. Databases allow users to collect large data sets, which are generally more complex than spreadsheets.

They store data in a structured way, often in tables which are similar to spreadsheets.

However, databases can also be linked together. E.g. A library could use a database; one table for books, another for authors. These can then be connected to show which books were written by which author.

Step 1. Define and set the data that is to be collected.

Step 2. Collect the data.

This data is then automatically collated in a table.

How you can collect, sort and analyse your data

Using a digital form (survey), you and your learners can collect data on a range of subjects.

It is important to think about both the questions that you want to find the answers to and how the respondents can answer.

Discussing closed and open questions is important, as well as choosing who can complete the form and if you collect the email addresses of the respondents, as you then need to ensure the data remains secure.

The data collected from the Forms can then be converted into a spreadsheet.

Once you have the responses to your digital survey...

The Timestamp shows when the response to the survey was submitted.

The other cells across Row 1 show the questions that were asked.

The rows below then show the answers that were given. Each row is the data of the person who answered

The columns will show all the answers to that question.

How to view your digital responses on your spreadsheet...

To ensure that you are able to continue to see the questions that have been asked you can freeze rows and columns as needed.

Select View
Click Freeze
Select the number of rows/columns you want to remain fixed in place.

How to sort the data on your spreadsheet...

Now that row 1 will remain in place it will not reorder when the data in that column is sorted.

Select the column of data you want to sort by clicking on the letter at the top of the column.

Once the column to be sorted is selected, learners then right-click/tap with two fingers, to open the menu.

Scroll down to Sort sheet A to Z, or Sort sheet Z to A as required.

Tips when sorting the data on your spreadsheet

If I want to know:

How many year 3 pupils live in a village?

Learners can sort their data to make counting responses easier.

First, they can sort the data in the 'where you live' column by A to Z.

Then, sort the data in the 'which year group' column by A to Z.

This will then group all the answers to those questions, making counting the responses easier.

Making the data more 'user-friendly'

In this example, the data is for all responses to the digital form. We can then create tables which have data in a simplified format.

In the example below you can see where a simplified table has been created.

The =COUNTIF () formula has then been used to add up the responses 'Yes/Ydw' to the questions about the use of Social Media platforms.

The data within the parentheses () is used to generate the final number, in this example:

'Online Safety Form' identifies the sheet the data is coming from

!H2:H26 identifies the cells which are being counted,

"Yes/Ydw" dictates the responses which will be counted.

The formula applied to cell B2 can be applied to the neighbouring cells by 'dragging' the circle in the bottom right of the cell across to the end cell.

NB. The order of these cells must be the same as those in the sheet they are relating to e.g. 'Online Safety Form'.

It is also important to check that when applying the formula vertically, the value of the columns to be counted is correct against those on the Sheet the data is coming from.

NB. In this example, Year 3 relates to the cells in rows 2 to 26, if not corrected, and the formula is applied vertically it will count the year 4 responses as rows 3 to 27. When in reality year 4 responses are in rows 27 to 51.

This simplified table can then be converted into a chart, which gives a visual representation of the data in the table that learners can use to answer questions about the subject. e.g.

Which is the most popular App in Year 3? (YouTube)

How many Year 5 pupils use Snapchat? (15)

Which Year group uses BeReal the most? (Year 4)

Page updated

Report abuse