Objectives

Students will be able to:

  • create drop down lists so users can only select valid data
  • create data validation rules to limit invalid data entry
  • understand that it is tricky to create validation rules for all types of data entered into a digital document
A B C D E
1 Code   Item Price Quantity Total cost
2 Orange juice $6.99 $6.99
3 Chocolate bar $2.99 $2.99
4 Orange juice $6.99 $6.99
5 Honey Tea $4.50 $4.50
6 Coffee $8.25 $8.25
7 Orange juice $6.99 $6.99
8 Earl Gray Tea $8.75 $8.25
9 Orange juice $6.99 $6.99
10
11 Total $51.95

Check out the smart spreadsheet! It uses a lookup function to display the item and price for any valid code that is entered into the model.

But what happens if you enter an invalid code eg #$%@? Try it. Everything gets messed up. The data being entered is invalid - it doesn't relate to the purpose of the cell or the spreadsheet in general.

Software specialists who design spreadsheet models have to be aware that normal users - eg office workers - are human and are likely to make mistakes.

Software specialists who design spreadsheet models are familiar with some powerful data validation techniques. One of them is in the spreadsheet above. Can you find it?

We can create drop-down boxes to force users to select only valid data as shown above.

We can also create rules that will force the spreadsheet software to alert a user if invalid data entry is attempted.

For example, how old are you?

The above example uses a data validation rule: if the number entered is outside the range 14:18, force the user to re-enter.

   Task

If you are using Google Sheets, use this sheet.

If you require a csv file to get started, download this one and this lookup data, too.

Follow these instructions to develop the spreadsheet model:

Insert a new column between columns B and C. Give it a heading Pay Rate Per Hour in row 4.
Insert another new column next to this. Give it a heading Overtime rate per hour in row 4.
Use a lookup function to enter the Staff Name, Pay Rate Per Hour and Overtime Rate Per Hour for each staff member in the model. When copying down, use absolute references when necessary.
Delete all of the IDs in column A. Create a list of items drop-down box in cell A5 which will allow the user to choose a valid ID only. You may need to research how to do this depending on the software you are using.
Copy or repeat your solution to the other cells in the range A5:A23. Remember, you are just designing a model. Select some random IDs - does the model update correctly?
In the company, the staff can only work a minimum of 0 hours per week and a maximum of 35 hours per week. In row 5 of the Number of Hours worked column create a validation rule which will reject data entered outside of this range and display a warning. You may need to research this data validation technique depending on the software you are using.
Copy or repeat your solution to the other cells in the column, to row 23.
Repeat this process for the Number of overtime hours column. The minimum number is 0 and the maximum number is 20. Any data entered outside this range is rejected and a warning displayed.
The Total Pay this week is the Pay rate per hour multiplied by the Number of hours worked + Overtime rate per hour multiplied by Number of overtime hours worked
Copy down your formula for other cells to row 23. Do you need any absolute references in your formula? Think about it!
Create a new column after Total Pay. Call it Net pay. This is the pay after tax. Create a formula that will deduct the taxed amount (ie Total Pay * Tax)from the Total Pay. Copy down your formula...

Make sure all data in your spreadsheet model is visible.

Create a pie chart which shows the number of staff members who worked more than 10 hours overtime and the number of staff members who worked 10 hours or less overtime.

Submit your final spreadsheet file as instructed. BEWARE of saving your work in a CSV file - all of your formatting, functions and chart will be lost!!

Food For Thought

Data Validation techniques are a fundamental part of Spreadsheet Model Design. In today's lesson, you designed a model without caring about the exact information that the spreadsheet needs to store. You are the designer, not the user. Hopefully the user will be happy with your design and will be able to use the model effectively.

Homework

Tags

spreadsheet data csv model merge insert row format currency column range decimal places cell reference

SUBSCRIBE

Join my mailing list to receive updates on the latest blog posts and other things.