Excel’s Format as Table feature on the Home ribbon has many advantages. One advantage that isn’t mentioned much is the automatic extension of Data Validations.

If you have a table that includes Data Validation cells in a column and then apply Format As Table, you will find that the cells in Data Validation column will extended automatically as the table extends.

If you add a new column to an existing formatted table and add Data Validation to a cell in that column you will need to copy the Data Validation to the other cells in the column (see note below). Once you have copied it to the other cells then the Data Validation will extend automatically.

Pasting Data Validations Only

Excel has a feature that only pastes Data Validation after a copy. It is in the Paste Special dialog – see image below.