Data Validation in Microsoft Excel

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 validation window that pops up select settings
  • In the text box below ‘Allow’ where you see ‘any value’ click on the drop down arrow
  • Select ‘whole number’
  • Under Data click on the drop down arrow and select between
  • 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 selected’
  • 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
  • Click OK
  • 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
  • Now there is no way to make a mistake

Hope you now understand the power and usefulness of data validation in MS Excel.
View the Excel training video:

Further reading:

One thought on “Data Validation in Microsoft Excel

  1. SivaShankar S

    Sir I need to protect the column after the date .
    For Example if i enter values in column 2 (Attendance) today ,the next day it should be automatically protected by password .This is the way to keep attendance for entire month .

    Could you help me sir ?


Leave a Reply

Your email address will not be published. Required fields are marked *