How to Copy Data Validation in Excel (5 Handy Methods)

Suppose you have to deal with lots of data. In that case, you may need to copy Data Validation in Excel. In this article, I will explain how to copy Data Validation in Excel using Microsoft 365 version.


Download Excel Workbook

You can download the Practice Workbook that I used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.


How to Add Data Validation to Cell in Excel

For this session, at first, I need a dataset that contains validation. So, let’s make a dataset with data validation. The steps are given below.

Steps:

  • Firstly, select cell B5 where you want to insert only two kinds of cell values (either Desktop or Notebook). Here, I have written those in the range F6:F7 named Category.
  • Secondly, from the Data tab >> go to the Data Tools option.
  • Finally, from the Data Validation feature >> choose Data Validation… option.

How to Add and Copy Data Validation to Cell in Excel

At this time, a dialog box named Data Validation will appear.

  • Now, from the Settings menu >> choose List in the Allow: box.
  • Then, select references in the Source: box. Here, you must freeze your source using the Dollar sign ($) to copy Data validation. Otherwise, in every cell, the source may change to the next cell of that.
  • Lastly, press OK to make the changes.

As a result, you will see the drop-down arrow beside cell B5. And you can select any of the values.

Now, I will apply another type of Data validation in cell C5.

  • Firstly, select cell C5 where I want to insert only dates as cell values. Furthermore, the date limit is from 1st October to 1st November 2022. Here, I have written those in the range F10:F11 named Date Limit.
  • Secondly, from the Data tab >> go to the Data Tools option.
  • Finally, from the Data Validation feature >> choose Data Validation… option.

How to Add Date type Data Validation in Excel

Again, the dialog box named Data Validation will appear.

  • Now, from the Settings menu >> choose Date in the Allow: box.
  • Then, select the start date and end date. Here, you must freeze your source using the Dollar sign ($) to copy Data validation. Otherwise, in every cell, the source may change to the next cell of that. There is another option, you may write the start and end date instead of using cell references.
  • Lastly, press OK to make the changes.

So, the dataset is ready now.

Final Dataset for How to Copy Data Validation in Excel


5 Methods to Copy Data Validation in Excel

Here, I will demonstrate 5 suitable methods with detailed steps of how to copy Data Validation in Excel. For your better understanding, I am going to use the above dataset. Which contains three columns. Those are Device, Delivery Date, and Sales. Now, let’s see the methods.


1. Use of Context Menu Bar to Copy Data Validation in Excel

Here, you can use the Context Menu Bar to copy Data Validation in Excel. The steps are given below.

Steps:

  • Firstly, select cell B5 and right-click on the Mouse.
  • Secondly, from the Context Menu Bar >> choose the Copy option.

Use of Context Menu Bar to Copy Data Validation in Excel

  • Similarly, select the cell range where you want to copy the Data validation and right-click on the Mouse. Here, I have selected the range B6:B12.
  • Secondly, from the Context Menu Bar >> choose the Paste Special… option.

At this time, a new dialog box named Paste Special will appear.

  • Now, choose the Validation option.
  • Then, press OK.

As a result, you will see the same data validation is working for the cells B6:B12.

Here, I have inserted the following cell values using the data validation.

  • Here, do the same for the Delivery Date column also.

As you can see, copied data validation is working nicely. Here, I have tried to insert a date older than the starting date, so I have got the Microsoft Excel notice.

Finally, using those data validation I have made this dataset.

Read More: How to Use Fill Handle in Excel (4 Quick Tricks)


2. Applying Keyboard Shortcuts

Another way is you can apply the Keyboard Shortcuts to copy Data Validation in Excel. The steps are given below.

Steps:

  • Firstly, select cell B5.
  • Secondly, press CTRL+C keys.

Applying Keyboard Shortcuts to Copy Data Validation in Excel

  • Subsequently, select the cell range B6:B12.
  • After that, press the CTRL+ALT+V keys.

At this time, a new dialog box named Paste Special will appear.

  • Now, choose the Validation option.
  • Then, press OK.

As a result, you will see the same data validation is working for the cells B6:B12.

  • Here, do the same for the Delivery Date column also. But you must freeze the cell references.

Finally, using those data validation I have made this dataset.

Result of How to copy Data Validation in Excel using Keyboard Shortcuts

Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)


Similar Readings


3. Employing Copy Feature in Excel

In the same way, you can use the Copy, and Paste Special feature under the Home tab. You just can use the Copy and Paste Special feature from the custom ribbon instead of choosing them from the Context Menu Bar or Keyboard Shortcuts.

Employing Copy Feature in Excel for Data Validation

  • Then, you may follow the steps of method-1 or method-2 to Copy the Data Validation in Excel.

Read More: How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)


4. Use of Fill Handle Icon to Copy Data Validation in Excel

Now, I will copy the same Data validation using relative cell references. Basically, you can use the Fill Handle icon to copy Data Validation in Excel. The steps are given below.

Steps:

  • To do this, select cell B5 where Data Validation is applied already A square box will be shown in the bottom-right corner of cell B5, it is called the Fill Handle icon.
  • Next, click the Fill Handle icon.
  • Then, hold it, and drag until you reach cell B12.

Use of Fill Handle Icon to Copy Data Validation in Excel

As a result, you will see the listed data validation is working for all the cells.

Finally, using those data validation I have made this dataset.

Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)


5. Re-Applying Data Validation Tool in Excel

Here, you can re-apply the Data Validation tool to copy the same Data validation in other cells in Excel. The steps are given below.

Steps:

  • Firstly, select the cell range up to which you want to keep the same data validation. Here, you have to select the range including the 1st cell where you have already applied data validation.
  • Secondly, from the Data tab >> go to the Data Tools option.
  • Finally, from the Data Validation feature >> choose Data Validation… option.

Re-Applying Data Validation Tool in Excel

At this time, you will see the message from Microsoft Excel.

  • Now, press Yes to it.

As a result, you will see the same Data Validation window, and all the box is filled as the validation for cell B5.

  • Here, simply just press OK.

As a result, you will see the same data validation is working for the cells B6:B12.

  • Lastly, do the same for column C.

Finally, using those data validation I have made this dataset.

Read More: [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)


đź’¬ Things to Remember

  • Here, if you use the cell references for data validation then you must freeze them. Otherwise, you may write the cell values individually.

Practice Section

Now, you can practice the explained method by yourself.

Practice Section for How to Copy Data Validation in Excel


Conclusion

I hope you found this article helpful. Here, I have explained 5 suitable methods on how to copy Data Validation in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo