If you use the Data Validation feature in Excel, you may come across situations where you want to put the Data Validation on after there is already data in the cells. If you copy/ paste them in, it overwrites all the data in the cells (which is not what you want).
Table of contents
YouTube Copy data validation to cells with data in
Copy Data Validation to cells that have data in them- Method 1- Paste Special
In the example below, we want to make column A use the list in column F.
We can click on cell A2, activate Data Validation and set up the list. This will however only put the validation option onto that first cell. We now need to get it onto all the other cells in column A, but we don’t want the names to change. They must keep the contents of the cells, but the data validation must be in the cell.
To do this, you can copy cell A2, highlight the rest of the cells that need this validation type, and click Paste Special.
There is an option for Validation. If you choose this option, ONLY the data validation is copied over. The contents of the cell remains as it was.
What about values that are not one of the data validation lists
The risk with this is that one/ some of the cells contain items that do not match the validation criteria. To easily see which items must be fixed with the approved Data Validation, you can use the ‘Circle Invalid Data’ tool.
As shown below, when you highlight the cells and use this tool, Excel highlights any cell that does not contain one of the data validation list items.
The method aboves requires that you physically run the tool each tim. This may not be ideal, as this error type can easily happen if data is copied into cells.
Another test would be to have a new column that, using VLOOKUP or COUNTIF, checks that the item in the cell is one of the items in the list.
Copy Data Validation to cells that have data in them- Method 2- Highlight all cells
Alternatively, you can highlight all the cells and then click on Data Validation. If you already have some validation in the first cell, Excel will warn you about this and offer to extend it.
If you click yes, then the validation is automatically applied to the other cells without affecting the values in the cells.
If there wasn’t any validation in the cells, you can set up the validation and the same validation will be applied to all the cells at the same time without affecting the contents.
Related
Add tick/ cross into your Data Validation and use it in IFs and Conditional Formatting