Intro Quiz: Modifying spreadsheets

Video: Modifying spreadsheets

﻿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 an alternative software such as Microsoft Excel or Purple Mash to calculate. Please take a moment now to clear any 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, apply an 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 to get at 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 set and data items are, we will now modify a spreadsheet. On this slide, you have been given ten 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 a 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 spreadsheet, what would you do first? Have a moment to think about what to 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 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 a 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 now, with three more data headings added onto. 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: its 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 column with the data heading discount? The right choice is percent. Here is 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 Xbox 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 have 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 entered the number of hours as a single digit, example three, the computer will delete the initial three to a typed as this is only shown as a place holder. 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 the remaining data headings? For cost per mile, 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 C-2 and drag until you reach C-11. These are the 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 and miles heading, this is shown as 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 task two which is 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've 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'm 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 is two hours and 29 minutes duration. And it is 143 miles away. We now need to feed this information in your spreadsheet. So go to your spreadsheet window. Under travel duration, write two hours 29 minutes. So two colon 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 are using 12p. So, .12 and press enter. Notice they're all in the right format now. Can you now collect the data for all the attractions using Google Maps? You can now attempt 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 will have different numbers than my spreadsheet because your duration and distance in miles will be different too from where travelling from. I hope you enjoyed 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 put two final things I'd like you to do now. First, look back at your spreadsheet today, and identify the two most important things you've 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 share a picture of your work with Oak National on Twitter. So I can see your awesome spreadsheets too. Take care, and enjoy the rest of your learning for today. Bye for now.

Worksheet: Modifying spreadsheets

Exit Quiz: Modifying spreadsheets

Completed: Modifying spreadsheets

During this lesson students will be taught that objects can be described using data. They will build a data set (a collection of related data that can be manipulated using a computer) within a spreadsheet application and apply appropriate number formats to cells.

Computing

Modifying spreadsheets

Introductory Quiz

Video

Worksheet

Final Quiz

Lesson Complete