Blog of Calc LLC

Excel has a data validation tool. However, that only works during data entry stage. It cannot validate existing data. Learn how to use Excel data validation tool on data entry Here we tell you a method about how to validate existing data. Below are scores of students in a class. The score should be between 0 and 100. However, the teacher made mistakes with the scores of David and Emily, which is below 0 and above 100 respectively. Of course, if there are only 5 cases, you can check it manually. However, if there are large number of records, manual inspection is inefficient. You can write a simple formula to do the data validation. Write a formula like this: =IF(AND(B2>=0,B2<=100),"correct","wrong") Two functions are used in this formula. The first function is the AND function. It combines the two logical tests: score >=0 and score <=100. A valid score has to meet both criteria to be valid. Then we used the IF function. If the condition is met, it return...

Excel has a convenient tool preventing you entering wrong values. It is called "Data validation". The name is somewhat misleading since what it actually does is data entry validation. If you already have a data with some wrong values in it, this "Data validation" tool cannot help you. Learn how to validate existing data Suppose you are a teacher and want to enter scores for each student. There are two columns. Column 1 is student name and column 2 is score. 1. Highlight the score column, since it is the column we want to do data entry validation. 2. Click on the Data tab and then choose " Data Validation " in the Data Tools group. 3. An option box pops up. 4. We want the score to be a whole number and the value is between 0 and 100. 5. You can ignore the " Input Message " options since we do not quite need them. Go to the " Error Alert " tab and enter a Title and Message that make sense to you. And click OK . 6. Check your data entry ...