Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less You can use data validation to restrict the type of data or values that users enter into cells. For example, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed abc , which is not an acceptable value in that cell.
Data validation is invaluable when you want to share a workbook with others, and you want the data entered to be accurate and consistent. Among other things, you can use data validation for the following:
You can choose to show an Input Message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message if you want to, and it remains visible until you move to another cell or press Esc. You set up your Input Message in the second data validation tab. Once your users get used to your Input Message, you can uncheck the Show input message when cell is selected option. You can also show an Error Alert that appears only after users enter invalid data. You can choose from three types of error alerts:
Use these tips and tricks for working with data validation in Excel.
Note: If you want to use data validation with workbooks in Excel Services or the Excel Web App you will need to create the data validation in the Excel desktop version first.
If you inherit a workbook with data validation, you can modify or remove it unless the worksheet is protected. If it’s protected with a password that you do not know you should try to contact the previous owner to help you unprotect the worksheet, as Excel has no way to recover unknown or lost passwords. You can also copy the data to another worksheet, and then remove the data validation. If you see a data validation alert when you try to enter or change data in a cell, and you're not clear about what you can enter, contact the owner of the workbook. You can always ask an expert in the Excel Tech Community or get support in the Answers community. |