做登记表格，最想让填表人只能选“是/否”、只能选部门名，不要手打“研发部 / 研发中心 / RD”三种写法。这就是数据验证的下拉列表。

最短路径：手填选项

  1. 选中要放下拉框的单元格（或整列）
  2. 数据 → 数据验证（旧版本叫“数据有效性”，WPS 叫“有效性”）
  3. “允许”选 序列
  4. “来源”填：是,否,待定
  5. 确定

来源里的逗号必须是英文半角逗号。中文逗号 ， 会让整个来源被当成一个超长选项，下拉框里只出现一项——这是手填法最高频的错误。

正式做法：引用区域

选项超过 5 个、或者以后要改，就别手填了，把选项写在一个区域里再引用：

  1. 新建一个工作表叫“字典”，A 列写部门全称
  2. 回到目标表 → 数据验证 → 允许“序列” → 光标点在“来源”框里
  3. 直接鼠标去“字典”表选中 A2:A20，来源会自动填成 =字典!$A$2:$A$20

好处很实际：改选项只改字典表，所有下拉框自动同步；选项里有空格、括号也不会被截断。

跨工作簿引用（来源是另一个 Excel 文件）需要用“名称”间接引用，直接写外部路径在新版 Excel 里通常无效。做法：公式 → 名称管理器 → 新建名称指向外部区域，数据验证来源填那个名称。

三个必勾的选项

数据验证对话框里有三页，只设置“设置”页是不够的：

  • 出错警告：样式选“停止”，别人输入不在列表里的值会直接拒绝。选“警告”只是提示，仍可强行输入
  • 输入信息：填一句“请选择全称，不要自行填写”，鼠标移到单元格就会提示。这一条能减少一半的返工询问
  • 忽略空单元格：保持勾选，否则空值也报错

下拉箭头不出现的六种原因

按排查顺序：

  1. “提供下拉箭头”被取消勾选：就在数据验证“设置”页右下角，最容易忽略
  2. 单元格已经在编辑状态：双击进入单元格内部时不显示箭头，单击选中即出现
  3. 选项内容含逗号或换行：手填法被分隔符切碎
  4. 来源区域是动态筛选后的隐藏行：隐藏行的值仍会出现在下拉里，看起来“选项不对”而不是“没有下拉”
  5. 表格被保护且未勾“设置单元格格式”：无法新建验证，但已有的下拉仍可用
  6. 列宽太窄或缩放低于 60%：箭头被挤出可视区，看起来消失了

还有一个真正常见的原因：你选中的区域里已经存在旧的数据验证设置，新设置只覆盖了部分单元格。用“数据验证 → 全部复制/粘贴验证”统一一次。

二级联动下拉（选了省份再出城市）

思路是“名称 + INDIRECT”：

  1. 字典表里，每一列存一个省份的城市，并把第一行写成省名
  2. 选中整个字典区域 → 公式 → 根据所选内容创建名称 → 只勾选“首行”
  3. 在目标表：第一个下拉来源填省名区域；第二个下拉来源填 =INDIRECT(A2)

A2 是刚选的省名。INDIRECT 把文本变成名称引用，从而取出对应列。

限制要如实说明：省份名称里不能有空格、括号、- 等字符，否则不是合法名称，联动会报“来源目前包含错误”。中文省名一般没问题，带括号的“内蒙古（蒙）”会失败。

用验证限制数字、日期、长度

下拉只是“序列”。数据验证还能做：

  • 整数 / 小数：限定范围，比如数量必须 1～999
  • 日期：必须落在起止日期之间
  • 文本长度：限制手机号必须 11 个字符
  • 自定义（公式）：最强也最难，比如 =COUNTIF($A$2:$A$100,A2)=1 可以禁止重复填写

这些限制只在手工输入时生效。粘贴、公式回填、程序导入的内容会绕开验证，所以不能当数据校验唯一手段。

发给别人之前建议转 PDF

Excel 的数据验证在国产办公软件、移动端 WPS、在线表格之间行为并不完全一致：有的不显示下拉箭头，有的把“停止”级错误降级成提示。如果目的是让人按格式提交，更稳的做法有两种：

  • 转成 PDF 作为“填写说明 + 规范样式”发出，让填表人看清规则：Excel 转 PDF
  • 收回后需要归档、又不希望再被改动的，同样转 PDF 定版

反过来说，PDF 是只读交付物，不能填。要“可填写的表单”，需要支持 AcroForm 的工具，本站不做表单字段创建；纸质表单要变成电子数据，走 表格识别转Excel（每页扣 2 点）取回内容后自己重做表。

一个容易忘的事

数据验证跟着单元格走，不跟着行走。整表新增一行时，下拉不会自动延伸。省事的做法是先把区域转成“表格（Ctrl+T）”，验证和格式会自动随新行扩展；代价是与部分超级表功能冲突，套了表格式的表在保护时行为也不同，见 锁定单元格不让别人改。