Excel Custom Data Validation (Use formulas to check for text, numbers & length)

Enhance your Excel skills with this detailed tutorial on creating complex data validations. Ideal for ensuring accurate data input in your Excel templates, this video is a must-watch for those needing to enforce specific text and number combinations in cells.

🌟 Key Learning Points:
- Custom Data Validation: Learn how to use Excel's custom data validation feature for complex checks.
- Formula-Based Validation: Discover how to construct formulas to validate specific data formats, such as a text character followed by numbers.
- Practical Examples: Get step-by-step guidance on setting up validations for different cases, like ensuring a specific character count or a fixed text prefix.
- Advanced Formula Techniques: Understand the use of functions like LEN, RIGHT, ISNUMBER, and MID to create robust validation criteria.
- Error Prevention: See how custom validations can prevent incorrect data entries and maintain data integrity.
- Input and Error Messages: Learn to add helpful input and error messages for users, enhancing the usability of your Excel file.

🚀 Practical Applications:
- Create templates that ensure users enter data correctly.
- Apply complex validations for data consistency in professional Excel reports.
- Enhance data integrity in templates shared within teams or organizations.

We also cover Excel's LEN, RIGHT, LEFT and MID functions.

★ Links to related videos: ★

★ My Online Excel Courses ★

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[darrengodkin]

Love the way that you just broke down in small manageable chunks an excel problem.
Like the use of the clipboard too, great tip!

[emrulhasan]

3rd condition I did it myself without watching the video, though it took long time. I failed many times but eventually I figured it out. Little sense of accomplishment but definitely I enjoyed it a lot. Thanks Leila, you are awesome.

[muhammetgylyjov]

Greatest tutorial about data validation, Thank you very much for your effort!!!

[signalmas]

The seed is planted. Never know when one needs another card from your sleeve. Clipboard thing, first time i see it, will try soon, looks very useful. Thank you Leila, always a pleasure.

[mike]

Hi Leila,
Thanks for the good video again. Thanks for that I have been enjoyed watching it again.
Just one little thing, if we didn't change any thing in a dialog box, like the Data Validation dialog box, it is better much to click the 'Cancel' button to close the window/dialog box than to click the 'Ok' button. The reason being is that when 'Ok' is clicked, it would trigger a whole string of programming functions/classes to be called to complete that action, while 'Cancel' button would not trigger any of those. Of course, on the surface, it looks the same but it is not in terms of using system resources and efficiency.
Once again enjoyed your video as always. Please keep up with the good work Leila.

[ElGordoFreeman]

In your first example, a code like ";1234" would be valid. You should use a function like =AND(CODE(UCASE(letra))>=65, CODE(UCASE(letra))<=90))) to ensure it's a letter between A to Z

[sachinrv]

Lovely..and very useful.
I prefer key-board short-cut ALT+D+L and then TAB or SHIFT+TAB to move around in data validation dialogue box. For those who are not well-versed with clip board; they can simply hit a space bar at the beginning of the partial function (so that their efforts are not lost) and then continue with the next leg. After completing individual functions combine them step-by-step in AND function. Thanks for upload Leila :)

[Pankaj-Verma-]

Full of value! As always! Will never see Data Validation the same way!

[sazzarello]

That was great...a big thumbs up!...exposing and using the clipboard that way was really great too.

[michaeldiamond]

Hi Leila,

Great video as always.

When I first saw you using the Value function to convert text to numeric I was reminded of Mike Girvin's videos where he used a double negative (- -) to do the trick. I looked at your comments from other views and saw this was option was already suggested a few days ago.

Another thought that came to mind was to use named ranges (or named range) in place of the actual formulas that would be entered into the custom box. This would save real estate in the data validation box but it demonstrates how you could say the same thing in Excel (get the same results) but just in another way..

The more I watch videos like yours, Bill Jelen and Mike Girvin I'm becoming more and more convinced Excel is an language unto itself. If you think about it, in Excel you do have 5 or 6 languages that exist presently (e.g. formulas, custom formatting, the M language, Dax, VBA, ASCII, etc).

Would it be possible to create a series of videos that covers all aspects of data Validation? I normally only use the list box feature and I think I might be missing out on other functionality it can provide in my spreadsheets.

I also wonder if you could create a video on actual spreadsheet design. I'm always floored by your Excel files in the manner in which you set them up. They look so neat, clean and organized. Not all cluttered like my spreadsheet tend to be (with too many colors, etc).

Michael

[dataisfun]

Ma, can I say you're awesome you seem to have solution to every excel problem I encounter. 👍 👍 👍

[sanjeevtyagi]

And I thought I know data validation quite well...but this something different and innovative..good work Leila

[TheLcsvince]

Thank you. Your tutorial guides me thru writing the custom DV formula.

[atiaabdelkader]

This is the best I have ever seen thank you very very much, I recommended this for my students

[shirish]

Every time your tutorial get me some useful ideas ! Thanks a lot !

[MohammadAshooryan]

as always you're method of teaching is very awesome. thanks a lot for sharing your knowledge.

[barttitulaerexcelbart]

Nice presentation of many text functions. But I really liked the clipboard functionality. In never used it, but I will give it a try. Very useful for nesting formulas.

[sudheerkumar-jtgm]

Hi Leila, You speech is very good and your English also very understandable..
thank you for your uploading valuable videos..

[hanskdavis]

you make all of this seem easy. thank you!!

[chillpill]

Excellent.. as usual.. Great learning. Thanks for such a nice tutorial.