Copying Data Validations to Different Areas of Your Worksheet

Data validation allows you to control what information is allowed to be in a cell. This is a handy feature to help ensure data accuracy. Quite often you may need to repeat the data validation in different areas of your worksheet. Well rather than recreating the data validation for each area, here’s a useful way to copy an existing cells validation to additional cells or ranges of cells.


In the below example I have setup the following validation for cells (A2..A7) where only whole numbers between 1 and 5 will be allowed.

It’s easy to copy this validation to different areas of my worksheet by first copying one of the cells between A2 and A7 using CTRL+C.

Next go to your target cell or range of cells and then press ALT+ESN. This copies only the data validation rule to your target cells or range of cells. You could also select Paste Special and then select Validation from the Paste options.

Click the OK button to complete the copy of your data validation.