How to Validate Data in Excel || ERROR FREE DATA || Detail || Beginners Tutorial || in Hindi
Please Like & Subscribe our Channel _ https://www.youtube.com/@UCws6j4jCnjLChw9CwOb-JdQ Data Validation in Excel : Data validation is a feature in MS Excel used to control what a user can enter in a cell of an excel sheet. Like, restrict entries in a sheet, such as a date range or whole numbers only. We can even create dropdowns as well, which saves un-necessary space and shows the values in a single cell. Also, we can create a customized message which will appear user insert any incorrect value or an incorrect format. As we can use data validation in excel to make sure a value is a positive number, a date between 15 and 30, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters etc. Locate in MS Excel Click on Data Tab in the Menu bar. Select on Data Validation from the toolbar under the Data Tab: How to Create Data Validation Rule in Excel? Valuation criteria of excel data validation Settings Any Value – To provide any type of data. Whole Number – Enter only whole numbers. Decimal – Enter only decimal numbers. List – Pick data from the drop-down list. Date – Accept the only date. Time – Enter only time. Text Length – Fixed the length of the text. Custom – Enter a custom formula. Conditions in data list in Valuation criteria settings Between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to. Some more condition of excel data validation Settings If a user wants to ignore the blank, then there is a checkbox called Ignore. 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 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. When to Use Data Validation in Excel? Best used when a user wants to share a sheet with another user, and he wants the data entered to be accurate and consistent. Restrict entries to predefined items in a list. Restrict numbers outside a specified range. Restrict dates outside a certain time frame. Restrict times outside a certain time frame. Limit the number of text characters. Validation of the data, which is available on other sheets or workbook. Choose to be displayed an Input Message when a user clicks on a cell as a user’s guide. A user can customize the error alert; it can be anything as per a user-defined. Things to Remember About Data Validation in Excel 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. 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. Always make in mind that there is no error in a formula like #REF! or #DIV/0! Anyone who can create a data validation rule in the filled cell MS excel will not detect it. How to Remove the Excel Data Validation rule? A user can remove excel data validation from a cell, select the cell, click Data Validation, and then click Clear All. A user can modify or delete the excel data validation rule only if the inherited sheet is unprotected. If it is password protected, contact the owner of the workbook. Only he can help to unprotect the workbook. As there is no way to recover or lost the password option in MS Excel. As an example, A user can specify a meeting scheduled between 9:00 AM and 6:00 PM. Apply data validation to cells Use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Select the cell(s) you want to create a rule for. Select Data _Data Validation. Data Validation On the Settings tab, under Allow, select an option: Whole Number - to restrict the cell to accept only whole numbers. Decimal - to restrict the cell to accept only decimal numbers. List - to pick data from the drop-down list. Date - to restrict the cell to accept only date. Time - to restrict the cell to accept only time. Text Length - to restrict the length of the text. Custom – for custom formula. Under Data, select a condition. Set the other required values based on what you chose for Allow and Data. Select the Input Message tab and customize a message users will see when entering data. 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 Error Alert tab to customize the error message and to choose a Style. Select OK. Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message. Thanks For watching Please Like & Subscribe our Channel _ https://youtube.com/playlist?list=PLx0QJ6kp8ZMRWg2fmgzKQ5ogzWeSy1zp8
Please Like & Subscribe our Channel _ https://www.youtube.com/@UCws6j4jCnjLChw9CwOb-JdQ Data Validation in Excel : Data validation is a feature in MS Excel used to control what a user can enter in a cell of an excel sheet. Like, restrict entries in a sheet, such as a date range or whole numbers only. We can even create dropdowns as well, which saves un-necessary space and shows the values in a single cell. Also, we can create a customized message which will appear user insert any incorrect value or an incorrect format. As we can use data validation in excel to make sure a value is a positive number, a date between 15 and 30, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters etc. Locate in MS Excel Click on Data Tab in the Menu bar. Select on Data Validation from the toolbar under the Data Tab: How to Create Data Validation Rule in Excel? Valuation criteria of excel data validation Settings Any Value – To provide any type of data. Whole Number – Enter only whole numbers. Decimal – Enter only decimal numbers. List – Pick data from the drop-down list. Date – Accept the only date. Time – Enter only time. Text Length – Fixed the length of the text. Custom – Enter a custom formula. Conditions in data list in Valuation criteria settings Between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to. Some more condition of excel data validation Settings If a user wants to ignore the blank, then there is a checkbox called Ignore. 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 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. When to Use Data Validation in Excel? Best used when a user wants to share a sheet with another user, and he wants the data entered to be accurate and consistent. Restrict entries to predefined items in a list. Restrict numbers outside a specified range. Restrict dates outside a certain time frame. Restrict times outside a certain time frame. Limit the number of text characters. Validation of the data, which is available on other sheets or workbook. Choose to be displayed an Input Message when a user clicks on a cell as a user’s guide. A user can customize the error alert; it can be anything as per a user-defined. Things to Remember About Data Validation in Excel 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. 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. Always make in mind that there is no error in a formula like #REF! or #DIV/0! Anyone who can create a data validation rule in the filled cell MS excel will not detect it. How to Remove the Excel Data Validation rule? A user can remove excel data validation from a cell, select the cell, click Data Validation, and then click Clear All. A user can modify or delete the excel data validation rule only if the inherited sheet is unprotected. If it is password protected, contact the owner of the workbook. Only he can help to unprotect the workbook. As there is no way to recover or lost the password option in MS Excel. As an example, A user can specify a meeting scheduled between 9:00 AM and 6:00 PM. Apply data validation to cells Use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Select the cell(s) you want to create a rule for. Select Data _Data Validation. Data Validation On the Settings tab, under Allow, select an option: Whole Number - to restrict the cell to accept only whole numbers. Decimal - to restrict the cell to accept only decimal numbers. List - to pick data from the drop-down list. Date - to restrict the cell to accept only date. Time - to restrict the cell to accept only time. Text Length - to restrict the length of the text. Custom – for custom formula. Under Data, select a condition. Set the other required values based on what you chose for Allow and Data. Select the Input Message tab and customize a message users will see when entering data. 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 Error Alert tab to customize the error message and to choose a Style. Select OK. Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message. Thanks For watching Please Like & Subscribe our Channel _ https://youtube.com/playlist?list=PLx0QJ6kp8ZMRWg2fmgzKQ5ogzWeSy1zp8