How to apply data validation to multiple sheets at once in Excel?

It may be easy for you to insert a drop down list in a worksheet, but, have you ever tried to insert this data validation to multiple sheets at once? In this article, I will talk about how to apply one data validation to multiple worksheets quickly and easily.

Apply data validation to multiple worksheets at once

---

Apply data validation to multiple worksheets at once

In fact, you can create one drop down list, and then copy and paste it to multiple worksheets as you like. Please do with the following step by step:

1. Define a range name for your data list which will be created to drop down list, select the data list, and enter a range name into the Name Box, and then press Enter key, see screenshot:

2. Then click a cell or range where you want to insert the drop down list, and click Data > Data Validation > Data Validation, see screenshot:

3. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down list, and then enter this formula: =Namelist (Namelist is the range name you are created in step1) into the Source text box, see screenshot:

4. And then click OK, the drop down list has been inserted in the active worksheet successfully, then copy the drop down list cell, and hold Ctrl key to select the worksheet tabs and click a cell or a range where you want to insert the drop down list, then right click choose Paste Special > Paste Special from the context menu, see screenshot:

5. In the popped out Paste Special dialog box, select Validation under the Paste section, see screenshot:

6. And then click OK button, the drop down list has been inserted into the selected worksheets at once, see screenshot:

---

Demo: Apply data validation to multiple worksheets at once