Don't trust data validation in Excel!

Don't trust data validation in Excel! | Excel Off The Grid

Рет қаралды 5,191

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
exceloffthegrid.com/dont-trus...
★ About this video ★
Excel has a dirty little secret - The data validation does not stop a user from entering an incorrect value. That's pretty shocking, as that's the whole point of data validation. 😲
I'm not talking about malicious actions here. I'm talking about simple everyday methods that circumvent data validation without users realizing it.
If you are ready to find out what the issue is... and possible ways to resolve it, then let's go!
0:00 Introduction
0:30 The problem
3:20 Circle invalid data
4:28 Identify invalid data with a formula
4:28 Identify invalid data with conditional formatting
7:08 VBA options
7:44 Prevent drag and drop
9:57 WARNING!
10:32 Prevent copy and paste
11:34 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

Пікірлер: 29

@GrainneDuggan_Excel 3 ай бұрын

Being aware of the issues around data validation is a major step in ensuring our data is valid

@ExcelOffTheGrid 3 ай бұрын

I agree 100%. Knowing the limitations allows us to create suitable processes to handle it. 👍

@TSSC 3 ай бұрын

Another source for breaking data validation is if the user select multiple cells and uses ctrl+enter to update all the selected cells. The validation of the active cell is evaluated, but not the validation rules in the other cells in the selection.

@ExcelOffTheGrid 3 ай бұрын

Yes, that’s another good one.

@TSSC 3 ай бұрын

@@ExcelOffTheGrid Or bad one. A VBA prevention method would be to put Target.Cells(1).Select in Worksheet_SelectionChange.

@ivanmamchych5802 3 ай бұрын

Thank you for the video. Suppose one of the future Excel upgrades will contain something like a new option in the "Protect Worksheet" list: "Prevent paste/drop to validated cells" or so

@ExcelOffTheGrid 3 ай бұрын

Would be nice. 👍

@chriswall4795 3 ай бұрын

Copy paste has always been an issue with validation: constant irritation in my work! Thank you for this, but not sure we can use it because of restrictions on use of VBA

@ExcelOffTheGrid 3 ай бұрын

Even if you allow VBA in your organization, if a user does not click Enable Macros, then a user can still do anything. A solid review process is key.

@rodolforibeiro6853 3 ай бұрын

I noticed this situation yesterday. Thank you very much for the tips!

@ExcelOffTheGrid 3 ай бұрын

You're welcome!

@alterchannel2501 3 ай бұрын

I have applied conditiinal formatting and created a simple macro that loops the cells to check the background color and clear that cell with a msgbox

@ExcelOffTheGrid 3 ай бұрын

That's a very good technique. I'm guessing it took a while to find out how to do that.

@alterchannel2501 3 ай бұрын

@@ExcelOffTheGridin my experience, there is never a unique way of solving issues, it always depends on the situation. Your channel has helped me a lot to find solutions for problems, so thanks a lot

@ziggle314 3 ай бұрын

I have certainly been caught by the cut-and-paste issue multiple times. Since my organization frowns on VBA, I will likely use conditional formatting. Thanks!

@ExcelOffTheGrid 3 ай бұрын

Even I would be uncomfortable with the VBA option in here. I think conditional formatting and a solid review process is the best option.

@maciejkopczynski55 3 ай бұрын

Awesome stuff! One off-topic thing confused me though. How is it that our Worksheet subprocedures are set to “Private” and we still get to reference them inside a Workbook code section?

@ExcelOffTheGrid 3 ай бұрын

Because I used the "Run" command and referenced the module. Run enables us to exclude any Private macro as long as we know the name of it.

@maciejkopczynski55 3 ай бұрын

@@ExcelOffTheGridAwesome Mark! Thank you so much!