Anyone who can create a data validation rule in the filled cell MS excel will not detect it.Always make in mind that there is no error in a formula like #REF! or #DIV/0!.If one user sender a workbook to another, then first, a user needs to make sure that the workbook is unlocking otherwise second, a user is never able to access the workbook cells to fill value.If a user wants to create a data validation rule in the workbook for excel web apps or Excel services, then he needs to create an excel data validation rule on the desktop first.Things to Remember About Data Validation in Excel A user can customize the error alert it can be anything as per a user-defined.Choose to be displayed an Input Message when a user clicks on a cell as a user’s guide.Validation of the data, which is available on other sheets or workbook.Restrict times outside a certain time frame.Restrict dates outside a certain time frame.Restrict numbers outside a specified range.Restrict entries to predefined items in a list.Best used when a user wants to share a sheet with another user, and he wants the data entered to be accurate and consistent.If a user wants to apply these changes to all other cells with the same setting, then there is a checkbox that needs to mark, and it will apply to the remaining cells in the sheet.If a user selects between, then a user needs to select the Minimum and Maximum values for the cell(s).If a user wants to ignore the blank, then there is a checkbox called Ignore.Some more condition of excel data validation Settings Text Length – Fixed the length of the text.Ĭonditions in data list in Valuation criteria settingsīetween, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.List – Pick data from the drop-down list.Whole Number – Enter only whole numbers.Any Value – To provide any type of data.Valuation criteria of excel data validation Settings Now enter any invalid e-mail Id- It will throw a described error Message. On the Selection of Cell, the Input message will display.Įnter a valid e-mail id – It will be selected successfully. Step 6: After that, click the Ok button, the setting will be applied to the selected cell. Step 5: Click on the Error Alert tab, provide the Title and an Error message, which will be displayed if a user entered an invalid length E-mail Id. Step 3: Write a formula for selecting only value if it is having ‘ 4: Click on the Input Message tab, provide the message, which will be displayed on the selection of a cell. Step 2: Select custom in Allow drop-down. Step 1: Select the C2 cell, Go to Data Tab and click on Validation data in the toolbar. It will throw the described error message.Įxample #3 – Custom option for e-mail address validation On the Selection of Cell, an Input message will be displayed.Įnter a text which has a text length of 11. Step 7: After that, click the Ok button, the setting will be applied to the selected cell. Step 6: Click on the Error Alert tab, provide a Title and the Error message, which will be displayed if a user entered an invalid length password. Step 5: Click on the Input Message tab and provide a message displayed on the selection of cell. Step 4: Enter your desired length of the password (as an example 11). Step 3: Select equal to from the data list drop-down. Step 2: Click on the data validation tool from the Data Menu Bar and select Text Length in Allow drop-down. Step 1: Select B5 Cell from Example 2 Sheet. We can restrict a user to enter a limited text. Now Enter 11 in cell B2- It will throw by default error.Įxample #2 – Set Input Message and Error Alert Now Enter 1 in cell B2- It will allow a user to enter any whole number from 0 to 10. Step 3.4: All settings will apply to the selected cell. Step 3.3: Select between from the drop-down of the Data list, Enter the Minimum and Maximum number for restriction. Step 3.2: Select the Whole number, then Some more required options will be enabled. Step 3.1: On the Settings tab, Click on Allow drop-down under validation Criteria. Step 3: A data validation Pop-Up will open: Step 2: Go to the Data tab, click on Data Validation from Data in the toolbar.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |