See all How-To Articles

How to Copy Data Validation in Excel & Google Sheets

This article will demonstrate how to copy data validation in Excel and Google Sheets.

DataValidationCopy intro

Copy Existing Data Validation to New Range

If we have one range of cells with an existing data validation rule set, we can copy the data validation rule with the Paste Special Command.

In the example below, the highlighted cells contain a data validation rule that only allows the user to enter a maximum value of 40 in the validation range.

DataValidationCopy existing

To copy the data validation to a new range of cells, first, (1), highlight a cell that contains the existing data validation and then (2) click Copy.

DataValidationCopy copy

Select the target range for your data validation.  This is the range that you wish to copy the data validation rule to.

DataValidationCopy selectrange

In the Ribbon, select Home > Clipboard > Paste > Paste Special.

DataValidationCopy pastespecial

Click on Validation, and then click OK.

DataValidationCopy pastevalidation

The existing data validation rule will now be pasted to the highlighted cells (target location).

DataValidationCopy validationpasted

How to Copy Data Validation in Google Sheets

In the example below, the highlighted cell already contains a data validation rule restricting the input in the cell to between 1 and 40.

DataValidationCopy gs paste existing

Highlight the cell that contains the data validation rule, and then, in the Menu, select Edit, Copy.

DataValidationCopy gs paste copy

 

Select the range of cells where you wish the data validation to be copied to, and then, in the Menu, select Edit > Paste special > Data validation only.

 

vDataValidationCopy-gs paste validation

 

The validation rule will now be copied to the destination cells.  To test the rule, we can type an invalid number into one of the cells.

DataValidationCopy gs invalid value