name exam mark coursework mark jane 12 lauren 15 Making sure that only sensible data is speedy 15 DATA VALIDATION woody 23 xena 43 Check the validation works by trying to ente lee 32 your name in the coursework mark column tom 62 junior 30 dom 64 paul 65 kirsty 13 linda 46 To make sure that stupid data isn't entered into a cell you can VALIDATE it. So for exam marks (and coursework mark) we could say that they have to be a whole number with a minimum of 0 and a maximum of 100. This would mean that none of -3 34.84 and 101 would be allowed. To validate cells, select the ones you want to validate and then go to DATA, VALIDATION and then fill in the input box as required You can also get the computer to make a message appear when you click on the cell (the input message) and also a different message to appear if they try and enter an invalid piece of data (error alert) ure that only sensible data is entered = DATA VALIDATION Check the validation works by trying to enter your name in the coursework mark column bus train car You can also limit what people put i walk How do you get to work? a different type of data validation ca cycle Create your list (blue) of option get to work normally name the list something sensib cells and then changing the na then pressing return To restrict people to entering o (yellow) cell, choose DATA, VA allow LIST and then in the sou and then the name of the list. C If you don't put the = sign th Choose the method of transpo arrow that has appeared by th n also limit what people put in a cell by doing ent type of data validation called a LIST Create your list (blue) of options that people can use (eg how they get to work normally - bus, train, car or walk) name the list something sensible (eg transport) by highlighting the cells and then changing the name in the box above cell A1and then pressing return To restrict people to entering options from the list, in the correct (yellow) cell, choose DATA, VALIDATION and then change it allow LIST and then in the source box you must have an = sign and then the name of the list. Click OK If you don't put the = sign then it doesn't work! Choose the method of transport by clicking on the drop down arrow that has appeared by the side of the cell name exam mark coursework mark total mark jane 12 23 35 lauren 15 54 69 Getting some data to stand o speedy 15 64 79 woody 23 78 101 xena 43 43 86 lee 32 52 84 tom 62 46 108 junior 30 54 84 dom 64 45 109 paul 65 21 86 kirsty 13 24 37 linda 46 46 92 This column has been conditionally formatted - so, if a person has a score of 100 or more the cell is automatically shaded in yellow and if they get less than 40 to shade in blue To do this, I went to Format, Conditional Formatting and then filled in the input box like this : Getting some data to stand out column 1 of the column 2 of column 3 of the table the table table total mark grade comment 0 e poor 35 d could do better 55 c ok 75 b pretty good 100 a fantastic To do a vlookup, I first of all set up a table of what I the computer is going to have to look up. In this case This column (which in the I have put the grade boundaries in the first column, so if they get 0, 1, table is column 1) must be set 2, 3, 4, 5……34 that is the first range which then if you move across up so that it is in ascending the table shows that they deserve a grade 'e' and the comment of order (either if you do it using 'crap' words or numbers). If you Anyone getting a 35, 36, ......54 will get a 'd' and 'could do better' and don’t put it in ascending so on. order, it won't work. I then highlighted the table (just the yellow bit) and to name it I then went to Insert, Name, Define and then called it 'comments' of what I the computer is column, so if they get 0, 1, h then if you move across 'e' and the comment of d' and 'could do better' and bit) and to name it I then ed it 'comments' name exam mark coursework mark total mark looked up grade jane 12 23 35 d lauren 15 54 69 c speedy 15 64 79 b woody 23 78 101 a xena 43 43 86 b lee 32 52 84 b tom 62 46 108 a junior 30 54 84 b dom 64 45 109 a paul 65 21 86 b kirsty 13 24 37 d linda 46 46 92 b The formula that I typed in cell F2 was =vlookup(D2,comments,2) vlookup stands for vertical lookup - its vertical because I want it to go down my 'comments' table. (You can do horizonatal lookups too if you want!) so, I want the computer to read the total mark that I got (eg the 35 which is in cell D2) then go to the 'comments' table, and then go down the first column until it gets to the category that 35 would be in. I then want it to go to the second column (2) and put the text/number that it finds in there into the current cell looked up comment could do better ok pretty good fantastic pretty good pretty good fantastic pretty good fantastic pretty good could do better pretty good the formula that I typed in cell G2 was =vlookup(D2,comments,3) my So its basically still looking up the number 35 (because that's whats in D2) in the comments table but it uses the answer that’s the 35 which is in cell in the 3rd column rather than the 2nd column first column until it gets e second column (2) name exam mark coursework mark total mark jane 12 23 35 lauren 15 54 69 Making sure that formulae or label c speedy 15 64 79 can't be deleted or changed accide woody 23 78 101 = CELL PROTECTION xena 43 43 86 lee 32 52 84 We don't want anyone to be able to change the formulae that ca tom 62 46 108 mark, as no matter what mark they have got you want to always junior 30 54 84 numbers together. We also want to never let people change the dom 64 45 109 names in (you might need to change them but if you are giving p paul 65 21 86 worksheet to use, they may not need the ability to change them) kirsty 13 24 37 our columns. So, highlight the cells that you are going to linda 46 46 92 example, the exam marks and the coursework marks, b2:c13) Then choose FORMAT, CELLS, PROTECTION and get rid of th LOCKED box: To actually then set protection, when you have FINISHED ALL T your sheet you can go to TOOLS, PROTECTION and choose PR This should mean (depending on what version of Excel you are u can either select the cell but when you try and change it the com you, or it won't even let you choose the cells. g sure that formulae or label cells be deleted or changed accidently = CELL PROTECTION yone to be able to change the formulae that calculates the total r what mark they have got you want to always add the two We also want to never let people change the cells with our ght need to change them but if you are giving people the they may not need the ability to change them), or the titles of ells that you are going to allow them to change (so in our m marks and the coursework marks, b2:c13) RMAT, CELLS, PROTECTION and get rid of the tick in the et protection, when you have FINISHED ALL THE SET UP of n go to TOOLS, PROTECTION and choose PROTECT SHEET. (depending on what version of Excel you are using) that you he cell but when you try and change it the computer won't let en let you choose the cells.