To data validate in Excel for Microsoft 365 on Windows, select the cells you want to control, go to Data > Data Tools > Data Validation, choose a rule such as List, Whole number, or Date, set its conditions, and select OK. Data validation helps people enter consistent values and can show a drop-down or warn when an entry does not meet your rules. Before starting, decide what values or limits should be accepted and whether you want to block invalid entries or simply warn about them.
What You’ll Need
- An Excel workbook with the cells you want to control identified.
- The accepted values or limits for those cells. For a drop-down, you can type a short list into the rule or keep the options in worksheet cells.
- Editing access to the workbook. If it is shared or protected, you may need permission to change its validation settings.
Step-by-Step
- Select the cells to validate. Click one cell or drag across the input range. For example, select B2:B100 if users will choose a department for each record. Selecting the full range first applies one rule to every selected cell. Leave headings out of the selection so a heading is not treated as an entry.
- Open the validation dialog. On Windows, choose Data > Data Tools > Data Validation, then choose Data Validation if the ribbon menu opens. In some ribbon layouts, the command appears directly on the Data tab. In Excel for Mac, use Data > Data Validation; the dialog layout can differ slightly. In Excel for the web, look on the Data tab for Data Validation. If a control is unavailable or laid out differently, use the desktop app to create or edit the rule.
- Choose the kind of rule. In the Settings tab, open the Allow list. Choose List for a drop-down, Whole number or Decimal for numeric limits, Date or Time for calendar or time limits, and Text length to restrict the number of characters. Choose Custom when a formula is needed. The available condition choices change according to the type you select.
- Set the rule’s conditions. For a Whole number rule, choose a condition such as between, then enter the minimum and maximum. For a Date rule, choose a condition and enter the required dates. For Text length, specify the acceptable character count. Check Ignore blank if empty cells should be allowed; clear it if entries are required. This setting does not make a cell visibly mandatory, so explain required fields in the worksheet as well.
- Create a drop-down list when that is the goal. Choose List, then enter the options in Source, separated by commas, or select a range containing the choices. For example, with allowed choices in A2:A5 and the target cells in B2:B100 on the same worksheet, enter
=$A$2:$A$5in Source. Check In-cell dropdown so users can open the list from the cell. A cell range is easier to maintain than a long typed list: changing an option in the source range updates the choices. Keep the source range organized and do not include its heading. - Use a named range for choices stored on another sheet. Excel’s list validation is more reliable across worksheets when the source is a defined name. Select the options on the list sheet and create a name using Formulas > Define Name. Then return to the validation dialog, choose List, and put the defined name in Source with a leading equals sign, such as
=DepartmentOptions. This assumes the name DepartmentOptions refers to the cells containing the allowed department names. If you later add choices beyond the named range, update the name or use a range designed to include the new items. - Add guidance and choose how invalid entries are handled. In the Input Message tab, check the option to show a message when the cell is selected, then provide a short title and instruction, such as “Choose a department from the list.” In the Error Alert tab, enable the alert and write a useful message. Choose Stop to prevent a typed invalid value, Warning to let the user continue after confirmation, or Information to show a notice that can be dismissed. Use Stop when consistency matters; use a softer alert when exceptions are legitimate.
- Apply, test, and check existing entries. Select OK, then test a valid value and an invalid one in a sample cell. Confirm the drop-down appears if you made a list and that the alert behaves as intended. Validation generally controls new typing, but it does not automatically repair entries already in the range. To find values that do not match the current rule, select the cells and choose Data > Data Validation > Circle Invalid Data. Review each circled cell and correct it; use the validation menu’s option to clear validation circles when finished.
For a custom rule, Excel evaluates a formula for each cell in the selected range. With numbers entered in B2:B100 and the range B2:B100 selected, choose Custom and enter =MOD(B2,5)=0. The relative reference B2 adjusts for each row; the rule accepts multiples of five. For example, 35 returns TRUE and is allowed, while 32 returns FALSE and is rejected by a Stop alert. If blank entries should be permitted, leave Ignore blank selected. Test the first, middle, and last cells of a large range to make sure the references adjust as intended.
Common Mistakes
Choosing the wrong cells: A correct rule applied only to the first row leaves the rest of the column uncontrolled. Select the full input range before creating the rule, or copy the rule to the remaining cells afterward.
Including a heading in a list source: If the source range includes “Department” as its first cell, that heading may appear as a selectable option. Select only the actual choices, and check the drop-down after applying the rule.
Expecting validation to clean old data: A rule usually does not remove or correct values already in cells. Use Circle Invalid Data or review the range and fix existing entries separately.
Assuming paste cannot bypass a rule: Copying and pasting, filling cells, or entering results from other processes can introduce values that do not meet a rule. Validation is an entry aid, not a complete data-security system. Check imported or pasted data after it arrives.
Making the alert unhelpful: A generic message such as “Invalid” does not tell someone how to correct an entry. State what is accepted, give a useful example, and avoid wording that suggests the cell is broken.
Tips
- Use a source range for a drop-down that may change. Keep choices in one clearly labeled place, and consider protecting the list sheet from accidental edits.
- Use consistent spelling and formatting in source values. “In progress” and “In Progress” can be treated as distinct entries in later exact-match calculations even if they look similar.
- Use an Input Message to explain the expected format before an error occurs. For example, tell users to enter a whole number of items, not a number followed by a word.
- When copying a validated cell, use Home > Paste > Paste Special > Validation to copy the validation rule without replacing the destination’s value or formatting. Check the destination range afterward.
- Keep a note of the rule’s purpose and accepted range in the worksheet documentation. This helps another editor understand why entries are restricted and how to update the rule safely.
- If you need to undo an accidental edit, use Ctrl+Z on Windows or Command+Z on Mac. Undo is useful for ordinary cell edits, but it is not a substitute for keeping a backup before major workbook changes.
Troubleshooting
The drop-down arrow is missing. Select the cell and reopen Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. Also verify that the source is not blank or pointing to the wrong cells. The arrow may only appear when the cell is selected.
Excel accepts a value that should be rejected. Check whether the Error Alert tab is enabled and set to Stop. Warning and Information alerts allow the user to proceed. Also consider whether the value was pasted, filled, or produced by a formula; these methods can bypass the normal typing prompt. Use Circle Invalid Data to identify entries that do not satisfy the rule.
Excel rejects a value that looks correct. Inspect spaces before or after the entry, the exact source spelling, and the rule’s boundaries. A date may be stored as text rather than as an Excel date value, and a number may include text characters. Check the selected cell’s rule and test a known-valid value. For a Custom rule, confirm that the formula refers to the top-left cell of the selected range and that its references shift correctly down the range.
The list does not show new choices. If the source is a fixed range, entries added below that range are not automatically included. Expand the source range or update the defined name to cover the new cells. If the source is on another worksheet, verify that the defined name still refers to the intended cells.
The validation command is unavailable. The worksheet may be protected, the workbook may be in a restricted editing state, or you may not have edit permission. Check whether you can edit other cells and ask the workbook owner to unlock the sheet or grant access. If a rule is difficult to manage in Excel for the web, open the workbook in the desktop app and check the Data tab there.
Frequently Asked Questions
Can I allow a value that is not in a drop-down list?
Yes. In the Error Alert tab, choose Warning or Information instead of Stop, or turn off the error alert. Users can then proceed with other values, though they may see a message. If you want to enforce the list, use Stop and keep the source options current.
Can I apply the same validation rule to an entire column?
Yes. Select the intended cells in the column before creating the rule. Applying it to a practical input range, such as B2:B1000, can be easier to manage than selecting the entire worksheet column, especially when the sheet has headings or other content. You can extend the rule later to new rows.
Does data validation stop invalid values from being pasted?
Not reliably. Pasting or filling can put values into validated cells without showing the usual entry alert. Use Circle Invalid Data after imports or bulk pastes, and review the flagged values. For critical records, add a separate checking process rather than relying on validation alone.
How do I remove a validation rule without deleting cell contents?
Select the cells, open Data Validation, and choose Clear All in the dialog, then select OK. This removes the validation settings; it does not clear the cells’ existing values. If you only want to change the rule, edit its settings instead of clearing it.
Can I have a drop-down list that updates when I add choices?
Yes. Use a source that expands to include new choices, such as an Excel table column or a defined name that covers the added rows. Check the validation source after changing the list, because a fixed cell range will not include entries added outside its boundaries.
Related
- Forgot Excel Sheet Password? Safe Recovery Options
- How to Lock and Protect an Excel Sheet in Microsoft 365
- Protect a Sheet in Excel From Viewing: What Actually Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Paste Special Shortcut: Keys and OptionsIn Excel for Microsoft 365 on Windows, copy the cells, select the destination, and press Ctrl+Alt+V to open…
- Best Way to Set Out of Office in Outlook AutomaticallyThe best way to set out of office in Outlook is to use Automatic Replies on your Microsoft…
- Examples of Ratings in Excel: Scores, Stars, and RulesExamples of ratings in Excel include 1-to-5 customer scores, star displays, letter-grade categories, and color-coded performance scales. For…
- Clear Outlook Cache via Run: Safe Steps and FixesTo clear Outlook’s local Auto-Complete cache in classic Outlook for Windows, close Outlook, press Windows+R, enter %localappdata%\Microsoft\Outlook\RoamCache, and…
- Excel Dropdown Lists With Colors: What Works?In Excel for Microsoft 365, you can create a dropdown list with Data Validation, but its built-in menu…
- Excel Table Color Schemes: How to Choose and ChangeTo change an Excel table’s color scheme, click inside the table, open Table Design, and choose a style…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.