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
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
