My New Book Is Now on Amazon

I recently published a new book on Amazon:

Data Validation… a back door to Master Excel

Here is the promo video for the book.

I am also including in this article the Intro pages and the Table of Contents to explore before you buy. You can also read what fellow MVPs said about this book.



Hi, My Name is Nabil Mourad and in this book, I will show you how Data Validation is the back door to learning, enjoying and mastering Excel.

Through Data Validation I will be covering over 60 functions, Charts, Slicers, Pivot Tables, Conditional Formatting, Macros, Dashboards, Power Query, Data Model, Dynamic Arrays and even VBA.

Be ready to elevate your Excel Skills and learn lots of Tips and tricks along the way.

The Instructor

I am Nabil Mourad

I am a Microsoft Most Valuable Professional

A Microsoft Certified Trainer since 2008

A Microsoft Office Specialist- Master in All versions of Office to date.

I work as a professor of Computer applications in Toronto Canada and I deliver Corporate Training at Client Site or remotely. Through my work and Popular YouTube Channel “OfficeInstructor” , I taught Excel to hundreds of thousands of Clients across the Globe.

Who are the target clients?

My clients are Excel users who want to impress their clients and colleagues by creating: Powerful, dynamic and unique functionalities.

Whether you are an Accountant, Financial Analyst, Business Administrator, project Manager an Educator, if you are working in Human resource, Insurance, sales, or marketing this course is for you.

Excel is the most widely used spreadsheet application, being used by over 750 million people across the globe. Data Validation will ensure making the bottom line of your worksheets look Great.

Who are NOT my target clients?

This book is not for absolute beginner Excel users as I do not spend much time on how to navigate in Excel, or any of the foundational principles.

Course Goal

By the end of this book you will be able to create effective and dynamic worksheet functionality cutting the data entry time to just a tiny fraction of what it currently is. You will ensure the data input is only what you want while the output will be impressive to your clients, colleagues, and boss.


Dynamic Data Validation… is a Game changer in Excel, at the basic level it controls what a user can enter or cannot enter in a cell, you are in control of the user’s input.

It also makes data entry easier and ensures the integrity of your worksheet by avoiding typing errors when users can simply select an option from a drop list.
In addition, more advanced scenario data Validation can make a regular worksheet look as a dynamic dashboard.

In this book, we explore the full potential of the 8 types of data validation.

We start with the basic functionality, then we discuss 2 types in profuse details: Custom and List Data validation. Along the way, we’ll be tackling almost every functionality in Excel and relate it to our topic.

We will learn about the extreme power of combining Drop lists with Dynamic arrays and touch the limit of what Data Validation can do.

Because practicing is an integral part of the learning process, I am providing for each section practical exercises for you to do, related to the section topic.

And now, let us start our course!

Download the exercise files from the link:


Table of Contents

Chapter One – The Basics of Data Validation 1

Types of Data Validation.xlsx 1

Input Message.xlsx 20

Error Message 24

Allow Typing in A Data Validation Range 30

Chapter Two – Custom Data Validation 34

Prevent duplicates 34

Unique values with exception 37

Allow unique records only 40

Allow only specific values 46

Prevent some characters in a data validation range 48

Allow only multiples 51

Allow only percentages 53

Preset sum value for a data validation range 57

Must begin with 60

Must contain – cannot contain 63

Case sensitive data validation 69

Weekdays or weekends validation 72

Date in the past validation 76

Allow a date in the next 30 days 79

Allow a date in a date range 82

Only numbers are accepted in a range 85

Allow only text in a data validation range 88

Prevent extra spaces in a data validation range 91

Conditional postal code in a data validation range 94

Cell dependency with data validation 98

Chapter Three – List Data Validation 101

Creating a basic drop list 101

Create multiple drop lists 107

Indenting items in a drop list 112

Chapter Four – Advanced Drop-Down List 115

Create a searchable drop list 115

Creating a diminishing drop list 136

Creating dependent drop list 145

Randomizing items in a drop list with every use 174

Data validation with wild cards 183

Conditional formatting on/off with a drop list 187

Chapter Five – Data Validation with Useful Codes 192

Resetting values for dependent drop lists 192

Prevent overwriting your drop lists 196

Select multiple options from a drop list 200

Improve legibility of items in a drop list 204

Switching macros with a drop list 207

Changing worksheet name with a drop list 214

Chapter Six – Power Query and Data Validation 217

Change source data or switch criteria 217

Extract specific folder contents with a drop list 259

Chapter Seven – Power Pivot and Data Validation 274

Data validation linked to data model (CUBEVALUE) 274

Chapter Eight – Data Validation Tips and Tricks 299

Locating data validation in the worksheet. 299

Copying or overwriting data validation 303

Clearing and locking data validation 306

Shortcuts, limit, and similarity to conditional formatting 312

Avoid excess data validation 317

Chapter Nine- Data Validation with Rich Data Types 323

Geography data type with data validation 323

Chapter Ten – Data Validation switching Charts and Slicers 345

Switching Charts with a data validation drop list 345

Data validation controls pivot tables and slicers 370


You can now start an exciting learning experience by purchasing the book from Amazon, available in Kindle version or printed paperback.


Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Seraphinite AcceleratorOptimized by Seraphinite Accelerator
Turns on site high speed to be attractive for people and search engines.