Excel data validation to whole column

Question

I want apply data validation list to a whole column in excel 2010. How can I do that with out copying the validation rule

Answer 1

Applying data validation to a whole column is not a good practice. It is very unlikely that a manually filled spreadsheet will use over a million rows. If you want to automatically apply data validation to new rows of data, you may want to consider using an Excel Table. Set up formulas, formats and data validation in the first data row. When a new row of data is entered, these formulas, formats and data validation will be applied to the new row.

You can start formulas and data validation in the first row of a table, with a little bit of tweaking, if you don't want to have a header row.

Answer 2

Instead of clicking in a cell, click on the header (A,B,C, etc) and go to: Data Tools > Data validation.

Example

Answer 3

In the Excel for Office 365 desktop application Data Validation is not reliably copied to new rows in a table. (I just confirmed this is the case, and the resulting search is what brought me to this post.)

A potential solution (admittedly rather inelegant) is to (1) select the new cells without Data Validation, and at least one cell with the desired Data Validation, then (2) click Data Validation on the Ribbon. A dialog will open asking if you'd like to apply the existing Data Validation rules to the new cells and voila...

Disclaimer: I haven't tested this at all, so YMMV.

Answer 4

select cells you want to apply similar rules, but changing according to different reference( rows change or column change), just give one data validation correct, fix those reference you do not want to change in the formula, then it apllies to every cell