One of the best features that Microsoft Excel offers for people who are really serious about their spreadsheets is the Data Validation option. This function allows users to control the data that is being inputted into their spreadsheets to make sure that no incorrect entries are made.
Using the function, you can make it possible for your spreadsheet to allow only entries that meet a certain criteria that you have assigned.
Here’s how it works.
Allowing only certain types of numbers
If you want to only allow certain types of numbers in your spreadsheet (whole numbers, decimals, dates, etc), you can use the Data Validation function to do so.
Select a cell. Go to the “Data” tab and click on the Data Validation icon and then on the Data Validation option.
Now you can select what type of numbers you want to allow from the drop-down list.
Creating input error messages
You can use the feature to have Excel warn you with a message when an incorrect entry is made.
Click on the “Input Message” tab and designate a title for the message and then write the message itself.
Creating a validation list
This option enables you to create a drop-down list that will limit the data that can be entered into a cell to a list of entries that you have previously created.
First create the data that you want as part of your list. Select it and then head over to the Data Validation function again. Click on “Allow” and select the “List” option.
Now specify the source by clicking on the “Source” icon and then reselecting the data that you have entered.
The cell will now have an arrow and your personally created drop-down list accompanying it.
Detecting invalid data
If you apply validation to a cell range that was previously created, these entries will not correct themselves manually. Therefore, you are going to have to find them an edit them yourself.
To do this, click on Data Validation and then Circle Invalid Data.
Now you will see which entries are invalid and be able to correct them manually.
No comments:
Post a Comment