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.

You can also enter a formula that returns a number value. Restrict data entry to a decimal number within limits. To set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box. You can also enter a formula that returns a number value.įor example, say you're validating data in cell F1. For example, to set upper and lower limits, select between.Įnter the minimum, maximum, or specific value to allow. In the Data box, select the type of restriction that you want. Restrict data entry to whole numbers within limits.įrom the Allow list, select Whole number. The following table lists other types of data validation and shows you ways to add it to your worksheets. Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message. Select the Error Alert tab to customize the error message and to choose a Style. Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s). Select the Input Message tab and customize a message users will see when entering data. Set the other required values based on what you chose for Allow and Data. Text Length - to restrict the length of the text. Time - to restrict the cell to accept only time. List - to pick data from the drop-down list.ĭate - to restrict the cell to accept only date.

Whole Number - to restrict the cell to accept only whole numbers.ĭecimal - to restrict the cell to accept only decimal numbers. On the Settings tab, under Allow, select an option: Select the cell(s) you want to create a rule for.

Microsoft excel no spreadsheet found