Data Validation in Microsoft Excel can ensure that we enter only the
desired data into the cells in our worksheet. For example, in the video
you see the marks of different students in different subjects. After
entering the marks we calculate the total and average marks. Now while
entering the marks we can make silly mistakes. You may enter 189 marks
for a subject by mistake. Or you may enter -98 because you're tired.
This situation can create havoc for a student whose grades will be
assigned based on the total or average marks. How can we prevent such
data entries. We can use the data validation feature in MS Excel.
Implementing data validation step by step:
Click on the Data tab
From the Data Tools group click on the drop down
arrow next to the item Data Validation
Select 'data validation...'
In the data vaidation window that pops up
In the text box below 'Allow' where you see 'anay
value' click on the drop down arrow
Select 'whole number'
Under Data click on the drop down arrow and select
In the text box under 'Minimum' enter the
value 0 because that is the minimum marks a student can get
In the text box under 'Maximum' enter the value 100
Next click on the Input Message Box
Check the item 'Show input message when cell is
In the text box under title type 'Marks'. You can
type something else if you like.
In the text box under the 'input message' type some
valuable tip for the user like ' Enter marks only between 0 and 100'
because that's our aim
Now click on the tab 'Error Alert"
Check the box next to 'Show error alert after invalid
data is entered'
In the text box under 'Style' select 'Stop' from the
drop down menu because we don't want the user to proceed unless and
until he enters a value between 0 and 100
In the text box under Title enter 'Marks'
In the multiline text box under Error Message enter a
valuable and easy to understand message like; Did you follow the data
entry instructions? Please check!
Now click on OK
When you are in your sheet and the appropriate cell
is selected we will see a message that tells us what data to enter
If we still enter a value outside of 0 like -90 or
above 100 like 190 we get an alert and can only proceed if we correct
our error or cancel the entry