Spreadsheets

IT and the world of work

Media

Project management

Computing (Non-GCSE)

Y11 Computing (Non-GCSE)

Key Stage 4

Year 11

Non-Computer Science

THEME

UNIT

In this lesson, we will revisit and develop KS3 spreadsheet skills. We will check our understanding of spreadsheet layout, and refamiliarise ourselves with formulae

Spreadsheet warm-up

﻿Hi, I'm Cash, your computer science teacher for the spreadsheets unit. In this first lesson, we're going to have a warmup. We're going to see where your skills are and what we need in order to complete the remaining lessons. For this lesson, you're going to need a pen, some paper, and you're going to need to remove any distractions that are going to get in your way of focusing. Once you've done that, let's begin. In this lesson, you will create a spreadsheet model for a given scenario, utilise a formulae to calculate income, and apply cell formatting. Spreadsheets data. So how much do you remember about spreadsheets? The Starter activity is a labelling exercise to help you remember common spreadsheet terminology. On the left-hand side, we've got a spreadsheet model with the labels one to six. On the right, we've got some key words that correspond to the labels one to four. And number five and six are to do with formulae that you can use within the spreadsheet. So let's have a go. So have a go at labelling the spreadsheet model. Okay. Let's see how you got on. So number one, it corresponded to column. Did you get that? So columns are often alphabetical. So it's A to Z, it even goes past A to Z as well. And number two was a row. So rows are numerical. Number three was cell B10. Number four, the last one that's left, is the active cell because that's the cell that's being clicked on. So number five, what is the formula in this cell? So as we can see a total's being calculated. There are a couple of ways you could have done this, but the most efficient way would have been =SUM E2:E6. If you did E2 plus E3 plus E4 plus E5 plus E6, it will give you the correct answer, but it's not the most efficient way. Number six, the correct formula for that was C5 times B5, because we're times-ing the, we're multiplying the price by the quantity. So task one, enter data and formulae. Open the spreadsheet RSC Voting. Pause the video to complete your task. Task one, check the specs. Using the worksheet and the spreadsheet RSC Voting, complete task one. Resume once you're finished. Task one, enter data and formulae solutions. So as you can see, we've got a completed spreadsheet there with the correct formulae in there because we can see the total income is correct. We can see total income from text messages and telephone is correct. The problem that you probably encountered was the following. So cell E9 was originally relative. To change that to absolute, in the formula there it was D9 times, and then we've got the dollar symbol there before the column and the row. Task two, charity donation and profit. Charity donation. I'm a Celebrity 2019 donated 15p from every vote to the Make-A-Wish charity. Of course, Rock Star Challenge wants to do the same. For every vote, RSC will donate 15p to charity. Calculate the profit. Once we know how much will be donated to charity, we can calculate how much profit RSC will make. Pause the video to complete your task. Task two, charity donation and profit. Using the worksheet and the spreadsheet RSC Voting, complete task two. Resume once you're finished. Task two, charity donation and profit solutions. So this task, if you found it challenging, that's perfectly fine. It was to get you back into using spreadsheets and to really get you thinking. So cell K4 was the first cell where we entered some information. So 15 pence is represented as 0.15. Then we went into the donation to charity, and we looked at the telephone votes in cell L8. Now, L8, the formula for it would... The theory behind it is we're going to look at the votes for telephone, we're going to add all of them up, and then we're going to multiply it by the 0.15. So how we would do that is =SUM, and that'd be D9 all the way to D18. All that would be in a bracket. And then we would multiply by cell K4. We wouldn't multiply it by 0.15. Now they both give us the correct answer. However, when we multiply it by 0.15, in the future, if we were to change the amount per vote, that wouldn't, it wouldn't get factored in, okay? So if we reference the cell, when we change the amount in the future, it will change the calculations within the spreadsheet model as well. For text votes, in cell L9, we will do something very similar. So it'd be =SUM, bracket open, H9 all the way to H18, bracket closed, multiply that by K4. The total in L10, is it L10? So this spreadsheet is a little bit difficult to follow because the formatting is not very good. That's going to be our next task, by the way. So in the cell for the total, which is L10, we would just add L8 plus L9 together. And it gives us the total of 559,735 pounds and 35 pence. For the profit in cell L13, we would look at the total income that's coming in. So we would add cell E19 plus cell I19. So E19 plus I19, put that all around the bracket. And then we would subtract the total that we give to charity, which is in cell L10. And that gives us the total profit there. How did you get on? It was a little bit challenging, but I hope you found it okay. And even now that you know the solutions, maybe you can go back and you can improve your spreadsheet model. Okay, formatting. Formatting makes data more readable. Cells containing money should be formatted as currency. Some headings which cover multiple columns could be merged, and it might help to add a background fill colour to headings. So for example, you can see how I've now formatted that spreadsheet. Looking at it previously, it was difficult to follow. So the easier something is to follow, the better it is, especially if it's going to be used by somebody else. Pause the video to complete your task. Task three, formatting. Using the worksheet and the spreadsheet RSC Voting, complete task three. Resume once you're finished. Task three, formatting solutions. For this task, I thought I'd show you the actual spreadsheet itself. So looking at the spreadsheet here, we've got a range of options at the top to help us with the formatting. So here I added a fill colour. Here we've got some borders. And here we've got certain words that are in bold. Even here, so we can't really see some of the writing. So as you can see V-O-T, that's being chopped off, so we can move that across. If I double click on it, it usually gives it the right distance from the border there. To manipulate the borders is the option here. If I click onto it, I could choose what kind of border I'd like. I can choose the line colour for the border. And then I can even choose the thickness of the lines as well. I can even have the dashed lines as an option. Here we've got the fill colour that we can choose. And the other options are similar to Word processes. We've got our bold, italics, underline, and so forth. Scenarios. By changing the values in cells B4, B5, and K4, you can affect the amount of income from votes and how much will be donated to charity. Spend a short time changing these values to see what happens. What value does K4 have to be so that RSC donates 500,000 pounds to charity? What values would B4 and B5 have to be so that the total income was one million pounds? Have a go at this. Pause the video and have an attempt. Okay, these scenarios are there to get you thinking about how you can manipulate spreadsheet models and how different factors are taken into account, and making one change can have a knock on effect as well. So that's the end of the lesson. Thank you very much for joining me on our journey to learning more about spreadsheets. Share your work with Oak National. If you'd like to, please ask a parent or carer to share your work on Instagram, Facebook, or Twitter, tagging @OakNational and #LearnwithOak. Thank you very much for your time today. I look forward to seeing you on the next lesson. Thank you and goodbye.

