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.
Combine / import multiple worksheets or csv files into one worksheet or workbook:
In your daily work, to combine multiple worksheets, workbooks and csv files into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks or csv files into one worksheet or workbook.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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: