Year Ten Spreadsheets

To help you revise Spreadsheets, spend 20 minutes going through the following quizzes. Make sure you review your answers and find out where you went wrong if you make a mistake.

• http://www.school-resources.co.uk/FramesForSprdSheetQ2.htm

---

Spreadsheet Validation

Most spreadsheets allow you to format cells so that they only accept certain data e.g., between 1-100. Some also let you create error messages if the user types in an in valid entry.

In Excel, the following gives information on creating a validation (Open Office's Calc spreadsheet works in almost the same way)

Display a message when incorrect data is entered in a cell

• (Watch tutorial)

1. Select the cells for which you want to display a message.

2. On the Data menu, click Validation, and then click the Settings tab.

3. Specify the data restrictions for which you want to display the message. (For Help on an option, click the question mark and then click the option.)

4. Click the Error Alert tab.

5. Make sure the Show error alert after invalid data is entered check box is selected.

6. In the Style box, specify the type of message you want.

To display a warning message with the text "Continue?", followed by Yes, No, and Cancel buttons, with No as the default button, click Warning.

In both message types, the OK and Yes buttons enter the invalid data in the cell. The No button returns to the cell for further editing. The Cancel button restores the previous value to the cell.

7. If you want a title to appear in the title bar of the message or in the Office Assistant balloon if the Office Assistant is displayed, type the text in the Title box.

8. If you want to display your own text for the message, type the text in the Error message box, up to 225 characters. Press ENTER to start a new line in the message. If you don't enter any text in the Error message box, the message displays the following:The value you entered is not valid. A user has restricted values that can be entered into this cell.

Open Excel and try to create a validation that will only accept between 1-50. Create a sensible warning message, and try to enter invalid data.

---

Related pages: Powerpoint

Categories: ICT, Secondary, Current Work