In this lesson, we will implement a spreadsheet which represents seating for the live event of Rock Star Challenge. The spreadsheet includes different seat prices for adults, students, and over 60s. We will use a drop-down list to select seats, and formulae will be used to calculate the price.

The RSC Live event

﻿Hi, I'm Kash your computer science teacher, for the spreadsheets unit. Lesson two is all about using conditional formatting and data validation. For this lesson, you going to need a pen, some paper, and you going to need to remove any distractions that are going to get in the way of you focusing. Once you've done this, let's begin. In this lesson you will, use common formulae to model sales and income from an event, use formatting to make the spreadsheet readable and to highlight different areas, for example, seats available or income, and use data validation when entering data, in order to reduce user error. So as a starter here always think about, do you know what spreadsheets are used for in the workplace? Now, spreadsheet skills are sought after by employers. So job adverts often specify the applicants must be able to demonstrate competency in using spreadsheets. On on the next slide here, we've got some examples of the criteria that they're looking for. So sound IT skills in Microsoft Office or Excel. And on the right hand side, we've got ideal candidate will be computer literate in Excel, Word, and so on. So these are all from reed.co.uk. So your first task, pause the video to complete your task. Who uses spreadsheets? Using the worksheet and the spreadsheet, starter activity, complete task one. Resume once you're finished. Starter solutions. So here I've got the solutions I've come up with. Now there were various options here. So there was more than one correct answer. So for example, for the small business, they can use spreadsheets for estimates for jobs, but also from this list, they could potentially use it for calendar as well. Put it in there, it's also correct. However, the small business might not use it for test scores. Right, it's not appeared. So that was a really useful task to get you thinking about how businesses, how people use spreadsheets, and the use of spreadsheets as well. Modelling the Rock Star Challenge Live event. We're going to be using the spreadsheet Live event seating. Let's take a look at the sections. So on the right hand side, we've got the stage which is broken down into premier, first class, and standard seating. Below that we've got the seating codes, so adults, students or over 60s could take a seat there. They've got their individual code and the amount of discount that they receive. On the right hand side, we've got the prices that they're paying, and below that we've got the income from the seats as well. Your first task is to add seating prices. By only having the seat prices in one place, you can easily change them if needed. So students pay 25% of adult price. Enter a formula to calculate this. And over 60s pay 75% of the adult price. Enter a formula to calculate this. Pause the video to complete your task. Task two, add seating prices Using the worksheet and the spreadsheet, RSC Live event seating, complete task two. Resume once you're finished. Task 2, add seating prices-solution. So as you can see there, on the right hand side, they're the prices that you should have got. Conditional formatting. Conditional formatting is a feature in many spreadsheet applications, that allows you to apply specific formatting to cells that meet certain criteria. We're going to use it for color-based formatting, to highlight seats, that have been taken in the RSC spreadsheet. Pause the video to complete your task. Task three, conditional formatting. Using the worksheet and the spreadsheet, RSC Live event seating, complete task three. Resume once you're finished. Task three, conditional formatting-solution. So what you should have got here, was each of the sections, premier, first class and standard, they've all been assigned a colour. And as soon as the cell is not empty, that colour changes. So that's the condition there, not empty. Data Validation. User error is a real possibility in this spreadsheet. It will be very easy to enter the wrong seating code, so you need to do as much as possible, to prevent user error. You're going to use validation to create a drop-down list using the seating codes, to indicate which seats are sold. Pause the video to complete your task. Task four, data validation. Using the worksheet and the spreadsheet, RSC Live event seating, complete task four. Resume once you're finished. Task four, data validation-solution. So as you can see now, the cells have got a little symbol on there. And once you click on them, it provides you with a drop-down. So the options are the adult, the student, and the over 60s. Counting sold seats and seats remaining. Counting sold seats. In the seat sold section of the spreadsheet, we're going to be using a COUNTIF function, to count the seats sold. There's going to be a lot of repetition, so make sure you working methodically. Pause the video to complete your task. Task five, counting seats. Using the worksheet and the spreadsheet, RSC Live event seating, complete task five. Resume once you're finished. Task five, counting seats-solution. So now on our model, we can count the seats that have been taken. So I've done some testing here. So as you can see in the premier section, an adult seat has been taken. So we can see there, a premier seat in the adult section has been taken, and now we've got 18 seats remaining, because one student seat has also been taken as well. So we had a total of 20 seats available, two of them have been sold, 18 seats remaining. Calculate income. The final stage in completing this spreadsheet model, is to calculate the income for each of the three seating sections, and to create a final total. Pause the video to complete your task. Task six, calculate income. Using the worksheet and the spreadsheet, RSC Live event seating, complete task six. Resume once you're finished. Task six, calculate income solution. So as we can see here, for the income what we did, so for example, for an adult in premier, we multiplied the price that the adults' paying, by the number of seats that have been taken. So here we've got two adults, if we were to change it to three, we then know that this has changed to 60 now, we've got three adults, and that's because the three has been multiplied by the 20, to give us a 60 there. For the total, we use the sum function, and we do the sum of cells X20 to Z20. So XYZ, we're adding all these together. And then for the grand total, we're doing a sum of the cells here. So it is AA20 all the way up to AA22. Fill those seats. So you should have been testing the spreadsheet model, while each each section was being completed. Now it's time to test it further. Sell all the seats in all the seating sections. Check that the seats sold and the seats remaining formulae are working correctly. Check that the income section is working correctly. And you could experiment with changing the seat prices, to see what effect it has on the financial model. Have a go at this. Thank you very much for joining me on lesson two. I hope you got a better understanding of conditional formatting, data validation and using the COUNTIF function. Share your work with us at Oak National. If you'd like to, ask a parent or carer to share your work on Instagram, Facebook or Twitter tagging @OakNational and #LearnwithOak. Thank you.

