Data Validation in Excel - Softotech

Data Validation in Excel: Rules, Custom Formulas and Alerts

Data validation stops wrong entries before they happen. You can limit a cell to numbers, dates, a list, or any rule you can write as a formula.

Where to find it

Select the cells, then go to Data > Data Validation. Pick a rule type under Allow, set the limits, then use the Input Message and Error Alert tabs to guide the user.

The rule types

AllowUse it forExample setting
Whole numberQuantities, agesBetween 1 and 100
DecimalPrices, percentagesBetween 0 and 1
ListFixed choicesYes,No or a cell range
DateDeadlinesGreater than or equal to 1/1/2027
TimeShift timesBetween 8:00 and 18:00
Text lengthCodes, IDsEqual to 6
CustomAny rule as a formulaSee below

Custom formula examples

Choose Custom and enter a formula that returns TRUE for allowed entries. Write it for the first selected cell.

  • No duplicates in A2:A100: =COUNTIF($A$2:$A$100,A2)=1
  • Text only: =ISTEXT(A2)
  • Weekdays only: =WEEKDAY(A2,2)<6
  • Email must contain @: =ISNUMBER(FIND("@",A2))

Error alerts

  • Stop: rejects the entry.
  • Warning: asks Yes or No.
  • Information: shows a message and accepts the entry.

Write a clear message such as “Enter a whole number from 1 to 100”.

Find and fix bad data

Validation does not check values that are already in the cells. Use Data Validation > Circle Invalid Data to see them. Remember that pasting over a cell removes its validation. For lists specifically, see how to create a drop-down list.

Free pricing tips for your business

Get short, practical tips on pricing jobs and using our free calculators. No spam, unsubscribe any time.

    Built with Kit

    Frequently asked questions

    Can users bypass data validation?

    Yes, by pasting values over the cell. Protect the sheet or paste as values carefully.

    How do I remove data validation?

    Select the cells, open Data Validation and click Clear All.

    Can validation check another sheet?

    Yes in modern Excel. Point the source or formula to the other sheet, or use a named range in older versions.

    How do I copy validation to other cells?

    Copy a cell with validation and use Paste Special > Validation.

    Need a custom Excel sheet or automation?

    Talk to us

    More free tools: All free tools · Drop-down lists · Conditional formatting · Lock cells