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.
Introduction
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.
Description
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.
