Function Problems

How to Restrict Excel Cells to Multiples of a Specific Number

Khadija Khan Oct 10, 2026 871 views

Question details

The user wants to restrict data entry in specific Excel cells so that they only accept multiples of a designated number, such as 5.

How to Restrict Excel Cells to Multiples of a Specific Number

Product: Excel
Device & OS: not provided
Scenario: Entering constrained numerical data into a spreadsheet where values must follow a specific multiple rule to prevent data entry errors.
Observed behavior: The designated cell range only accepts numerical values that are exact multiples of the specified target number, rejecting all other inputs with an error prompt.

Before you start

Identify the range of cells where you want to apply the restriction and decide on the specific number whose multiples you want to allow (e.g., 5, 10, or 100).

Solution 1Recommended

Use Data Validation with the MOD Function

Apply a custom formula in the Data Validation settings to check if the entered number is evenly divisible by your target multiple.

The MOD function calculates the remainder of a division operation. By setting up a Data Validation rule that ensures the remainder is zero when divided by your target number, you can effectively restrict inputs to multiples of that number.

Select the target range

Highlight the cells where you want to apply the restriction, for example, cells A1 through A20.

Open Data Validation

Navigate to the Data tab on the Excel ribbon and click on the Data Validation button.

Choose Custom criteria

In the Data Validation dialog box, under the Settings tab, click the Allow drop-down menu and select Custom.

Enter the MOD formula

In the Formula box, type =MOD(A1,5)=0. Replace A1 with the first cell in your selected range, and replace 5 with your target multiple.

Apply the rule

Click OK to save and enforce the data entry restriction on the selected cells.

Use Data Validation with the MOD Function

Validation Active: Your selected cells will now only accept multiples of the designated number, triggering an error alert if an invalid number is entered.

Efficient Spreadsheet Management

Easily Apply Custom Data Validation in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for applying custom data validation rules, including advanced MOD formulas, helping you maintain perfect data accuracy.

1. Highlight target cells: Open your workbook in WPS Spreadsheet and select the range of cells you wish to restrict.
2. Navigate to Data Validation: Go to the Data tab on the top menu and click on the Validation icon.
3. Set criteria to Custom: In the Settings tab of the prompt window, choose Custom from the Allow dropdown menu.
4. Input your formula: Type your formula, such as =MOD(A1,5)=0, into the Formula input field.
5. Confirm settings: Click OK to enforce the data entry restriction instantly.

Completely free to use for daily tasksFully compatible with Microsoft Excel formulas and file formatsLightweight software with quick installationUser-friendly interface for advanced data management

microsoft office alternative - wps office

Frequently Asked Questions

Can I customize the error message when someone enters an invalid number?

Yes. In the Data Validation dialog box, switch to the Error Alert tab. You can type a custom Title and Error message, such as 'Please enter a multiple of 5', to guide the user when they input an invalid number.

How do I restrict cells to multiples of a cell reference instead of a fixed number?

You can lock a cell reference in your MOD formula. For example, use =MOD(A1,$B$1)=0 where cell B1 contains your target multiple. This allows you to change the target multiple dynamically without editing the validation rule.

Why is my validation rule allowing blank cells?

By default, the 'Ignore blank' checkbox in the Data Validation settings is checked, allowing users to leave the cell empty. If you require a value to be entered, uncheck this box.

How do I find cells that already violate this multiple rule?

After applying your data validation, navigate to Data > Data Validation > Circle Invalid Data. This will place red circles around any existing numbers in your range that are not multiples of your specified number.

Excel & Data Problems