To get to Data Validation in Excel, select the cell or range of cells you want to apply it to, then go to the Data tab on the ribbon and click Data Validation in the Data Tools group. That opens the Data Validation dialog box where you set the rules. This works in Excel for Microsoft 365, Excel 2021, 2019, and 2016 on Windows; on a Mac, the path is the same (Data tab > Data Validation), and in Excel for the web, the feature is available but with fewer options.
What Data Validation Actually Does
Data Validation is Excel’s built-in tool for controlling what users can type into a cell. It prevents typos, enforces lists, limits numbers to a range, and blocks invalid dates. For example, you can force a cell to accept only whole numbers between 1 and 100, or only entries from a drop-down list you define. It’s not just for data entry—it also helps keep formulas accurate because bad input often produces wrong results. The rule applies when someone types or pastes into the cell, and you can add an input message (a tooltip that appears when the cell is selected) and an error alert (a pop-up that appears when invalid data is entered).
Opening the Data Validation Dialog Step by Step
- Click the cell or drag to select a range of cells.
- Go to the Data tab on the ribbon (it’s between Formulas and Review).
- In the Data Tools group, click the Data Validation button (it looks like a small grid with a checkmark).
- The dialog box opens with three tabs: Settings, Input Message, and Error Alert.
On a Mac, the ribbon is similar: click the Data tab, then Data Validation. In Excel for the web, you’ll find Data Validation under the Data tab as well, but the dialog is simpler—no Input Message or Error Alert tabs. If you can’t find the button, make sure you’re not in cell edit mode (press Esc) and that the ribbon is fully expanded (double-click any tab to expand it).
Choosing the Right Validation Type
The Settings tab has a drop-down called Allow. This is where you pick the rule type:
- Any value – removes existing validation (the default).
- Whole number – requires an integer; you set minimum, maximum, or equal to.
- Decimal – allows numbers with decimals, same operators.
- List – restricts entries to a predefined list, shown as a drop-down arrow.
- Date – restricts to a date range or specific date.
- Time – restricts to a time range.
- Text length – limits the number of characters (e.g., exactly 5 for ZIP codes).
- Custom – uses a formula to define the rule, giving full flexibility.
For a list, you can type items directly in the Source box separated by commas (e.g., Red,Green,Blue) or reference a range on the sheet (e.g., =$A$1:$A$5). If you reference a range, the list updates automatically when you add items—but only if you use a table or a named range, because a plain range won’t expand automatically.
Common Problems and How to Fix Them
One frequent issue: you apply validation to a cell, but pasting data bypasses it. By default, Excel only validates typed entries, not pasted ones. To block pasted invalid data, you need a VBA macro or use the Circle Invalid Data feature (Data tab > Data Validation > Circle Invalid Data) to visually flag bad entries after the fact. Another problem is that validation doesn’t work on merged cells—Excel only validates the top-left cell. Unmerge or apply to the range before merging. Also, if you copy a cell with validation and paste it elsewhere, the validation comes along; to copy only values, use Paste Special > Values. If you accidentally lock yourself out, select the cells and go to Data Validation > Settings > Allow > Any value, then OK.
Using a Custom Formula for Advanced Rules
Sometimes the built-in types aren’t enough. For example, to allow only dates that are weekdays, choose Custom in the Allow box and enter this formula (assuming the first cell is A2):
=WEEKDAY(A2,2)<6
This returns TRUE for Monday–Friday (WEEKDAY with return type 2 gives Monday=1, Sunday=7). The validation applies to the selected range, and the formula adjusts relative to each cell. Another example: to prevent duplicates in a column (say B2:B20), select B2:B20, choose Custom, and enter:
=COUNTIF($B$2:$B$20,B2)=1
This counts how many times the value in B2 appears in the whole range; if it’s more than once, the rule fails. Note that this formula uses absolute references for the range but a relative reference for the current cell (B2). Custom formulas must return TRUE (or anything non-zero) for valid input. They can’t reference other sheets directly unless you use a named range.
Input Messages and Error Alerts
The Input Message tab lets you show a tooltip when the cell is selected. For example, for a cell expecting a date, you could write:
Related
- Excel Align Cells Evenly: 4 Quick Ways
- Excel and Access LLC: What’s the Difference?
- Excel and Access Training: What You Actually Need
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data to Word: Choose the Best Way to Transfer ItTo put Excel data in Word, select the cells in Excel, press Ctrl+C, click where the data should…
- Excel Range: What It Means and How to Work With OneIn Excel, a range is one cell or a group of cells, usually identified by a reference such…
- Excel AutoFit Column Width Shortcut and How It WorksIn Excel for Microsoft 365 on Windows, select the column or columns, then press Alt+H, O, I in…
- How to Delete a Page in Word UK: A Practical GuideTo delete a page in Word, remove the text, paragraph marks or break creating it; Word does not…
- Flash Fill Dates in Excel: How to Use ItTo flash fill dates in Excel for Microsoft 365 on Windows, enter one or two examples of the…
- Power Query to Combine Excel Files: A Practical GuideTo combine multiple Excel files in Microsoft 365 for Windows, put them in one folder, then select Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.