How to Copy Data Validation in Excel & Google Sheets

This tutorial demonstrates how to copy data validation in Excel and Google Sheets.

 

 

Copy Existing Data Validation to New Range

If you have a range of cells with existing data validation, you can copy the data validation rule to another range 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.

 

 

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

 

 

2. Select the target range – the range you wish to copy the data validation rule to.

 

 

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

 

 

4. Click on Validation, and then click OK.

 

 

The existing data validation rule is pasted to the highlighted cells.

 

 

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.

 

 

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

 

 

2. 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.

 

 

3. The validation rule is copied to the destination cells. To test the rule, type an invalid number into one of the cells. You get a red triangle on the cell and an error message.