When you copy data from one cell to another, Microsoft Excel copies the entire cell, including formulas, values, comments, and most importantly, cell formats.

 

For example, if you copy a number from a cell in a column that is formatted as text and paste it into a cell in a column that requires a number, you will get a data validation error. If you then try to type a number into that cell to resolve the error, you will again get a data validation error.

This validation error occurs because the format for the cell changed from a number format to textual one.  When the data was copied data to the new cell with a text format, the number is treated as text.

 

To resolve this error, change the format of the cell  to a format that is suitable for the data type. For example, to correct the problem described above, do the following:

  1. Select the cell you want to modify
  2. On the Home tab, click the drop-down menu under of the Number group, select the Number format.