Heru “eyoe” Afrizal

In many worksheets that you create, users will enter data to get the desired calculations and results. Ensuring valid data entry is an important task. You may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Providing immediate help to instruct users and clear messages when invalid data is entered is also essential to make the data entry experience go smoothly.

Once you decide what validation you want to use on a worksheet, you can set up the validation by doing the following:

1. Select one or more cells to validate.
2. On the Data menu, click Validation, and then click the Settings tab.
3. To specify the type of validation that you want, do one of the following:

• Allow values from a list

1. In the Allow box, select List.
2. Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default).
3. Make sure that the In-cell dropdown check box is selected.

For example:

• To limit entry to a question, such as “Do you have children?”, to two choices, type Yes, No.
  
• You can also create a list of values from a range of cells.

Note The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

• Allow a whole number within limits

1. In the Allow box, select Whole Number.
2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
   

   

3. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value. For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.

• Allow a decimal number within limits

1. In the Allow box, select Decimal.
2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
3. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson’s salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.

Note To allow a user to enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style  on the Formatting toolbar.

• Allow a date within a timeframe

1. In the Allow box, select Date.
2. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
3. Enter the start, end, or specific date to allow. You can also enter a formula that returns a date. For example, to set a time frame between today’s date and 3 days from today’s date, select between in the Data box, enter =TODAY() in the Start Date box, and enter =TODAY()+3 in the End Date box.

• Allow a time within a timeframe

1. In the Allow box, select Time.
2. In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.
   
3. Enter the start, end, or specific time to allow. You can also enter a formula that returns a time value.
   For example, to set a time frame for serving breakfast between the time when the restaurant opens in cell H1 and 5 hours after the restaurant opens, select between in the Data box, enter =H1 in the Minimum box, and enter =H1+”5:00″ in the Maximum box.

To be continue…