In this lesson, we will learn how databases are used to manage sales and stock levels. We will look at a partly completed spreadsheet showing items of merchandise to be sold at the RSC Live event.

RSC merchandise

﻿Hi, I'm Cache your computer science teacher, for the spreadsheets unit. Welcome back to lesson three, where we going to be learning all about formatting cells correctly. So in terms, if you've got currency, how to format them and then also a little bit more on conditional formatting. So for this lesson, you got your pen, some paper and you need to remove any destructions that going to get in the way of you focusing. Once you've done this, let's begin. In this lesson you will, use common formulae to model sales of merchandise, implement conditional formatting techniques and format cells correctly, e.g cells representing money should be formatted as currency. Starter. There's always something to buy. Any event, concert, gig or occasion will have its own collection of collectibles. In this lesson, you'll be looking at the different types of merchandise that could be offered to visitors to the Rock Star Challenge Live events. Spend a short amount of time researching the merchandise available for your favourite band. Pause the video to complete your task. Task one- Merchandise. Using the worksheet complete task one. Resume once you're finished. Task one merchandise solution. So this is a possible solution that you could have got. It'd be interesting to see what you've got and at the end of the lesson I'll share with you how you can share your list of merchandise with us as well. So we've got for example, Taylor Swift, Stormzy and so on the prices are there, the web address links there as well. We going to use this data later, when we are doing certain calculations. Complete the merchandise model. Open the RSC merchandise spreadsheet, the item and cost price column have been populated. Pause the video to complete your task. Task two, complete the model. Using the worksheet and the spreadsheet, "RSC merchandise" complete task two. Resume once you're finished. Task two, complete the model solution, how did you get on? Here's an example of a spreadsheet that's been completed. So the forecast might be a little bit challenging. It is cell C eight minus B eight. So, it's the selling price minus the cost price. As you can see, the cost price is usually considerably less than what the item's being sold for. So that was a point out there, and then we're multiplying it by the target that we're projecting to sell and that gives us the forecast. A couple of pointers here, ensure that the cells are in the correct format. So the cost and the selling should be in the currency format. And the target price is fine and forecast is in currency as well. Calculate forecast profit. Using the RSC merchandise spreadsheet, calculate the total forecast and add conditional formatting. Pause the video to complete your task. Task three, calculate forecast profit. Using the worksheet on the spreadsheet "RSC merchandise" complete task three. Resume once you're finished Task three, calculating total forecast profit solution. As you can see in cell G five, we've got the total forecast profit. So that's done by using the formula =sum So what that's doing is adding up, all the cells from E8, all the way open tailed E22. We could have used = but that's not the most efficient way of doing it. What else we can see is that the cell is currently green and that's because it's meeting that target there. If I was to change one of the, some of the figures here. So if I was to change this target for the baseball caps to a 100. We can see that that total forecast now has changed to red. So how's all being done. Let's have a look behind the scenes. So that's all done using conditional formatting, if I click onto format-conditional formatting, we'll be able to see the two rules that are in play at the moment. So if the value is less than whatever is in cell C5, it's going to turn red as the value is greater than whatever is in cell C5 is going to turn green. How we did that, was click on the add another rule, we then chose the cell that we're looking at, we then chose the rule that's applicable which was less than, we then clicked on to the pin bouquet tool, to choose the colour. So we're going to have red, and if it's less than, we can specify a figure. So we could have written a one, five, zero, zero, zero, zero, zero. We could have written in that way however, we made it a little bit more efficient by directing it to the cell. That's because if we now change the target from here it will affect the remaining spreadsheet. Format the spreadsheet. To make the RSC spreadsheet more readable, we can add appropriate formatting to include things like borders, fill headings, bold headings, centre headings, and warp heading text. Pause the video to complete your task. Task four, Format the spreadsheet. Using the worksheet on the spreadsheet, "RSC merchandise" complete task four. Resume once you're finished. Format the spreadsheet solution. So as you see, the spreadsheet is much more readable. It's easy to see the different headings that the boarders make it easy to see the different fill colours, they really add a lot of impact to that spreadsheet. We'd love to see how you have format your spreadsheet. So at the end, I'll share with you how you can get in touch. Can you make a profit? You should have been testing your spreadsheet as each section was completed. Now it's finished you can test it further. Change the sales of items, change the prices of items in column C, you could experiment with changing the target profit and does the conditional formatting work? Pause the video to complete your task. Task five- Can you make a profit? Using the worksheet and the spreadsheet, "RSC merchandise", complete task five. Resume once you're finished. Thank you for joining me on this third spreadsheets lesson. I hope you've got a much better understanding of conditional formatting and formatting the spreadsheet. We'd love to see what you've done, so we'd love to see the merchandise that you came up with and how you've formatted your own spreadsheet. Share your work with us. If you'd like to, please ask a parent or carer to share your work on Instagram, Facebook or Twitter tagging @OakNational and #LearnwithOak. Thank you.

