Docstoc

How to do really useful stuff in Excel - DVRC

Document Sample
How to do really useful stuff in Excel - DVRC Powered By Docstoc
					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.

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:2
posted:4/20/2010
language:English
pages:12