Do you want the data entered in your Excel sheets to fall within a specific range? Do you have multiple cells to validate data at the same time? Well, we have the perfect solution for you. All you have to do is use the file Data validation functionality available in Excel. Read on to learn how to hack this simple trick.
- Open the Excel sheet for which you want to validate data.
- Suppose you want to validate the value in cell B6 of the age column. You want the value in B6 to be between 20 and 90, as this is your preferred age value. For this, select cell B6. You can select multiple cells to apply the same validation. After selecting the cells, click Data language. Then click Data validation button and then up Data validation option. Take the screenshot below for reference.
- A new window called Data validation opens. Under the settings tab, you can set the file Validation criteria. I have set my age criteria between 20 and 90 as shown in the screenshot below.
- Now under the Input message tab, you can note the message that will be shown to the user when they try to insert values into the validated cells.
- Under the Error notice tab, you can choose one Style. This is the icon that will appear with your warning message. I chose warning style. You can also set a custom title and message for your error alert. When you are done, press ok button.
- Now if you try to enter a value into the validated cell, it will show you the input message.
- If you enter a value outside the specified range, an error warning will be displayed.
Try validating your Excel sheets today! Hope you found the article useful. Check back for more tips, tricks and tips. In the meantime, you can check out our article on how to freeze rows or columns in Excel sheets.