In this lesson, we will learn how to create accurate and useful charts based on generated data. Charts are often used to quickly give information and to help visualise sometimes quite complex data, but it is important to select the right chart for the data and to implement the chart accurately.

RSC data visualisation

﻿Hi. I'm Cache, your computer science teacher for the spreadsheets unit. Welcome back to lesson four, where we're going to be using charts to represent data and also using macros to carry out repetitive tasks. For this lesson, you're going to need a pen, some paper, and you're going to need to remove any distractions that are going to get in the way of you focusing. Once you've done this, let's begin. In this lesson, you will implement formatting to design a clear and readable chart. Implement and test a macro to carry out a repetitive task. Starter. So in this age of information overload, the use of charts and infographics makes it a lot easier for users to understand data. Time is precious and we want to make sure that any charts that we've got are from reputable sources, because any that are from less-than-reputable sources, they can be shared with people and it could spread misinformation. We're going to start off by analysing some charts and I want you to have a think about what's wrong with each of the charts. Starter. What is wrong with this chart? Take a few moments to have a look at the chart, to analyse it and to have some thoughts on what you think might be wrong with it. Okay. Let's have a look at the solutions. So on this chart, the bottom left-hand corner starts at 50, charts usually start at zero. Now because it starts at 50, the jump between the first bar and the second bar is not as much, but because of the 50 start, it looks like it's quite a lot. If you look on the right-hand side, when we do start the baseline at zero, the information is much more accurate. What is wrong with this chart? Once again, take a few moments to have a think. Okay. Let's go over the solutions. So looking at this chart, the Y axis, we're not really using it from 20 to 40, so we don't really need that in there. So manipulating the Y axis. Expanding or compressing the scale on the graph can make changes in the data seem more or less significant than they are. Okay. One last one. What is wrong with this chart? Okay. Let's go through some answers. So here we've used the wrong chart, or we can see the wrong chart is being used. Pie charts are usually out of a whole, so the percentages on the left-hand side, we see 64, 38, 50, they don't add up to a 100% percent. On a pie chart, you know we're expecting it to add up to a 100% percent. On the right-hand side, a bar chart is what we'd most likely use for that type of data. Visualise voting data. Using the RSC voting spreadsheet, create charts to represent the data, column chart of telephone votes and column chart of telephone and text votes. Pause the video to complete your task. Task 1 - Visualise Voting Data. Using the worksheet and the spreadsheet, "RSC voting spreadsheet", complete Task 1. Resume once you're finished. Task 1 - Visualise voting data - solution. So as you can see on the left-hand side, we've got the answers for the first chart. And on the right-hand side, we've got the chart for the telephone and text votes per week. Task 1 - Visualise voting data - solution. So these were the charts that I produced. As you can see, they've got the headings on there. I've even coloured the headings in and the charts themselves, they're nicely laid out and they're on separate tabs at the bottom of that sheet. Make a pie chart. Using the RSC Live event seating spreadsheet, create a pie chart to represent the total seat sales. Pause the video to complete your task. Task 2 - Make a Pie Chart. Using the worksheet and the spreadsheet, "RSC Live events seating", complete Task 2. Resume once you're finished. Make a pie chart - solution. As you can see there, the pie chart's being created. We've got it on a separate sheet. The headings have been enlarged and also the main heading is in the centre and it's got a different colour on it as well. Reset! All the seats have been sold. Now we need to reset the spreadsheet, ready for the next show. When you have tasks that need to be repeated, you can record a macro to automate those tasks. A macro is an action or a set of actions that can run as many times as you want. When you create a macro, you're recording your mouse clicks and keystrokes. On the right-hand side, you can see the steps that are going to be involved in the next task. So we've clicked on Tools at the top, we've clicked on macros and then we've clicked on record macros. Once you do that, you that, every single click that you're taking your, your, every single clip that you're making is being recorded. Pause the video to complete your task. Make a pie solution. So the pie chart you can see there is an example. It's got its own tab at the bottom. The headings are enlarged. The main heading is in a different colour and has also been centred. Reset! All the seats have been sold. Now we need to reset the spreadsheet, ready for the next show. When you have tasks that need to be repeated, you can use a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you're recording your mouse clicks and keystrokes. On the right-hand side, you can see the spreadsheet that we're using. To get onto the macro option, you click on Tools, then macro, and then you click onto record macro. Every step you take after that is then being logged. Pause the video to complete your task. Task 3 - Code a Macro. Using the worksheet and the spreadsheet, "RSC Live events seating", complete Task 3. Resume once you're finished. Task 3 - Code a Macro - solution. So as you can see here, we've got the spreadsheet loaded. We're going to assign some seats. And now as soon as I click onto this reset button, you'll be able to see that all the seats have been reset. So the actions that I recorded, they've been saved and they've been assigned to the reset button. Thank you for joining me on this lesson. I hope you've got a better understanding of using macros and using graphs and charts when representing data. You can share your work with us at Oak National using Instagram, Facebook, or Twitter, tagging @OakNational and #LearnwithOak. Make sure you get a parent's or carer's permission before doing so. Thank you.

