when entering research data in an MS Excel spreadsheet

1. Research Methods Group when entering research data in an MS Excel spreadsheet Data validation and file protection Wim Buysse – ICRAF-ILRI Research Methods Group August 2004

2. Research Methods Group Data ValidationExample 1: numerical treatment levels • There are 4 different treatment levels • We want to avoid entering a treatment level that is out of the range

3. Research Methods Group Example 1 • Step 1: select the range where data have to be entered • Step 2: Choose Data => Validation

4. Research Methods Group Example 1 • Step 3: allow only whole numbers

5. Research Methods Group Example 1 • Step 4: allow only whole numbers between 1 and 4

6. Research Methods Group Example 1 • Step 5: customize the input message

7. Research Methods Group Example 1 • Step 5: resulting input message when cell is selected

8. Research Methods Group Example 1 • Step 6: customizing error message

9. Research Methods Group Example 1 • Step 6: resulting error message when entering a value out of range

10. Research Methods Group Example 1 • WARNING 1 !!!!!!! • You can still enter an incorrect value within the range 1 - 4

11. Research Methods Group Example 1 • WARNING 2 !!!!!!! • Data Validation does only work when manually entering values. It doesn’t work when copying values.

12. Research Methods Group Example 1 • WARNING 2 !!!!!!!

13. Research Methods Group Example 1 • WARNING 2 !!!!!!! Result:

14. Research Methods Group Data ValidationExample 2: non-numerical treatment levels • Same example but this time we will add the levels of the factor “Fallow” as text

15. Research Methods Group Example 2 • Step 1: Type somewhere in the spreadsheet a list with possible treatment levels

16. Research Methods Group Example 2 • Step 2: select the range where data have to be entered • Step 3: Choose Data => Validation

17. Research Methods Group Example 2 • Step 4: this time, select that only data will be allowed from a list

18. Research Methods Group Example 2 • Step 5: indicate the cell references of the location of the list in the “Source” box

19. Research Methods Group Example 2 • The validated cells now contain a drop-down list

20. Research Methods Group Example 2 • Entering an incorrect treatment level results in an error message. Also here, this message can be customized.

21. Research Methods Group File protection • We want to protect our spreadsheet and data from accidental deletion or from unwanted modification. For instance we want to avoid that another person who will enter the data will overwrite cells containing formulas. • Possibilities: • Hide rows or columns • Locking cells

22. Research Methods Group Hiding • Hiding rows or columns : Format => Column (or Row) => Hide • This is the same as putting the column width to 0

23. Research Methods Group Protecting sheets

24. Research Methods Group Protecting sheets • By default, any cell in a worksheet is locked but not protected (cfr. door is closed but not locked)

25. Research Methods Group Protecting sheets • To protect the sheet: choose Tools => Protection => Protect Sheet • First unlock those parts of the worksheet that still have to be changed by your colleagues, for instance the columns where data have to be entered.

26. Research Methods Group Protecting sheets • Result: not possible to change contents of the locked cells • You still can enter data in the unlocked cells

27. Research Methods Group File protection • It is also possible to protect workbooks with a password. • HOWEVER: aim is cooperation among colleagues and sharing of data. • Most researchers working in our field are not secret agents. • Forgetting the password = losing all data!