How to Apply Data Validation to Multiple Sheets at Once in Excel?

Adding data validation to a single sheet could be a simple matter; we just need to select the cells and click on data validation and enter a formula, but have you ever tried to add data validation to multiple cells at once?

Read this tutorial to learn how you can apply data validation to multiple sheets at once in Excel. This process will only allow the data present in the list on the first sheet to be entered in the other cells. The data validation will be applied to the same location where the data is present in the source sheet.

Applying Data Validation to Multiple Sheets at Once

In this case, we will first create a data validation rule in one sheet and then duplicate it across multiple sheets. Let us see a simple process to apply data validation to multiple sheets at once in Excel.

Step 1

Let us consider that we have an Excel file that contains data that is similar to the data shown in the below image.

First, select the data present in the sheet and create a list named mylist, then click on "Enter." A list will then be created, as shown in the below image.

Step 2

Now keep the data selected, click on data, and select data validation. A new pop-up window will be opened similar to the screenshot shown below.

Now in the new pop-up window, select "Allow to List" and "Source to My List," then click "OK" to apply the data validation to the first sheet.

Step 3

Now select the data, then copy the data using the CTRL + C command and go to sheet 2, then select sheet 2, sheet 2, and sheet 3 at once by holding the Ctrl button on the keyboard.

Then, on the Excel ribbon, click "Paste Special" under "Home," and a pop-up window will appear, as shown below.

In the new pop-up, select validation from the list and click on OK to successfully complete our process.

Step 4

When we try to enter data other than the list in any sheet, an error message will be displayed, as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can apply data validation to multiple sheets at once in Excel to highlight a particular set of data.