In this lesson, we will go behind the scenes to examine the database of RSC merchandise. We will discuss the use of barcodes and QR codes (2D codes) and the different kinds of data they can hold.

RSC stock control

﻿Hi, I'm Cache. Your computer science teacher for the spreadsheets unit. In this lesson, we're going to be using a LOOKUP function to retrieve data. And an IF function to provide users with feedback. For this lesson, you going to use a pen, some paper, and you're going to need to remove any destructions that are going to stop you from focusing. Once you've done that, let's begin. In this lesson, you will implement a LOOKUP function to retrieve data, implement conditional formatting to highlight stock levels and implement an IF statement or IF function to give the user feedback about stock level. Starter. The name of products are sometimes too long to fit on the till receipt. As you can see on the right hand side. Some of the product names have been abbreviated. Our database of RSC merchandise will also have shortened names for the items. Though you can see that some item names have been shortened, loose items get two lines and the one highlighted towards the bottom is Mueller Light Greek yoghourt and it's a four-pack. Starter. A barcode reader or barcode scanner is an optical scanner that can read printed barcodes, decode the data contained in the barcode and send the data to a computer. So what happens after the beep? Pause the video to complete your task. Task one, after the beep. Using the worksheet on the spreadsheet, lesson five starter, complete task one. Resume once you're finished. Task one, after the beep solution. So as you can see, we've got some advantages and disadvantages of using barcodes here. So an advantage could have been, compared for items quickly, but a disadvantage is, it's unreadable if it's crumpled. So the list of solutions is there. And on the right hand side, we can see that they are the correct options. How did you get on? Create the lookup sheet. Using the RSC merchandise database, add sequential item numbers to the first two items, then use the fill handle to drag the sequence down. What this does, is it gives the remaining items a sequential number as well. Drag the formula in E2 with the fill handle. So E2 contains the VAT for that particular item. The formula there, D2, contains the price of the item, and we're multiplying it by dollar I dollar one. In the cell I1, we have the VAT. So the reason why we're using absolute cell referencing, why do you think? Yes, because if we were to just drag it down, it would then become I two I three I four, however, with absolute cell referencing, that doesn't change. Let me show you a demo. Here we have the RSC merchandise database spreadsheet. What you're going to do first is click onto the lookup sheet. Once you get onto the sheet, you can see item numbers, the full names, the shorter names, the price at the till and the VAT. So first as we mentioned, we're going to drag down this item number. If we were to just drag it down without putting a sequential number in the cell A3, what would happen? What do you think would happen? Right? Let's see. So what happens is they've all got the same number. So if we were scanning these items and the barcode was the same for each of them, it would cause a problem. So instead, what we're going to do is, let's delete that. We're going to put in a sequential number here. So one double zero, one double zero two. Now, if you highlight both these two cells and then we use the fill handle, they'll now give us sequential numbers all the way to item one zero zero, one zero, one six. In cell E2, here we have the VAT. What's happening here, is cell D2, which is 15 pounds is being multiplied by cell I1 which is 20%. And here we're working out 20% of 15 pound, which is three pound. We can drag this down using the fill handle, and it will give us a VAT for each of the items. The reason why we've got that conditional formatting, not conditional formatting, sorry. The reason why we've got the absolute cell referencing is because we don't want this cell to change. So if we didn't have this, and in this cell, if we got rid of the absolute cell referencing. Got a little cell there. Yeah, so if I clicked under here, now have dragged down. As you can see, it's not worked. Why? Because this cell is using the correct cells. However, the next cell is now using D3, which is correct. But it's now using this cell here. So that's the reason why we use the absolute cell referencing. So if I put that in, put the dollar signs in, and now if I drag it down, it should work right. Okay. That's all on that. LOOKUP function. The LOOKUP function searches for a value in one column or row and returns a matching value from the same position in another column or row. Using the RSC merchandise database spreadsheet. We want to enter an item number, so the barcode and have the LOOKUP function return data about the item, such as the short name for the product, the price, and the amount of VAT paid on the item. Enter an item number in cell A8 of the RSC merchandise sheet. You will see this formula in cell B8. So the formula is equals VLOOKUP. That's the function that we're using. A8 is the cell that we're looking up. So look up the cell. Now, the, after that comma, you can see it says lookup sheet dollar, A, dollar two, all the way to dollar C, dollar 17. So what that section there is telling us is the range of cells that we're looking in. The three after the comma, tells us that we're returning the third column. And FALSE at the end, tells us that we're searching for an exact match. Drag the formula in B8 to the cells below. Use the fill handle to drag the formula in C8 to the cells below. And that's the formula for that cell. And then use the fill handle to drag the formula in D8 to the cells below. And there we have the formula for that as well. So the column number sold, we're going to enter some numbers into that in the moment. On the input check, so this is going to tell us some important information. So if we input a incorrect number, it'll pop up and it'll say check input. So just have a look at the dialogue box on the right hand side. Number sold cannot be greater than in Stock. So we can't sell, for example, 10 Taylor Swifts t-shirts, if we've only got, you know, eight in stock. So this formula in E8 outputs a message. If this happens, the formula is equals, IF, bracket open G8 minus E8 less than zero comma and then in the speech marks, we have the text that's going to be output, which is check input. Use the fill handle to drag it down. The remaining columns, we've got in stock. So that's the stock level that we're starting off with. And remaining stock. So that will be calculated by the formula equals G8 minus E8. To show you a demonstration of this, let's have a look at the spreadsheet. So we've just added the numbers, the sequential item numbers, and we've just sorted out the VAT column as well. So now, if we go back to the RSC merchandise database sheet, if we enter one of the numbers. So one of the barcodes was for example, one zero zero, one zero zero one. When I click off of the cell, I can tell that this has all been populated for me. However, if I enter a number to this cell, so one zero zero, one zero zero three for example. Nothing appears. That's because the formulas that we've just discussed or formulae should we say, because it's a plural. Here do not or are not applied to cell B9. So what we're going to do is we're going to click on that cell and we're going to drag down. So we can do that for cell B8, cell C8, and cell D8 as well. And now, let's have a look at this, so. That's been done, that's been done, that's been done. Now, if I was to enter another. So for example, one zero zero, one zero zero seven, Z. And that's been populated. Don't worry too much about this if it says N/A. It will only be populated once we put a item number into here. So we also mentioned about this cell here. So this is the number of. That's been sold, or the number of baseball caps that's been sold. This is the input check. Now the input check will only appear if the input's incorrect. So if we've got a 1000 sold and in stock we've got a 1000, that's fine. But if we sold 1001, you can see that this error message appears here. We can always adjust the spreadsheet. So I format it. So that we can see the error message a little clearly. And remaining stock is also in a minus now, because remember the formula for that? We use equals G8 minus E8. Oh, we shouldn't really have a negative here. So let's fix this issue. And that seems fine now. Okay. Pause the video to complete your task. Task two, LOOKUP function. Using the worksheet and the spreadsheet RSC merchandise database, complete task two. Resume once you're finished. Add a status output. In the final column, I8 to I28, we will flag up when an item needs to be reordered. In cell I8, there's a formula for this. Use the fill handle to drag it down. All the way to I28. The formula is equals IF, bracket open, H8 is less than a 100, comma, speech marks, reorder. So what that means is, if the remaining stock is less than a 100, I'll put the text else I'll put nothing. Let's have a look at this on the spreadsheet. Here on the spreadsheet model, in cell I8, we can see that formula being used. So H8 is the remaining stock. If it's less than a 100, we're going to display the text reorder. At the moment, the remaining stock is 500. Thus a cell which gets calculated by G8 minus E8. Which is the number that we've sold and the number that we've got in stock. So we subtract sold by in stock. And that gives us 500. So let's imagine we sold 950. What do you think would happen? Yes. That text now appears reorder. However, if we sold, it can read, we going to see that text. That's really useful for a business, for them to understand how much stock they have and when they need to reorder. We can always manipulate this. So instead of it being less than a 100. When it's less than, for example, 50. We want to reorder. So more expensive items such as the guitar hoodie. We might use that for this one. Pause the video to complete your task. Task three, add a status output. Using the worksheet and the spreadsheet RSC merchandise database, complete task three. Resume once you're finished. Conditional formatting. In the final column, I8 to I28. The cells will either be blank or will show the text reorder. A conditional format could be added to these cells to add a stronger visual reminder for the user. Let's have a look at a demo. So first, we're going to highlight cells I8 to I28. We're then going to click on format. Conditional formatting. And then the options will appear on the right hand side here. So the cells have been highlighted. Under where it says format rules, we're going to select text is exactly, yes. And then we're going to put in the text that appears there. So as you can see at the top here, we've got the word reorder. So just copy that, so I know I'm not making any errors. I'm going to paste it into here. And then I'm going to choose a fill colour of red. Once that's done, I'm going to click Okay. And now let's test this model. So if the total number sold now is 950. In stock, less than, 50. All right, it's going less than 50. All right. So let's make it 951. 49 left on reorder is in red. Pause the video to complete your task. Task four, conditional formatting. Using the worksheet and the spreadsheet RSC merchandise database, complete task four. Resume once you're finished. Testing the database. Let's explore further ideas for exploring, changing, and testing the database, as you can see below. So we can make changes to the VAT rate, which makes changes to each items VAT. Pause the video to complete your task. Task five, testing the database. Using the worksheet and the spreadsheet RSC merchandise database, complete task five. Resume once you're finished. Why have we used conditional formatting in this spreadsheet? So here we have a spreadsheet of students studying computer science and the level in programming and homework that's been done or is missing. Why do you think conditional formatting has been used? Let's have a look. Conditional formatting has been used to give a visual indication when criteria are met. So it's quite easily, quite easy for me to have a look at the spreadsheet and see which students got level three. And it's quite easy for me to see which students have not done particular pieces of homework. What's left to say is thank you very much for joining me on this spreadsheets unit. I hope you learned a lot more about VLOOKUPS, about conditional formatting and about using a functions within spreadsheets. Share your work with us at Oak National. You can use Instagram, Facebook, or Twitter. But be sure to ask a parent or carers permission. Thank you.

Unit Overview: Spreadsheets

Lessons:

Spreadsheet warm-up

The RSC Live event

RSC merchandise

RSC data visualisation

RSC stock control