Stop Copy & Paste overwriting Data Validation / Need ability to allow only paste values in protected sheets
When you have data validation on a cell, a drop down list for example. A user can circumvent all of this by either copying and pasting into the cell or when dragging from other cells numbers can increment and therefore render the data validation, invalid. A user should not be able to copy and paste values which are not in the validation.
Pasting should never have ignored data validation. Also need to prevent pasting ruining conditional formatting. Allowing "paste value" only on a locked sheet.
This is the number one reason why Excel is unsuitable for data entry. Amazing after all these years but still true.
My scenario is I set up a table (the structure formerly known as a list) with data validation and I want to protect the workbook so ONLY validated data may be entered ONLY in my table. It is impossible! The user can paste rubbish, destroy formatting, add formulae and enter outside the table.
OK, I understand that a paste of values should not trigger a validation dialog separately on every cell, but invalid data should prevent save and prompt the user to fix the invalid cells. I know you can show circles but I want the data fixed.
How has this not been done yet? This would turn Excel into a powerful data collection tool that is system friendly for loading in a database.
Bahram Bohlouli commented
Agree, users should not be able to copy and paste a value out of the valid range.At the moment a user can paste any value irrespective of what the value limit set for a cell.
Wim de Groot commented
Agree with this idea. It should not be possible to overwrite a DV cell by a 'normal' cell.
Copy/Paste as well as VBA should trigger data validation check applied to a cell.
So this is why microsoft software generally sucks: A core feature (if you're going to have different ways of pasting values) but not supporting proper interfaces / methods to actually use it.
Ridiculous that this can happen and I agree with all that this needs to be fixed ASAP
this feature is absolutely required.
Ian KR commented
Yes, absolutely needed, please. I manage data templates for my organisation and I've had to write VBA code to reset DV rules for the input columns. To do so manually would be long-winded and tedious. Need to be able to assert DV rules when user pastes large ranges of data onto DV-driven columns.
It would be great to have the ability to protect conditional formatting when copy/paste from a cell with a different formula
Rajeev Paul commented
It will be a very useful feature to have. Microsoft, please include.
Jim White commented
SpiffyDog ... your instructions do not work (for me, in Office 365 ProPlus). Locking a cell with a DV list does allow me to select the drop-down still, but as soon as I select an entry in the list then I get an error (cell is protected …).
This obviously makes sense … the cell is protected which means you cannot change its value by any means.
1. Create the data validation list (dropdown) for the cell(s).
2. Make sure that the protection is set to 'Locked'.
3. Lock the worksheet.
The user can still use the dropdown on the cell(s) but cannot paste to it.
Agreed. This problem still unsolved in Oct. 2020. One more idea in my mind is not just allowing paste value only, but banning paste any format information:
i.e., Paste formulas allowed, paste value allowed, paste cell formats including shades, lines, fonts, and conditional formatting not allowed.
Restoring all the formulas is time consuming and annoying chores especially when I have to merge data from many individuals. You know, people just don't want to let the macro work for various reasons.
Seriously need the feature to prevent copy-paste or dragging down that overwrite whatever data validation that has in place. It is creating too much hassle and time consuming to correct these inputs manually when you have a big data set.
Agree with the sentiment below
Agree with the sentiment below, its needed yesterday.
Yes this is stupid, Excel should change it immediately
agreeing with other comments, this is a rudimentary feature that makes excel validation very week.
We receive thousands of emails from our vendors using our data validated template. Half of vendors bypass validation via pasting wrong data. Macro files are not allowed.
This feature was needed years ago