Lesson video

Hi, my name is Charu.

This is lesson two of six in data and information unit.

In this lesson, we will be modifying spreadsheets.

For today's lesson, you will need a pen and paper, you will also need access to a spreadsheet application.

You can use Google Sheets or alternative software such as Microsoft Excel or Purple Mash to calculate.

Please take a moment now to clear a bit distractions including turning off the notifications on any apps and find a comfortable place to work.

Please pause the video and get comfortable, and when you're ready, press play In this lesson, you will explain that objects can be described using data, explain what an item of data is, applying appropriate number format to a cell, and finally build a data set in a spreadsheet application.

Items of data.

What different data types are there? Do you know what different data types are there? Take a moment to think about it.

Data items can take many forms including words, numbers, dates, images, sounds.

What is a data set? A data set is a collection of related data that can be modified using a computer.

We saw data items and an example of data set in this spreadsheet in our last lesson.

Items of data are stored in cells.

Like here these cells.

And together the data that is related, like in this slide, the data shown on the slide is linked showing sales of computer equipment.

This is called data set.

Now that we know what data cell and data items are, we will not modify a spreadsheet.

On this slide, you have been given 10 destinations from around the UK.

These attractions are data objects.

They can be described using data, for example, Harry Potter Studio Tour is in Watford, Science Museum is in London.

We will now be modifying that spreadsheet holding data about trips from your school to these locations around the United Kingdom.

The questions that we want to answer are: how far are these places? How long would it take to get there? So we need to get started by creating a spreadsheet.

When you start to create your spread sheet, what would you do first? Have a moment to think about what do you do first? That's right, we think of data headings to start with.

We are going to use the following data headings today, attraction, location, travel duration, distance in miles, and cost per mile.

These headings will be the same for every location.

As these are common attributes, all of the attractions have a name, a place where they are located, et cetera.

So the spreadsheet that you will modify would already be populated with attraction and location as data heading.

You will need to fill these three data headings.

This brings you to task one, modifying spreadsheet.

If you follow this link, this will take you to a Google Sheet with two headings and pieces of data already appearing in the spreadsheet.

Can you add the missing data headings? Which is travel duration, distance in miles and cost per mile.

You can now pause your video, take few minutes to fill these three data headings and then resume the video when you're ready.

Welcome back.

This is how your spreadsheet should look like now, with three more data headings added on two.

We will now be formatting data.

There are ways to change how the data is displayed in a cell.

For example, a number can be date or percentage.

Let's look at this table here.

You can change the format in which data is displayed.

This date is in date format.

It's got month and year.

This is just a number.

This number has got a pound sign in front, and this number has got a percentage sign after the number.

Let's do a few questions together and decide what format the data should be displayed in.

Which format is best for data held in a column with the data heading cost? The right choice is currency.

Which format is best for data held in a column with the data heading number of items sold? The right choice is number.

Which format is best for data held in a column with the data heading arriving on? The right choice is date.

which format is best for the data held in a column for data heading discount? The right choice is percent.

Here's another format you will need for this lesson.

Duration format.

This is used to record periods of time such as the length of a movie? How long your computer lesson is for? How long did you play on your PlayStation or X-Box for? If you write text and numbers in the same cell, it cannot be used for calculation.

For example, we cannot write number and text together in one cell, and you could not write 33 in the cell as well because that might indicate 33 hours.

That would not be sufficient.

To solve this problem, spreadsheet format the duration into hour hour, minute minute, second second.

You might've seen this format in digital clocks or stop watches.

So three hours and three minutes would look like this in a duration format.

Did you notice when you enter the number of hours as a single digit example, three the computer will delete the initial zero typed as this is only shown as a placeholder.

Let's try and answer some questions.

What do you think seven hours and 50 minutes would look like in a format? That's the right format.

What do you think six hours and 20 minutes would look like in this format? That is the right format.

We will now be collecting and entering data.

How could you collect data for remaining data heading.

For cost per miles, petrol price will be included on the spreadsheet for you at 12 pence per minute.

This will be same for all the attractions.

How could you collect data for the remaining data heading? Before we collect the data, we should ensure that the cells are in the correct format to hold the data.

I'm now going to demonstrate how to format the cells.

We are now formatting the cells that we need to for the task.

Click on C2 and drag until you reach C11.

These other cells you want to format.

Choose format from the menu, move the cursor down to number.

Now, move the cursor across and select duration.

For the distance in miles heading, this is shown as a number.

So these cells do not need formatting.

The next data heading cost per mile, click and drag the cells you want to format, then choose format from the menu, choose number, move the cursor across, and now move the cursor down and choose currency.

Now collecting, enter the data in these formatted cells.

You can now attempt to task two which formatting your spreadsheet.

Using the instructions on the worksheet, please complete the task.

You can now pause the video to complete your task and please resume once you're finished.

Welcome back.

After formatting the cells, we will collect data for two data headings, travel duration and distance in miles.

We will use Google Maps to find out the distance and the travel duration from school.

Please watch the collecting data demonstration.

We will now collect data for these two data headings, travel duration and distance in miles.

We will use Google Maps to collect this data.

Open Google Maps in another browser window, then click on this direction arrow, in the top box here where it says choose starting point, type in your school post code, in the bottom box type in your destination, I am going to write Alton Tower Theme Park, choose the destination from the dropdown menu.

This will show you the distance from your school to the destination.

It just two hours and 29 minutes duration, and it does 143 miles away.

We now meet to feed this information in your spreadsheet.

So go to your spreadsheet window, under travel duration write two hours, 29 minutes.

So 2:29 and press Enter.

It's going to format it for you.

Remember, we did cell formatting earlier.

And now for distance, type in 143 miles, press Enter, and for cost per miles, we're using 12P so 0.

12 and press Enter.

Notice that all in the right format now.

Can you now collect the data for all the attractions using Google Maps? You can now attempt to task three, collecting and entering data into your spreadsheet.

Pause the video to complete your task, and when you're done, resume.

Welcome back.

This is how your spreadsheet should look like.

Obviously it would have different numbers than my spreadsheet because your duration and distance and miles would be different to from where I'm travelling from.

I hope you enjoy doing this task.

Well, that brings us to the end of today's lesson.

A really big well done on all the incredible learning you have achieved today.

I've got two final things I'd like you to do now.

First, look back at your spreadsheet today and identify the most important things you have learned today.

It's totally up to you what they are.

Second, if you're able to, please take a picture of your work and ask your parent or carer to share it with your teacher.

And if you'd like, also shared a picture of your work with OakNational on Twitter so I can see your awesome spreadsheets too.

Take care and enjoy the rest of your learning for today.

Bye for now.