Use Data Validation Instead of Input Masks in Excel for Mac
Question details
Find an alternative to an older input mask feature in Excel 2016 for Office 365 on Mac.

- Product
- Excel 2016 for Office 365 for Mac
- Device & OS
- Mac
- Scenario
- The user wants to restrict spreadsheet entries to a required format or set of values.
- Observed behavior
- Excel for Mac does not provide the same input mask feature; Data Validation can check entries against specified rules.
Identify the cells and the entry format or allowed values you need. If entries must retain leading zeros, format the cells as Text before entering data.
Create a custom Data Validation rule in Excel for Mac
Use a custom formula to check whether entries match a specified pattern.
Data Validation checks entries but does not insert formatting characters automatically like an input mask. Pasting data can bypass validation.
Select the target cells, then choose Home > Number Format > Text if entries need leading zeros or should be treated as text.
With the cells selected, choose Data > Data Validation on the ribbon.
On the Settings tab, choose Custom under Allow and enter a formula that refers to the first selected cell. For example, if the selection starts at A2, use =AND(LEN(A2)=7,MID(A2,4,1)="/") to require seven characters with a slash in the fourth position.
On the Error Alert tab, enable the alert and choose Stop to reject invalid typed entries, then click OK.

Limit Excel cell entries to a list of values
Use a validation list when users should choose from predefined entries.
Restrict spreadsheet entries with WPS Data Validation
Use Data Validation in WPS Spreadsheets to check entries against a custom rule or an allowed list. Format cells as text first when leading zeros must be retained.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your workbook.
- 2. Select the input cells: Select the cells to restrict. If entries need leading zeros, choose Text from the Home tab's number format options.
- 3. Set a validation rule: Choose Data > Data Validation, select Custom or List, and enter the formula or source range for your allowed entries.
- 4. Save the settings: Configure an error alert if needed, click OK, and test an entry in one of the selected cells.

Frequently Asked Questions
Does Data Validation automatically add slashes to an entry?
No. A custom validation rule can check for a slash, but the user must type it. For example, =MID(A2,4,1)="/" checks for a slash in the fourth position of A2.
How do I keep leading zeros in Excel for Mac?
Before entering values, select the cells and choose Home > Number Format > Text. Then type the values, including their leading zeros.
Can I show a dropdown of permitted entries?
Yes. Select the cells, open Data > Data Validation, choose List, and set Source to a range containing the permitted values.
Why can an invalid value still appear in a validated cell?
Pasting data can bypass Data Validation. Check pasted entries against your required format, and use the Error Alert tab to reject invalid values typed directly into cells.




